BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website: Bug reference: 19912 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: Unsupported/Unknown Operating system: Linux x86_64; master build 81b2179b492 (20devel) Description: **PostgreSQL version:** 20devel master build (commit 81b2179b492, built 2026-10-27). The MERGE WHEN NOT MATCHED BY SOURCE feature and the EvalPlanQual handling involved date back to PG17, so released versions may be affected as well; I have not verified on a release build. **Operating system:** Linux x86_64 **Description:** For a MERGE command with a WHEN NOT MATCHED BY SOURCE action, if a target row that matched the source when the MERGE started is concurrently updated so that it no longer satisfies the join condition, the executor re-evaluates the join via EvalPlanQual and (correctly) runs the NOT MATCHED BY SOURCE action for that row. However, that action and its RETURNING clause observe stale, non-NULL source column values â the values from the originally-matched source row â instead of NULL, which is what a genuine not-matched-by-source row sees. This is internally inconsistent: a single MERGE statement returns different source column values for the two kinds of NMBS rows (genuine vs. concurrently-unmatched). The stale value is visible in RETURNING, is stored into the table when referenced in the SET clause, and can select a different NMBS action branch when referenced in a WHEN ... AND qual over a source column. The official regression test (src/test/regress/sql/merge.sql, the bug18634 test added with the NMBS feature) asserts that source columns are NULL for NOT MATCHED BY SOURCE rows, and prepjointree.c comments that the join subplan "nulls the output from the source relation when the join condition doesn't match" â the semantics an NMBS action is entitled to rely on. **Reproduction (READ COMMITTED, two sessions):** Setup: ```sql CREATE TABLE b2_t (a int, b int, c text); CREATE TABLE b2_s (a int, c text); INSERT INTO b2_s VALUES (1, 's1_val'); INSERT INTO b2_t VALUES (1, 10, 'orig1'), (2, 20, 'orig2'); ``` Session B (holds an uncommitted row lock): ```sql BEGIN; UPDATE b2_t SET a = 999 WHERE a = 1; ``` Session A (blocks on the row locked by B; then B COMMITs): ```sql MERGE INTO b2_t t USING b2_s s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 777 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 888, c = 'NMBS' RETURNING merge_action(), t.a, t.b, t.c, s.c AS returning_src; ``` Observed in session A (after B commits): ```text merge_action | t.a | t.b | t.c | returning_src --------------+-----+-----+------+--------------- UPDATE | 999 | 888 | NMBS | s1_val <-- EPQ-transitioned row: stale non-NULL source value UPDATE | 2 | 888 | NMBS | <-- genuine NMBS row: NULL, as expected ``` Control (no concurrency): starting from the same final logical state reached directly, the same MERGE returns NULL for both rows: ```sql TRUNCATE b2_s, b2_t; INSERT INTO b2_s VALUES (1, 's1_val'); INSERT INTO b2_t VALUES (999, 10, 'orig1'), (2, 20, 'orig2'); -- run the same MERGE as above ``` ```text merge_action | t.a | t.b | t.c | returning_src --------------+-----+-----+------+--------------- UPDATE | 2 | 888 | NMBS | UPDATE | 999 | 888 | NMBS | ``` So the same final state, reached through a concurrent update, yields a different (stale) source value for the EPQ-transitioned row. The stale value is not only visible in RETURNING; it is stored into the table when the NMBS action references the source column. With a join source: ```sql CREATE TABLE b2_a (a int, c text); CREATE TABLE b2_b (a int, c text); INSERT INTO b2_a VALUES (1, 's1_val'); INSERT INTO b2_b VALUES (1, 's2_s1_val'); INSERT INTO b2_t VALUES (1, 10, 'orig'); -- Session B: BEGIN; UPDATE b2_t SET a = 999 WHERE a = 1; -- Session A (then B commits): MERGE INTO b2_t t USING (b2_a s JOIN b2_b s2 ON s.a = s2.a) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 777 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c = 'NMBS_' || coalesce(s.c, 'NULLSRC') RETURNING merge_action(), t.a, t.c, s.c AS returning_src; ``` Observed: ```text merge_action | t.a | t.c | returning_src --------------+-----+--------------+--------------- UPDATE | 999 | NMBS_s1_val | s1_val <-- stale source value stored in the table ``` A genuine NMBS row (same setup, no concurrency, target row a=2) stores 'NMBS_NULLSRC'. With a second NMBS action guarded by a qual on the source column: ```sql WHEN NOT MATCHED BY SOURCE AND s.c IS NOT NULL THEN UPDATE SET c = 'WRONG_BRANCH' WHEN NOT MATCHED BY SOURCE THEN UPDATE SET c = 'CORRECT_BRANCH' ``` the EPQ-transitioned row takes the first branch (observed c='WRONG_BRANCH'), while the genuine control takes CORRECT_BRANCH. **Root cause:** In ExecMergeMatched() (src/backend/executor/nodeModifyTable.c), when the target tuple was concurrently updated (TM_Updated), the join quals are re-evaluated with the updated target tuple via EvalPlanQual, but neither context->planSlot nor econtext->ecxt_innertuple is refreshed from the EPQ re-evaluation result. When the re-check concludes the row no longer matches and the executor transitions to the NOT MATCHED BY SOURCE action, the action's projection (ExecProject) and RETURNING (ExecProcessReturning(..., context->planSlot)) therefore read the stale source tuple from the original plan slot. Regular UPDATE/DELETE pass the EPQ tuple back to callers via *epqreturnslot and build the new tuple from it; MERGE's MATCHEDâNMBS transition has no equivalent refresh.
Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
PG Bug reporting form 于2026年11月18日周三 02:28写道: > **Root cause:** > > In ExecMergeMatched() (src/backend/executor/nodeModifyTable.c), when the > target tuple was concurrently updated (TM_Updated), the join quals are > re-evaluated with the updated target tuple via EvalPlanQual, but neither > context->planSlot nor econtext->ecxt_innertuple is refreshed from the EPQ > re-evaluation result. When the re-check concludes the row no longer matches > and the executor transitions to the NOT MATCHED BY SOURCE action, the > action's projection (ExecProject) and RETURNING (ExecProcessReturning(..., > context->planSlot)) therefore read the stale source tuple from the original > plan slot. Regular UPDATE/DELETE pass the EPQ tuple back to callers via > *epqreturnslot and build the new tuple from it; MERGE's MATCHED→NMBS > transition has no equivalent refresh. > 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. The attached patch uses the EPQ slot for the NOT MATCHED BY SOURCE action quals, projection, and RETURNING. It leaves context->planSlot unchanged, because a subsequent NOT MATCHED BY TARGET action still needs the original source tuple. All regression and isolation tests pass. CC'ing Dean in case he has any thoughts. -- Tender Wang
Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
Tender Wang 于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