Re: updateable cursors & visibility - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> Neil Conway writes:
> 
> > 11) If an <updatability clause> of FOR UPDATE with or without a <column
> > name list> is specified, then INSENSITIVE shall not be specified and QE
> > shall be updateable.
> >
> > I'm not really sure I see the point of this restriction, though...
> 
> If you allow updatable insensitive cursors, then you are really saying,
> whatever happens in the rest of the world does not affect my cursor, but
> what I do in my cursor affects the rest of the world.  You can easily
> construct some cases where this would have bizarre results.  For example,
> someone inserts a primary key into the underlying table.  You don't see it
> in the cursor, so you happily insert the same primary key.  How can you
> and when should you detect this error?  Also, since the snapshot of
> insensitive cursors is frozen when the cursor is created, would you insert
> new rows "in the past"?  It's not really workable when you think it
> through.

You bring up a very good point here I didn't realize --- that when you
have a cursor, then do an UPDATE using the cursor information, your
UPDATE must have current visibility, not visibility at the time of
cursor creation, and I agree with you.

I talked to Neil Conway on the phone and we discussed various options. 
One idea is to require FOR UPDATE on the cursor --- while that prevents
other transactions from changing the cursor, it doesn't deal with the
current transaction modifying the table outside the cursor.  One idea is
to have UPDATE/DELETE WHERE CURRENT OF behave like UPDATE/DELETE do now
when they find a row that is locked by another transaction --- they wait
to see if the transaction commits or aborts, then if committed they
follow the tid to the newly updated row, check the WHERE clause to see
if it still is satisfied, then perform the update.  (Is this correct?) 
I think WHERE CURRENT OF could do the same thing --- take the tid of the
cursor row, find the newest version of the row, wait for any active
transaction, re-test the cursor WHERE clause, and update the row.  Seems
this would make WHERE CURRENT OF behave just like UPDATE, except it is
getting its rows from the cursor.

As far as someone inserting into the table at the same time, I don't
know how to show that row in the cursor, but referential integrity
constraints are going to be checked by the UPDATE, and that UPDATE has
current visibility, so it should see any inserts that are valid.  It
doesn't seem much worse than what we have now.

Comments?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073