postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
13+ messages / 3 participants
[nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-17 17:11 "neetaghadge (@neetaghadge)" <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: neetaghadge (@neetaghadge) @ 2025-11-17 17:11 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-17 19:16 ` "jraiford1 (@jraiford1)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: jraiford1 (@jraiford1) @ 2025-11-17 19:16 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-18 10:35 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-18 10:35 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
fixed with #144
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-18 20:09 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-18 20:09 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
@net-nsg can you test these https://github.com/postgresql-interfaces/psqlodbc/actions/runs/19474647118/ and let me know if it works?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-19 06:55 ` "neetaghadge (@neetaghadge)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: neetaghadge (@neetaghadge) @ 2025-11-19 06:55 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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);_
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-19 16:11 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-19 16:11 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
@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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-20 05:47 ` "neetaghadge (@neetaghadge)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: neetaghadge (@neetaghadge) @ 2025-11-20 05:47 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-20 09:29 ` "jraiford1 (@jraiford1)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: jraiford1 (@jraiford1) @ 2025-11-20 09:29 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
What about querying the current values and only set them if they aren't already what the driver needs?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-20 19:32 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-20 19:32 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Ok, that would work. I can make that change.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-21 10:10 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-21 10:10 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
@jraiford1 I just pushed a change that does what you suggested, good idea
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-22 04:32 ` "neetaghadge (@neetaghadge)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: neetaghadge (@neetaghadge) @ 2025-11-22 04:32 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-25 09:45 ` "neetaghadge (@neetaghadge)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: neetaghadge (@neetaghadge) @ 2025-11-25 09:45 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Can you please submit this fix?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
@ 2025-11-25 13:56 ` "davecramer (@davecramer)" <[email protected]>
11 siblings, 0 replies; 13+ messages in thread
From: davecramer (@davecramer) @ 2025-11-25 13:56 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
> Can you please submit this fix?
huh ? It's already done
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2025-11-25 13:56 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-17 17:11 [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy "neetaghadge (@neetaghadge)" <[email protected]>
2025-11-17 19:16 ` "jraiford1 (@jraiford1)" <[email protected]>
2025-11-18 10:35 ` "davecramer (@davecramer)" <[email protected]>
2025-11-18 20:09 ` "davecramer (@davecramer)" <[email protected]>
2025-11-19 06:55 ` "neetaghadge (@neetaghadge)" <[email protected]>
2025-11-19 16:11 ` "davecramer (@davecramer)" <[email protected]>
2025-11-20 05:47 ` "neetaghadge (@neetaghadge)" <[email protected]>
2025-11-20 09:29 ` "jraiford1 (@jraiford1)" <[email protected]>
2025-11-20 19:32 ` "davecramer (@davecramer)" <[email protected]>
2025-11-21 10:10 ` "davecramer (@davecramer)" <[email protected]>
2025-11-22 04:32 ` "neetaghadge (@neetaghadge)" <[email protected]>
2025-11-25 09:45 ` "neetaghadge (@neetaghadge)" <[email protected]>
2025-11-25 13:56 ` "davecramer (@davecramer)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox