Named Prepared statement problems and possible solutions

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