Thread

1. Re: Weird prepared stmt behavior

   Oliver Jowett <oliver@opencloud.com> — 2004-05-01T22:08:50Z

   (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
   
   
   

2. Re: [HACKERS] Weird prepared stmt behavior

   Karel Zak <zakkr@zf.jcu.cz> — 2004-05-06T08:25:45Z

   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/