Cannot open database list (Error: Failed to expand node)

[bwong365]

Using PostgreSQL 15.0 MacOS Monterey 12.6 Azure Data Studio 1.39.1

Unable to open the list of databases (silently fails on left-click). Error pops up when right clicking the database list and hitting Refresh.

[jonashilmersson]

I also get this for Postgres 15, but not for 14, with ADS 1.39.1.

When expanding the databases node, the following command is run:

SELECT db.oid as oid, db.datname as name, ta.spcname as spcname, db.datallowconn, db.datlastsysoid, has_database_privilege(db.oid, 'CREATE') as cancreate, datdba as owner, db.datistemplate , has_database_privilege(db.datname, 'connect') as canconnect, datistemplate as is_system FROM pg_database db LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace = ta.oid

The error given is that db.datlastsysoid isn't a valid column anymore.

[ScriptBytes]

I am also having this issue. Postgres 15 Azure Data Studio v1.40.0 PostgreSQL Extension v0.2.7

[wehnertb]

This fixed the error for me. https://github.com/microsoft/azuredatastudio-postgresql/issues/334

[ScaryDBA]

I'm hitting this error. Also, the System Databases folder hits the same issue. PostgreSQL 15 ADS 1.40.1 PostgreSQL Extension 0.2.7

Using the fix from above did work though.

[NarishSingh]

Facing the same issue. I can connect and run queries but this error occurs for Databases and System Databases

PostgreSQL 15 ADS 1.40.1 PostgreSQL Extension 0.2.7 Windows 10

The fix cited earlier on #334 did not work for me

[jammerware]

Occurs when I try to expand databases on a local server. The fix from #334 got me part of the way there, but, possibly due to server configuration (I'm new to Postgres), I had to put double quotes around column aliases in nodes.sql. Final code here:

{#
 # pgAdmin 4 - PostgreSQL Tools
 #
 # Copyright (C) 2013 - 2017, The pgAdmin Development Team
 # This software is released under the PostgreSQL Licence
 #}
SELECT
    db.oid as "oid", 
    db.datname AS "name", 
    ta.spcname as "spcname", 
    db.datallowconn as "datallowconn",
    0 as "datlastsysoid",
    has_database_privilege(db.oid, 'CREATE') as "cancreate", 
    datdba as "owner", 
    db.datistemplate as "datistemplate", 
    has_database_privilege(db.datname, 'connect') as "canconnect",
    datistemplate as "is_system"
FROM
    pg_database db
LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace = ta.oid
{% if did %}
WHERE db.oid = {{ did|qtLiteral }}::OID
{% elif last_system_oid %}
WHERE db.oid > {{ last_system_oid }}::OID
{% endif %}

ORDER BY datname;

[solmazeradat]

Having the same issue with the same setup as @NarishSingh. Any Ideas on how to fix this? thanks

[jammerware]

@solmazeradat Did you see my comment above? I had to quote the column aliases, which I think is some config thing specific to certain installations of Postgres. Give it a try, maybe?

[LanceTrahan]

For anyone that may not be aware, if you're on a Mac, the path to the nodes.sql that @jammerware supplied a correction to can be found at:

/.azuredatastudio/extensions/microsoft.azuredatastudio-postgresql-0.2.7/out/ossdbtoolsservice/OSX/v1.5.0/pgsqltoolsservice/lib/pgsmo/objects/database/templates/+default/nodes.sql

[Ninja95-cyber]

Occurs when I try to expand databases on a local server. The fix from #334 got me part of the way there, but, possibly due to server configuration (I'm new to Postgres), I had to put double quotes around column aliases in nodes.sql. Final code here:

{#
 # pgAdmin 4 - PostgreSQL Tools
 #
 # Copyright (C) 2013 - 2017, The pgAdmin Development Team
 # This software is released under the PostgreSQL Licence
 #}
SELECT
    db.oid as "oid", 
    db.datname AS "name", 
    ta.spcname as "spcname", 
    db.datallowconn as "datallowconn",
    0 as "datlastsysoid",
    has_database_privilege(db.oid, 'CREATE') as "cancreate", 
    datdba as "owner", 
    db.datistemplate as "datistemplate", 
    has_database_privilege(db.datname, 'connect') as "canconnect",
    datistemplate as "is_system"
FROM
    pg_database db
LEFT OUTER JOIN pg_tablespace ta ON db.dattablespace = ta.oid
{% if did %}
WHERE db.oid = {{ did|qtLiteral }}::OID
{% elif last_system_oid %}
WHERE db.oid > {{ last_system_oid }}::OID
{% endif %}

ORDER BY datname;

Thanks this worked

[RodrigoNunes2004]

Great, somehow I figure out the issue Thanks!

[potatoqualitee]

This bug's workaround is inconvenient and the fix appears to be straightforward. Would love to have this fixed in an upcoming update. This comment fixed my issue but I pretty much can't use ADS until I modify that file.

[potatoqualitee]

It's also happening when I try to expand Indexes

[solmazeradat]

Couldn't get the workaround to work so used pgAdmin for the time being.

[DaeunYim]

Tracking from https://github.com/microsoft/azuredatastudio-postgresql/issues/357, closing the issue