diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/commands/tablecmds.c | 28 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 24 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 18 |
3 files changed, 59 insertions, 11 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f3d64c6a9ec..d07476d8ddf 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8549,13 +8549,13 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) List *clone = NIL; /* - * Search for any constraints where this partition is in the referenced - * side. However, we must ignore any constraint whose parent constraint - * is also going to be cloned, to avoid duplicates. So do it in two - * steps: first construct the list of constraints to clone, then go over - * that list cloning those whose parents are not in the list. (We must - * not rely on the parent being seen first, since the catalog scan could - * return children first.) + * Search for any constraints where this partition's parent is in the + * referenced side. However, we must not clone any constraint whose + * parent constraint is also going to be cloned, to avoid duplicates. So + * do it in two steps: first construct the list of constraints to clone, + * then go over that list cloning those whose parents are not in the list. + * (We must not rely on the parent being seen first, since the catalog + * scan could return children first.) */ pg_constraint = table_open(ConstraintRelationId, RowShareLock); ScanKeyInit(&key[0], @@ -8571,10 +8571,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); @@ -8605,6 +8601,16 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel) constrForm = (Form_pg_constraint) GETSTRUCT(tuple); /* + * As explained above: don't try to clone a constraint for which we're + * going to clone the parent. + */ + 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 * AccessShareLock suffices (assumes that dropping the constraint diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 9f2dda499ef..9e1d7496014 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 6f94bf88851..b03a6670a21 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; |