Обсуждение: 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

On Sun, May 02, 2004 at 10:08:50AM +1200, Oliver Jowett wrote:
> 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.

 Exactly.

 Tom, will work these two scenarios:

1/ I have web application that  uses persistent connetions to PostgreSQL
   backend. After the  connection opening  the application  prepares all
   queries and the  rest of the application code  uses EXECUTE statement
   only. It  means the  EXECUTE  statemens are  used  in next  arbitrary
   transactions.

2/ The  other way  which my  application  uses is  "prepare query  first
   time  when some  code needs  it" --  and it's  independend on  actual
   transaction  of  course. I  use  this  way  now,  beacuse  it's  more
   effective for me than prepare all queries after the connection startup.

 If I good understand your idea the case 1/ will work, but case 2/ not.

 I have no care about

        BEGIN;
        CREATE TABLE xxx (id serial);
        PREPARE q AS SELECT * FROM xxx;
        ABORT;

        EXECUTE q;
        ERROR:  relation with OID 38242 does not exist

 because I can detect it by  error message and it's too academic problem
 for me.  I don't change DB schema in stable and production server and I
 think ALTER/DROP/CREATE is  nothing often in running  and good designed
 databases.

    Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/