Re: Prepared Statements vs. pgbouncer

On Mon, Oct 01, 2007 at 11:44:58AM -0700, Josh Berkus wrote:
> Heikki,
>
> >It's clearly pgBouncer here that's braking the protocol, so it's
> >pgBouncer that needs fixing. A workaround in JDBC driver would only help
> >JDBC apps. If you're having non-java applications in the mix, you'd have
> >to implement the workaround in *all* the drivers.
> >
> >BTW: I just stumbled across this Greg Sabine Mullane's blog post, which
> >describes a workaround for DBD::Pg perl module, so we're not alone:
> >http://people.planetpostgresql.org/greg/index.php?/archives/110-Using-DBDPg-with-pg_bouncer.html
> >
>
> Well, I think we have the right group of people here to think about what
> needs to be fixed. One of the Sun engineers can help with it, which I
> think is necessary since I doubt Marco has the time.

> So, given pgBouncer's purpose, what exactly should it be doing to handle
> prepared connections? That is, how can it support the v3 protocol
> without eliminating its tremendous scalability?

Here's a couple of ideas:

* Make more commands have modes that ignore errors. This allows for
idempotent behavior and won't result in transactions failing.

PREPARE IF NOT EXISTS foo() AS xxxx
PREPARE OR REPLACE foo() AS xxxx
DEALLLOCATE IF EXISTS foo;

Or fix the general case and allow for a way to catch errors without
invalidating the transaction.

* make it possible to request server-side state be transaction-scoped.
For example, prepared-statements, temporary tables, and the like
could be cleaned up at the end of a transaction automatically. Some
of this behavior exists already:

CREATE TEMP TABLE foo (integer a) on commit drop;

I think temporary views, temporary tables, and prepared statements
are the big things here.

* Have server-side support for 'pooled' connections. If you request a
pooled connection then certain features will be disabled to insure
that server-side state is contained properly.

This could be specified on connection, or perhaps it could be
specified as a user attribute...

For example, if this was in place no session state modifications
could occur outside a transaction.

* Have some way of dumping and restoring transient state. This would
allow pgbouncer to preserve client state without having to intercept
and track all of the various temporary information. Something
simple like this would suffice.

SHOW CLIENTSTATE;

clientstate
--------------------
aa$*afjhak1324834....

SET CLIENTSTATE="aa$*afjhak1324834...."

.. or ..

Have the database itself track client state separate from backend
processes.

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