psycopg2 - DateStyle option on Redshift Serverless

[r3manoj]

Please complete the following information:

• OS: Debian GNU/Linux v12
• Psycopg version: 2.9.3
• Python version: 3.8.18
• PostgreSQL version: -
• pip version≥ 23.0.1

Describe the bug
Please let us know:
The issue we're facing seems to be with connecting to an Amazon Redshift serverless instance using Psycopg2's simple connection pool. Psycopg2 is attempting to execute the SET datestyle TO 'ISO' query during connection, but despite setting the options with -c DateStyle=ISO, it's not being recognized for Redshift connections. I have also enabled PSYCOPG_DEBUG for debugging purpose. I have added logs below:

[816] psyco_connect: dsn = 'user=test password=test dbname=test host=test_host port=5432 options='-c datestyle=ISO,YMD'', async = 0
[816] connection_setup: init connection object at 0xffff63cc6b8, async 0, refcnt = 1
[816] con_connect: connecting in SYNC mode
[816] conn_connect: new PG connection at 0xffff647ceb6
[816] conn_connect: server standard_conforming_strings parameter: unavailable
[816] conn_connect: server requires E'' quotes: NO
[816] conn_connect: using protocol 3
[816] conn_connect: client encoding: UNICODE
[816] clear_encoding_name: UNICODE -> UNICODE
[816] conn_set_fast_codec: encoding=UNICODE
[816] conn_set_fast_codec: no fast codec
[816] conn_connect: DateStyle (null) _**This is NULL despite setting DateStyle option**_
[816] pq_set_guc_locked: setting datestyle to ISO
[816] pq_execute_command_locked: pgconn = 0xffff647ceb6, query = SET datestyle TO 'ISO'
[816] connection_setup: good connection object at 0xffff63cc6b8, refcnt = 1
[816] conn_close: PQfinish called
[816] connection_dealloc: deleted connection object at 0xffff63cc6b8, refcnt = 0

The same option works with postgresql but not with Redshift serverless.

Tried via psycopg2.connect() and psycopg2.pool.SimpleConnectionPool as well. Example code used:
psycopg2.pool.SimpleConnectionPool( min_connections, max_connections, database=db_name, user=user, password=password, host=host, port=port, options='-c DateStyle=ISO' )

Could this be a bug? Is this the expected behaviour? Apologies - if it is.

[dvarrazzo]

We cannot provide Redshift support for free. If you are interesting in professional support please get in touch privately.