Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vYNeE-002Ani-1f for pgsql-bugs@arkaria.postgresql.org; Wed, 24 Dec 2025 12:07:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vYNeD-004djt-1b for pgsql-bugs@arkaria.postgresql.org; Wed, 24 Dec 2025 12:07:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vYNeD-004djl-0d for pgsql-bugs@lists.postgresql.org; Wed, 24 Dec 2025 12:07:22 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vYNeB-002W56-1j for pgsql-bugs@lists.postgresql.org; Wed, 24 Dec 2025 12:07:21 +0000 Received: by mail-ej1-x62a.google.com with SMTP id a640c23a62f3a-b7a02592efaso866343066b.1 for ; Wed, 24 Dec 2025 04:07:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1766578033; x=1767182833; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OXcBzLF3AygdsKHYnvobMfAHSoFJ3w30f1eZR4IkEVc=; b=FXsnRtFGuJ/9EzdZatNbfWPJle4WrrtI3tOYShp3FbudVi4cOXZfFxkl3nIxKhQ8rb xTxsefJyGWLPn4RIVW1gwQGPMJAnMrz19fyJ9rW9NthtHTZ2rMHZFKRnJchreorakjiQ zGWK7TRxA1XvBMw8co2d38iNw+/86nh8p5VJhpsyYnDDxD/9HuEstQVxsrK9UOH9578n t+tfaJZFzZv7r5MoFCkl2BlcftBeU6jK4F51ArHX5EtQIfCabUjA06ruGbiNIU/pCzOK n3lCWwuZE4QqZXfFWTj2QL89roc06m7JTdP0dVmprUYFxn+vos6JCA3HJISPXTvKowI7 rHGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1766578033; x=1767182833; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=OXcBzLF3AygdsKHYnvobMfAHSoFJ3w30f1eZR4IkEVc=; b=cLfXJGFowmHrlrPcaLuz7FoIgQ5rbiiHoXAv45rn9QXhwieG6k4MKK2sPIuAtEreeo Ai3nHWnmQhipHs0/NzX/m2bDmH0xINTqhLLv1S0SSI3J+FfWCqc16hokVm2K48Oxtozk mw1hm21S8ru+OmS3Fz/HA0+fVP8Ggu0ayIHjL/DTI653berFqvBxuPhWWG4o3vdCOBq6 Ko5B8ZjIGz1mPEQYKHmf4VYWUOI1uxDCii4I4ulgvIpSegmni02EsG4mXCl8FjwCoILZ 5g42E4lMF5N/904/vknPcjqchBnYCEkWj2NuvBmQH3e473WTLgnS/63Ck7HmwEUGoxMx i4Gw== X-Forwarded-Encrypted: i=1; AJvYcCXWm0L8MkqU59OUuIGZcpjcbQijU9MhrICQeXI1L9PzGTsnrTOYN4HvVv7nd9g/UzICcwnCjjSHFVgM@lists.postgresql.org X-Gm-Message-State: AOJu0YwiLpZ0yySmsogPCS7HWx1egxPz13qTI37G6cbcB3UFhh/bZxWq yFU1Vae2GY/+S5R8L8e5I+POYeuSskfCyt4RpWIjH8LECX/4pUMod9aswIbctN3VC0WCvVwUSFm WY0GQBucIZzzNG40ED9Ylo7d35wQ+ySI= X-Gm-Gg: AY/fxX5/jrdAbB2iQmK71zbHKp7zSsZZAppF5LT8MwpDnxUk1BH5qG6NMlNcLJry6BW LG6s3o/E7oHi2FWYCrJgGzq4mrZ3xRpIQQNeyEelIeW9gcC8nBhAokZs6nqiOMe/0yQ5PF2KR49 zafsYFf7sYYWCkIdu7xlZXy7GrwPJ+XmDN3JBnfOWDAoYeWZ5+UONj85wQ0Fgvl7H5l/5VRaZwY g/sgpqMBoAeSika/4p06TBvylb6UN4H5P3Dotfy4WWqczO+7Koej1W9fIQSuYT5cTk2nKQuWY91 x70i/Lo= X-Google-Smtp-Source: AGHT+IEpJo990Yc5VlUvYI7lzMEqrkrsXtrf308PskXSZh9ZwL5hKJuOf09bXi8wtJDTk/zpY32WX+sH1nIppJ82uCo= X-Received: by 2002:a17:907:7fa5:b0:b77:1a33:1669 with SMTP id a640c23a62f3a-b80371ef8bcmr1841848666b.57.1766578033209; Wed, 24 Dec 2025 04:07:13 -0800 (PST) MIME-Version: 1.0 References: <19355-57d7d52ea4980dc6@postgresql.org> <868ff2a518820c8864b6d28510294b2457a126af.camel@cybertec.at> In-Reply-To: From: Tender Wang Date: Wed, 24 Dec 2025 20:07:00 +0800 X-Gm-Features: AQt7F2o3ZKsdzYwdiwlQZPhQ7RRc-dxbvRw5LltYhtb0O_RCJyBjSEGa2Q4qe5A Message-ID: Subject: Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update To: Amit Langote Cc: Dean Rasheed , Bh W , Laurenz Albe , pgsql-bugs@lists.postgresql.org Content-Type: multipart/mixed; boundary="000000000000237adc0646b18038" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000237adc0646b18038 Content-Type: multipart/alternative; boundary="000000000000237ad90646b18036" --000000000000237ad90646b18036 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Amit Langote =E4=BA=8E2025=E5=B9=B412=E6=9C=8824= =E6=97=A5=E5=91=A8=E4=B8=89 16:08=E5=86=99=E9=81=93=EF=BC=9A > Hi, > > On Tue, Dec 23, 2025 at 4:07 Dean Rasheed > wrote: > >> On Mon, 22 Dec 2025 at 14:51, Bh W wrote: >> > >> > The issue is that the MERGE INTO match condition is not updated. >> > In the MATCHED path of MERGE INTO, when the target row satisfies the >> match condition and the condition itself has not changed, the system sho= uld >> still be able to handle concurrent updates to the same target row by >> relying on EvalPlanQual (EPQ) to refetch the latest version of the tuple= , >> and then proceed with the intended update. >> > However, in the current implementation, even though the concurrent >> update does not modify any columns relevant to the ON condition, the EPQ >> recheck unexpectedly results in a match condition failure, causing the >> update path that should remain MATCHED to be treated as NOT MATCHED. >> >> I spent a little time looking at this, and managed to reduce the >> reproducer test case down to this: >> >> -- Setup >> drop table if exists t1,t2; >> create table t1(a int primary key, b int); >> create table t2(a int, b int); >> >> insert into t1 values(1,0),(2,0); >> insert into t2 values(1,1),(2,2); >> >> -- Session 1 >> begin; >> update t1 set b =3D b+1; >> >> -- Session 2 >> merge into t1 using (values(1,1),(2,2)) as t3(a,b) on (t1.a =3D t3.a) >> when matched then >> update set b =3D t1.b + 1 >> when not matched then >> insert (a,b) values (1,1); >> >> -- Session 1 >> commit; >> >> This works fine in PG17, but fails with a PK violation in PG18. >> Git-bisecting points to this commit: >> >> cbc127917e04a978a788b8bc9d35a70244396d5b is the first bad commit >> commit cbc127917e04a978a788b8bc9d35a70244396d5b >> Author: Amit Langote >> Date: Fri Feb 7 17:15:09 2025 +0900 >> >> Track unpruned relids to avoid processing pruned relations >> >> Doing a little more debugging, it looks like the problem might be this >> change in InitPlan(): >> >> - /* ignore "parent" rowmarks; they are irrelevant at runtime = */ >> - if (rc->isParent) >> + /* >> + * Ignore "parent" rowmarks, because they are irrelevant at >> + * runtime. Also ignore the rowmarks belonging to child >> tables >> + * that have been pruned in ExecDoInitialPruning(). >> + */ >> + if (rc->isParent || >> + !bms_is_member(rc->rti, estate->es_unpruned_relids)) >> continue; >> >> which seems to cause it to incorrectly skip a rowmark, which I suspect >> is what is causing EvalPlanQual() to return the wrong result. > > > Thanks for the detailed analysis and adding me to the thread, Dean. > > I would think that a case that involves no partitioning at all would be > untouchable by this code, but it looks like the logic I added is > incorrectly affecting cases where pruning isn=E2=80=99t even relevant. I= =E2=80=99ll need to > look more carefully at why such a rowmark would exist in the rowmarks lis= t > if its relation isn=E2=80=99t in es_unpruned_relids. Maybe the set popula= tion is > incorrect at some point, or perhaps it matters that the set is a copy in > the EPQ estate. > I did some debugging, and I found that: In add_rte_to_flat_rtable(), the RTE of value was not added into glob->AllRelids, because below codes: ..... if (newrte->rtekind =3D=3D RTE_RELATION || (newrte->rtekind =3D=3D RTE_SUBQUERY && OidIsValid(newrte->relid))) { glob->relationOids =3D lappend_oid(glob->relationOids, newrte->relid); glob->allRelids =3D bms_add_member(glob->allRelids, list_length(glob->finalrtable)); } .... The VALUE rte was not satisfied above if, so it was not added into the glob->allRelids. Then in standard_planner(), we have: .... result->unprunableRelids =3D bms_difference(glob->allRelids, glob->prunableRelids); .... So the result->unprunableRelids contains only 1. In InitPlan(), we have: ..... /* * Ignore "parent" rowmarks, because they are irrelevant at * runtime. Also ignore the rowmarks belonging to child tables * that have been pruned in ExecDoInitialPruning(). */ if (rc->isParent || !bms_is_member(rc->rti, estate->es_unpruned_relids)) continue; ..... the estate->es_unpruned_relids equals with result->unprunableRelids contains. So the rowMark was skipped incorrectly. I did a quick fix as the attached patch. Any thoughts? --=20 Thanks, Tender Wang --000000000000237ad90646b18036 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


