From 8c0944ac878d3d3e433da8feb2f7e134be8353f3 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Thu, 8 Aug 2024 19:35:13 -0400 Subject: Refuse ATTACH of a table referenced by a foreign key Trying to attach a table as a partition which is already on the referenced side of a foreign key on the partitioned table that it is being attached to, leads to strange behavior: we try to clone the foreign key from the parent to the partition, but this new FK points to the partition itself, and the mix of pg_constraint rows and triggers doesn't behave well. Rather than trying to untangle the mess (which might be possible given sufficient time), I opted to forbid the ATTACH. This doesn't seem a problematic restriction, given that we already fail to create the foreign key if you do it the other way around, that is, having the partition first and the FK second. Backpatch to all supported branches. Reported-by: Alexander Lakhin Reviewed-by: Tender Wang Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org --- src/backend/commands/tablecmds.c | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) (limited to 'src/backend/commands/tablecmds.c') diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 8e3e3919f75..44629fb52f4 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10318,6 +10318,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); } -- cgit v1.2.3