Unhandled exception while executing query with PostgreSQL v0.4.1

[jagdishrajan]

Something has changed with extension PostgreSQL v0.4.1 where every query executed gives errors such as: Unhandled exception while executing query: 'int' object has no attribute 'encode' Example query: select count(*) from t1

The 'int' datatype changes based on what's queried.

This was all working fine prior to v.0.4.1 and there appears no way to revert back to a previous version.

[DaeunYim]

Can you provide your desktop platform and PG version please

You can download the previous version here.

[tlunsfordCXP]

I'm seeing the identical error.

Windows 11 22H2 Azure Database for PostgreSQL flexible server PostgreSQL version 13.10

[tlunsfordCXP]

@DaeunYim Going back to the previous version did work, thank you.

Guidance on installing a .vsix file in vscode:

https://stackoverflow.com/a/38866913/284598

[jagdishrajan]

Can you provide your desktop platform and PG version please

You can download the previous version here.

Windows 10 Pro (2009) 10.0.19045

[DaeunYim]

Hi, although we haven't been able to reproduce this issue on our end, we suspect it might be related to the psycopg version upgrade we introduced in version 0.4. To ensure compatibility with the latest version, could you please try the following steps?

1. Navigate to C:\Users\.azuredatastudio\extensions and clear out any existing PostgreSQL extensions. (Don't worry about losing your connection info, they will appear once you re-install the extension)

2. Open your file explorer, go to the %temp% location, and remove any folders named _MEI<6digit number> if they exist.

Once you've completed these steps, please try downloading and installing the new version of the PostgreSQL extension again.

Please let us know if this resolves the issue or if you need further assistance.

[nasc17]

Hello, we would appreciate your verification if this issue is still persistent in latest unstable release of PostgreSQL extension v0.4.3-insiders.

[nasc17]

Latest stable release of PostgreSQL extension v0.5.0 is out. Please reopen if issue is still present, thank you.

[tlunsfordCXP]

Same issue in v0.5.0. Please reopen the issue. Thank you.

[nasc17]

Bug has been addressed in our unstable release of PostgreSQL extension v0.5.1-insiders. We would appreciate your verification that it has been handled appropriately.

[tlunsfordCXP]

New error when running queries:

Unhandled exception while executing query: unknown encoding:

No encoding is shown so I suspect that it's an empty or null string. Would be helpful to wrap such output in a way that makes empty, null, and leading/trailing spaces obvious.

[tlunsfordCXP]

Oh, one more thing... Expanding the databases folder under the server name just has a couple of empty accordian arrows followed by some blank space.

[DaeunYim]

@tlunsfordCXP Can you identify what that value and data type is? We tried to cover all data types that are only supported in PG (not python). It's hard for us to capture the root cause without more details.

[tlunsfordCXP]

@DaeunYim here's a simple repro:

[DaeunYim]

@tlunsfordCXP we were not able to reproduce this issue with the same query. Can you provide more details?

• ADS version
• Full exception can be captured here, Windows: C:\Users\\AppData\Roaming\sqlops\PGSQL

[it-is-nidah]

I installed v0.5.1 but I still get the following error "Couldn't start client pgSQLToolsService" alongside the attached error on the terminal. I think i will resort to installing earlier stable versions as v0.5.0 was giving me the common "Unhandled exception while executing query: 'int' object has no attribute 'encode' " .

MAC M1 PG v0.5.1

Bug has been addressed in our unstable release of PostgreSQL extension v0.5.1-insiders. We would appreciate your verification that it has been handled appropriately.

[nasc17]

[dbnate]

I am still encountering this in 0.6.0.

Version: 1.47.0 (system setup) Commit: c7c2b2f21505562d21972d4c135119d00806db4f Date: 2023-11-07T17:17:45.669Z VS Code: 1.82.0 Electron: 25.8.4 Chromium: 114.0.5735.289 Node.js: 18.15.0 V8: 11.4.183.29-electron.0 OS: Windows_NT x64 10.0.22631

Full Exception: ossdbtoolsservice_issue426.log

Supporting Screenshots:

[nasc17]

@dbnate we haven't been able to reproduce this issue on our end following the example you gave. However, an error in your logs make me lean to the previous extension has not been fully cleaned from your sytem. To ensure compatibility with the latest version, could you please try the following steps?

Navigate to C:\Users.azuredatastudio\extensions and clear out any existing PostgreSQL extensions. (Don't worry about losing your connection info, they will appear once you re-install the extension)

Open your file explorer, go to the %temp% location, and remove any folders named _MEI<6digit number> if they exist.

Once you've completed these steps, please try downloading and installing the new version of the PostgreSQL extension again.

Please let us know if this resolves the issue or if you need further assistance. Also which PG database and version you are using?

[dbnate]

Hi @nasc17 still no joy unfortunately.

Extensions: Deleted the microsoft.azuredatastudio-postgresql-0.6.0 extension directory (was the only one) and also removed references to it from the extensions.json file. _MEI Directory: Didn't exist. Reinstalled: Performed reinstall of microsoft.azuredatastudio-postgresql-0.6.0.

Same issue, new log attached: ossdbtoolsservice_issue426_240105.log

Also which PG database and version you are using? Interesting question. So new developments are that we have three Azure Tenancies. Let's call them Tenancy A, B and C. Tenancy A has two PostgreSQL Databases on it, while Tenancies B and C both have one PostgreSQL Database on them each (so four db's in total). All PostgreSQL Databases are on Version 12.16.

What we have discovered is that this issue is happening only on the Databases in Tenancy A. The other Databases do not have this issue.

Additionally, what we have also discovered is this issue in the ADS Connections panel, also only occurring when connected to Tenancy A. Notice how we are connected but can't see anything under the Server apart from a Databases directory.

So, this issue could possibly be to do with the Tenancy as opposed to the Extension.

Workaround: I have been able to install the PostgreSQL ODBC Driver and connect / query the database via SSMS Linked Server.

[dbnate]

Hi @nasc17 we have found the solution to the issue we were experiencing.

Someone or something (we aren't the only admins for this Azure Tenancy) had change the client encoding against the Postgre database.

To resolve this we:

1. Logged into Azure and browsed into the Postgre database that was experiencing the issue.
2. Selected 'Server Parameters' from the menu on the left.
3. Filtered the Parameters but typing 'encoding' into the filter.
4. Upon finding the 'client_encoding' we noticed the value was set to 'SQL_ASCII'. We changed this to 'UTF8' Saved the changes and it resolved our issue.

[tlunsfordCXP]

I have the same issue, and my server is also set to have client_encoding of SQL_ASCII. I cannot change the parameter on the server side, so guidance on how to fix it client side would be appreciated.