Re: Questions about update, delete, ctid...

Martijn van Oosterhout wrote:
> On Sun, Jul 30, 2006 at 04:37:26PM +0200, Florian G. Pflug wrote:
>> Martijn van Oosterhout wrote:
>>> The word "become" is not really right. The old version has become
>>> invisible to you and the new version is visible. Some other
>>> transactions will see the old one, some the new one. However, there is
>>> a link between the old and the new version do detect conflicting
>>> updates.
>> That sounds interesting... how is that link between old and new
>> tuple implemented? I've been playing with the idea to implement
>> "update table foo where current of <cursor>", and such a link
>> would help tremendously. I read the code, but couldn't find any
>> such link - which part of the code should I read closer?
>
> It's the t_ctid field of the tuple header. But I don't see what that
> has to do with "WHERE CURRENT OF". That expression should return the
> tuple visible to your transaction, not some updated version which you
> won't be able to fetch. If you end up updating the old version, the
> system will take care of finding the newer version if necessary.

The problem is that the ctid of the tuple that "fetch" returned
might not actually be the tuple that needs to be updated. Imagine:
create cursor c_foo as select * from foo;
fetch c_foo into v_foo ;
update foo set <somevars> where current of c_foo ;
update foo set <someothervars> where current of c_foo ;

The second update needs to find the correct tuple to update. In
case of a serializable transaction, or a cursor declared "for update",
you only need to that updates by that same transaction into account -
but for the general case, it might even haven been another transaction
that did the update.

My first conclusion was that a possible solution would be to
restrict "where current of" to "for update"-cursors, and then somehow
remember old->new ctid mappings inside a transaction - but that sounds
rather messy, and intrusive.

Being able to follow ctid chains should make that quite a bit easier

greetings, Florian Pflug