MIME-Version: 1.0
References: <19355-57d7d52ea4980dc6@postgresql.org> <868ff2a518820c8864b6d28510294b2457a126af.camel@cybertec.at>
In-Reply-To: <868ff2a518820c8864b6d28510294b2457a126af.camel@cybertec.at>
From: Bh W <wangbihua.cn@gmail.com>
Date: Mon, 22 Dec 2025 22:51:02 +0800
Message-ID: <CANjBVm3AMm=9-mB7=uqJjaWN-+RrA8+w-gXhZn6L7VHu7K_Urw@mail.gmail.com>
Subject: Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000001ae02006468b8f5d"
Archived-At: <https://www.postgresql.org/message-id/CANjBVm3AMm%3D9-mB7%3DuqJjaWN-%2BRrA8%2Bw-gXhZn6L7VHu7K_Urw%40mail.gmail.com>
Precedence: bulk

--0000000000001ae02006468b8f5d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 should 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.

In the scenario shown above, if no primary key exists, an extra row will be
inserted.

Further investigation shows that execution plans using Hash Join do not
exhibit this problem.

Could you please take a look at my explanation and let me know if anything
is inaccurate? I=E2=80=99d also appreciate it if you could check the test s=
cenario
I provided. Thanks a lot!

Laurenz Albe <laurenz.albe@cybertec.at> =E4=BA=8E2025=E5=B9=B412=E6=9C=8815=
=E6=97=A5=E5=91=A8=E4=B8=80 14:25=E5=86=99=E9=81=93=EF=BC=9A

> On Mon, 2025-12-15 at 01:40 +0000, PG Bug reporting form wrote:
> > Start two transaction and update on same tuple, raise concurrent update
> and
> > evalplanqual.  It will be found out that the session with evalplanqual
> did
> > not successfully update the data, but instead attempted to insert a row
> of
> > data incorrectly.
>
> I'd say that is expected.
>
> If you need a guarantee that either INSERT or UPDATE succeed, you have to
> use
> INSERT ... ON CONFLICT ... DO UPDATE
>
> Yours,
> Laurenz Albe
>

--0000000000001ae02006468b8f5d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">The issue is that the MERGE INTO match condition is not up=
dated.<div>In the MATCHED path of MERGE INTO, when the target row satisfies=
 the match condition and the condition itself has not changed, the system s=
hould 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, a=
nd then proceed with the intended update.<br>However, in the current implem=
entation, even though the concurrent update does not modify any columns rel=
evant 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.<div><br>In the scenario shown above, if no primary=
 key exists, an extra row will be inserted.<br><br>Further investigation sh=
ows that execution plans using Hash Join do not exhibit this problem.<br></=
div></div><div><br></div><div>Could you please take a look at my explanatio=
n and let me know if anything is inaccurate? I=E2=80=99d also appreciate it=
 if you could check the test scenario I provided. Thanks a lot!</div></div>=
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at=
">laurenz.albe@cybertec.at</a>&gt; =E4=BA=8E2025=E5=B9=B412=E6=9C=8815=E6=
=97=A5=E5=91=A8=E4=B8=80 14:25=E5=86=99=E9=81=93=EF=BC=9A<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-wid=
th:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-l=
eft:1ex">On Mon, 2025-12-15 at 01:40 +0000, PG Bug reporting form wrote:<br=
>
&gt; Start two transaction and update on same tuple, raise concurrent updat=
e and<br>
&gt; evalplanqual.=C2=A0 It will be found out that the session with evalpla=
nqual did<br>
&gt; not successfully update the data, but instead attempted to insert a ro=
w of<br>
&gt; data incorrectly.<br>
<br>
I&#39;d say that is expected.<br>
<br>
If you need a guarantee that either INSERT or UPDATE succeed, you have to u=
se<br>
INSERT ... ON CONFLICT ... DO UPDATE<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--0000000000001ae02006468b8f5d--