CODEWITHSUNDEEP
postgresqlazure-data-studio
TimB
TimB

Reputation: 71

Azure Data Studio Failed to Expand Node

I just installed Azure Data Studio (ADS) and PostgreSQL extension. ADS connects to my Postgresql and I can do queries on my database. However, the dropdown 'Databases' tab does not expand. When I right click, to do Refresh, an error message shows up "Error: Failed to expand node". Would someone please help? Thank you so much in advance.

Upvotes: 4

Views: 4376

Answers (4)

gusknows
gusknows

Reputation: 130

I downloaded the SQL templates from Microsoft's pgtoolsservice in Github which fixed the errors in ADS when expanding the database dropdowns in "Servers" for a Postgres database.

Microsoft pgtoolsservice templates

I just copied all the SQL files to my AppData\Local template location for ADS and then restared ADS. My (Windows 11) location looked like:

[username]\AppData\Local\Temp\[random app string]\pgsmo\objects\database\templates\+default

This path was copied from the error message from ADS.

Upvotes: 1

Rachel Ambler
Rachel Ambler

Reputation: 1604

For folks on MacOS, here's the requisite file:

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

And for completions sake, the updated contents:

{#
 # 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,
    0 As 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
{% if did %}
WHERE db.oid = {{ did|qtLiteral }}::OID
{% elif last_system_oid %}
WHERE db.oid > {{ last_system_oid }}::OID
{% endif %}

ORDER BY datname;

Upvotes: 7

Brett Grantham
Brett Grantham

Reputation: 61

Followed Crocodilus's instruction to edit the nodes query replacing line db.datlastsysoid with 0 as datlastsysoid worked for me. One update is adding a slash after the userprofile reference: %USERPROFILE%\.azuredatastudio\extensions\microsoft.azuredatastudio-postgresql-0.2.7\out\ossdbtoolsservice\Windows\v1.5.0\pgsqltoolsservice\lib\pgsmo\objects\database\templates\+default\nodes.sql

Upvotes: 6

Crocodilus
Crocodilus

Reputation: 51

Seems to be a known issue with ADS since pg_database.datlastsysoid field was removed in PostgreSQL 15. I am also waiting for a solution.

Azure Data Studio issue: https://github.com/microsoft/azuredatastudio-postgresql/issues/333

Rationale for removing datlastsysoid in version 15: https://www.postgresql.org/message-id/CA%2BTgmoa14%3DBRq0WEd0eevjEMn9EkghDB1FZEkBw7%2BUAb7tF49A%40mail.gmail.com

Upvotes: 5

Related Questions