Thread

1. CURRENT OF cursor without OIDs

   Ian Lance Taylor <ian@airs.com> — 2001-08-07T15:59:13Z

   Oracle PL/SQL supports a very convenient feature in which you can say
   something like
     DECLARE
       CURSUR cur IS SELECT * FROM RECORD;
     BEGIN
       OPEN cur;
       UPDATE record SET field = value WHERE CURRENT OF cur;
       CLOSE cur;
     END
   
   We have cursors in the development version of PL/pgSQL, but they don't
   support CURRENT OF.  In the patch I wrote a few months back to add
   cursor support to PL/pgSQL, which was not adopted, I included support
   for CURRENT OF.  I did it by using OIDs.  Within PL/pgSQL, I modified
   the cursor select statement to also select the OID.  Then I change
   WHERE CURRENT OF cur to oid = oidvalue.  Of course this only works in
   limited situations, and in particular doesn't work after OID
   wraparound.
   
   Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   My question now is: if there is no OID, is there any comparable way to
   implement CURRENT OF cursor?  Basically what is needed is some way to
   identify a particular row between a SELECT and an UPDATE.
   
   Ian
   
   

2. Re: CURRENT OF cursor without OIDs

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-07T18:01:07Z

   Ian Lance Taylor <ian@airs.com> writes:
   > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   > My question now is: if there is no OID, is there any comparable way to
   > implement CURRENT OF cursor?  Basically what is needed is some way to
   > identify a particular row between a SELECT and an UPDATE.
   
   I'd look at using TID.  Seems like that is more efficient anyway (no
   index needed).  Hiroshi has opined that TID is not sufficient for ODBC
   cursors, but it seems to me that it is sufficient for SQL cursors.
   
   			regards, tom lane
   
   

3. Re: CURRENT OF cursor without OIDs

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-08T00:12:15Z

   Tom Lane wrote:
   > 
   > Ian Lance Taylor <ian@airs.com> writes:
   > > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   > > My question now is: if there is no OID, is there any comparable way to
   > > implement CURRENT OF cursor?  Basically what is needed is some way to
   > > identify a particular row between a SELECT and an UPDATE.
   > 
   > I'd look at using TID.  Seems like that is more efficient anyway (no
   > index needed).  Hiroshi has opined that TID is not sufficient for ODBC
   > cursors, but it seems to me that it is sufficient for SQL cursors.
   > 
   
   Yes TID is available and I introduced Tid Scan in order
   to support this kind of implementation. However there
   are some notices.
   1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
      (It doesn't seem easy for me).
   2) If no, there could be UPDATE operations for the
      current tuple from other backends between a
      SELECT and an UPDATE and the TID may be changed.
      In that case, you couldn't find the tuple using
      saved TID but you could use the functions to
      follow the UPDATE link which I provided when I
      I introduced Tis Scan.
      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 it's possible,
      there must be another information like OID to iden-
      tify tuples.
   
   Anyway optional OIDs aren't preferable IMHO.
   
   regards,
   Hiroshi Inoue
   
   

