public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bernice Southey <[email protected]>
To: [email protected]
Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
Date: Wed, 19 Nov 2025 19:20:26 +0000
Message-ID: <CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com> (raw)
In-Reply-To: <CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>
References: <CAEDh4ny-6xDG5FJ9Zp_EQKnTL+ouef_JqKACUo2RNQ9KFKKnXA@mail.gmail.com>
	<CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>

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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
  In-Reply-To: <CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox