summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/foreign_key.out104
-rw-r--r--src/test/regress/expected/triggers.out8
-rw-r--r--src/test/regress/sql/foreign_key.sql35
3 files changed, 103 insertions, 44 deletions
diff --git a/src/test/regress/expected/foreign_key.out b/src/test/regress/expected/foreign_key.out
index cf188450ba3..3a105f6306d 100644
--- a/src/test/regress/expected/foreign_key.out
+++ b/src/test/regress/expected/foreign_key.out
@@ -2042,58 +2042,90 @@ 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;
+INSERT INTO part2_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 = 2 RETURNING *; -- error: reference remains
+ERROR: update or delete on table "part1_self_fk" violates foreign key constraint "part2_self_fk_id_abc_fkey" on table "part2_self_fk"
+DETAIL: Key (id)=(2) is still referenced from table "part2_self_fk".
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 eaeb0608030..7125fee3f85 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2796,11 +2796,13 @@ select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
---------+-------------------------+------------------------+-----------
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
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | O
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | O
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | O
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | O
-(6 rows)
+(8 rows)
alter table parent disable trigger all;
select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
@@ -2811,11 +2813,13 @@ select tgrelid::regclass, rtrim(tgname, '0123456789') as tgname,
---------+-------------------------+------------------------+-----------
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D
child1 | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D
+ child1 | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_ins" | D
parent | RI_ConstraintTrigger_c_ | "RI_FKey_check_upd" | D
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_del" | D
parent | RI_ConstraintTrigger_a_ | "RI_FKey_noaction_upd" | D
-(6 rows)
+(8 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 c0125823256..442a6cbc093 100644
--- a/src/test/regress/sql/foreign_key.sql
+++ b/src/test/regress/sql/foreign_key.sql
@@ -1490,29 +1490,52 @@ 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;
+
+INSERT INTO part2_self_fk VALUES (16, 9); -- error: referenced doesn't exist
+DELETE FROM parted_self_fk WHERE id = 2 RETURNING *; -- error: reference remains
+
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.46.0) at 2025-10-05 17:21:07 +0000