Re: Questions about update, delete, ctid... - Mailing list pgsql-general

Martijn van Oosterhout wrote:
> On Mon, Jul 31, 2006 at 01:47:41AM +0200, Florian G. Pflug wrote:
>> I agree, at least for "for-update"-cursors. If the cursor was not
>> declared "for update", then it is not even cleaer to me what the
>> correct behaviour would be. Imagine that you declared a cursor, and fetched
>> a row. After fetching, but before you call "update table foo where current
>> of
>> mycursor" someone updates the row (and commits), and the new version would
>> have
>> never been returned by your select statement in the first place. Should
>> that row be
>> updates, or not? What if the other transaction deleted the row - should your
>> update raise an error? fail silently?
>
> That's what the EPQ machinery that Tom talked about does. It will trace
> a newer version, raise a serialization failure or do nothing as
> appropriate. I really don't think you need to do anything special in
> this case.

I can see how the EPQ machinery can be used to chain forward to the
correct row to be updated, even if I originally found an older version
(e.g. by searching for a specific ctid). But for non-"for
update"-cursors, the newest version of the row returned by fetch could
be modified such that it would have never been returned by fetch in the
first place. Image two transactions A and B:
A: begin
B: begin
A: declare c_foo cursor for select * from foo where type='A';
A: fetch c_foo -- the returned row is *not* locked.
B: update foo set type='B' -- There are no rows with type='B' now
B: commit
A: update foo set type='A' where current of c_foo

The EPQ machinery can now guarantee that we actually try to update
the correct row version. But I'm unsure if the last update should
do anything at all. Because at the time of the update, the current
row of the cursor c_foo wouldn't haven been returned by fetch, because
it's type is now 'B' and not 'A'.

This is why I believe that the use of "current of" to cursors that take
rowlevel share locks in the right thing to do.

>
> Read the src/backend/executor/README file for details.
Done that ;-)

>> Because of those problems, I'll always assume that the cursor was declared
>> "for update" from now on.
>>
>> After reading backend code for the last hours, I've created the following
>> plan for
>> implementing "where current of"
>
> <snip>
>
> Looks like a good plan, but I think you've overlooked something: if
> you've locked the tuple FOR UPDATE then by definition there cannot be a
> newer version, right? (Incidently, I see message about FOR UPDATE not
> being supported on cursors).
There can be, because the transaction owning the lock could have updated
the row itself.

> More specifically, if you have a tuple locked FOR UPDATE and there is a
> newer version, that almost certaintly means this new version is dead,
> part of an aborted transaction. Comments from more knowledgable people
> would be good though. Locked tuples are usually easier.
>
> I think plan A is the way to go. Good luck.
Sounds cleaner to me too, even though it extends the spec a bit.

greetings, Florian Pflug