| From: | Tender Wang <tndrwang(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | MERGE fails to reject source references in NOT MATCHED BY SOURCE actions |
| Date: | 2026-11-05 04:52:17 |
| Message-ID: | CAHewXNmFA5dWp7AUivBjqLdjA3GyG4uVS2XDYdf=rChU-KMXGQ@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
I noticed that MERGE allows source references in NOT MATCHED BY SOURCE
actions when the source is an unaliased join. For example:
postgres=# CREATE TABLE t (id int, balance int);
CREATE TABLE s (id int, delta int);
CREATE TABLE
CREATE TABLE
postgres=# MERGE INTO t
USING s CROSS JOIN (SELECT 1 AS k) j ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = 1
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
MERGE 0
This should reject s.delta, as it does when USING contains only s.
Source references in WHEN conditions are also incorrectly accepted.
postgres=# MERGE INTO t
USING s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = 1
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
ERROR: invalid reference to FROM-clause entry for table "s"
LINE 4: ...HEN NOT MATCHED BY SOURCE THEN UPDATE SET balance = s.delta;
^
DETAIL: There is an entry for table "s", but it cannot be referenced
from this part of the query.
setNamespaceForMergeWhen() hides only the top-level source RTE. For an
unaliased join, its input relations remain visible by name.
The attached patch temporarily selects the appropriate namespace for
each action and restores it afterward, following transformJoinOnClause().
It preserves the source visibility flags established by
transformFromClause(). Regression tests are included.
Thoughts?
--
Thanks,
Tender Wang
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Fix-source-visibility-in-MERGE-NOT-MATCHED-BY-SOURCE.patch | text/plain | 9.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shihao zhong | 2026-11-05 04:54:26 | Re: Partitioned index marked valid while a partition is pending detach |
| Previous Message | Tatsuo Ishii | 2026-11-05 04:18:09 | Re: nodeWindowAgg.c IGNORE NULLS abs overflow |