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,
