aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--src/backend/commands/tablecmds.c21
-rw-r--r--src/test/regress/expected/foreign_key.out112
-rw-r--r--src/test/regress/expected/triggers.out24
-rw-r--r--src/test/regress/sql/foreign_key.sql38
4 files changed, 126 insertions, 69 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6cbc0b196d6..53dc3296c81 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9577,14 +9577,14 @@ CloneForeignKeyConstraints(List **wqueue, Relation parentRel,
Assert(parentRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
/*
- * Clone constraints for which the parent is on the referenced side.
+ * First, clone constraints where the parent is on the referencing side.
*/
- CloneFkReferenced(parentRel, partitionRel);
+ CloneFkReferencing(wqueue, parentRel, partitionRel);
/*
- * Now clone constraints where the parent is on the referencing side.
+ * Clone constraints for which the parent is on the referenced side.
*/
- CloneFkReferencing(wqueue, parentRel, partitionRel);
+ CloneFkReferenced(parentRel, partitionRel);
}
/*
@@ -9595,8 +9595,6 @@ CloneForeignKeyConstraints(List **wqueue, Relation parentRel,
* clone those constraints to the given partition. This is to be called
* when the partition is being created or attached.
*
- * This ignores self-referencing FKs; those are handled by CloneFkReferencing.
- *
* This recurses to partitions, if the relation being attached is partitioned.
* Recursion is done by calling addFkRecurseReferenced.
*/
@@ -9673,17 +9671,6 @@ CloneFkReferenced(Relation parentRel, Relation partitionRel)
}
/*
- * Don't clone self-referencing foreign keys, which can be in the
- * partitioned table or in the partition-to-be.
- */
- if (constrForm->conrelid == RelationGetRelid(parentRel) ||
- constrForm->conrelid == RelationGetRelid(partitionRel))
- {
- 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 496361392e9..2deb0fb68b0 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -1970,58 +1970,98 @@ CREATE TABLE part33_self_fk (
id_abc bigint
);
ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);
-SELECT cr.relname, co.conname, co.contype, co.convalidated,
+-- verify that this constraint works
+INSERT INTO parted_self_fk VALUES (1, NULL), (2, NULL), (3, NULL);
+INSERT INTO parted_self_fk VALUES (10, 1), (11, 2), (12, 3) RETURNING tableoid::regclass;
+ tableoid
+---------------
+ part2_self_fk
+ part2_self_fk
+ part2_self_fk
+(3 rows)
+
+INSERT INTO parted_self_fk VALUES (4, 5); -- error: referenced doesn't exist
+ERROR: insert or update on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"
+DETAIL: Key (id_abc)=(5) is not present in table "parted_self_fk".
+DELETE FROM parted_self_fk WHERE id = 1 RETURNING *; -- error: reference remains
+ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey1" on table "parted_self_fk"
+DETAIL: Key (id)=(1) is still referenced from table "parted_self_fk".
+SELECT cr.relname, co.conname, co.convalidated,
p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
FROM pg_constraint co
JOIN pg_class cr ON cr.oid = co.conrelid
LEFT JOIN pg_class cf ON cf.oid = co.confrelid
LEFT JOIN pg_constraint p ON p.oid = co.conparentid
-WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
-ORDER BY co.contype, cr.relname, co.conname, p.conname;
- relname | conname | contype | convalidated | conparent | convalidated | foreignrel
-----------------+----------------------------+---------+--------------+----------------------------+--------------+----------------
- part1_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part2_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part32_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk
- part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t |
- part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t |
- part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- parted_self_fk | parted_self_fk_pkey | p | t | | |
-(12 rows)
+WHERE co.contype = 'f' AND
+ cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
+ORDER BY cr.relname, co.conname, p.conname;
+ relname | conname | convalidated | conparent | convalidated | foreignrel
+----------------+-----------------------------+--------------+-----------------------------+--------------+----------------
+ part1_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part2_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part32_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part33_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part3_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey | t | | | parted_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey1 | t | parted_self_fk_id_abc_fkey | t | part1_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey2 | t | parted_self_fk_id_abc_fkey | t | part2_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey3 | t | parted_self_fk_id_abc_fkey | t | part3_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey4 | t | parted_self_fk_id_abc_fkey3 | t | part32_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey5 | t | parted_self_fk_id_abc_fkey3 | t | part33_self_fk
+(11 rows)
-- detach and re-attach multiple times just to ensure everything is kosher
ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
+\d+ part2_self_fk
+ Table "public.part2_self_fk"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+--------+-----------+----------+---------+---------+--------------+-------------
+ id | bigint | | not null | | plain | |
+ id_abc | bigint | | | | plain | |
+Indexes:
+ "part2_self_fk_pkey" PRIMARY KEY, btree (id)
+
+INSERT INTO part2_self_fk VALUES (16, 9); -- good, but it'll prevent the attach below
+ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
+ERROR: insert or update on table "part2_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"
+DETAIL: Key (id_abc)=(9) is not present in table "parted_self_fk".
+DELETE FROM part2_self_fk WHERE id = 16;
ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
+INSERT INTO parted_self_fk VALUES (16, 9); -- error: referenced doesn't exist
+ERROR: insert or update on table "part2_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey"
+DETAIL: Key (id_abc)=(9) is not present in table "parted_self_fk".
+DELETE FROM parted_self_fk WHERE id = 3 RETURNING *; -- error: reference remains
+ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "parted_self_fk_id_abc_fkey1" on table "parted_self_fk"
+DETAIL: Key (id)=(3) is still referenced from table "parted_self_fk".
ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
-SELECT cr.relname, co.conname, co.contype, co.convalidated,
+ALTER TABLE parted_self_fk DETACH PARTITION part3_self_fk;
+ALTER TABLE parted_self_fk ATTACH PARTITION part3_self_fk FOR VALUES FROM (30) TO (40);
+ALTER TABLE part3_self_fk DETACH PARTITION part33_self_fk;
+ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);
+SELECT cr.relname, co.conname, co.convalidated,
p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
FROM pg_constraint co
JOIN pg_class cr ON cr.oid = co.conrelid
LEFT JOIN pg_class cf ON cf.oid = co.confrelid
LEFT JOIN pg_constraint p ON p.oid = co.conparentid
-WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
-ORDER BY co.contype, cr.relname, co.conname, p.conname;
- relname | conname | contype | convalidated | conparent | convalidated | foreignrel
-----------------+----------------------------+---------+--------------+----------------------------+--------------+----------------
- part1_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part2_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part32_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part33_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- part3_self_fk | parted_self_fk_id_abc_fkey | f | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
- parted_self_fk | parted_self_fk_id_abc_fkey | f | t | | | parted_self_fk
- part1_self_fk | part1_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- part2_self_fk | part2_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- part32_self_fk | part32_self_fk_pkey | p | t | part3_self_fk_pkey | t |
- part33_self_fk | part33_self_fk_pkey | p | t | part3_self_fk_pkey | t |
- part3_self_fk | part3_self_fk_pkey | p | t | parted_self_fk_pkey | t |
- parted_self_fk | parted_self_fk_pkey | p | t | | |
-(12 rows)
+WHERE co.contype = 'f' AND
+ cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
+ORDER BY cr.relname, co.conname, p.conname;
+ relname | conname | convalidated | conparent | convalidated | foreignrel
+----------------+-----------------------------+--------------+-----------------------------+--------------+----------------
+ part1_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part2_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part32_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part33_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ part3_self_fk | parted_self_fk_id_abc_fkey | t | parted_self_fk_id_abc_fkey | t | parted_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey | t | | | parted_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey1 | t | parted_self_fk_id_abc_fkey | t | part1_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey2 | t | parted_self_fk_id_abc_fkey | t | part2_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey3 | t | parted_self_fk_id_abc_fkey | t | part3_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey4 | t | parted_self_fk_id_abc_fkey3 | t | part32_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey5 | t | parted_self_fk_id_abc_fkey3 | t | part33_self_fk
+(11 rows)
-- Leave this table around, for pg_upgrade/pg_dump tests
-- Test creating a constraint at the parent that already exists in partitions.
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index a3211056481..827816d24a9 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2713,11 +2713,13 @@ select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
tgfoid::regproc, tgenabled
from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass)
order by tgrelid::regclass::text, tgfoid;
- tgrelid | tgname | tgfoid | tgenabled
----------+-------------------------+---------------------+-----------
- child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
- child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
-(2 rows)
+ tgrelid | tgname | tgfoid | tgenabled
+---------+-------------------------+------------------------+-----------
+ child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
+ child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O
+(4 rows)
-- Before v15, this has no effect because parent has no triggers:
alter table parent disable trigger all;
@@ -2725,11 +2727,13 @@ select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
tgfoid::regproc, tgenabled
from pg_trigger where tgrelid in ('parent'::regclass, 'child1'::regclass)
order by tgrelid::regclass::text, tgfoid;
- tgrelid | tgname | tgfoid | tgenabled
----------+-------------------------+---------------------+-----------
- child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
- child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
-(2 rows)
+ tgrelid | tgname | tgfoid | tgenabled
+---------+-------------------------+------------------------+-----------
+ child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
+ child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O
+(4 rows)
drop table parent, child1;
-- Verify that firing state propagates correctly on creation, too
diff --git a/src/test/regress/sql/foreign_key.sql b/src/test/regress/sql/foreign_key.sql
index 0d0a52375dd..6d33f4c2042 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1438,29 +1438,55 @@ CREATE TABLE part33_self_fk (
);
ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);
-SELECT cr.relname, co.conname, co.contype, co.convalidated,
+-- verify that this constraint works
+INSERT INTO parted_self_fk VALUES (1, NULL), (2, NULL), (3, NULL);
+INSERT INTO parted_self_fk VALUES (10, 1), (11, 2), (12, 3) RETURNING tableoid::regclass;
+
+INSERT INTO parted_self_fk VALUES (4, 5); -- error: referenced doesn't exist
+DELETE FROM parted_self_fk WHERE id = 1 RETURNING *; -- error: reference remains
+
+SELECT cr.relname, co.conname, co.convalidated,
p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
FROM pg_constraint co
JOIN pg_class cr ON cr.oid = co.conrelid
LEFT JOIN pg_class cf ON cf.oid = co.confrelid
LEFT JOIN pg_constraint p ON p.oid = co.conparentid
-WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
-ORDER BY co.contype, cr.relname, co.conname, p.conname;
+WHERE co.contype = 'f' AND
+ cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
+ORDER BY cr.relname, co.conname, p.conname;
-- detach and re-attach multiple times just to ensure everything is kosher
ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
+
+\d+ part2_self_fk
+
+INSERT INTO part2_self_fk VALUES (16, 9); -- good, but it'll prevent the attach below
+ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
+
+DELETE FROM part2_self_fk WHERE id = 16;
ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
+
+INSERT INTO parted_self_fk VALUES (16, 9); -- error: referenced doesn't exist
+DELETE FROM parted_self_fk WHERE id = 3 RETURNING *; -- error: reference remains
+
ALTER TABLE parted_self_fk DETACH PARTITION part2_self_fk;
ALTER TABLE parted_self_fk ATTACH PARTITION part2_self_fk FOR VALUES FROM (10) TO (20);
-SELECT cr.relname, co.conname, co.contype, co.convalidated,
+ALTER TABLE parted_self_fk DETACH PARTITION part3_self_fk;
+ALTER TABLE parted_self_fk ATTACH PARTITION part3_self_fk FOR VALUES FROM (30) TO (40);
+
+ALTER TABLE part3_self_fk DETACH PARTITION part33_self_fk;
+ALTER TABLE part3_self_fk ATTACH PARTITION part33_self_fk FOR VALUES FROM (30) TO (40);
+
+SELECT cr.relname, co.conname, co.convalidated,
p.conname AS conparent, p.convalidated, cf.relname AS foreignrel
FROM pg_constraint co
JOIN pg_class cr ON cr.oid = co.conrelid
LEFT JOIN pg_class cf ON cf.oid = co.confrelid
LEFT JOIN pg_constraint p ON p.oid = co.conparentid
-WHERE cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
-ORDER BY co.contype, cr.relname, co.conname, p.conname;
+WHERE co.contype = 'f' AND
+ cr.oid IN (SELECT relid FROM pg_partition_tree('parted_self_fk'))
+ORDER BY cr.relname, co.conname, p.conname;
-- Leave this table around, for pg_upgrade/pg_dump tests
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-19 13:23:46 +0000