Re: Weird prepared stmt behavior

(I'm not on -hackers, but saw this in the archives)

Alvaro Herrera wrote:
> On Fri, Apr 30, 2004 at 09:44:52PM -0400, Tom Lane wrote:
>> Alvaro Herrera <alvherre ( at ) dcc ( dot ) uchile ( dot ) cl> writes:
>> > Is this expected?  If so, why?  I'd expect the prepared stmt to be
>> > deallocated.
>>
>> prepare.c probably should have provisions for rolling back its state to
>> the start of a failed transaction ... but it doesn't.
>>
>> Before jumping into doing that, though, I'd want to have some
>> discussions about the implications for the V3 protocol's notion of
>> prepared statements.  The protocol spec does not say anything that
>> would suggest that prepared statements are lost on transaction rollback,
>> and offhand it seems like they shouldn't be because the protocol is
>> lower-level than transactions.
>
> Right now there is no distinction between a PREPARE prepared statement
> and a protocol-level one.  If we want to have the v3proto's statements
> behave different from PREPARE's, it's just a matter of adding a new
> field into the PreparedStatement.  I can do that and make them behave
> different if people think this is how it should be.
>
> I don't really have an opinion on whether protocol-level should behave
> different.  What do people think?

At least from the JDBC driver's point of view, having prepared
statements roll back is more work for the driver. Currently it uses
PREPARE/EXECUTE statements, but eventually it'll use the protocol-level
messages.

When the JDBC driver is given a query to execute and decides to use
server-side preparation, it sends a PREPARE (or eventually a Parse
message). Thereafter, when that same query is executed it will send an
EXECUTE (or Bind/Execute) instead of the full query. It does this by
setting some state in the driver-side object representing the query to
say "this query is prepared with name 'foo'".

If PREPARE can roll back, the driver must maintain a set of all
statements that were sucessfully PREPAREd in the current transaction,
and fix up the corresponding query object state whenever a transaction
rolls back.

 From that point of view, it's much simpler to keep PREPARE (or at least
Parse) as it currently is. I suspect the same argument applies to any
interface layer that uses PREPARE or Parse automatically.

-O