Skip to content

Commit a90bdd7

Browse files
alvherrealvherre
committed
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 <exclusion@gmail.com> Reviewed-by: Tender Wang <tndrwang@gmail.com> Discussion: https://postgr.es/m/18541-628a61bc267cd2d3@postgresql.org
1 parent 498ee9e commit a90bdd7

File tree

3 files changed

+51
-0
lines changed

3 files changed

+51
-0
lines changed

‎src/backend/commands/tablecmds.c

+17
Original file line numberDiff line numberDiff line change
@@ -10759,6 +10759,23 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel)
1075910759
{
1076010760
ForeignKeyCacheInfo *fk = lfirst(cell);
1076110761

10762+
/*
10763+
* Refuse to attach a table as partition that this partitioned table
10764+
* already has a foreign key to. This isn't useful schema, which is
10765+
* proven by the fact that there have been no user complaints that
10766+
* it's already impossible to achieve this in the opposite direction,
10767+
* i.e., creating a foreign key that references a partition. This
10768+
* restriction allows us to dodge some complexities around
10769+
* pg_constraint and pg_trigger row creations that would be needed
10770+
* during ATTACH/DETACH for this kind of relationship.
10771+
*/
10772+
if (fk->confrelid == RelationGetRelid(partRel))
10773+
ereport(ERROR,
10774+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
10775+
errmsg("can't attach table \"%s\" as a partition which is referenced by foreign key \"%s\"",
10776+
RelationGetRelationName(partRel),
10777+
get_constraint_name(fk->conoid))));
10778+
1076210779
clone = lappend_oid(clone, fk->conoid);
1076310780
}
1076410781

‎src/test/regress/expected/foreign_key.out

+17
Original file line numberDiff line numberDiff line change
@@ -1967,6 +1967,23 @@ INSERT INTO fk_notpartitioned_pk VALUES (1600, 601), (1600, 1601);
19671967
ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
19681968
FOR VALUES IN (1600);
19691969
-- leave these tables around intentionally
1970+
-- Verify that attaching a table that's referenced by an existing FK
1971+
-- in the parent throws an error
1972+
CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY);
1973+
CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_6) PARTITION BY LIST (a);
1974+
ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1975+
ERROR: can't attach table "fk_partitioned_pk_6" as a partition which is referenced by foreign key "fk_partitioned_fk_6_a_fkey"
1976+
DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
1977+
-- This case is similar to above, but the referenced relation is one level
1978+
-- lower in the hierarchy. This one fails in a different way as the above,
1979+
-- because we don't bother to protect against this case explicitly. If the
1980+
-- current error stops happening, we'll need to add a better protection.
1981+
CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY) PARTITION BY list (a);
1982+
CREATE TABLE fk_partitioned_pk_61 PARTITION OF fk_partitioned_pk_6 FOR VALUES IN (1);
1983+
CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_61) PARTITION BY LIST (a);
1984+
ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1985+
ERROR: cannot ALTER TABLE "fk_partitioned_pk_61" because it is being used by active queries in this session
1986+
DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
19701987
-- test the case when the referenced table is owned by a different user
19711988
create role regress_other_partitioned_fk_owner;
19721989
grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner;

‎src/test/regress/sql/foreign_key.sql

+17
Original file line numberDiff line numberDiff line change
@@ -1417,6 +1417,23 @@ ALTER TABLE fk_partitioned_fk ATTACH PARTITION fk_partitioned_fk_2
14171417

14181418
-- leave these tables around intentionally
14191419

1420+
-- Verify that attaching a table that's referenced by an existing FK
1421+
-- in the parent throws an error
1422+
CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY);
1423+
CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_6) PARTITION BY LIST (a);
1424+
ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1425+
DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
1426+
1427+
-- This case is similar to above, but the referenced relation is one level
1428+
-- lower in the hierarchy. This one fails in a different way as the above,
1429+
-- because we don't bother to protect against this case explicitly. If the
1430+
-- current error stops happening, we'll need to add a better protection.
1431+
CREATE TABLE fk_partitioned_pk_6 (a int PRIMARY KEY) PARTITION BY list (a);
1432+
CREATE TABLE fk_partitioned_pk_61 PARTITION OF fk_partitioned_pk_6 FOR VALUES IN (1);
1433+
CREATE TABLE fk_partitioned_fk_6 (a int REFERENCES fk_partitioned_pk_61) PARTITION BY LIST (a);
1434+
ALTER TABLE fk_partitioned_fk_6 ATTACH PARTITION fk_partitioned_pk_6 FOR VALUES IN (1);
1435+
DROP TABLE fk_partitioned_pk_6, fk_partitioned_fk_6;
1436+
14201437
-- test the case when the referenced table is owned by a different user
14211438
create role regress_other_partitioned_fk_owner;
14221439
grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner;

0 commit comments

Comments
 (0)