Re: Prepared Statements vs. pgbouncer - Mailing list pgsql-jdbc

Your proposal below is interesting, but is a much larger scale problem
than I want to deal with.  It also requires that the middleware layer
to do deep packet inspection, which is suboptimal from a latency and
performance standpoint.

Anyway, I've been thinking about this for a little while and think the
following changes would not cripple the driver, but would provide
correct behavior in the face of a server that could potentially change
backends:

1) Use hashing to choose a prepared statement name

   If we take the hash of the prepared statement text and prefix with
   S_ we can be assured of using the same unique prepared statement
   name across all application servers.

   (And yes, I know that hashes are not perfect and collisions can
    occur.  Highly unlikely if we choose a good hash)

2) If we try to prepare a statement with an hashed name and it already
   exists then we ignore the error and continue.

3) If we receive an error while executing a prepared statement with an
   hashed name the driver will try to re-prepare the statement and
   re-execute the query.  If an error occurs after this retry step then
   error are surfaced to the caller.


If no-one has a more workable solution I'll probably go ahead and
implement the modifications I've listed above.

Of course I'd be happy to publish the patch and maintain the fork for
anyone else that might want to use middleware software with their Java
clients.


-- Paul

On Sun, Sep 30, 2007 at 11:50:10AM +1300, Oliver Jowett wrote:
> 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

--
Paul Lindner        ||||| | | | |  |  |  |   |   |
lindner@inuus.com