diff options
Diffstat (limited to 'src/test/regress/sql/foreign_key.sql')
| -rw-r--r-- | src/test/regress/sql/foreign_key.sql | 38 |
1 files changed, 32 insertions, 6 deletions
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 |