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