On 07.06.2023 10:48 PM, Dave Cramer wrote:
Greetings,
At pgcon last week I was speaking to some people about the problem we
have with connection pools and named prepared statements.
For context pgjdbc (and others) use un-named statements and then
switch to named statements after using the statement N (default 5)
times. In session mode this is not a problem. When the connection is
closed by the application the pools generally issue "DISCARD ALL" and
close all prepared statements. The next time the connection is opened
the statement is prepared and all works as it should.
However one of the more interesting use cases for pgbouncer is to use
"TRANSACTION MODE" to manage idle sessions. In transaction mode the
connection is returned to the pool after each transaction. There are
usage patterns in large applications where clients have client pools
and subsequently have large numbers of connections open. Sometimes in
the thousands, unfortunately many of these are idle connections. Using
transaction mode reduces the number of real connections to the
database in many cases by orders of magnitude.
Unfortunately this is incompatible with named prepared statements.
From the client's point of view they have one session and named
prepared statements are session objects. From one transaction to the
next the physical connection can change along with the attached
prepared statements.
The idea that was discussed is when we prepare the statement we cache
it in a statement cache and return a queryid much like the queryid
used in pg_stat_statements. Instead of executing the statement name we
would execute the queryid.
If the queryid did not exist, attempting to execute it would cause an
error and cause the running transaction to fail. Retrieving the
statement from the query cache would have to happen before the attempt
to execute it and return an error to the client subsequently the
client could re-prepare the statement and execute. This would have to
happen in such a way as to not cause the transaction to fail.
The one other idea that was proposed was to cache the statements in
the client. However this does nothing to address the issue of managing
idle connections.
Regards,
Dave Cramer
There is a PR with support of prepared statement support to pgbouncer:
https://github.com/pgbouncer/pgbouncer/pull/845
any feedback, reviews and suggestions are welcome.