From 027e4367605ec7d428e1c7859286d9315fa7cd3f Mon Sep 17 00:00:00 2001 From: jian he Date: Thu, 21 Mar 2024 16:16:53 +0800 Subject: [PATCH v31 1/1] refactor temporal FKs with multiranges regression test --- .../regress/expected/without_overlaps.out | 127 +++++++++-------- src/test/regress/sql/without_overlaps.sql | 129 ++++++++++-------- 2 files changed, 140 insertions(+), 116 deletions(-) diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 47b4ff05..5e8fa6dc 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -1168,43 +1168,74 @@ ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign k DETAIL: Key (parent_id, valid_at)=([8,9), {[2018-01-02,2018-03-01)}) is not present in table "temporal_mltrng". -- ALTER FK DEFERRABLE BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES + ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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)'); - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. COMMIT; -- should fail here. -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". +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([5,6), {[2018-01-05,2018-01-10)}) is not present in table "temporal_mltrng". +-- if no reference, then update, delete, insert on referenced table will work. +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)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -- -- test FK referenced updates NO ACTION -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; --- 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)'; +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 UPDATE NO ACTION; +BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) +VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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 that fails because both are referenced: +-- a PK update that fails because both are referenced. +-- but we only fail at the end of transaction. 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 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". --- 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 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". +COMMIT; +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([5,6), {[2018-01-05,2018-01-10)}) is not present in table "temporal_mltrng". +-- +-- 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) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON UPDATE NO ACTION; +BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) + VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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'))); +-- a PK delete that fails because both are referenced: but will fail at the end of commmit. +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +COMMIT; --fail at here. +ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" +DETAIL: Key (parent_id, valid_at)=([5,6), {[2018-01-05,2018-01-10)}) is not present in table "temporal_mltrng". -- -- test FK referenced updates RESTRICT -- @@ -1216,50 +1247,27 @@ 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)'; +BEGIN; 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)'); +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 that fails because both are referenced: +SAVEPOINT s; +-- a PK update that fails because both are referenced, fail immediately 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 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". --- changing the scalar part fails: +rollback to s; +-- changing the scalar part fails, fail immediately 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 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 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) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- a PK delete 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'))); -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: -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 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". +ROLLBACK; -- -- test FK referenced deletes RESTRICT -- @@ -1271,18 +1279,19 @@ ALTER TABLE temporal_fk_mltrng2mltrng 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: +begin; 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)'); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) +VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: 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: +-- a PK delete that fails because both are referenced, fail immediately. 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 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". +rollback; -- -- test FOREIGN KEY, box references box -- (not allowed: PERIOD part must be a range or multirange) diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 8cb63843..b0a9d978 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -975,45 +975,80 @@ UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; -- ALTER FK DEFERRABLE BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES + ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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)'); - ALTER TABLE temporal_fk_mltrng2mltrng - ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk - DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. COMMIT; -- should fail here. +-- if no reference, then update, delete, insert on referenced table will work. +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)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)'; + -- -- test FK referenced updates NO ACTION -- - TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; --- 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)'; +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 UPDATE NO ACTION; + +BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) +VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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 that fails because both are referenced: + +-- a PK update that fails because both are referenced. +-- but we only fail at the end of transaction. 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')); --- 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')); +COMMIT; + +-- +-- 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) + REFERENCES temporal_mltrng (id, PERIOD valid_at) + ON UPDATE NO ACTION; + +BEGIN; + ALTER TABLE temporal_fk_mltrng2mltrng + ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk + DEFERRABLE INITIALLY DEFERRED; + +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) + VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[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'))); +-- a PK delete that fails because both are referenced: but will fail at the end of commmit. +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +COMMIT; --fail at here. -- -- test FK referenced updates RESTRICT -- - TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; @@ -1022,51 +1057,29 @@ 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)'; + +BEGIN; 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)'); +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 that fails because both are referenced: +SAVEPOINT s; + +-- a PK update that fails because both are referenced, fail immediately 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')); --- changing the scalar part fails: +rollback to s; +-- changing the scalar part fails, fail immediately 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) - REFERENCES temporal_mltrng (id, PERIOD valid_at); --- a PK delete 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'))); -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: -DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +ROLLBACK; -- -- test FK referenced deletes RESTRICT -- - TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; @@ -1075,17 +1088,19 @@ ALTER TABLE temporal_fk_mltrng2mltrng 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: + +begin; 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)'); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) +VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); + +-- a PK delete that succeeds even though the numeric id is referenced because the range isn't: 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: +-- a PK delete that fails because both are referenced, fail immediately. DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); - +rollback; -- -- test FOREIGN KEY, box references box -- (not allowed: PERIOD part must be a range or multirange) base-commit: a145f424d5248a09d766e8cb503b999290cb3b31 prerequisite-patch-id: 9836c2bdaffcdd9ac5e7756df9a25fac1fe2de58 prerequisite-patch-id: 3eea913f2dca564307cd607eae18c1cb88a249e7 -- 2.34.1