public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Bernice Southey <[email protected]>
Cc: [email protected]
Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
Date: Wed, 19 Nov 2025 15:04:18 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com>
References: <CAEDh4ny-6xDG5FJ9Zp_EQKnTL+ouef_JqKACUo2RNQ9KFKKnXA@mail.gmail.com>
<CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>
<CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com>
Bernice Southey <[email protected]> writes:
> 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)
I'm reminded of Markus Winand's talk at PGConf.EU 2019 [1], where
he was handing out coasters that said "Notice: All employees must
wash hands after using self-joins". (Think I've still got mine,
somewhere.) This is a mess because the initial table scans find
the row version with i = 1, so the join qual succeeds, and then the
UPDATE step blocks because it notices that the target row has an
uncommitted update pending. After waiting for that to commit,
we run the "EvalPlanQual" logic, which rechecks the updated version
of the target row to see if it still satisfies the WHERE condition.
But it doesn't, because as you say, we still think that x.i is 1.
(EvalPlanQual is careful to re-use the same row versions from
other tables that were used the first time.)
> The same thing happens with
> update t set i = 2 from (select i from t for update) x where t.i = x.i;
Right, the common advice if you need to make such scenarios work
is to add FOR UPDATE to the non-target relations. But here, that
just breaks in the opposite direction: the sub-select blocks
waiting for the concurrent commit and then returns x.i = 2.
But the UPDATE's initial scan of t only sees t.i = 1, so the join
fails before we ever get to EvalPlanQual. I'm not sure there is
any way to make self-join cases work when the concurrent update
is changing the join column.
> 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.
Yup. True SERIALIZABLE mode with retries on serialization failures is
the best recipe if you need to deal with concurrent-update situations
this complicated. In this particular example though, I wonder if you
couldn't get rid of the self-join.
regards, tom lane
[1] https://www.postgresql.eu/events/pgconfeu2019/schedule/session/2644-more-than-a-query-language-sql-i...
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], [email protected]
Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
In-Reply-To: <[email protected]>
* 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