Re: Prepared Statements vs. pgbouncer

[Oliver Jowett <oliver(at)opencloud(dot)com>]

Paul Lindner wrote:

> 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.

The driver uses server-side statements for frequently-used internal 
utility commands such as BEGIN too, and the lifetime of those statements 
is essentially "the whole connection", not a particular transaction.

> 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.

We only rely on GC for statement deallocation if you don't explicitly 
close your Statements. When you explicitly close them the server-side 
statement is immediately enqueued on the reference queue, and will be 
deallocated automatically when the next query is executed on the same 
connection. If you just discard the statement, the enqueue is driven by GC.

There's no real reason for the driver to aggressively deallocate 
statements merely because they were created so a portal could be used. 
That sort of query is just as likely to be reused as any other.

> > 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.

And now you have two problems ;-)

> 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.

That's going to break things even further since now the driver will have 
statements that it thinks the server has prepared that will fail when 
used because pgbouncer has decided it should go and deallocate them all!

-O