Re: Prepared Statements vs. pgbouncer

On Mon, Oct 01, 2007 at 02:44:10PM -0400, Tom Lane wrote:
> Dave Cramer <pg(at)fastcrypt(dot)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)