Unable to get object source/DDL

[bcoulam-fmg]

Issue Type: Bug

This client is an Azure shop. They are on Azure Postgres due to Hasura which forced Postgres when they adopted it. I am using ADS to connect to Azure Postgres (v 11.X). I am using AAD to authenticate and am connected as the developer role instead of datareader, which means I should be able to view the DDL of non-system tables and views and indexes, and the source for stored functions.

I am able to view the DDL for tables and views. However, whenever I attempt to right-click and Script as Create on indexes and functions I get errors.

With indexes I get "Failed to expand node". Under the "Log (Window)" output I see [2023-01-09 11:38:41.207] [renderer1] [error] Failed to expand node: 'NodeCollection' object has no attribute 'refresh' [2023-01-09 11:39:06.964] [renderer1] [error] An unknown error occurred. Please consult the log for more details.

With functions I get "No script returned when scripting as Create". Under the "ossdbToolsService" output, I see [Error - 11:43:30 AM] Request scripting/script failed. Message: Object of type NoneType does not support script operation ScriptOperation.CREATE Code: 0 [object Object]

By the way, under the Output tab's source drop-down, there are two "ossdbToolsService" choices. One of them has output, the other does not.

I've asked around, and the managers and developers I've talked to all have the same problem. They have thrown up their hands in frustration and resort to querying the pg catalog and information_schema to browse database objects, instead of being able to do it visually.

Azure Data Studio version: azuredatastudio 1.40.2 (661384637db384fe5d4e6224069adbe708580b16, 2022-12-22T22:17:02.658Z) OS version: Windows_NT x64 10.0.19042 Restricted Mode: No Preview Features: Enabled

System Info

|Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz (8 x 2112)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: disabled_off
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
oop_rasterization: enabled
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
skia_renderer: enabled_on
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled| |Load (avg)|undefined| |Memory (System)|15.88GB (7.33GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|

Extensions (1)

Extension|Author (truncated)|Version ---|---|--- azuredatastudio-postgresql|Mic|0.2.7

[kburtram]

@bcoulam-fmg thanks for reporting this issue! The Postgres extension is tracking its bugs on a different repo so I'll transfer this there.

[bcoulam-fmg]

@bcoulam-fmg thanks for reporting this issue! The Postgres extension is tracking its bugs on a different repo so I'll transfer this there.

Thank you @kburtram ! When I click Report Issue in ADS, I'm brought here. If I find more issues with ADS on Azure Postgres, is there a name/address for the correct repo so I can bypass this step of moving it to the right one?

[nasc17]

Please update to latest stable release of PostgreSQL extension v0.4.2. We would appreciate your verification if this issue is still present.

[bcoulam-fmg]

Between 0.2.7 and 0.4.2 someone fixed Azure Postgres function reverse engineering. I can now click Script on Create for functions and it works. The following bug, and new bugs, remain. All were found within 5 minutes of testing the database object tree in the Servers pane, and the Script as Create capabilities: I shudder to think of the bugs I'd find if I were to spend a day with it.

1. Opening the folder for Columns under a table yields a column list with only column name and datatype name. There should be some visual indicator which are nullable and which are required. The columns that have a length specificiation, like varchar(N), or numeric(N,#), should show that length specified in this list. Ideally the columns that part of a primary key, or unique key, or foreign key, would also be visually set apart from the "regular" columns that aren't part of data integrity checks.
2. When I Script as Create on a table, it reverse engineers the table's structure and constraints well. But it does not reverse engineer the table's indexes.
3. When I attempt to Script as Create on any index under the Indexes folder of a table, I get an error message dialog with title "Scripting Failed" and the only text in the dialog window is 'Index' (verbatim with the single quotes).
4. If the index happens to be part of a unique key constraint, the only right-click option for the index is Refresh. Script as Create is not offered for unique key indexes.
5. And primary key indexes aren't even shown under the Indexes folder
6. Then I made the mistake of opening the Constraints folder under several tables. Constraints should show primary key, unique key, foreign key and check constraints for the table under which the Constraints folder is folder. Instead this version of the extension includes a mish-mash of constraints from all over the database (like valid_status, tag_not_empty, spatial_ref_sys_srid_check, yes_or_no_check...none of which belong to the tables I tested). Tried to attach image below.

[nasc17]

@bcoulam-fmg Thank you for such a thorough response and taking the time to leave this review. We will do our best to keep you updated as we continue to work on correcting the extension.

[samir-puranik]

Hi @bcoulam-fmg, thank you for taking the time to investigate these issues. We will be refiling some of these issues into separate issues so that we can keep better track of them. Through looking at these issues here is what we have decided:

1. This would be a useful feature to have but isn't necessary for the stability of the extension as of right now. Will refile, but label as enhancement.
2. Will be refiled as a separate issue, targeting for next release.
3. Should be fixed with v0.5.0 release!
4. Will be refiled as a separate issue, targeting for next release.
5. Will be refiled as a separate issue targeting for next release.
6. Will be refiled as a separate issue targeting for next release.

Will update this issue once all issues are refiled and link them here. I'll then close this issue.

[samir-puranik]

Closing this issue, the new separate issues have been refiled.