| From: | Tender Wang <tndrwang(at)gmail(dot)com> |
|---|---|
| To: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Cc: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
| Subject: | MERGE fails with RETURNING when an excluded inheritance parent has an extra-column child |
| Date: | 2026-11-05 05:01:50 |
| Message-ID: | CAHewXNm-SQqJ6UeT2sTb4q-CUVyA3NN7tKgcSmPRjYFoZQvdGA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Fix-MERGE-into-inheritance-parents-with-extra-column.patch | text/plain | 6.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | shihao zhong | 2026-11-05 04:54:26 | Re: Partitioned index marked valid while a partition is pending detach |