pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
3+ messages / 2 participants
[nested] [flat]

* BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
@ 2026-11-17 14:57 PG Bug reporting form <noreply@postgresql.org>
  2026-11-18 01:42 ` Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: PG Bug reporting form @ 2026-11-17 14:57 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: 303677365@qq.com

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.








^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
  2026-11-17 14:57 BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) PG Bug reporting form <noreply@postgresql.org>
@ 2026-11-18 01:42 ` Tender Wang <tndrwang@gmail.com>
  2026-11-18 12:30   ` Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) Tender Wang <tndrwang@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tender Wang @ 2026-11-18 01:42 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>

PG Bug reporting form <noreply@postgresql.org> 于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

Attachments:

0001-Use-EPQ-tuple-for-MERGE-NOT-MATCHED-BY-SOURCE-action.patch (text/plain, 3.1K) download From 1ed4f9bd1a74507e85b63f090dfddb6c5658d152 Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Wed, 18 Nov 2026 09:37:52 +0800 Subject: [PATCH] Use EPQ tuple for MERGE NOT MATCHED BY SOURCE actions When a concurrently updated target tuple no longer satisfies the MERGE join condition, EvalPlanQual returns a tuple with the source side correctly set to null, and ExecMergeMatched switches from MATCHED to NOT MATCHED BY SOURCE actions. However, the action quals and projections continued to use the original plan slot, which still contained values from the previously matched source tuple. RETURNING expressions also used that slot. As a result, NOT MATCHED BY SOURCE actions could select the wrong action, store stale source values, or return them to the user. Keep a separate plan slot for the action currently being executed. When EPQ changes a MATCHED row to NOT MATCHED BY SOURCE, use the EPQ slot for the action quals, projection, and RETURNING. Leave context->planSlot unchanged, since a subsequent NOT MATCHED BY TARGET action still needs the original source tuple. --- src/backend/executor/nodeModifyTable.c | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 966094b6a32..3a68f4e9601 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -3312,6 +3312,7 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, List *actionStates; TupleTableSlot *newslot = NULL; TupleTableSlot *rslot = NULL; + TupleTableSlot *actionPlanSlot = context->planSlot; EState *estate = context->estate; ExprContext *econtext = mtstate->ps.ps_ExprContext; bool isNull; @@ -3336,7 +3337,7 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * or NOT MATCHED BY SOURCE tuple and UPDATE/DELETE actions. */ econtext->ecxt_scantuple = resultRelInfo->ri_oldTupleSlot; - econtext->ecxt_innertuple = context->planSlot; + econtext->ecxt_innertuple = actionPlanSlot; econtext->ecxt_outertuple = NULL; /* @@ -3718,6 +3719,14 @@ lmerge_matched: /* Switch lists, if necessary */ if (!*matched) { + /* + * Use the EPQ tuple for NOT MATCHED BY SOURCE + * expressions, since its source columns have + * been nulled. Keep context->planSlot unchanged + * for any subsequent NOT MATCHED BY TARGET action. + */ + actionPlanSlot = epqslot; + econtext->ecxt_innertuple = actionPlanSlot; actionStates = mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]; /* @@ -3819,7 +3828,7 @@ lmerge_matched: false, resultRelInfo->ri_oldTupleSlot, newslot, - context->planSlot); + actionPlanSlot); break; case CMD_DELETE: @@ -3828,7 +3837,7 @@ lmerge_matched: true, resultRelInfo->ri_oldTupleSlot, NULL, - context->planSlot); + actionPlanSlot); break; case CMD_NOTHING: -- 2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
  2026-11-17 14:57 BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) PG Bug reporting form <noreply@postgresql.org>
  2026-11-18 01:42 ` Re: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) Tender Wang <tndrwang@gmail.com>
