Re: Prepared Statements vs. pgbouncer

On Sat, Sep 29, 2007 at 01:05:54PM +1200, Oliver Jowett wrote:
> Paul Lindner wrote:
> >the assumption of a singular
> >client->server backend is causing this problem.
>
> Well, yes, that's fairly fundamental to the client-server protocol. It
> sounds like pgbouncer is breaking the protocol.

That's a given. Given the messages I've seen in the archives this
affects pgpool as well. The advice offered was to downgrade jdbcv2.
This, of course only works because the v2 code does not use
server-side prepared statements.

> You could perhaps do transparent statement/portal renaming in pgbouncer
> but that seems to be only scratching the surface of the problem.
> Consider your case here:
>
> > * Client A creates statement S_1, fetches results.
> > When finished the statement is put in a cleanup queue.
> > * time passes
> > * pgbouncer notices that client A is idle and reassigns backend to
> > client B
>
> Ok, now client A wakes up and wants to reuse S_1. What happens? The
> backend that knows about S_1 is currently in use by B.

We're currently using Transaction pooling with pgbouncer. With
prepareThreshhold set to 0, the only cases where server-side
statements are used are with cursors. These occur inside transactions
on our end.

It seems that the driver should immediately deallocate the server-side
prepared statements it creates for cursors when it finishs fetching
data for that cursor. This would be much better than waiting for the
garbage collector to issue the deallocation.

> Changing the JDBC driver to handle a "server" that doesn't follow the
> documented server protocol seems a bit backwards. I think you're going
> to have to teach pgbouncer a whole lot more about statements and portals
> to get this one working.

Well.. I only use pgBouncer to solve my particular issues.

Digging a little deeper I see that Postgres 8.3 will have DEALLOCATE
ALL and DISCARD ALL commands that can be used in pgbouncers health check.

Still, it would be nice if the end-user could have some control over
how the JDBC driver uses prepared statements other than
prepareThreshhold

Thanks!

--
Paul Lindner ||||| | | | | | | | | |
lindner(at)inuus(dot)com