Thread: Questions about CURSORS
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/
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/
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