Re: Named Prepared statement problems and possible solutions

Hi Konstantin,

Yes, I ran into Euler at pgcon and he mentioned this. I intend to test it.
I'd still like to see my proposal in the server.

Dave Cramer

On Thu, 8 Jun 2023 at 02:15, Konstantin Knizhnik <knizhnik(at)garret(dot)ru> wrote:

>
>
> 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.
>