Re: MERGE fails to reject source references in NOT MATCHED BY SOURCE actions

From: Joao Rito <joaovictor(dot)rito(at)gmail(dot)com>
To: Tender Wang <tndrwang(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: MERGE fails to reject source references in NOT MATCHED BY SOURCE actions
Date: 2026-11-18 19:47:02
Message-ID: 20261118194702.1135-1-joaovictor.rito@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Tender,

Thanks for the patch. I reviewed
0001-Fix-source-visibility-in-MERGE-NOT-MATCHED-BY-SOURCE.patch.

Setup: master at debfb3c8a8, and the same commit with the patch applied
(the only version on the CF page, 2026-11-05). The patch applied
cleanly. Both were built with --without-icu, and the build showed no
warnings.

What I tested

1. Your report case (unaliased join, "WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET balance = s.delta"): master returns "MERGE 2". The patched
build returns "invalid reference to FROM-clause entry for table "s""
with the usual DETAIL. The same happens for the WHEN condition ("AND
s.delta > 0", "AND s2.k > 0"), for both inputs of a CROSS JOIN, for a
subquery that references the source ("(SELECT s.delta)"), for a
nested join (s JOIN (s2 JOIN s3 ON true) ON true), and for a
partitioned target. On master all of these were accepted.

2. Valid cases: target-only references in NOT MATCHED BY SOURCE
(qualified, unqualified, with a target alias, in a scalar subquery,
in a DELETE action), MATCHED actions that use both source and target,
NOT MATCHED INSERT that uses join inputs, a NOT MATCHED BY SOURCE
action placed before the other actions, and RETURNING that references
the source. For these statements the output is the same on master and
on the patched build. NOT MATCHED INSERT still cannot see the target,
on both.

3. Regression tests: "make check" failed for me in the temp-install
step (initdb-template). I did not find the cause. I was building on a
Windows-mounted filesystem under WSL, so I suspect my setup and not
the patch. So I ran "make installcheck" against a separately
initialized cluster with the patched build instead: 243/243 tests
passed, including merge with your new cases.

Review comments

a) Behavior difference you may want to mention. For an unqualified
source column in a NOT MATCHED BY SOURCE action, master prints 'HINT:
To reference that column, you must use a table-qualified name.' The
patched build no longer prints it (for example "UPDATE SET balance =
delta" on a join). I think dropping it is right, because a qualified
name does not work there either, so the hint was misleading. The
error text and DETAIL are the same.

b) The patch deletes the three Assert()s on commandType that were in
setNamespaceForMergeWhen() (for example, NOT MATCHED BY SOURCE must
be UPDATE, DELETE or DO NOTHING). Maybe keep them in the new switch,
or mention in the commit message that they are dropped on purpose.

c) parse_merge.c now ends with an extra blank line after the closing
brace of transformMergeStmt(), left over from removing
setNamespaceVisibilityForRTE(). pgindent or git diff --check would
probably point at it.

d) In the new tests, the first two failing statements have "EXPLAIN
(COSTS OFF)" in front. It is not needed, since the statements fail
during parse analysis. Plain MERGE statements would be simpler.

Overall the patch looks good to me and fixes the problem as
described. The simplification (no flag mutation, a temporary
namespace per action, restored afterwards) is easier to read than the
old code.

What I did not test: other platforms, the docs (I did not look for text
that needs updating), and the TAP tests.

Regards,
Joao Rito

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Joao Rito 2026-11-18 19:47:41 Re: EXPLAIN ANALYZE overcounts MERGE inserts suppressed by triggers
Previous Message João Rito 2026-11-18 19:44:27 Re: Fix jsonpath printing of operations followed by an accessor