Re: second DML operation fails with updatable cursor

Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> Hmm, what I'm seeing is that it returns the original (unmodified) row;
>> is that what you meant to say?

> I do get the original unmodified tuple (2) if I leave out the FOR UPDATE.

Ah, I was testing without FOR UPDATE. I traced through it and the
problem seems to be where ExecutePlan tries to do heap_lock_tuple
during the re-fetch of the row. heap_lock_tuple quite correctly
reports "HeapTupleSelfUpdated" and ExecutePlan just punts:

case HeapTupleSelfUpdated:
/* treat it as deleted; do not process */
goto lnext;

I wonder if it's sane to have this case chase forward to the newest
row version and lock that. Offhand, seeing that FOR UPDATE is supposed
to always return the newest row version, that seems self-consistent;
but I wonder what behaviors it might break.

Another question: if you do DELETE WHERE CURRENT OF, what would you
expect to happen to the cursor position?

regards, tom lane