Re: Questions about CURSORS

Marcos Barreto de Castro <mbdecastro(at)yahoo(dot)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