v31-0001-refactor-temporal-FKs-with-multiranges-regressio.no-cfbot
application/octet-stream
Filename: v31-0001-refactor-temporal-FKs-with-multiranges-regressio.no-cfbot
Type: application/octet-stream
Part: 0
Message:
Re: SQL:2011 application time
From 027e4367605ec7d428e1c7859286d9315fa7cd3f Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
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