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

Bernice Southey <bernice(dot)southey(at)gmail(dot)com> 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-in-the-21st-century/