Re: updateable cursors & visibility

OK, no one has replied to this proposal, so I assume the approach is
good.

As far as implementation, I assume we add a field to the cursor
structure to record the most recently fetched tid (if multiple rows are
fetched, it is the last row).

Now, when UPDATE/DELETE ... WHERE CURRENT OF, make sure the table
mentioned is the same as the single table in the cursor. Then, use the
tid to find the most recent version of that row. heap_update seems to
be where this all happens. Also, make sure you check the cursor WHERE
condition before doing the update.

Basically, this allows us to get rows from the INSENSITIVE cursor, but
have the UPDATE have current visibility.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

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