From 2adf11c0de3cbfaf25608c43f67ec5694ab5121f Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 25 Feb 2025 19:30:29 -0800 Subject: [PATCH v49 1/6] Remove support for temporal RESTRICT foreign keys It isn't clear how these should behave, so let's wait to implement them until we are sure how to do it. Discussion: https://postgr.es/m/e773bc11-4ac1-40de-bb91-814e02f05b6d%40eisentraut.org --- src/backend/commands/tablecmds.c | 6 +- .../regress/expected/without_overlaps.out | 209 +----------------- src/test/regress/sql/without_overlaps.sql | 194 ---------------- 3 files changed, 7 insertions(+), 402 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index ce7d115667e..c50c41a5291 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10034,7 +10034,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, */ if (fkconstraint->fk_with_period) { - if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE || + if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_RESTRICT || + fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE || fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL || fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT) ereport(ERROR, @@ -10042,7 +10043,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, errmsg("unsupported %s action for foreign key constraint using PERIOD", "ON UPDATE")); - if (fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE || + if (fkconstraint->fk_del_action == FKCONSTR_ACTION_RESTRICT || + fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE || fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL || fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT) ereport(ERROR, diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index fcadcd8d6e5..e38472079cc 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -1770,55 +1770,11 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- A PK update sliding the edge between two referenced rows: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[6,7)', daterange('2018-01-01', '2018-02-01')), - ('[6,7)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[4,5)', daterange('2018-01-15', '2018-02-15'), '[6,7)'); -UPDATE temporal_rng - SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05') - WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END - WHERE id = '[6,7)'; -ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng". --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng". -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng". --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1860,27 +1816,7 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE RESTRICT; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng". -ROLLBACK; --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD -- -- test ON UPDATE/DELETE options -- @@ -1888,7 +1824,6 @@ DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01 INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -1898,7 +1833,6 @@ ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -1910,7 +1844,6 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -2289,51 +2222,11 @@ ALTER TABLE temporal_fk_mltrng2mltrng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- A PK update sliding the edge between two referenced rows: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[6,7)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[6,7)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[4,5)', datemultirange(daterange('2018-01-15', '2018-02-15')), '[6,7)'); -UPDATE temporal_mltrng - SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05')) - WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END - WHERE id = '[6,7)'; -ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng". --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng". -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng". +ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -2361,34 +2254,6 @@ COMMIT; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". -- --- test FK referenced deletes RESTRICT --- -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON DELETE RESTRICT; -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng". -ROLLBACK; --- -- FK between partitioned tables: ranges -- CREATE TABLE temporal_partitioned_rng ( @@ -2469,40 +2334,6 @@ DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- --- partitioned FK referenced updates RESTRICT --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; -ALTER TABLE temporal_partitioned_fk_rng2rng - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". --- --- partitioned FK referenced deletes RESTRICT --- -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates RESTRICT setting of foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng" -DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is referenced from table "temporal_partitioned_fk_rng2rng". --- -- partitioned FK referenced updates CASCADE -- ALTER TABLE temporal_partitioned_fk_rng2rng @@ -2625,40 +2456,6 @@ DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemu ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_partitioned_fk_mltrng2mltrng". -- --- partitioned FK referenced updates RESTRICT --- -TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; -ALTER TABLE temporal_partitioned_fk_mltrng2mltrng - DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_partitioned_fk_mltrng2mltrng - ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_mltrng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2016-01-01', '2016-02-01'))); -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')) WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- should fail: -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_partitioned_fk_mltrng2mltrng". --- --- partitioned FK referenced deletes RESTRICT --- -TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- should fail: -DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ERROR: update or delete on table "tp1" violates RESTRICT setting of foreign key constraint "temporal_partitioned_fk_mltrng2mltrng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_mltrng2mltrng" -DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_partitioned_fk_mltrng2mltrng". --- -- partitioned FK referenced updates CASCADE -- ALTER TABLE temporal_partitioned_fk_mltrng2mltrng diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index f1d8bc2bcb1..4aaca242bbe 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -1308,51 +1308,12 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_rng WHERE id = '[5,6)'; -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- A PK update sliding the edge between two referenced rows: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[6,7)', daterange('2018-01-01', '2018-02-01')), - ('[6,7)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[4,5)', daterange('2018-01-15', '2018-02-15'), '[6,7)'); -UPDATE temporal_rng - SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05') - WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END - WHERE id = '[6,7)'; --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_rng SET id = '[7,8)' - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- then delete the objecting FK record and the same PK update succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_rng, temporal_fk_rng2rng; -ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1393,25 +1354,6 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE RESTRICT; -INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -DELETE FROM temporal_rng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_rng (id, valid_at) VALUES - ('[5,6)', daterange('2018-01-01', '2018-02-01')), - ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES - ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_rng2rng - ALTER CONSTRAINT temporal_fk_rng2rng_fk - DEFERRABLE INITIALLY DEFERRED; - DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ROLLBACK; --- then delete the objecting FK record and the same PK delete succeeds: -DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; -DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -- -- test ON UPDATE/DELETE options @@ -1421,7 +1363,6 @@ DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01 INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -1431,7 +1372,6 @@ ALTER TABLE temporal_fk_rng2rng INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); ALTER TABLE temporal_fk_rng2rng - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -1443,7 +1383,6 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', - DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng @@ -1792,48 +1731,12 @@ ALTER TABLE temporal_fk_mltrng2mltrng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ON UPDATE RESTRICT; --- a PK update that succeeds because the numeric id isn't referenced: -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) WHERE id = '[5,6)'; --- a PK update that succeeds even though the numeric id is referenced because the range isn't: -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- A PK update sliding the edge between two referenced rows: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[6,7)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[6,7)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES - ('[4,5)', datemultirange(daterange('2018-01-15', '2018-02-15')), '[6,7)'); -UPDATE temporal_mltrng - SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05')) - WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END - WHERE id = '[6,7)'; --- a PK update that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ROLLBACK; --- changing the scalar part fails: -UPDATE temporal_mltrng SET id = '[7,8)' - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1858,35 +1761,6 @@ BEGIN; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; --- --- test FK referenced deletes RESTRICT --- - -TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; -ALTER TABLE temporal_fk_mltrng2mltrng - DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_fk_mltrng2mltrng - ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_mltrng (id, PERIOD valid_at) - ON DELETE RESTRICT; -INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --- a PK delete that succeeds even though the numeric id is referenced because the range isn't: -INSERT INTO temporal_mltrng (id, valid_at) VALUES - ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), - ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- a PK delete that fails because both are referenced (even before commit): -BEGIN; - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; - - DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -ROLLBACK; - -- -- FK between partitioned tables: ranges -- @@ -1968,40 +1842,6 @@ DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange -- should fail: DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); --- --- partitioned FK referenced updates RESTRICT --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -ALTER TABLE temporal_partitioned_fk_rng2rng - DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk; -ALTER TABLE temporal_partitioned_fk_rng2rng - ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_rng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01-01', '2016-02-01')); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') - WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - --- --- partitioned FK referenced deletes RESTRICT --- - -TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')); -INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01')); -INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); --- should fail: -DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); - -- -- partitioned FK referenced updates CASCADE -- @@ -2132,40 +1972,6 @@ DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemu -- should fail: DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); --- --- partitioned FK referenced updates RESTRICT --- - -TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; -ALTER TABLE temporal_partitioned_fk_mltrng2mltrng - DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk; -ALTER TABLE temporal_partitioned_fk_mltrng2mltrng - ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk - FOREIGN KEY (parent_id, PERIOD valid_at) - REFERENCES temporal_partitioned_mltrng - ON DELETE RESTRICT; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2016-01-01', '2016-02-01'))); -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')) WHERE id = '[5,6)'; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- should fail: -UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) - WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); - --- --- partitioned FK referenced deletes RESTRICT --- - -TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))); -INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); -INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); -DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); --- should fail: -DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); - -- -- partitioned FK referenced updates CASCADE -- -- 2.39.5