Amit Langote &l= t;amitlangote09@gmail.com>= ; =E4=BA=8E2025=E5=B9=B412=E6=9C=8824=E6=97=A5=E5=91=A8=E4=B8=89 16:08=E5= =86=99=E9=81=93=EF=BC=9A
Hi,

On Tue, Dec 23, 2025 at 4:07 Dean = Rasheed <d= ean.a.rasheed@gmail.com> wrote:
On Mon, 22 Dec 2025 at 14:51, Bh W <wangbihua.cn@gmail.com> wrote:
>
> The issue is that the MERGE INTO match condition is not updated.
> In the MATCHED path of MERGE INTO, when the target row satisfies the m= atch condition and the condition itself has not changed, the system should = still be able to handle concurrent updates to the same target row by relyin= g on EvalPlanQual (EPQ) to refetch the latest version of the tuple, and the= n proceed with the intended update.
> However, in the current implementation, even though the concurrent upd= ate does not modify any columns relevant to the ON condition, the EPQ reche= ck unexpectedly results in a match condition failure, causing the update pa= th that should remain MATCHED to be treated as NOT MATCHED.

I spent a little time looking at this, and managed to reduce the
reproducer test case down to this:

-- Setup
drop table if exists t1,t2;
create table t1(a int primary key, b int);
create table t2(a int, b int);

