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

Hiroshi Inoue wrote:
> Bruce Momjian wrote:
> > 
> > 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.
> 
> I don't understand what you two are discussing.
> What's is SENSITIVE, INSENSITIVE or ASESNSITIVE ?

In SQL99 standard, I see:
        -  If the cursor is insensitive, then significant changes are not           visible.
        -  If the cursor is sensitive, then significant changes are           visible.
        -  If the cursor is asensitive, then the visibility of significant           changes is
implementation-dependent.

So, I think we have two issues --- what does the cursor see, and what
does the UPDATE see.  I think we have to have the cursor remain
INSENSITIVE, because we don't at fetch time whether WHERE CURRENT OF is
going to be used.  One nice thing is that while the standard says you
can't specify INSENSITIVE for a WHERE CURRENT OF cursor, we can say it
is ASENSITIVE and that will match our behavior. (We just need a boolean
to make sure if they do specify INSENSTIVIVE that WHERE CURRENT OF
throws an error.)

Then, when we do the UPDATE, the UPDATE is SENSITIVE in that it sees the
most recent version of the tuple, assuming the newest tuple still
matches the WHERE clause of the cursor.  The UPDATE also has to do
contraint checking using current visibility.

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