Unexpected DateStyle

[DiverseInternet]

After update to v. 0.4.1 I can no longer run any queries containing dates, with the following error:

Started executing query at Line 1 Commands completed successfully Unhandled exception while executing query: unexpected DateStyle: Redwood, SHOW_TIME Total execution time: 00:00:00.046

[DaeunYim]

Can you provide your desktop platform and PG version please

[DiverseInternet]

Hi DaeunYim, thanks for your response.

I have Azure Data Studio installed on my development machine - Windows 11 and two staging servers, both Windows Server 2016 Datacenter.

The issue affects all three installations. As a workaround I have to prepend all SQL queries with

SET datestyle TO "ISO, DMY";

We use PostgreSQL version 11 hosted by Big Animal.

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

[DiverseInternet]

Thanks very much DaeunYim.

That worked.

The folders named _MEI<6digit number> took up 10Gb of disk space!

[DiverseInternet]

Or at least it worked on my Windows 11 installation.

However, it didn't work on my Windows Server 2019 Datacenter installation (tried twice).

[DaeunYim]

Thanks for the information. We will try to repro this issue on Windows Server 2019 Datacenter installation.

[DaeunYim]

Thanks very much DaeunYim.

That worked.

The folders named _MEI<6digit number> took up 10Gb of disk space!

The next release will include deactivation code to remove these temporary folders.

[nasc17]

Hello, issue has been addressed in our unstable release of PostgreSQL extension v0.4.3-insiders. We would appreciate your verification that it has been handled appropriately.

[DiverseInternet]

Thanks very much.

However, the assets under v0.4.3-insiders on https://github.com/microsoft/azuredatastudio-postgresql/releases are labelled v0.4.2.

When installed it calls itself v0.4.2 and reports a last updated date of 2023-07-06.

From where do I download v0.4.3-insiders?

[nasc17]

Thanks very much.

However, the assets under v0.4.3-insiders on https://github.com/microsoft/azuredatastudio-postgresql/releases are labelled v0.4.2.

When installed it calls itself v0.4.2 and reports a last updated date of 2023-07-06.

From where do I download v0.4.3-insiders?

Thank you, the naming has been updated https://github.com/microsoft/azuredatastudio-postgresql/releases/tag/v0.4.3-insiders

The date will be changed once extension is published to ADS marketplace, sorry for the confusion. Can view changelog on the release page provided as well on the pgtoolsservice insiders release: https://github.com/microsoft/pgtoolsservice/releases/tag/v1.8.1-insiders

[DiverseInternet]

Thanks.

OK, successfully installed, but unfortunately, it doesn't solve the DateStyle issue above.

[DaeunYim]

Hello @OnenessUK,

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.

[nasc17]

Latest stable release of PostgreSQL extension v0.5.0. is currently available.