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@inuus.com