MIME-Version: 1.0
References: <CAEDh4ny-6xDG5FJ9Zp_EQKnTL+ouef_JqKACUo2RNQ9KFKKnXA@mail.gmail.com>
 <CAEDh4nxKnq1txtjRfBd2eDtRdeXEq5Dbi+n+z0MXkqMMBk=vBA@mail.gmail.com>
 <CAEDh4nyxze285Ku=nFahj-3j6+rA2+CkrXFji3PheG4sCKQ7Aw@mail.gmail.com> <1645231.1763582658@sss.pgh.pa.us>
In-Reply-To: <1645231.1763582658@sss.pgh.pa.us>
From: Bernice Southey <bernice.southey@gmail.com>
Date: Thu, 20 Nov 2025 10:33:00 +0000
Message-ID: <CAEDh4nx9Hx_pm4rCvGhWjgzy_w9qR7Ydiha+D4mchddHZPft7A@mail.gmail.com>
Subject: Re: Is this expected concurrency behaviour for EvalPlanQual and ctid?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAEDh4nx9Hx_pm4rCvGhWjgzy_w9qR7Ydiha%2BD4mchddHZPft7A%40mail.gmail.com>
Precedence: bulk

On Wed, Nov 19, 2025 at 8:04=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > The same thing happens with
> > update t set  i =3D 2 from (select i from t for update) x where t.i =3D=
 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 =3D 2.
> But the UPDATE's initial scan of t only sees t.i =3D 1, so the join
> fails before we ever get to EvalPlanQual.

Thanks for the clear explanation. When I tried to think this through I
couldn't fill in the details.