Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?

For the record, I was wrong, the patch in [1] doesn't affect lost
concurrent updates with ctid. It was applied in 17.7 and after looking
much harder at the thread and code, I can see it's unrelated.

I've worked out what's happening, and it has nothing to do with ctid.

Here's a much simplified example:
create table t(i int);
insert into t(i) values (1);
--s1
begin;
update t set i = 2 from (select i from t) x where t.i = x.i;
---------------
UPDATE 1
--s2
update t set i = 2 from (select i from t) x where t.i = x.i;
----------------
UPDATE 0 (after commit s1)

The same thing happens with
update t set i = 2 from (select i from t for update) x where t.i = x.i;

x.i is not updated when s1 releases the lock and so s2 is still
looking for x.i = 1. Based on [2], I'm guessing that because the where
clause is indirect, it doesn't qualify for re-evalution after the lock
is released. So it continues to use the version of the record from the
start of the transaction. But I don't know nearly enough about the
internals to give a proper explanation.

Here's the ctid version that helped me figure this out.

with x as (select ctid from t for update),
y as (update t set i = 2 from x where t.ctid = x.ctid returning t.ctid)
select 'x', ctid from x union select 'y', ctid from y;
--s1
x (0, 1)
y (0, 2)
--s2
x (0, 2)

Even though x is updated in s2, the updated version isn't what's used
by y. I suspect the x version is only updated here because of the
final select.

So don't use this pattern to avoid deadlocks if this is a one shot update.
with x as (select ctid from t where ... order by id for update)
update t set ... where t.ctid = x.ctid;
Use an immutable unique column, or retry deadlocks.

Thanks,
Bernice

[1] https://www.postgresql.org/message-id/flat/4a6268ff-3340-453a-9bf5-c98d51a6f729%40app.fastmail.com
[2] https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED