Certain tables are no longer queryable

[wehnertb]

Describe the bug So I was querying my database today (db: customers, table: "Accounts"). Everything is fine. Updated plugin to v0.4.2 and now I get a "None" response when trying to query the table. I can query every other table in DB, just this one will not return any results.

To Reproduce Steps to reproduce the behavior:

1. Update plugin to v0.4.2
2. Connect to Database (Azure PostgreSQL Single Server v11)
3. select "customers" database
4. select * from "Accounts" limit 10;

Expected behavior Expect to see first 10 results from query, instead I get:

9:22:56 AMStarted executing query at Line 1 None Total execution time: 00:00:00.030

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: Windows 11 Version 22H2 (OS Build 22621.1702)

ADS (please complete the following information):

• 1.44.1
  • v0.4.2
  • PostgreSQL version 11 (Azure PostgreSQL Single Server)

Additional context v0.3.1 did not have this problem, only going to versions v0.4.0 and higher cause issue.

[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?

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.

[nasc17]

Hello, please inform us if steps outlined by Daeun were able to resolve your issue. We would also appreciate your verification if issue is persistent in latest unstable release of PostgreSQL extension v0.4.3-insiders.

[jqing]

I have similar errors like this. suddenly the query return no result. following is what I have tried.

table users has 188 records

select * from users - no result select * from users limit 100 - no result select * from users order by id limit 100 - show result select * from users order by id limit 200 - no result select * from users order by id - no result

and finally I found out that one field in the table causing this. table users has a field call CreateTime with datatype timestamptz, if this field is not include in the query or been convert to a text, it works as expected.

which means select id from users - show result select id, "CreateTime" from users - no result select id, "CreateTime"::text from users - show reault

this only happens on my PC. my team number's works fine.

I will try Daeun's suggestion and report back.

[jqing]

tried Daeun's suggestion. no difference.

[nasc17]

tried Daeun's suggestion. no difference.

Thank you for taking the time to give us more detail about what is triggering this bug. Can you please inform us what platform, PG version, and extension version you are using?

We currently have an insiders release out now that you can test against as well to see if issue is still present with our newer updates: https://github.com/microsoft/azuredatastudio-postgresql/releases/tag/v0.4.3-insiders

[jqing]

Version: 1.45.0 (user setup) Commit: 8eff468100f02157f2219335f7a566d14451ca49 Date: 2023-07-24T18:22:12.106Z VS Code: 1.79.0 Electron: 22.3.14 Chromium: 108.0.5359.215 Node.js: 16.17.1 V8: 10.8.168.25-electron.0 OS: Windows_NT x64 10.0.19045

Name: PostgreSQL Id: Microsoft.azuredatastudio-postgresql Description: PostgreSQL extension for Azure Data Studio Version: 0.4.2 Publisher: Microsoft

PostgreSQL 11.16 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit

[jqing]

tried Daeun's suggestion. no difference.

Thank you for taking the time to give us more detail about what is triggering this bug. Can you please inform us what platform, PG version, and extension version you are using?

We currently have an insiders release out now that you can test against as well to see if issue is still present with our newer updates: https://github.com/microsoft/azuredatastudio-postgresql/releases/tag/v0.4.3-insiders

installed the insider version, no luck

Name: PostgreSQL Id: Microsoft.azuredatastudio-postgresql Description: PostgreSQL extension for Azure Data Studio Version: 0.4.3 Publisher: Microsoft

[DaeunYim]

Hello @jqing, I'm assuming you were using different datestyle than ISO format.

Our PostgreSQL extension relies on the psycopg Python library to connect to PostgreSQL servers. According to the official psycopg documentation, unless the connection is explicitly configured to accept a specific date format at the initialization stage, the library will default to working with data in the ISO format.

At this point in time, we believe it is in the best interest of the product to maintain this behavior. While we understand that this might pose some inconvenience, we are currently not prioritizing changes to this functionality.

Running this query will temporarily fix the issue. SET DATESTYLE = 'ISO, MDY';

[jqing]

Hello @jqing, I'm assuming you were using different datestyle than ISO format.

Our PostgreSQL extension relies on the psycopg Python library to connect to PostgreSQL servers. According to the official psycopg documentation, unless the connection is explicitly configured to accept a specific date format at the initialization stage, the library will default to working with data in the ISO format.

At this point in time, we believe it is in the best interest of the product to maintain this behavior. While we understand that this might pose some inconvenience, we are currently not prioritizing changes to this functionality.

Running this query will temporarily fix the issue. SET DATESTYLE = 'ISO, MDY';

is this the new change in 0.4.x? it didn't happen before. and it didn't explain why select * from users order by id limit 100 works

also we are not using some special datestyle.

tried SET DATESTYLE = 'ISO, MDY no luck. rolling back to 0.3.1

[jqing]

hope this can help. after look into the value in users table, I noticed that there are some record stored as 0001-01-01T00:00:00+00:00, that might be the reason.

[GennadNY]

Closing for now, need better error handling in case of this issue.

[wehnertb]

Hello, please inform us if steps outlined by Daeun were able to resolve your issue. We would also appreciate your verification if issue is persistent in latest unstable release of PostgreSQL extension v0.4.3-insiders.

Sorry it took me so long to reply. I tried this and I still am not able to query certain tables.

I'm not certain it's an issue with the timestamp field.

1. I have my timestamp fields defined at "timestamp without time zone".
2. Secondly, I have other tables in this DB that have the same fields defined (CreatedDate, UpdatedDate) and I can query those.
3. The dates stored in these columns, look like this: 2023-01-18T20:53:14.366021

I've tested v0.5 - it too fails to retrieve data.

Is there a way to specify the date format in the connection string so that it knows how it should connect? Not ideal to have to monkey around that low level, but needs must.

[DaeunYim]

@wehnertb we found out this could be possibly due to the extreme PG datetime values such as 'infinity' that can't be converted into Python datetime object. We are working on the fix for the next release.

[dferretti]

@DaeunYim it does look to be related to that - I get the same error if I just run

select 'infinity'::timestamptz