insert into t1 values(1,0),(2,0);
insert into t2 values(1,1),(2,2);

-- Session 1
begin;
update t1 set b =3D b+1;

-- Session 2
merge into t1 using (values(1,1),(2,2)) as t3(a,b) on (t1.a =3D t3.a)
when matched then
=C2=A0 =C2=A0 =C2=A0 update set b =3D t1.b + 1
when not matched then
=C2=A0 =C2=A0 =C2=A0 insert (a,b) values (1,1);

-- Session 1
commit;

This works fine in PG17, but fails with a PK violation in PG18.
Git-bisecting points to this commit:

cbc127917e04a978a788b8bc9d35a70244396d5b is the first bad commit
commit cbc127917e04a978a788b8bc9d35a70244396d5b
Author: Amit Langote <amitlan@postgresql.org>
Date:=C2=A0 =C2=A0Fri Feb 7 17:15:09 2025 +0900

=C2=A0 =C2=A0 Track unpruned relids to avoid processing pruned relations
Doing a little more debugging, it looks like the problem might be this
change in InitPlan():

-=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0/* ignore "parent" rowm= arks; they are irrelevant at runtime */
-=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (rc->isParent)
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0/*
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * Ignore "parent" rowm= arks, because they are irrelevant at
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * runtime.=C2=A0 Also ignore the= rowmarks belonging to child tables
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 * that have been pruned in ExecD= oInitialPruning().
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 */
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0if (rc->isParent ||
+=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0!bms_is_member(rc-&= gt;rti, estate->es_unpruned_relids))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 continue;

which seems to cause it to incorrectly skip a rowmark, which I suspect
is what is causing EvalPlanQual() to return the wrong result.
<= div dir=3D"auto">
Thanks for the detailed analysis and adding me to the thread, = Dean.

I would think that a case that involves no partitioning at al= l would be untouchable by this code, but it looks like the logic I added is= incorrectly affecting cases where pruning isn=E2=80=99t even relevant. I= =E2=80=99ll need to look more carefully at why such a rowmark would exist i= n the rowmarks list if its relation isn=E2=80=99t in es_unpruned_relids. Ma= ybe the set population is incorrect at some point, or perhaps it matters th= at the set is a copy in the EPQ estate.

