Re: Prepared Statements vs. pgbouncer

On Mon, Oct 01, 2007 at 02:44:10PM -0400, Tom Lane wrote:
> Dave Cramer <pg@fastcrypt.com> writes:
> > Josh Berkus wrote:
> >> So where is it going to be easier to fix this ... pgBouncer, or pg-JDBC?
> > pgBouncer is broken so I'd fix it.
> It's an enormous mistake to imagine that prepared statements are the
> only issue.  What about GUC settings and temp tables, to mention a
> couple other bits of per-session state?

i think that calling it broken is "a bit" far fetched.

i dont know how familiar you are with pgbouncer, but the mode in which
paul ran pgbouncer is *purposedly* not working correctly with prepared
statement.s

basically - ppgbouncer supports 3 modes:
- session pooling
- transaction pooling
- statement pooling.

description of all of them is clear in manual:

------------------------------
  Session pooling::
    Most polite method.  When client connects, a server connection
    will be assigned to it for the whole duration it stays connected.
    When client disconnects, the server connection will be put back
    into pool.

  Transaction pooling::
    Server connection is assigned to client only during a transaction.
    When PgBouncer notices that transaction is over, the server
    will be put back into pool.

  Statement pooling::
    Most aggressive method.  The server connection will be put back into
    pool immidiately after a query completes.  Multi-statement
    transactions are disallowed in this mode as they would break.
------------------------------

so, pgbouncer is not broken. if you want to keep your connection between
transactions (which is perfectly sensible) - use session pooling.

both transaction pooling and statement pooling are modes which trade
some performance for missing features.

i wouldn't suggest anyone using statement pooling, but if i would use
it, then what right do i have to complain about bad transactions?!

best regards,

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)