4. Re: CURRENT OF cursor without OIDs

   Ian Lance Taylor <ian@airs.com> — 2001-08-08T00:46:15Z

   Hiroshi Inoue <Inoue@tpf.co.jp> writes:
   
   > > Ian Lance Taylor <ian@airs.com> writes:
   > > > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   > > > My question now is: if there is no OID, is there any comparable way to
   > > > implement CURRENT OF cursor?  Basically what is needed is some way to
   > > > identify a particular row between a SELECT and an UPDATE.
   > > 
   > > I'd look at using TID.  Seems like that is more efficient anyway (no
   > > index needed).  Hiroshi has opined that TID is not sufficient for ODBC
   > > cursors, but it seems to me that it is sufficient for SQL cursors.
   > > 
   > 
   > Yes TID is available and I introduced Tid Scan in order
   > to support this kind of implementation. However there
   > are some notices.
   > 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
   >    (It doesn't seem easy for me).
   
   No, it is not supported right now.
   
   Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause
   and turn it into an explicit LOCK statement.  The TID hack will only
   work for a cursor which selects from a single table, so this is the
   only case for which turning FOR UPDATE into LOCK has to work.
   
   Admittedly, this is not the same as SELECT FOR UPDATE, because I think
   PL/pgSQL would have to lock the table in ROW EXCLUSIVE mode.  But I
   think it would work, albeit not with maximal efficiency.
   
   Ian
   
   

5. Re: CURRENT OF cursor without OIDs

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

   Ian Lance Taylor wrote:
   > 
   > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
   > 
   > > > Ian Lance Taylor <ian@airs.com> writes:
   > > > > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   > > > > My question now is: if there is no OID, is there any comparable way to
   > > > > implement CURRENT OF cursor?  Basically what is needed is some way to
   > > > > identify a particular row between a SELECT and an UPDATE.
   > > >
   > > > I'd look at using TID.  Seems like that is more efficient anyway (no
   > > > index needed).  Hiroshi has opined that TID is not sufficient for ODBC
   > > > cursors, but it seems to me that it is sufficient for SQL cursors.
   > > >
   > >
   > > Yes TID is available and I introduced Tid Scan in order
   > > to support this kind of implementation. However there
   > > are some notices.
   > > 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
   > >    (It doesn't seem easy for me).
   > 
   > No, it is not supported right now.
   > 
   > Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause
   > and turn it into an explicit LOCK statement.
   
   It's impossible to realize *FOR UPDATE* using LOCK statement.
   Each row must be locked individually to prevent UPDATE/DELETE
   operations for the row. You could acquire an EXCLUSIVE
   LOCK on the table but it doesn't seem preferable.
   
   I'm planning to implement updatable cursors with no lock
   using TID and OID. TID is for the fast access and OID is
   to verify the identity. OID doesn't provide a specific
   access method in the first place and the access would be
   veeery slow for large tables unless there's an index on OID.
   
   regards,
   Hiroshi Inoue
   
   

6. Re: CURRENT OF cursor without OIDs

   Ian Lance Taylor <ian@airs.com> — 2001-08-08T01:05:02Z

   Hiroshi Inoue <Inoue@tpf.co.jp> writes:
   
   > > > > Ian Lance Taylor <ian@airs.com> writes:
   > > > > > Anyhow, I see that there is a move afoot to eliminate mandatory OIDs.
   > > > > > My question now is: if there is no OID, is there any comparable way to
   > > > > > implement CURRENT OF cursor?  Basically what is needed is some way to
   > > > > > identify a particular row between a SELECT and an UPDATE.
   > > > >
   > > > > I'd look at using TID.  Seems like that is more efficient anyway (no
   > > > > index needed).  Hiroshi has opined that TID is not sufficient for ODBC
   > > > > cursors, but it seems to me that it is sufficient for SQL cursors.
   > > > >
   > > >
   > > > Yes TID is available and I introduced Tid Scan in order
   > > > to support this kind of implementation. However there
   > > > are some notices.
   > > > 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
   > > >    (It doesn't seem easy for me).
   > > 
   > > No, it is not supported right now.
   > > 
   > > Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause
   > > and turn it into an explicit LOCK statement.
   > 
   > It's impossible to realize *FOR UPDATE* using LOCK statement.
   > Each row must be locked individually to prevent UPDATE/DELETE
   > operations for the row. You could acquire an EXCLUSIVE
   > LOCK on the table but it doesn't seem preferable.
   
   It's definitely not preferable, but how else can it be done?
   
   > I'm planning to implement updatable cursors with no lock
   > using TID and OID. TID is for the fast access and OID is
   > to verify the identity. OID doesn't provide a specific
   > access method in the first place and the access would be
   > veeery slow for large tables unless there's an index on OID.
   
   I apologize if I've missed something, but how will that work when OIDs
   become optional?
   
   Ian
   
   

7. Re: CURRENT OF cursor without OIDs

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-08T01:11:24Z

   Ian Lance Taylor wrote:
   > 
   
   [snip]
   
   > > > >
   > > > > Yes TID is available and I introduced Tid Scan in order
   > > > > to support this kind of implementation. However there
   > > > > are some notices.
   > > > > 1) Is *FOR UPDATE* cursor allowed in PL/pgSQL ?
   > > > >    (It doesn't seem easy for me).
   > > >
   > > > No, it is not supported right now.
   > > >
   > > > Conceptually, however, PL/pgSQL could pull out the FOR UPDATE clause
   > > > and turn it into an explicit LOCK statement.
   > >
   > > It's impossible to realize *FOR UPDATE* using LOCK statement.
   > > Each row must be locked individually to prevent UPDATE/DELETE
   > > operations for the row. You could acquire an EXCLUSIVE
   > > LOCK on the table but it doesn't seem preferable.
   > 
   > It's definitely not preferable, but how else can it be done?
   > 
   > > I'm planning to implement updatable cursors with no lock
   > > using TID and OID. TID is for the fast access and OID is
   > > to verify the identity. OID doesn't provide a specific
   > > access method in the first place and the access would be
   > > veeery slow for large tables unless there's an index on OID.
   > 
   > I apologize if I've missed something, but how will that work when OIDs
   > become optional?
   > 
   
   So I've objected optional OIDs.
   
   regards,
   Hiroshi Inoue
   
   

8. Re: CURRENT OF cursor without OIDs

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

   Hiroshi Inoue <Inoue@tpf.co.jp> writes:
   > 2) If no, there could be UPDATE operations for the
   >    current tuple from other backends between a
   >    SELECT and an UPDATE and the TID may be changed.
   >    In that case, you couldn't find the tuple using
   >    saved TID but you could use the functions to
   >    follow the UPDATE link which I provided when I
   >    I introduced Tis Scan.
   
   Yes, you could either declare an error (if serializable mode) or follow
   the TID links to find the latest version of the tuple, and update that
   (if read-committed mode).  This is no different from the situation for
   any other UPDATE, AFAICS.
   
   >    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.
   
   Of course not.  Concurrent VACUUM has to follow the same rules as
   old-style VACUUM: it must never remove or move any tuple that is still
   visible to any open transaction.  (Actually, it never moves tuples at
   all, but the point is that it cannot remove any tuple that the open
   cursor could have seen.)  So, the fact that SQL cursors don't survive
   across transactions is enough to guarantee that a TID returned by a
   cursor is good as long as the cursor is open.
   
   The reason you have a harder time with ODBC cursors is that you aren't
   restricting them to be good only within a transaction (or at least
   that's how I interpreted what you said earlier).
   
   			regards, tom lane
   
   

9. Re: CURRENT OF cursor without OIDs

   Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-09T00:20:55Z

   
   Tom Lane wrote:
   > 
   > Hiroshi Inoue <Inoue@tpf.co.jp> 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.
   > 
   > Of course not.  Concurrent VACUUM has to follow the same rules as
   > old-style VACUUM: it must never remove or move any tuple that is still
   > visible to any open transaction.  (Actually, it never moves tuples at
   > all, but the point is that it cannot remove any tuple that the open
   > cursor could have seen.)  So, the fact that SQL cursors don't survive
   > across transactions is enough to guarantee that a TID returned by a
   > cursor is good as long as the cursor is open.
   > 
   > The reason you have a harder time with ODBC cursors is that you aren't
   > restricting them to be good only within a transaction (or at least
   > that's how I interpreted what you said earlier).
   > 
   
   Yes mainly but I want the verification by OID even in
   *inside a transaction* cases. For example,
   
   1) A backend tx1 fetch a row using cursor.
   2) Very old backend tx_old deletes the row and commits.
   3) The new VACUUM starts to run and find the row to be
      completely dead.
   
   The page is pinned by tx1, so the new VACUUM refuses
   to change the page ? I there could be another story.
   
   2)' Very old backend tx_old updated the row and deletes
       the updated row and commits.
   3)' The new VACUUM starts to run and find the updated
       row to be completely dead but the page may not be
       pinned.
   
   Both seems to be detected by FULL VACUUM as 
   'NOTICE: Child itemid in update-chain marked as unused - can't
   continue repair_frag' though it may be too late.
   
   regards,
   Hiroshi Inoue
   
   

