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 <knizh...@garret.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. >