Re: Named Prepared statement problems and possible solutions

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.