Re: Prepared Statements vs. pgbouncer

Paul Lindner wrote:

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...) 

It does use cursors if you set the fetch size appropriately. Prepared Statements are not the same as cursors.

One is a reusable statement that you can just change the parameters without reparsing the statement.
Cursors are the result of the statement being executed.

* 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.