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

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 303677365(at)qq(dot)com
Subject: BUG #19912: MERGE NOT MATCHED BY SOURCE sees stale source values after concurrent update (EPQ)
Date: 2026-11-17 14:57:39
Message-ID: 19912-ceea4a1c0a7dd41c@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19912
Logged by: chunling qin
Email address: 303677365(at)qq(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-11-17 15:01:50 BUG #19913: Parent ALTER TABLE during concurrent DETACH leaves pending partition unusable (42804)
Previous Message Tom Lane 2026-11-17 14:49:34 Re: PG 18 JIT regression: int4range hash breaks multi-key GROUP BY