@ 2026-11-18 12:30   ` Tender Wang <tndrwang@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tender Wang @ 2026-11-18 12:30 UTC (permalink / raw)
  To: 303677365@qq.com; pgsql-bugs@lists.postgresql.org; +Cc: Dean Rasheed <dean.a.rasheed@gmail.com>

Tender Wang <tndrwang@gmail.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

Attachments:

v2-0001-Use-EPQ-tuple-for-MERGE-NOT-MATCHED-BY-SOURCE-act.patch (text/plain, 4.1K) download From 15d99d372f3277bce12811abdaeb1bcffd5bdf41 Mon Sep 17 00:00:00 2001 From: Tender Wang <tndrwang@gmail.com> Date: Wed, 18 Nov 2026 09:37:52 +0800 Subject: [PATCH v2] Use EPQ tuple for MERGE NOT MATCHED BY SOURCE actions When a concurrently updated target tuple no longer satisfies the MERGE join condition, EvalPlanQual returns a tuple with the source side correctly set to null, and ExecMergeMatched switches from MATCHED to NOT MATCHED BY SOURCE actions. However, the action quals and projections continued to use the original plan slot, which still contained values from the previously matched source tuple. RETURNING expressions also used that slot. As a result, NOT MATCHED BY SOURCE actions could select the wrong action, store stale source values, or return them to the user. Keep a separate plan slot for the action currently being executed. When EPQ changes a MATCHED row to NOT MATCHED BY SOURCE, use the EPQ slot for the action quals, projection, and RETURNING. Leave context->planSlot unchanged, since a subsequent NOT MATCHED BY TARGET action still needs the original source tuple. --- src/backend/executor/nodeModifyTable.c | 35 +++++++++++++++++++++++--- 1 file changed, 32 insertions(+), 3 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 966094b6a32..8ac566dfdff 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -3312,6 +3312,8 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, List *actionStates; TupleTableSlot *newslot = NULL; TupleTableSlot *rslot = NULL; + TupleTableSlot *actionPlanSlot = context->planSlot; + TupleTableSlot *nullSourceSlot = NULL; EState *estate = context->estate; ExprContext *econtext = mtstate->ps.ps_ExprContext; bool isNull; @@ -3336,7 +3338,7 @@ ExecMergeMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * or NOT MATCHED BY SOURCE tuple and UPDATE/DELETE actions. */ econtext->ecxt_scantuple = resultRelInfo->ri_oldTupleSlot; - econtext->ecxt_innertuple = context->planSlot; + econtext->ecxt_innertuple = actionPlanSlot; econtext->ecxt_outertuple = NULL; /* @@ -3718,6 +3720,25 @@ lmerge_matched: /* Switch lists, if necessary */ if (!*matched) { + /* + * The EPQ subplan may be a full join, in which case + * its first output tuple might be the NOT MATCHED + * BY TARGET tuple, with non-NULL source values. + * Therefore, don't use epqslot for the NOT MATCHED + * BY SOURCE action. Instead, use an explicitly + * null-extended subplan tuple, while leaving + * context->planSlot unchanged for any + * subsequent NOT MATCHED BY TARGET action. + */ + if (nullSourceSlot == NULL) + { + nullSourceSlot = + MakeSingleTupleTableSlot(context->planSlot->tts_tupleDescriptor, + &TTSOpsVirtual); + ExecStoreAllNullTuple(nullSourceSlot); + } + actionPlanSlot = nullSourceSlot; + econtext->ecxt_innertuple = actionPlanSlot; actionStates = mergeActions[MERGE_WHEN_NOT_MATCHED_BY_SOURCE]; /* @@ -3819,7 +3840,7 @@ lmerge_matched: false, resultRelInfo->ri_oldTupleSlot, newslot, - context->planSlot); + actionPlanSlot); break; case CMD_DELETE: @@ -3828,7 +3849,7 @@ lmerge_matched: true, resultRelInfo->ri_oldTupleSlot, NULL, - context->planSlot); + actionPlanSlot); break; case CMD_NOTHING: @@ -3851,6 +3872,14 @@ lmerge_matched: * Successfully executed an action or no qualifying action was found. */ out: + if (nullSourceSlot != NULL) + { + econtext->ecxt_innertuple = NULL; + if (resultRelInfo->ri_projectReturning != NULL) + resultRelInfo->ri_projectReturning->pi_exprContext->ecxt_outertuple = NULL; + ExecDropSingleTupleTableSlot(nullSourceSlot); + } + if (ItemPointerIsValid(&lockedtid)) UnlockTuple(resultRelInfo->ri_RelationDesc, &lockedtid, InplaceUpdateTupleLock); -- 2.43.0
^ permalink raw reply [nested|flat] 3+ messages in thread


end of thread, other threads:[~2026-11-18 12:30 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-17 14:57 BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ) PG Bug reporting form <noreply@postgresql.org>
2026-11-18 01:42 ` Tender Wang <tndrwang@gmail.com>
2026-11-18 12:30   ` Tender Wang <tndrwang@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox