Re: updateable cursors & visibility

Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > 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.
> 
> That would only keep existing rows from being deleted but not new rows
> from being added.
> 
> > 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?)
> 
> Surely it would have to do something like that, but that's a matter of the
> transaction isolation, not the sensitivity.  It doesn't do anything to
> address the potential problems I mentioned.

Well, a unique constraint on the row would see your other INSERT.  I
don't see how making an INSERT visible in the cursor would help us, and
I don't see how we would implement that except by rerunning the query
for each fetch, which seems like a bad idea.

--  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