Re: second DML operation fails with updatable cursor

Tom Lane wrote:
> 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.

Yes. I suppose it would change the behavior of "SELECT *, volatilefunc()
FROM foo", where volatilefunc would update rows in foo. Doesn't seem
like very well-defined behavior anyway.

Our FOR UPDATE cursors aren't exactly INSENSITIVE right now. For example:

postgres=# truncate foo; INSERT INTO foo SELECT a from
generate_series(1,5) a;
TRUNCATE TABLE
INSERT 0 5
postgres=# BEGIN; DECLARE c CURSOR FOR SELECT id FROM foo FOR UPDATE;
BEGIN
DECLARE CURSOR
postgres=# FETCH RELATIVE 1 FROM c;
id
----
1
(1 row)

postgres=# UPDATE foo set id=20 WHERE id = 2;
UPDATE 1
postgres=# FETCH RELATIVE 1 FROM c;
id
----
3
(1 row)

postgres=# FETCH RELATIVE 1 FROM c;
id
----
4
(1 row)

postgres=# FETCH RELATIVE 1 FROM c;
id
----
5
(1 row)

postgres=# FETCH RELATIVE 1 FROM c;
id
----
(0 rows)

Updating a row makes that row disappear from the cursor.

But that's actually ok. According to the spec, a cursor is "ASENSITIVE"
by default, which basically means the behavior is
implementation-dependent. Our read-only cursors are INSENSITIVE, because
of MVCC.

To summarize the above random thoughts, I think if you change that case
in ExecutePlan, we'd have pretty sane behavior:
- read-only cursors (no FOR UPDATE or FOR SHARE) would be INSENSITIVE.
- FOR UPDATE and FOR SHARE cursors would see any updates or deletes
performed in the same transaction. Updated tuples would appear in the
position of the original tuple. Inserted tuples wouldn't be visible.
Neither would deleted tuples.

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

According to the spec: before the next row.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com