Re: Prepared Statements vs. pgbouncer

[Paul Lindner <lindner(at)inuus(dot)com>]

On Sat, Sep 29, 2007 at 10:36:33AM +0100, Gregory Stark wrote:
> 
> "Paul Lindner" <lindner(at)inuus(dot)com> writes:
> 
> >   * pgbouncer notices that client A is idle and reassigns backend to 
> >     client B
> 
> What do you mean by "notices"? 

Okay, I wasn't being clear.  In pgbouncer at the END of a commit the
backend will be put into the idle pool.  Please read the following:

https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer

> Prepared statements are only one form of state which can persist beyond a
> transaction end. I don't think you can reassign connections unless you get
> some sort of explicit notice that the client is done with any state it has set
> up. Either the driver supports noticing such a state because there are no
> active references to its handle or the client issues a statement like RESET
> ALL or something equivalent.

Okay.

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

For this specific case a long term fix might involve
transaction-scoped prepared statements.  Of course that would require
adding this feature on many levels.

Should middleware products track all prepared statements and re-send
those to each backend?  What happens when you have collisions between
names?

Should auto-generated prepared statements use a common hashing method
to insure that we don't recreate the same prepared statement over and
over?

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

Attachment: pgpogdIgZfCRx.pgp
Description: PGP signature