MIME-Version: 1.0
References: <CAEDh4ny-6xDG5FJ9Zp_EQKnTL+ouef_JqKACUo2RNQ9KFKKnXA@mail.gmail.com>
 <CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>
In-Reply-To: <CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Wed, 19 Nov 2025 19:20:26 +0000
Message-ID: <CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com>
Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
To: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nyxze285Ku%3DnFahj-3j6%2BrA2%2BCkrXFji3PheG4sCKQ7Aw%40mail.gmail.com>
Precedence: bulk

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