avoid session pinning with psqlODBC and RDS Proxy

[neetaghadge]

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?

[jraiford1]

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.

[davecramer]

fixed with #144

[davecramer]

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

[neetaghadge]

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);

[davecramer]

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

[neetaghadge]

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.

[jraiford1]

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

[davecramer]

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

[davecramer]

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

[neetaghadge]

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.

[neetaghadge]

Can you please submit this fix?

[davecramer]

Can you please submit this fix?

huh ? It's already done