I did some debugging, and I found that:
In=C2=A0=C2=A0add_rte_to_flat_rtable(),=C2=A0 the RTE of value was not ad= ded into glob->AllRelids, because below codes:
.....
if (newrte->rtekind =3D=3D RTE_RELATION ||
(newrte->rtekind =3D= =3D RTE_SUBQUERY && OidIsValid(newrte->relid)))
{
=C2=A0 = =C2=A0 =C2=A0glob->relationOids =3D lappend_oid(glob->relationOids, n= ewrte->relid);
=C2=A0 =C2=A0 =C2=A0glob->allRelids =3D bms_add_mem= ber(glob->allRelids,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0list_length(gl= ob->finalrtable));
}
....

T= he VALUE rte was not satisfied above if, so it was not added into the glob-= >allRelids.
Then in standard_planner(), we have:
...= .
result->unprunableRelids =3D bms_difference(glob->allReli= ds,
=C2=A0glob->prunableRelids);
....
So the result->unprunableRelids contains only 1.

In InitPlan(), we have:
.....
/*
* Ignore &qu= ot;parent" rowmarks, because they are irrelevant at
* runtime.= =C2=A0 Also ignore the rowmarks belonging to child tables
* that hav= e been pruned in ExecDoInitialPruning().
*/
if (rc->isParen= t ||
=C2=A0 =C2=A0!bms_is_member(rc->rti, estate->es_unpruned_reli= ds))
=C2=A0 =C2=A0 continue;
.....
the estate->es= _unpruned_relids equals with result->unprunableRelids contains. So the r= owMark was skipped incorrectly.

I did a quick fix = as the attached patch.
Any thoughts?

--
Thanks,
Tender Wang
--000000000000237ad90646b18036-- --000000000000237adc0646b18038 Content-Type: application/octet-stream; name="0001-fix-concurrent-update.patch" Content-Disposition: attachment; filename="0001-fix-concurrent-update.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_mjjywfpx0 RnJvbSBlZDkxOTRhNTczNWRiMzVjNmEyMWMzZDdlOWYzYTZiMjgzY2JmYmYyIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBUZW5kZXIgV2FuZyA8dG5kcndhbmdAZ21haWwuY29tPgpEYXRl OiBXZWQsIDI0IERlYyAyMDI1IDE5OjU3OjU2ICswODAwClN1YmplY3Q6IFtQQVRDSF0gZml4IGNv bmN1cnJlbnQgdXBkYXRlLgoKLS0tCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9zZXRyZWZz LmMgfCA0ICsrKysKIDEgZmlsZSBjaGFuZ2VkLCA0IGluc2VydGlvbnMoKykKCmRpZmYgLS1naXQg YS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9zZXRyZWZzLmMgYi9zcmMvYmFja2VuZC9vcHRp bWl6ZXIvcGxhbi9zZXRyZWZzLmMKaW5kZXggY2Q3ZWExZTZiNTguLjgzNjYyMTM0ZDJlIDEwMDY0 NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9zZXRyZWZzLmMKKysrIGIvc3JjL2Jh Y2tlbmQvb3B0aW1pemVyL3BsYW4vc2V0cmVmcy5jCkBAIC01ODQsNiArNTg0LDEwIEBAIGFkZF9y dGVfdG9fZmxhdF9ydGFibGUoUGxhbm5lckdsb2JhbCAqZ2xvYiwgTGlzdCAqcnRlcGVybWluZm9z LAogCQkobmV3cnRlLT5ydGVraW5kID09IFJURV9TVUJRVUVSWSAmJiBPaWRJc1ZhbGlkKG5ld3J0 ZS0+cmVsaWQpKSkKIAl7CiAJCWdsb2ItPnJlbGF0aW9uT2lkcyA9IGxhcHBlbmRfb2lkKGdsb2It PnJlbGF0aW9uT2lkcywgbmV3cnRlLT5yZWxpZCk7CisJfQorCWlmIChuZXdydGUtPnJ0ZWtpbmQg PT0gUlRFX1JFTEFUSU9OIHx8IG5ld3J0ZS0+cnRla2luZCA9PSBSVEVfVkFMVUVTIHx8CisJCShu ZXdydGUtPnJ0ZWtpbmQgPT0gUlRFX1NVQlFVRVJZICYmIE9pZElzVmFsaWQobmV3cnRlLT5yZWxp ZCkpKQorCXsKIAkJZ2xvYi0+YWxsUmVsaWRzID0gYm1zX2FkZF9tZW1iZXIoZ2xvYi0+YWxsUmVs aWRzLAogCQkJCQkJCQkJCSBsaXN0X2xlbmd0aChnbG9iLT5maW5hbHJ0YWJsZSkpOwogCX0KLS0g CjIuMzQuMQoK --000000000000237adc0646b18038--