10. Re: CURRENT OF cursor without OIDs

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-09T01:22:56Z

    Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > Yes mainly but I want the verification by OID even in
    > *inside a transaction* cases. For example,
    
    > 1) A backend tx1 fetch a row using cursor.
    > 2) Very old backend tx_old deletes the row and commits.
    > 3) The new VACUUM starts to run and find the row to be
    >    completely dead.
    
    This cannot happen.  If VACUUM thought that, VACUUM would be completely
    broken.  Although the row is committed dead, it is still visible to the
    transaction using the cursor, so it must not be deleted.  This is true
    *whether or not the row has been fetched yet*, or ever will be fetched,
    by the cursor.
    
    If cursors had this risk then ordinary UPDATE would be equally broken.
    What is a cursor except an externally-accessible scan-in-progress?
    There is no difference.
    
    > The page is pinned by tx1, so the new VACUUM refuses
    > to change the page ? I there could be another story.
    
    The pin stuff doesn't have anything to do with whether TIDs remain
    valid.  A pin guarantees that a *physical pointer* into a shared buffer
    will remain valid --- it protects against VACUUM reshuffling the page
    data to compact free space after it's deleted completely-dead tuples.
    But reshuffling doesn't invalidate non-dead TIDs.  A TID remains valid
    until there are no open transactions that could possibly consider the
    tuple visible.
    
    > Both seems to be detected by FULL VACUUM as 
    > 'NOTICE: Child itemid in update-chain marked as unused - can't
    > continue repair_frag' though it may be too late.
    
    AFAICS, that code cannot be executed unless someone has violated the
    update protocol (or the on-disk tuple status bits have gotten trashed
    somehow).  We are never supposed to update a tuple that has been
    inserted or deleted by another, not-yet-committed transaction.
    Therefore the child tuple should have been inserted by a
    later-committing transaction.  There is no way that VACUUM can see the
    child tuple as dead and the parent tuple as not dead.
    
    Or have I missed something?
    
    			regards, tom lane
    
    

11. Re: CURRENT OF cursor without OIDs

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-09T02:04:02Z

    Tom Lane wrote:
    > 
    > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > > Yes mainly but I want the verification by OID even in
    > > *inside a transaction* cases. For example,
    > 
    > > 1) A backend tx1 fetch a row using cursor.
    > > 2) Very old backend tx_old deletes the row and commits.
    > > 3) The new VACUUM starts to run and find the row to be
    > >    completely dead.
    > 
    > This cannot happen.  If VACUUM thought that, VACUUM would be completely
    > broken. Although the row is committed dead, it is still visible to the
    > transaction using the cursor, so it must not be deleted.
    
    Yes it should be but it could happen.
    GetXmaxRecent() ignores the backend tx_old because it had been
    committed when VACUUM started and may return the xid > the
    very old xid of tx_old.  As far as I see, the current VACUUM
    considers the row completely dead.
    
    > This is true
    > *whether or not the row has been fetched yet*, or ever will be fetched,
    > by the cursor.
    > 
    
    I must apologize for leaving the bug unsolved.
    Unfortunately VACUUM and MVCC are ill-suited.
    For example, complicated update chain handling wasn't
    needed before MVCC. 
    
    regards,
    Hiroshi Inoue
    
    

12. Re: CURRENT OF cursor without OIDs

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-09T02:09:00Z

    Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > GetXmaxRecent() ignores the backend tx_old because it had been
    > committed when VACUUM started and may return the xid > the
    > very old xid of tx_old.
    
    Absolutely not; things would never work if that were true.
    GetXmaxRecent() returns the oldest TID that was running *when any
    current transaction started*, not just VACUUM's transaction.  Thus,
    no transaction that could be considered live by the cursor-holding
    transaction will be considered dead by VACUUM.
    
    			regards, tom lane
    
    

13. Re: CURRENT OF cursor without OIDs

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-09T02:36:13Z

    Tom Lane wrote:
    > 
    > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > > GetXmaxRecent() ignores the backend tx_old because it had been
    > > committed when VACUUM started and may return the xid > the
    > > very old xid of tx_old.
    > 
    > Absolutely not; things would never work if that were true.
    > GetXmaxRecent() returns the oldest TID that was running *when any
    > current transaction started*, not just VACUUM's transaction.  Thus,
    > no transaction that could be considered live by the cursor-holding
    > transaction will be considered dead by VACUUM.
    > 
    
    Oops I've misunderstood GetXmaxRecent() until now.
    Now I'm checking the current source.
    Hmm is there any place setting proc->xmin other than
    the following ?
    
    [in storage/ipc/sinval.c]
           if (serializable)
                   MyProc->xmin = snapshot->xmin;
    
    regards,
    Hiroshi Inoue
    
    

14. Re: CURRENT OF cursor without OIDs

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-08-09T02:59:45Z

    Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > Hmm is there any place setting proc->xmin other than
    > the following ?
    
    > [in storage/ipc/sinval.c]
    >        if (serializable)
    >                MyProc->xmin = snapshot->xmin;
    
    AFAICT that's the only place that sets it.  It's cleared to zero during
    transaction commit or abort in xact.c.
    
    			regards, tom lane
    
    

15. Re: CURRENT OF cursor without OIDs

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-08-09T04:15:33Z

    Tom Lane wrote:
    > 
    > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
    > > Hmm is there any place setting proc->xmin other than
    > > the following ?
    > 
    > > [in storage/ipc/sinval.c]
    > >        if (serializable)
    > >                MyProc->xmin = snapshot->xmin;
    > 
    > AFAICT that's the only place that sets it.  It's cleared to zero during
    > transaction commit or abort in xact.c.
    > 
    
    You are right.
    Now I understand I've completely misunderstood
      'NOTICE: Child itemid in update-chain marked as unused - can't
      continue repair_frag'.
    
    regards,
    Hiroshi Inoue