Hi, I found that MERGE can fail when the target is an inheritance parent and a child has additional columns:
postgres=# CREATE TABLE mp2(a int, b text); CREATE TABLE postgres=# CREATE TABLE mc2(extra int) INHERITS (mp2); CREATE TABLE postgres=# ALTER TABLE mp2 ADD CONSTRAINT mpc2 CHECK (a < 0) NO INHERIT; ALTER TABLE postgres=# set constraint_exclusion = on; SET postgres=# MERGE INTO mp2 t USING (VALUES (7, -5)) s(k, ins) ON t.a = s.k AND t.a = 7 WHEN NOT MATCHED THEN INSERT VALUES (s.ins, 'x') RETURNING t.a, t.b; ERROR: could not convert row type DETAIL: Attribute "extra" of type mc2 does not exist in type mp2. The parent's scan is excluded because its CHECK constraint contradicts the join condition. However, the INSERT still targets the parent, and the inserted value satisfies its constraint. ExecInitMerge() uses the first surviving result relation's RETURNING list to initialize the root relation's projection. The attribute map is built with missing_ok = false, which rejects child-only columns even though the expressions originated from the parent and cannot reference them. The WITH CHECK OPTION code has the same issue. The attached patch passes missing_ok = true in both places and adds regression tests for RETURNING and row-level security. This failure occurs in the code added by b006bcd5310 (Fix MERGE into a plain inheritance parent table), so I've CC'd Dean. -- Thanks, Tender Wang
From 6db45c91edd5a8aa58e8565f92493b0a8397a592 Mon Sep 17 00:00:00 2001 From: Tender Wang <[email protected]> Date: Thu, 5 Nov 2026 12:28:44 +0800 Subject: [PATCH] Fix MERGE into inheritance parents with extra-column children. When an inheritance parent is excluded from the target scan, ExecInitMerge() may use a child's RETURNING and WITH CHECK OPTION lists to initialize the parent's INSERT processing. Building the attribute map fails if the child has columns absent from the parent, even though those columns cannot be referenced by the expressions, which originated from the parent. Allow missing columns when building these maps, so that unused child-only columns do not cause initialization to fail. --- src/backend/executor/nodeModifyTable.c | 8 +++--- src/test/regress/expected/merge.out | 37 ++++++++++++++++++++++++++ src/test/regress/sql/merge.sql | 33 +++++++++++++++++++++++ 3 files changed, 75 insertions(+), 3 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 558ceece9bb..bc73fdbb15e 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -4092,7 +4092,9 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) * Note that the planner does not build a withCheckOptionList or * returningList for the root relation, but as in ExecInitPartitionInfo, * we can use the first resultRelInfo entry as a reference to calculate - * the attno's for the root table. + * the attno's for the root table. Allow columns present only in the child + * when building the attribute map, since the expressions were derived from + * the root relation and cannot reference such columns. */ if (rootRelInfo != mtstate->resultRelInfo && rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && @@ -4126,7 +4128,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) part_attmap = build_attrmap_by_name(RelationGetDescr(rootRelation), RelationGetDescr(firstResultRel), - false); + true); wcoList = (List *) map_variable_attnos((Node *) wcoList, @@ -4170,7 +4172,7 @@ ExecInitMerge(ModifyTableState *mtstate, EState *estate) part_attmap = build_attrmap_by_name(RelationGetDescr(rootRelation), RelationGetDescr(firstResultRel), - false); + true); returningList = (List *) map_variable_attnos((Node *) returningList, diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 9cb1d87066a..21f80f68846 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -2773,6 +2773,43 @@ RETURNING merge_action(), m.*; INSERT | 0 | 01-18-2007 | 25 | 200 (1 row) +-- A pruned inheritance parent still needs its INSERT RETURNING projection. +-- The first surviving child may have columns absent from the parent. +BEGIN; +SET LOCAL constraint_exclusion = on; +ALTER TABLE measurement_y2007m01 ADD COLUMN extra integer; +ALTER TABLE measurement DISABLE ROW LEVEL SECURITY; +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, 25, 100) +RETURNING m.city_id, m.peaktemp; + city_id | peaktemp +---------+---------- + 0 | 25 +(1 row) + +ROLLBACK; +-- Exercise the corresponding WITH CHECK OPTION attribute map as well. +BEGIN; +SET LOCAL constraint_exclusion = on; +ALTER TABLE measurement_y2007m01 ADD COLUMN extra integer; +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, 25, 100); +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, NULL, 100); -- should fail +ERROR: new row violates row-level security policy for table "measurement" +ROLLBACK; DROP TABLE measurement, new_measurement CASCADE; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table measurement_y2006m02 diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 2660b19f238..b8cccd414b6 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1771,6 +1771,39 @@ WHEN NOT MATCHED THEN INSERT VALUES (city_id - 1, logdate, 25, 200) RETURNING merge_action(), m.*; +-- A pruned inheritance parent still needs its INSERT RETURNING projection. +-- The first surviving child may have columns absent from the parent. +BEGIN; +SET LOCAL constraint_exclusion = on; +ALTER TABLE measurement_y2007m01 ADD COLUMN extra integer; +ALTER TABLE measurement DISABLE ROW LEVEL SECURITY; +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, 25, 100) +RETURNING m.city_id, m.peaktemp; +ROLLBACK; + +-- Exercise the corresponding WITH CHECK OPTION attribute map as well. +BEGIN; +SET LOCAL constraint_exclusion = on; +ALTER TABLE measurement_y2007m01 ADD COLUMN extra integer; +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, 25, 100); +MERGE INTO measurement m +USING (VALUES (1, '2007-01-19'::date)) nm(city_id, logdate) +ON m.city_id = nm.city_id AND m.logdate = nm.logdate +WHEN NOT MATCHED THEN + INSERT (city_id, logdate, peaktemp, unitsales) + VALUES (0, nm.logdate, NULL, 100); -- should fail +ROLLBACK; + DROP TABLE measurement, new_measurement CASCADE; DROP FUNCTION measurement_insert_trigger(); -- 2.43.0
