diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/commands/tablecmds.c | 17 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 17 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 17 |
3 files changed, 51 insertions, 0 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1f94f4fdbbc..7a36db6af6d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10759,6 +10759,23 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) { ForeignKeyCacheInfo *fk = lfirst(cell); + /* + * Refuse to attach a table as partition that this partitioned table + * already has a foreign key to. This isn't useful schema, which is + * proven by the fact that there have been no user complaints that + * it's already impossible to achieve this in the opposite direction, + * i.e., creating a foreign key that references a partition. This + * restriction allows us to dodge some complexities around + * pg_constraint and pg_trigger row creations that would be needed + * during ATTACH/DETACH for this kind of relationship. + */ + if (fk->confrelid == RelationGetRelid(partRel)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("can't attach table \"%s\" as a partition which is referenced by foreign key \"%s\"", + RelationGetRelationName(partRel), + get_constraint_name(fk->conoid)))); + clone = lappend_oid(clone, fk->conoid); } diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index 46764bd9e3b..fed3d99b34f 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -1967,6 +1967,23 @@ INSERT INTO fk_notpartitioned_pk VALUES (1600, 601), (1600, 1601); ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 FOR VALUES IN (1600); -- leave these tables around intentionally +-- Verify that attaching a table that's referenced by an existing FK +-- in the parent throws an error +CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY); +CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_6) PARTITION BY LIST (a); +ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1); +ERROR: can't attach table "fk_partitioned_pk_6" as a partition which is referenced by foreign key "fk_partitioned_fk_6_a_fkey" +DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6; +-- This case is similar to above, but the referenced relation is one level +-- lower in the hierarchy. This one fails in a different way as the above, +-- because we don't bother to protect against this case explicitly. If the +-- current error stops happening, we'll need to add a better protection. +CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY) PARTITION BY list (a); +CREATE TABLE fk_partitioned_pk_61 PARTITION OF fk_partitioned_pk_6 FOR VALUES IN (1); +CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_61) PARTITION BY LIST (a); +ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1); +ERROR: cannot ALTER TABLE "fk_partitioned_pk_61" because it is being used by active queries in this session +DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6; -- test the case when the referenced table is owned by a different user create role regress_other_partitioned_fk_owner; grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index f5e09389997..d1aac5357f0 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1417,6 +1417,23 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2 -- leave these tables around intentionally +-- Verify that attaching a table that's referenced by an existing FK +-- in the parent throws an error +CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY); +CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_6) PARTITION BY LIST (a); +ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1); +DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6; + +-- This case is similar to above, but the referenced relation is one level +-- lower in the hierarchy. This one fails in a different way as the above, +-- because we don't bother to protect against this case explicitly. If the +-- current error stops happening, we'll need to add a better protection. +CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY) PARTITION BY list (a); +CREATE TABLE fk_partitioned_pk_61 PARTITION OF fk_partitioned_pk_6 FOR VALUES IN (1); +CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_61) PARTITION BY LIST (a); +ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1); +DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6; + -- test the case when the referenced table is owned by a different user create role regress_other_partitioned_fk_owner; grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner; |