Re: Prepared Statements vs. pgbouncer

On Mon, Oct 01, 2007 at 12:49:18PM -0700, Josh Berkus wrote:
> Paul,
>
> > PREPARE IF NOT EXISTS foo() AS xxxx
> > PREPARE OR REPLACE foo() AS xxxx
> > DEALLLOCATE IF EXISTS foo;
>
> This has been a request of various app developers anyway. Mind you, they
> want it mostly to support sloppy programming, but it would make it easier
> to port applications from MySQL.

> > Or fix the general case and allow for a way to catch errors without
> > invalidating the transaction.
>
> heh, that would be a major PostgreSQL change.

People underestimate the importance of idempot operations when dealing
with many real world situations.

Another alternative might be

PREPARE foo() AS xxxxx ON COMMIT DEALLOCATE;

except I just realized that I'm recreating CURSORs :)

Why doesn't the Postgres JDBC driver use cursors for ResultSets
instead of creating prepared statements? Is this not supported in the
v3 protocol? (I'd answer this question myself, but no time to read
code at the moment...)

> > * 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:
>
> This would rather limit the utility of prepared plans.

> > * 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.
>
> I don't see that this would fix the issue with JDBC.

With pgbouncer you can use transaction scoping. In a given
transaction all commands are guaranteed to go to the same backend.

If you can match your server side state with a BEGIN/END block then
you can be assured that this state is preserved for the duration of
this mini-session.

JDBC could be modified to either:

* Only use server-side prepared statements inside transactions.
(Or use cursors instead)

> > * 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.
> > .. or ..
> > Have the database itself track client state separate from backend
> > processes.
>
> This approach seems incompatible with the goal of using pgBouncer in the
> first place, namely scalability.

Right, it's the difference between a router and switch.

In my case it wouldn't matter. I don't want to keep state on the
server, I want my app-clients to be as stateless as possible. The
driver is subverting this desire.

Right now the benefit from pgbouncer is much better than benefits from
prepared plans.

> Basically, I see two things we need to do. In the short term, we need some
> kind of fix for pgBouncer so it at least works with JDBC. In the long
> term, we should work on support for shared prepared plans.

Okay. I'm willing to lend a hand and work with anyone that's working
in large scale java Postgres environments.

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