Subject: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy

From: "neetaghadge (@neetaghadge)" <[email-masked]>
Date: Mon, 17 Nov 2025 17:11:16 +0000

We are currently using PostgreSQL 16.5 with the ODBC driver (psqlodbcw.so version 17.00.0006) in an environment that relies on Amazon RDS Proxy.
We have observed that the driver automatically issues the following SQL statements at connection startup:
SET DateStyle = 'ISO';
SET extra_float_digits = 2;
SHOW transaction_isolation;
 
While harmless in most cases, these queries cause AWS RDS Proxy to pin sessions, which reduces pooling efficiency. 

Could you please confirm whether this behavior can be modified or controlled through configuration?

---

From: "jraiford1 (@jraiford1)" <[email-masked]>
Date: Mon, 17 Nov 2025 19:16:04 +0000

Here is the RDS documentation describing why this is happening:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html

(look for the PostgreSQL section at the bottom)

Edit - In case they change or remove the page...

Conditions that cause pinning for RDS for PostgreSQL

For PostgreSQL, the following interactions also cause pinning:

- Using SET commands.
- Using PREPARE, DISCARD, DEALLOCATE, or EXECUTE commands to manage prepared statements.
- Creating temporary sequences, tables, or views.
- Declaring cursors.
- Discarding the session state.
- Listening on a notification channel.
- Loading a library module such as auto_explain.
- Manipulating sequences using functions such as nextval and setval.
- Interacting with locks using functions such as pg_advisory_lock and pg_try_advisory_lock.

Note
RDS Proxy does not pin on transaction level advisory locks, specifically pg_advisory_xact_lock, pg_advisory_xact_lock_shared, pg_try_advisory_xact_lock, and pg_try_advisory_xact_lock_shared.

- Setting a parameter, or resetting a parameter to its default. Specifically, using SET and set_config commands to assign default values to session variables.
- Calling stored procedures and stored functions doesn't cause pinning. RDS Proxy doesn't detect any session state changes resulting from such calls. Make sure that your application doesn't change session state inside stored routines if you rely on that session state to persist across transactions. For example, RDS Proxy isn't currently compatible with a stored procedure that creates a temporary table that persists across all transactions.
- Discarding session state. If you use connection pooling libraries with DISCARD ALL query configured as a reset query, RDS Proxy pins your client connection on release. This reduces the proxy's multiplexing efficiency and might lead to unexpected results because the DISCARD ALL command can interfere with session state management.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 18 Nov 2025 10:35:03 +0000

fixed with #144

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 18 Nov 2025 20:09:54 +0000

@net-nsg can you test these https://github.com/postgresql-interfaces/psqlodbc/actions/runs/19474647118/ and let me know if it works?

---

From: "neetaghadge (@neetaghadge)" <[email-masked]>
Date: Wed, 19 Nov 2025 06:55:50 +0000

Hi @davecramer ,
With this fix, the RDS Proxy fails to connect to the database, returning: **FATAL: Feature not supported: RDS Proxy currently doesn’t support command-line options.**

After I removed the newly added code from **connection.c,** my application was able to connect to the RDS Proxy:
__/* Add DateStyle and extra_float_digits as startup parameters */
if (cnt < PROTOCOL3_OPTS_MAX - 2)
{
    opts[cnt] = "options";  vals[cnt++] = "-cextra_float_digits=2";
    opts[cnt] = "options";  vals[cnt++] = "-cDateStyle=ISO";
}_

For reference, when I tested locally by removing only the SET commands from **LIBPQ_CC_connect**() method, I noticed that sessions were no longer being pinned with this fix:
**Before:**
_res = CC_send_query(self, "SET DateStyle = 'ISO';SET extra_float_digits = 2;" ISOLATION_SHOW_QUERY, NULL, READ_ONLY_QUERY, NULL);_
**After:**
_res = CC_send_query(self, ISOLATION_SHOW_QUERY, NULL, READ_ONLY_QUERY, NULL);_

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 19 Nov 2025 16:11:21 +0000

@net-nsg 
Hmmm.... quite a quandry. 
I'm not sure that the driver would pass all the tests without setting the DateStyle if it is not ISO in the server.

---

From: "neetaghadge (@neetaghadge)" <[email-masked]>
Date: Thu, 20 Nov 2025 05:47:01 +0000

Enforcing SET DateStyle TO ISO; at the driver level would risk session pinning with RDS Proxy. 
Can we document ISO DateStyle as a prerequisite so users set it via parameter groups? That way we avoid pinning and still keep behavior predictable.

---

From: "jraiford1 (@jraiford1)" <[email-masked]>
Date: Thu, 20 Nov 2025 09:29:42 +0000

What about querying the current values and only set them if they aren't already what the driver needs?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 20 Nov 2025 19:32:10 +0000

Ok, that would work. I can make that change.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Fri, 21 Nov 2025 10:10:09 +0000

@jraiford1 I just pushed a change that does what you suggested, good idea

---

From: "neetaghadge (@neetaghadge)" <[email-masked]>
Date: Sat, 22 Nov 2025 04:32:53 +0000

Thank you, @davecramer and @jraiford1. 
The latest changes (verify the output of **SHOW DateStyle;** and execute **SET** only if it is not ISO) are working fine.
I no longer observe any session pinning after the new fix.

---

From: "neetaghadge (@neetaghadge)" <[email-masked]>
Date: Tue, 25 Nov 2025 09:45:18 +0000

Can you please submit this fix?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 25 Nov 2025 13:56:43 +0000

> Can you please submit this fix?

huh ? It's already done