diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/foreign_key.out | 91 | ||||
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 56 |
2 files changed, 147 insertions, 0 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out index a4ec915e153..5eecdf4af2b 100644 --- a/src/test/regress/expected/foreign_key.out +++ b/src/test/regress/expected/foreign_key.out @@ -2647,3 +2647,94 @@ DROP SCHEMA fkpart9 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table fkpart9.pk drop cascades to table fkpart9.fk +-- When a table is attached as partition to a partitioned table that has +-- a foreign key to another partitioned table, it acquires a clone of the +-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer +-- versions make it a standalone constraint.) +CREATE SCHEMA fkpart12 + CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id) + CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd) + CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1) + CREATE TABLE fk_p_1_2 PARTITION OF fk_p_1 FOR VALUES IN (2) + CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd) + CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1) + CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2) + CREATE TABLE fk_r_1 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) + CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id) + CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1) + CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL, + FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd) + ) PARTITION BY list (id); +SET search_path TO fkpart12; +INSERT INTO fk_p VALUES (1, 1); +ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); +ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2); +\d fk_r_2 + Partitioned table "fkpart12.fk_r_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + p_id | integer | | not null | + p_jd | integer | | not null | +Partition of: fk_r FOR VALUES IN (2) +Partition key: LIST (id) +Indexes: + "fk_r_2_pkey" PRIMARY KEY, btree (id) +Foreign-key constraints: + TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd) +Number of partitions: 1 (Use \d+ to list them.) + +INSERT INTO fk_r VALUES (1, 1, 1); +INSERT INTO fk_r VALUES (2, 2, 1); -- fails +ERROR: insert or update on table "fk_r_2_1" violates foreign key constraint "fk_r_p_id_p_jd_fkey" +DETAIL: Key (p_id, p_jd)=(2, 1) is not present in table "fk_p". +ALTER TABLE fk_r DETACH PARTITION fk_r_1; +ALTER TABLE fk_r DETACH PARTITION fk_r_2; +\d fk_r_2 + Partitioned table "fkpart12.fk_r_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + p_id | integer | | not null | + p_jd | integer | | not null | +Partition key: LIST (id) +Indexes: + "fk_r_2_pkey" PRIMARY KEY, btree (id) +Number of partitions: 1 (Use \d+ to list them.) + +INSERT INTO fk_r_1 VALUES (2, 1, 2); -- works: there's no FK anymore +DELETE FROM fk_p; -- works +ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); -- fails +ERROR: partition constraint of relation "fk_r_1" is violated by some row +INSERT INTO fk_r_2 VALUES (2, 2, 2); +INSERT INTO fk_p VALUES (2, 2); +ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2); +\d fk_r_2 + Partitioned table "fkpart12.fk_r_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + p_id | integer | | not null | + p_jd | integer | | not null | +Partition of: fk_r FOR VALUES IN (2) +Partition key: LIST (id) +Indexes: + "fk_r_2_pkey" PRIMARY KEY, btree (id) +Foreign-key constraints: + TABLE "fk_r" CONSTRAINT "fk_r_p_id_p_jd_fkey" FOREIGN KEY (p_id, p_jd) REFERENCES fk_p(id, jd) +Number of partitions: 1 (Use \d+ to list them.) + +DELETE FROM fk_p; -- fails +ERROR: update or delete on table "fk_p_2_2" violates foreign key constraint "fk_r_p_id_p_jd_fkey6" on table "fk_r" +DETAIL: Key (id, jd)=(2, 2) is still referenced from table "fk_r". +-- these should all fail +ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey; +ERROR: constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_1" does not exist +ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1; +ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey1" of relation "fk_r" +ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey; +ERROR: cannot drop inherited constraint "fk_r_p_id_p_jd_fkey" of relation "fk_r_2" +SET client_min_messages TO warning; +DROP SCHEMA fkpart12 CASCADE; +RESET client_min_messages; +RESET search_path; diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql index 9b23fc2d4f3..6b54400d27c 100644 --- a/src/test/regress/sql/foreign_key.sql +++ b/src/test/regress/sql/foreign_key.sql @@ -1886,3 +1886,59 @@ DELETE FROM fkpart9.pk WHERE a=35; SELECT * FROM fkpart9.pk; SELECT * FROM fkpart9.fk; DROP SCHEMA fkpart9 CASCADE; + +-- When a table is attached as partition to a partitioned table that has +-- a foreign key to another partitioned table, it acquires a clone of the +-- FK. Upon detach, Postgres 14 and earlier remove the foreign key (newer +-- versions make it a standalone constraint.) +CREATE SCHEMA fkpart12 + CREATE TABLE fk_p ( id int, jd int, PRIMARY KEY(id, jd)) PARTITION BY list (id) + CREATE TABLE fk_p_1 PARTITION OF fk_p FOR VALUES IN (1) PARTITION BY list (jd) + CREATE TABLE fk_p_1_1 PARTITION OF fk_p_1 FOR VALUES IN (1) + CREATE TABLE fk_p_1_2 PARTITION OF fk_p_1 FOR VALUES IN (2) + CREATE TABLE fk_p_2 PARTITION OF fk_p FOR VALUES IN (2) PARTITION BY list (jd) + CREATE TABLE fk_p_2_1 PARTITION OF fk_p_2 FOR VALUES IN (1) + CREATE TABLE fk_p_2_2 PARTITION OF fk_p_2 FOR VALUES IN (2) + CREATE TABLE fk_r_1 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) + CREATE TABLE fk_r_2 ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL) PARTITION BY list (id) + CREATE TABLE fk_r_2_1 PARTITION OF fk_r_2 FOR VALUES IN (2, 1) + CREATE TABLE fk_r ( id int PRIMARY KEY, p_id int NOT NULL, p_jd int NOT NULL, + FOREIGN KEY (p_id, p_jd) REFERENCES fk_p (id, jd) + ) PARTITION BY list (id); +SET search_path TO fkpart12; + +INSERT INTO fk_p VALUES (1, 1); + +ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); +ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2); + +\d fk_r_2 + +INSERT INTO fk_r VALUES (1, 1, 1); +INSERT INTO fk_r VALUES (2, 2, 1); -- fails + +ALTER TABLE fk_r DETACH PARTITION fk_r_1; +ALTER TABLE fk_r DETACH PARTITION fk_r_2; + +\d fk_r_2 + +INSERT INTO fk_r_1 VALUES (2, 1, 2); -- works: there's no FK anymore +DELETE FROM fk_p; -- works + +ALTER TABLE fk_r ATTACH PARTITION fk_r_1 FOR VALUES IN (1); -- fails + +INSERT INTO fk_r_2 VALUES (2, 2, 2); +INSERT INTO fk_p VALUES (2, 2); +ALTER TABLE fk_r ATTACH PARTITION fk_r_2 FOR VALUES IN (2); +\d fk_r_2 +DELETE FROM fk_p; -- fails + +-- these should all fail +ALTER TABLE fk_r_1 DROP CONSTRAINT fk_r_p_id_p_jd_fkey; +ALTER TABLE fk_r DROP CONSTRAINT fk_r_p_id_p_jd_fkey1; +ALTER TABLE fk_r_2 DROP CONSTRAINT fk_r_p_id_p_jd_fkey; + +SET client_min_messages TO warning; +DROP SCHEMA fkpart12 CASCADE; +RESET client_min_messages; +RESET search_path; |