Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)

From: Tender Wang <tndrwang(at)gmail(dot)com>
To: 303677365(at)qq(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>
Subject: Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
Date: 2026-11-18 12:30:20
Message-ID: CAHewXNnHWqW49cnYP+3REo=zZhzQ3oO0ZPc-K=y2m=Q0znh_Cg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Tender Wang <tndrwang(at)gmail(dot)com> 于2026年11月18日周三 09:42写道:
>
> Thanks for the detailed report and analysis. I can reproduce the issue on
> current master.
>
> The diagnosis is correct. When EvalPlanQual determines that a concurrently
> updated target tuple no longer matches the source tuple, it returns an EPQ
> tuple with the source side correctly set to NULL. ExecMergeMatched() then
> switches to the NOT MATCHED BY SOURCE action list, but the action quals and
> projections continue to use the original plan slot. RETURNING also receives
> that original slot, so all of them can observe stale source values.
>

After further analysis and some AI-assisted testing, I found a case
where the assumption made by the v1 patch does not hold.

The v1 patch assumed that, after EvalPlanQual determines that a
previously matched target row is now NOT MATCHED BY SOURCE,
the tuple returned by EvalPlanQual has its source side set to NULL.
That is true when the MERGE only has MATCHED and NOT MATCHED BY SOURCE
actions, because the transformed join is a left join.
However, it is not necessarily true when the MERGE also has a NOT
MATCHED BY TARGET action, and is therefore implemented using a full
join.

Here is a reproducer.

Setup:
CREATE TABLE t(a int, b int, c text);
CREATE TABLE s(a int, c text);
INSERT INTO s VALUES (1, 'sv');
INSERT INTO t VALUES (1, 10, 't1'), (9, 90, 't9');

Session 1:
BEGIN;
UPDATE t SET a = 999 WHERE a = 1;
Session 2, while session 1 still holds the row lock:
MERGE INTO t AS t
USING s AS s
ON t.a = s.a
WHEN MATCHED THEN
UPDATE SET b = 777
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET b = 888
WHEN NOT MATCHED BY TARGET AND s.c IS NOT NULL THEN
INSERT VALUES (s.a + 100, 0, 'ins:' || s.c)
RETURNING
merge_action(),
t.a,
t.b,
s.c AS source_c;
Then commit session 1:
COMMIT;
With v1, the result is:
merge_action | a | b | source_c
--------------+-----+-----+----------
UPDATE | 999 | 888 | sv
INSERT | 101 | 0 | sv
UPDATE | 9 | 888 |
(3 rows)

The reason is that the original matched pair:
(T_old, S1)
is effectively split after the concurrent update into:
(T_new, NULL) -- NOT MATCHED BY SOURCE
(NULL, S1) -- NOT MATCHED BY TARGET
For a full join, rerunning the EPQ subplan can produce both unmatched sides.
EvalPlanQual returns only the first tuple from the rerun subplan.
Which side is returned first depends on the join implementation and
ordering.

For example, with a merge join, the source-only row can be returned
first because its key is 1, while the concurrently updated target key
is 999.
In that case, the EPQ tuple has a NULL target ctid, but still
contains the non-NULL source values.

Thus, the v1 code: “actionPlanSlot = epqslot;” is not valid for a full join.
A NULL target ctid tells us that this EPQ output has no target row,
but it does not imply that the source side of the tuple is NULL.

The v2 patch no longer uses epqslot as the expression input for the
NOT MATCHED BY SOURCE action.
Instead, when an EPQ recheck changes the row from MATCHED to NOT
MATCHED BY SOURCE, it creates an explicitly all-NULL plan-shaped slot
and uses that slot for:

- evaluating the NOT MATCHED BY SOURCE action
- projecting the updated tuple
- evaluating its RETURNING clause

The original context->planSlot is left unchanged, because it is still
needed for a possible subsequent NOT MATCHED BY TARGET action using
the original source row.
The temporary NULL slot is local to ExecMergeMatched() and is released
before the function returns, so no additional field is required in
ModifyTableState.

The updated v2 patch is attached.

--
Tender Wang

Attachment Content-Type Size
v2-0001-Use-EPQ-tuple-for-MERGE-NOT-MATCHED-BY-SOURCE-act.patch text/plain 4.1 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Ishjaap Singh 2026-11-18 17:39:03 Re: ECPG: NULL pointer dereference in EXECUTE with a NULL parameter
Previous Message miracee 2026-11-18 10:56:24 Inconsistent time zone handling in timetz