From 955016311602af5fb8a8175d191df6bd155dc266 Mon Sep 17 00:00:00 2001 From: Jehan-Guillaume de Rorthais Date: Wed, 5 Feb 2020 23:56:43 +0100 Subject: [PATCH] Fix FK violation when referencing a multi-level partitioned table. On a multi-level partioned table, when adding a partition not connected to the root table, foreign key constraints referencing this table were not cloned to the new partition. This bug leads to FK contraint violation. When looking for constraints referencing new partition's parent table in pg_constraint, original code was discarding any constraints referencing a parent constraint to avoid dupplicate constraints with the ones cloned from top-level ones. However, no code was adding the top-level constraint. This patch implement what was actually explained as comment in the begin of the CloneFkReferenced function, but not done in code. Author: Jehan-Guillaume de Rorthais --- src/backend/commands/tablecmds.c | 9 +++++---- src/test/regress/expected/foreign_key.out | 24 +++++++++++++++++++++++ src/test/regress/sql/foreign_key.sql | 18 +++++++++++++++++ 3 files changed, 47 insertions(+), 4 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f599393473..22f6806b2f 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8984,10 +8984,6 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) { Form_pg_constraint constrForm = (Form_pg_constraint) GETSTRUCT(tuple); - /* Only try to clone the top-level constraint; skip child ones. */ - if (constrForm->conparentid != InvalidOid) - continue; - clone = lappend_oid(clone, constrForm->oid); } systable_endscan(scan); @@ -9016,6 +9012,11 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) elog(ERROR, "cache lookup failed for constraint %u", constrOid); constrForm = (Form_pg_constraint) GETSTRUCT(tuple); + if (list_member_oid(clone, constrForm->conparentid)) { + ReleaseSysCache(tuple); + continue; + } + /* * Because we're only expanding the key space at the referenced side, * we don't need to prevent any operation in the referencing table, so diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 9f2dda499e..9e1d749601 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2444,3 +2444,27 @@ DROP SCHEMA fkpart8 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table fkpart8.tbl1 drop cascades to table fkpart8.tbl2 +-- ensure FK referencing a multi-level partitioned table are +-- enforce reference to sub-children. +CREATE SCHEMA fkpart9 + CREATE TABLE pk (a INT PRIMARY KEY) PARTITION BY RANGE (a) + CREATE TABLE fk ( + fk_a INT REFERENCES pk(a) ON DELETE CASCADE + ) + CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (30) TO (50) PARTITION BY RANGE (a) + CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES FROM (30) TO (40); +INSERT INTO fkpart9.pk VALUES (35); +INSERT INTO fkpart9.fk VALUES (35); +DELETE FROM fkpart9.pk WHERE a=35; +SELECT fk.fk_a, pk.a +FROM fkpart9.fk +LEFT JOIN fkpart9.pk ON fk.fk_a = pk.a +WHERE fk.fk_a=35; + fk_a | a +------+--- +(0 rows) + +DROP SCHEMA fkpart9 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table fkpart9.pk +drop cascades to table fkpart9.fk diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 6f94bf8885..b03a6670a2 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1722,3 +1722,21 @@ INSERT INTO fkpart8.tbl2 VALUES(1); ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey; COMMIT; DROP SCHEMA fkpart8 CASCADE; + +-- ensure FK referencing a multi-level partitioned table are +-- enforce reference to sub-children. +CREATE SCHEMA fkpart9 + CREATE TABLE pk (a INT PRIMARY KEY) PARTITION BY RANGE (a) + CREATE TABLE fk ( + fk_a INT REFERENCES pk(a) ON DELETE CASCADE + ) + CREATE TABLE pk1 PARTITION OF pk FOR VALUES FROM (30) TO (50) PARTITION BY RANGE (a) + CREATE TABLE pk11 PARTITION OF pk1 FOR VALUES FROM (30) TO (40); +INSERT INTO fkpart9.pk VALUES (35); +INSERT INTO fkpart9.fk VALUES (35); +DELETE FROM fkpart9.pk WHERE a=35; +SELECT fk.fk_a, pk.a +FROM fkpart9.fk +LEFT JOIN fkpart9.pk ON fk.fk_a = pk.a +WHERE fk.fk_a=35; +DROP SCHEMA fkpart9 CASCADE; -- 2.20.1