AWS RDS Proxy (Postgresql) is pinning requests for undetermined reasons

[ElementalWarrior]

Hello,

We are using RDS Proxy to manage connection pooling and load balancing for db server instances.

One benefit of using rds proxy is that it will split transactions to different db connections. I think in practice it means that if our application creates 10 connections, but not all are actively writing, we would need < 10 connections between the RDS proxy and RDS Postgres.

However we are seeing that RDS proxy is pinning connections from our application to specific RDS proxy connections. They have a list of guidelines to avoid in order to prevent having "stateful" connections: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html#rds-proxy-pinning.postgres

We are not using any of the queries that they list in that article. I have turned on sqlalchemy query logging, there are no SET statements, or any of the other ones listed in that article.

I am using psycopg2, and I have also tried configuring the connection to use the NullPool, and it still is presenting the same issue.

2026-03-03T16:02:07.674Z [WARN] [proxyEndpoint=default] [clientConnection=2090405307] The client session was pinned to the database connection [dbConnection=2840725991] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "select users.id as users_id, ... from users where users.id = $1".

I have removed some columns, but it is a relatively simple select statement it is complaining about.

Is there anything missing from logging that would make it difficult to see session state changes?

[ElementalWarrior]

One last thing to note, is that the query it is complaining about, is after a commit, and the ORM model for user is refreshed in a new transaction.

[zzzeek]

One benefit of using rds proxy is that it will split transactions to different db connections. I think in practice it means that if our application creates 10 connections, but not all are actively writing, we would need < 10 connections between the RDS proxy and RDS Postgres.

I assume this means, if you run three transactions on a python-side connection, it may use three connections on the server side. This is what PGBouncer also does, though this mode causes a lot of problems with prepared statements.

note that all the PG drivers other than psycopg2 use prepared statements, and that psycopg2 itself is legacy (it's been superseded by psycopg, which uses prepared statements). so if this proxy layer cannot handle PREPARE at all, you are not in a good situation for the future.

I am using psycopg2, and I have also tried configuring the connection to use the NullPool,

right so you should have very little going on that would get in the way of such proxies. there is one SET we have and we use it to set the transaction isolation level, but only if you are specifying a transaction isolation level other than the default (this statement is not logged client side, however). we also use nextval() for sequences in many forms of INSERT statements, which you'd see in the logs.

psycopg2 itself might be emitting other kinds of SET so you might want to try experimenting with a plain psycopg2 connection.

[ElementalWarrior]

I did not see any set statements, or anything that resembles session setting changes.

It basically receives:

COMMIT
BEGIN
SELECT users.id ...

I presume SET queries would show up if they were present.

[CaselIT]

If you don't see set-like statements, your best option is likely to look at the rds proxy docs and/or amazon support.

[ElementalWarrior]

I've re-read the aws rds proxy docs, and have engaged aws support through this process as well.

I don't really have a resolution unfortunately. If aws support amounts to something I will update the thread.

Thanks for the support folks, its very appreciated.

[ElementalWarrior]

Alright. So I found something incredibly peculiar, and I'm guessing it doesn't really relate to sqlalchemy, but at least this will show up in search if someone else encounters this same issue with RDS Proxy.

If I use a column alias on any field ending in _reset, rds proxy will pin the connection. A reproducible example:

session.execute(text("""select foobar_reset as something from (SELECT 1 as foobar_reset ) inn"""))

Will lead to:

2026-03-04T21:19:09.925Z [WARN] [proxyEndpoint=default] [clientConnection=1749753541] The client session was pinned to the database connection [dbConnection=3296231024] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "select foobar_reset as something from ( select $1 as foobar_reset ) inn".

I am guessing they have some arbitrary query filtering to pin requests. And its overly broad.

[CaselIT]

seems quite sloppy on their part...

[ElementalWarrior]

Just to follow up here. The final response from AWS was:

The root cause was a defect in RDS Proxy's SQL query processor that incorrectly detected "reset" within column names as a RESET statement, causing unnecessary connection pinning on your queries.

Our team has identified this issue and we are working on a permanent fix for this issue, however, please be informed that it would not be possible for us to provide you with an ETA on this fix. Changes like these have to go through the software development life cycle which includes development and rigorous testing before it can be released to the customers to ensure the best customer experience. 

To mitigate this issue in the meantime, you can modify your SQL queries by enclosing column names in double quotes. For example:
table.some_reset AS table_some_reset
------->
"'table"."some_reset" AS "table_some_reset"

If someone else encounters this before aws fixes the bug. We mitigated it by setting quote=True on our orm model

some_reset = Column(..., quote=True)

[CaselIT]

thanks for following up