Re: Prepared Statements vs. pgbouncer

Paul Lindner wrote:

> How do we fix this? Short term? Long term?

Build a connection pooling thing that does what you want it to do and
implements the protocol *completely*.

For example the whole named statement problem goes away if the bit of
software doing the pooling keeps track of

(a) which statements were prepared under which names on the "client"
side connections (including query text & OIDs)
(b) which statements were prepared under which names on the "server" side
(c) relevant transaction and session state on both sides

Note that the names don't necessarily match up, the pool can merrily
assign its own names on the server side.

Then it doesn't matter at all what names the clients use, it's
irrelevant, they're still scoped within the connection they originated
from as the protocol expects. Internally the pool then maps them to a
new statement name on whichever real server connection it decides to
push queries to. If the statement hasn't been prepared on that
connection yet, that's fine, you just re-prepare it under a new name
from the data you have stored.

And you get a "shared" prepared statement cache per server connection
for free. You can also implement whatever logic you want for managing
this cache, garbage collection and timing out idle statements, etc, as
you see fit.

This is basically what I meant by fixing pgbouncer. No, it's not trivial
to do, but there's no technical reason why it can't be done, you'll just
need to throw development time at it.

You can also run this with a heterogenous client environment and not
have to worry about clients following some particular subset of the
protocol or cooperating over statement names and the like.

-O