Re: BUG #19355: Attempt to insert data unexpectedly during concurrent update

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’d also appreciate it if you could check the test scenario
I provided. Thanks a lot!

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 于2025年12月15日周一 14:25写道：

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