v32-0001-minor-refactor-temporal-FKs-with-multiranges-.no-cfbot

From dca68656d934cab3e88a39bb8b834f0027d80f26 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 22 Mar 2024 13:30:56 +0800
Subject: [PATCH v32 1/1] minor refactor temporal FKs with multiranges
 regression test

---
 src/test/regress/expected/without_overlaps.out | 10 ++++++++++
 src/test/regress/sql/without_overlaps.sql      | 11 +++++++++++
 2 files changed, 21 insertions(+)

diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index ca26acb8..3da6d869 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -837,11 +837,16 @@ INSERT INTO temporal_rng (id, valid_at) VALUES
 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');
+begin;
+	ALTER TABLE temporal_fk_rng2rng
+		ALTER CONSTRAINT temporal_fk_rng2rng_fk
+		DEFERRABLE INITIALLY DEFERRED;
 -- a PK update that fails because both are referenced:
 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 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 still 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');
@@ -905,10 +910,15 @@ INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[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');
+begin;
+	ALTER TABLE temporal_fk_rng2rng
+		ALTER CONSTRAINT temporal_fk_rng2rng_fk
+		DEFERRABLE INITIALLY DEFERRED;
 -- a PK delete that fails because both are referenced:
 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 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 still 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');
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index e5fec3fc..cb59ac47 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -674,9 +674,15 @@ INSERT INTO temporal_rng (id, valid_at) VALUES
 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');
+
+begin;
+	ALTER TABLE temporal_fk_rng2rng
+		ALTER CONSTRAINT temporal_fk_rng2rng_fk
+		DEFERRABLE INITIALLY DEFERRED;
 -- a PK update that fails because both are referenced:
 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');
@@ -739,8 +745,13 @@ INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[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');
+begin;
+	ALTER TABLE temporal_fk_rng2rng
+		ALTER CONSTRAINT temporal_fk_rng2rng_fk
+		DEFERRABLE INITIALLY DEFERRED;
 -- a PK delete that fails because both are referenced:
 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');

base-commit: 085e759e9da77b7f5e859d23f72653f36277a053
prerequisite-patch-id: 3589d4c51b5f52b03318705b1c070d7db559898a
prerequisite-patch-id: f03afdd3b00acd6310276e66e4c3a9fd26b3f631
-- 
2.34.1