Message-ID: <gh-postgresql-interfaces-psqlodbc-143-c3543483007@github.com>
From: "jraiford1 (@jraiford1)" <noreply+jraiford1@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Mon, 17 Nov 2025 19:16:04 +0000
Subject: Re: [postgresql-interfaces/psqlodbc] issue #143: avoid session pinning with psqlODBC and RDS Proxy
In-Reply-To: <gh-postgresql-interfaces-psqlodbc-143@github.com>
References: <gh-postgresql-interfaces-psqlodbc-143@github.com>
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Login: jraiford1
X-GitHub-Comment-Id: 3543483007
X-GitHub-Comment-Type: issue_comment
X-GitHub-Edited-At: 2025-11-17T19:24:20Z
X-GitHub-Issue: 143
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/143#issuecomment-3543483007
Content-Type: text/plain; charset=utf-8

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.