Re: Prepared Statements vs. pgbouncer

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.

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

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

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.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco