Thread

1. Questions about CURSORS

   Marcos Barreto de Castro <mbdecastro@yahoo.com> — 2000-05-28T11:53:42Z

   Hi,
   
     Suppose there is a table that's being shared among 5
   computers at the same time. 
     Suppose I've created a CURSOR for a SELECT * FROM
   <that table> GROUP BY <column1>.
     Suppose I have fetched the 3rd record and am going
   to fetch the 4th and in the meantime someone at
   another computer just DELETED that 4th record from
   that table.    Will the fetch succeed? Will the record
   be shown although it no longer exists in the table?
   And if someone at another computer had inserted a new
   record which, according to my GROUP BY clause, would
   be the 4th, would it be shown for the next fetch?
     My big questions are: Do CURSORS perform their
   SELECT operations in the TABLE directly or in a file?
   Any changes to rows selected through a CURSOR will be
   shown right away or they will only appear as I perform
   another SELECT?
     Is there a book that I could read in order to get a
   better knowledge on SQL implementation or even a
   website where I could read about this?
   
   
   __________________________________________________
   Do You Yahoo!?
   Kick off your party with Yahoo! Invites.
   http://invites.yahoo.com/
   
   

2. Re: Questions about CURSORS

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-05-28T13:45:00Z

   Marcos Barreto de Castro wrote:
   > 
   > Hi,
   > 
   >   Suppose there is a table that's being shared among 5
   > computers at the same time.
   >   Suppose I've created a CURSOR for a SELECT * FROM
   > <that table> GROUP BY <column1>.
   >   Suppose I have fetched the 3rd record and am going
   > to fetch the 4th and in the meantime someone at
   > another computer just DELETED that 4th record from
   > that table.    Will the fetch succeed? Will the record
   > be shown although it no longer exists in the table?
   
   Yes the fetch will succeed. A cursor is a set of tuples
   and after the cursor is created, the tuples will remain
   no matter what happend to the actual table.
   
   > And if someone at another computer had inserted a new
   > record which, according to my GROUP BY clause, would
   > be the 4th, would it be shown for the next fetch?
   
   No.
   
   >   My big questions are: Do CURSORS perform their
   > SELECT operations in the TABLE directly or in a file?
   > Any changes to rows selected through a CURSOR will be
   > shown right away or they will only appear as I perform
   > another SELECT?
   
   They will appear in the next SELECT unless it's in the
   same transaction.
   
   >   Is there a book that I could read in order to get a
   > better knowledge on SQL implementation or even a
   > website where I could read about this?
   
   Look like you need to learn about transactions.
   Sorry, can't suggest a good site.
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   

3. Re: Questions about CURSORS

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-28T15:07:18Z

   Marcos Barreto de Castro <mbdecastro@yahoo.com> writes:
   > computers at the same time. 
   >   Suppose I've created a CURSOR for a SELECT * FROM
   > <that table> GROUP BY <column1>.
   >   Suppose I have fetched the 3rd record and am going
   > to fetch the 4th and in the meantime someone at
   > another computer just DELETED that 4th record from
   > that table.    Will the fetch succeed? Will the record
   > be shown although it no longer exists in the table?
   
   Yes --- that's what transaction semantics are all about.
   You don't see the effects of a different transaction unless
   it committed before yours started.  (The actual implementation
   is that a deleted or modified tuple is still in the table,
   but it's in a "zombie" state.  Old transactions can still see it,
   new transactions ignore it.)
   
   > And if someone at another computer had inserted a new
   > record which, according to my GROUP BY clause, would
   > be the 4th, would it be shown for the next fetch?
   
   No.  See above.
   
   >   My big questions are: Do CURSORS perform their
   > SELECT operations in the TABLE directly or in a file?
   
   A cursor is no different from a select; it's just expressed in a form
   that lets you suspend execution part way through.
   
   > Any changes to rows selected through a CURSOR will be
   > shown right away or they will only appear as I perform
   > another SELECT?
   
   Other backends won't be able to see your changes until you commit.
   I'm not sure about the behavior if you modify the table in your own
   transaction and then resume scanning with a pre-existing cursor.
   It might be that you will be able to see the updates in that case.
   (If so, is that a bug?  Possibly, not sure...)
   
   >   Is there a book that I could read in order to get a
   > better knowledge on SQL implementation or even a
   > website where I could read about this?
   
   There are several books recommended in our FAQ, I believe.
   
   (Hey Bruce, does your new book go into this stuff?)
   
   			regards, tom lane