Disabling prepared statements in gino

[ilanKeshet]

Hi all
We have an app using gino==1.0.1 with asyncpg==0.22.0

At the moment I'm trying to start using RDS proxy (which is basically a managed pgBouncer solution from aws) and I'm getting session-pinning due to prepared statements.

We don't use prepared statements ourselves, and I've already followed asyncpg instructions for working with pgBouncer and specified statement_cache_size=0 to disable automatic usage of prepared statements by asyncpg.
However session pinning still persists, I then came across this Gino issue from 2019 #565 saying Gino itself is using prepared statements internally.

RDS proxy logs indicating session pinning due to a prepared statement

2022-02-01T09:23:43.098Z [WARN] [proxyEndpoint=default] [clientConnection=4092203507] The client session was pinned to the database connection [dbConnection=2469225544] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: A parse message was detected.

Question:

• Does GIno 1.0.1 automatically self uses prepared statements?
• Is there any way to disable the automatic usage of prepared statements?

🙏

[Pentusha]

@ilanKeshet, please check this test case. I have not tried this method, but I think it might be helpful.

[ilanKeshet]

Thank you @Pentusha , the test case you mention specifies statement_cache_size=0, this affects asyncpg.

We use this parameter but unfortunately we still see that something is preparing statements. #565 makes me thing that this thing might be Gino itself

[wwwjfy]

can you try to disable bakery? https://github.com/python-gino/gino/blob/master/docs/how-to/bakery.rst#i-dont-want-the-prepared-statements

[ilanKeshet]

Thank you @wwwjfy, however bakery hasn't been added till version 1.1 while we are at 1.0.1

[fantix]

Does GIno 1.0.1 automatically self uses prepared statements?

Depends on how you define "prepared statements" - in Postgres protocol, there are two ways to execute a query, the Simple Query and the Extended Query. While the Simple Query is done with a single request message, the extended query is done with multiple messages including Parse, Bind, and Execute. Specically, the Parse message could contain an optional name. If the name is specified, it is equivalent to the "PREPARE" SQL, or else it's similar to Simple Query (but you still get an "anonymous" prepared statement). asyncpg always uses the Extended Query, and automatically assign names to prepared statements on regular queries (not the prepare() method) to speed up the query unless statement_cache_size=0 is set. So there's always a "prepared statement" in any PostgreSQL query, the difference is only that, some of them are named and some are anonymous.

GINO is not using the PREPARE SQL automatically for sure, but it uses asyncpg which then uses Extended Query. That means there's always a Parse message for each query. If you set statement_cache_size=0, the Parse message will result in an anonymous "prepared statement", but it's no different than the Simple Query, just more messages allowing fine controls.

AWS here is complaining about receiving a Parse message. My guess is that AWS is not checking if the name in the Parse message is empty or not, and directly pin the session regardlessly. Theoretically you can reuse the connection if it is only a Parse without name, because another anonymous Parse or even Simple Query would overwrite the prepared statement. So this is likely a bug in AWS, but I didn't do more research on that, they might have their reasons to pin sessions for Extended Queries.

Is there any way to disable the automatic usage of prepared statements?

It's not possible for GINO not to send the Parse message, because it is a fundamental design in asyncpg to use the Extended Query to make things fast, and sometimes necessary.

More about Postgres protocols: https://segmentfault.com/a/1190000017136059

[ilanKeshet]

Thank you very much @fantix 🙏
This explanation definitely shades some light on our mystery