Thread

1. RE: CURRENT OF cursor without OIDs

   Zeugswetter Andreas ADI SD <zeugswettera@spardat.at> — 2001-08-08T12:46:10Z

   >    There could be DELETE operations for the tuple
   >    from other backends also and the TID may disappear.
   >    Because FULL VACUUM couldn't run while the cursor
   >    is open, it could neither move nor remove the tuple
   >    but I'm not sure if the new VACUUM could remove
   >    the deleted tuple and other backends could re-use
   >    the space under such a situation.
   
   If you also save the tuple transaction info (xmin ?) during the
   select in addition to xtid, you could see whether the tupleslot was
   reused ?
   (This might need a function interface to make it reasonably portable to
   future 
   versions)
   Of course the only thing you can do if you notice it has changed is bail
   out.
   But that leaves the question to me on what should actually be done when
   the tuple has changed underneath. 
   I for one would not like the update to succeed if someone else modified
   it 
   inbetween my fetch and my update.
   
   Andreas
   
   

2. Re: CURRENT OF cursor without OIDs

   Ian Lance Taylor <ian@airs.com> — 2001-08-08T14:42:48Z

   "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
   
   > >    There could be DELETE operations for the tuple
   > >    from other backends also and the TID may disappear.
   > >    Because FULL VACUUM couldn't run while the cursor
   > >    is open, it could neither move nor remove the tuple
   > >    but I'm not sure if the new VACUUM could remove
   > >    the deleted tuple and other backends could re-use
   > >    the space under such a situation.
   > 
   > If you also save the tuple transaction info (xmin ?) during the
   > select in addition to xtid, you could see whether the tupleslot was
   > reused ?
   > (This might need a function interface to make it reasonably portable to
   > future 
   > versions)
   > Of course the only thing you can do if you notice it has changed is bail
   > out.
   > But that leaves the question to me on what should actually be done when
   > the tuple has changed underneath. 
   > I for one would not like the update to succeed if someone else modified
   > it 
   > inbetween my fetch and my update.
   
   If PL/pgSQL doesn't lock the table before doing the select, then I
   think it has to mark the tuples for update when it does the select.
   Unfortunately, the portal code explicitly rejects FOR UPDATE
   (transformSelectStmt in parser/analyze.c).
   
   Ian
   
   

3. Re: CURRENT OF cursor without OIDs

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-08T16:41:51Z

   Ian Lance Taylor <ian@airs.com> writes:
   > Unfortunately, the portal code explicitly rejects FOR UPDATE
   > (transformSelectStmt in parser/analyze.c).
   
   AFAIK, that error check is there specifically because we don't have
   UPDATE WHERE CURRENT.  Try removing it and see what happens --- AFAIK,
   things might "just work".
   
   			regards, tom lane
   
   

4. Re: CURRENT OF cursor without OIDs

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-08T23:54:04Z

   Zeugswetter Andreas SB SD wrote:
   > 
   > >    There could be DELETE operations for the tuple
   > >    from other backends also and the TID may disappear.
   > >    Because FULL VACUUM couldn't run while the cursor
   > >    is open, it could neither move nor remove the tuple
   > >    but I'm not sure if the new VACUUM could remove
   > >    the deleted tuple and other backends could re-use
   > >    the space under such a situation.
   > 
   > If you also save the tuple transaction info (xmin ?) during the
   > select in addition to xtid, you could see whether the tupleslot was
   > reused ?
   
   I think TID itself is available for the purpose as long as
   PostgreSQL uses no overwrite storage manager. If the tuple
   for a saved TID isn't found, the tuple may be update/deleted.
   If the tuple is found but the OID is different from the saved
   one, the space may be re-used. If we switch to an overwriting
   storage manager, TID would be no longer transient and we need
   another item like xmin to detect the change of rows.
   I agree with you that detecting the change of rows is very
   critical and xmin may be needed in the future.
   
   regards,
   Hiroshi Inoue