v1-0001-refactor-temporal-FOREIGN-KEYs-test.patch

From 8ba03aa6a442d57bd0f2117e32e703fb211b68fd Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 2 Feb 2024 10:31:16 +0800
Subject: [PATCH v1 1/1] refactor temporal FOREIGN KEYs test

make related tests closer, remove unnecessary primary key constraint
so it improve test's readability
---
 .../regress/expected/without_overlaps.out     | 76 ++++++++++---------
 src/test/regress/sql/without_overlaps.sql     | 76 ++++++++++---------
 2 files changed, 82 insertions(+), 70 deletions(-)

diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index c633738c..2d0f5e21 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -421,53 +421,58 @@ DROP TABLE temporal3;
 --
 -- test FOREIGN KEY, range references range
 --
+--test table setup.
+DROP TABLE IF EXISTS temporal_rng;
+CREATE TABLE temporal_rng (id int4range, valid_at tsrange);
+ALTER TABLE temporal_rng
+	ADD CONSTRAINT temporal_rng_pk
+	PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
 -- Can't create a FK with a mismatched range type
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at int4range,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 ERROR:  foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented
 DETAIL:  Key columns "valid_at" and "valid_at" are of incompatible types: int4range and tsrange.
+---both referencing and referenced table's last columns specified PERIOD. shoule be ok.
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 DROP TABLE temporal_fk_rng2rng;
 -- with mismatched PERIOD columns:
 -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at)
+-- REFERENCES column part should also specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, valid_at)
 );
 ERROR:  foreign key uses PERIOD on the referencing table but not the referenced table
 -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at)
+-- foreign key last column should specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
 -- (parent_id, valid_at) REFERENCES [implicit]
+-- foreign key last column should specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at)
 		REFERENCES temporal_rng
 );
@@ -477,7 +482,6 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id)
 );
@@ -487,17 +491,15 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
--- with inferred PK on the referenced table:
+-- with inferred PK on the referenced table. ok
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng
 );
@@ -507,18 +509,23 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id)
 		REFERENCES temporal_rng (id, PERIOD id)
 );
 ERROR:  foreign key referenced-columns list must not contain duplicates
+DROP TABLE IF EXISTS temporal_rng2;
+CREATE TABLE temporal_rng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
 -- Two scalar columns
 CREATE TABLE temporal_fk2_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id1 int4range,
 	parent_id2 int4range,
-	CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
 		REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at)
 );
@@ -526,12 +533,10 @@ CREATE TABLE temporal_fk2_rng2rng (
            Table "public.temporal_fk2_rng2rng"
    Column   |   Type    | Collation | Nullable | Default 
 ------------+-----------+-----------+----------+---------
- id         | int4range |           | not null | 
- valid_at   | tsrange   |           | not null | 
+ id         | int4range |           |          | 
+ valid_at   | tsrange   |           |          | 
  parent_id1 | int4range |           |          | 
  parent_id2 | int4range |           |          | 
-Indexes:
-    "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
 Foreign-key constraints:
     "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
 
@@ -601,6 +606,13 @@ ERROR:  foreign key referenced-columns list must not contain duplicates
 -- test with rows already
 --
 DELETE FROM temporal_fk_rng2rng;
+DELETE FROM temporal_rng;
+-- okay:
+INSERT INTO temporal_rng VALUES 
+('[1,1]', tsrange('2018-01-02', '2018-02-03')),
+('[1,1]', tsrange('2018-03-03', '2018-04-04')),
+('[2,2]', tsrange('2018-01-01', '2018-01-05')),
+('[3,3]', tsrange('2018-01-01', NULL));
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk;
 INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]');
@@ -677,8 +689,9 @@ INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
 -- a PK update that succeeds even though the numeric id is referenced because the range isn't:
 DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
 WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -708,14 +721,15 @@ ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
 	REFERENCES temporal_rng
-	ON DELETE RESTRICT;
+	ON UPDATE RESTRICT;
 -- a PK update that succeeds because the numeric id isn't referenced:
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
 -- a PK update that succeeds even though the numeric id is referenced because the range isn't:
 DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
 WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -749,8 +763,9 @@ ALTER TABLE temporal_fk_rng2rng
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 DELETE FROM temporal_rng WHERE id = '[5,5]';
 -- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
 -- a PK delete that fails because both are referenced:
@@ -773,8 +788,9 @@ ALTER TABLE temporal_fk_rng2rng
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 DELETE FROM temporal_rng WHERE id = '[5,5]';
 -- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
 -- a PK delete that fails because both are referenced:
@@ -785,11 +801,9 @@ DETAIL:  Key (id, valid_at)=([5,6), ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:0
 DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
 --
--- test ON UPDATE/DELETE options
+-- test ON UPDATE/DELETE {set null | set default | CASCADE}
 --
 -- test FK parent updates CASCADE
-INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]');
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -798,8 +812,6 @@ ALTER TABLE temporal_fk_rng2rng
 		ON DELETE CASCADE ON UPDATE CASCADE;
 ERROR:  action not supported for temporal foreign keys
 -- test FK parent updates SET NULL
-INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]');
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -808,9 +820,6 @@ ALTER TABLE temporal_fk_rng2rng
 		ON DELETE SET NULL ON UPDATE SET NULL;
 ERROR:  action not supported for temporal foreign keys
 -- test FK parent updates SET DEFAULT
-INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null));
-INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]');
 ALTER TABLE temporal_fk_rng2rng
   ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -932,7 +941,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE CASCADE ON UPDATE CASCADE;
 ERROR:  action not supported for temporal foreign keys
--- partitioned FK parent deletes CASCADE
 -- partitioned FK parent updates SET NULL
 ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
@@ -941,7 +949,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE SET NULL ON UPDATE SET NULL;
 ERROR:  action not supported for temporal foreign keys
--- partitioned FK parent deletes SET NULL
 -- partitioned FK parent updates SET DEFAULT
 ALTER TABLE temporal_partitioned_fk_rng2rng
   ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
@@ -951,6 +958,5 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 ERROR:  action not supported for temporal foreign keys
--- partitioned FK parent deletes SET DEFAULT
 DROP TABLE temporal_partitioned_fk_rng2rng;
 DROP TABLE temporal_partitioned_rng;
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 641cdd5b..17bf7bdf 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -316,21 +316,27 @@ DROP TABLE temporal3;
 -- test FOREIGN KEY, range references range
 --
 
+--test table setup.
+DROP TABLE IF EXISTS temporal_rng;
+CREATE TABLE temporal_rng (id int4range, valid_at tsrange);
+ALTER TABLE temporal_rng
+	ADD CONSTRAINT temporal_rng_pk
+	PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+
 -- Can't create a FK with a mismatched range type
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at int4range,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 
+---both referencing and referenced table's last columns specified PERIOD. shoule be ok.
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
@@ -338,29 +344,29 @@ DROP TABLE temporal_fk_rng2rng;
 
 -- with mismatched PERIOD columns:
 -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at)
+-- REFERENCES column part should also specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id, valid_at)
 );
 -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at)
+-- foreign key last column should specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 -- (parent_id, valid_at) REFERENCES [implicit]
+-- foreign key last column should specify PERIOD
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at)
 		REFERENCES temporal_rng
 );
@@ -369,7 +375,6 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng (id)
 );
@@ -378,17 +383,15 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 
--- with inferred PK on the referenced table:
+-- with inferred PK on the referenced table. ok
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng
 );
@@ -399,18 +402,23 @@ CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id int4range,
-	CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id)
 		REFERENCES temporal_rng (id, PERIOD id)
 );
 
+DROP TABLE IF EXISTS temporal_rng2;
+CREATE TABLE temporal_rng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
 -- Two scalar columns
 CREATE TABLE temporal_fk2_rng2rng (
 	id int4range,
 	valid_at tsrange,
 	parent_id1 int4range,
 	parent_id2 int4range,
-	CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
 	CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
 		REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at)
 );
@@ -473,6 +481,14 @@ ALTER TABLE temporal_fk_rng2rng
 --
 
 DELETE FROM temporal_fk_rng2rng;
+DELETE FROM temporal_rng;
+-- okay:
+INSERT INTO temporal_rng VALUES 
+('[1,1]', tsrange('2018-01-02', '2018-02-03')),
+('[1,1]', tsrange('2018-03-03', '2018-04-04')),
+('[2,2]', tsrange('2018-01-01', '2018-01-05')),
+('[3,3]', tsrange('2018-01-01', NULL));
+
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk;
 INSERT INTO temporal_fk_rng2rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-01'), '[1,1]');
@@ -545,8 +561,9 @@ INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
 -- a PK update that succeeds even though the numeric id is referenced because the range isn't:
 DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
 WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -574,14 +591,15 @@ ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
 	REFERENCES temporal_rng
-	ON DELETE RESTRICT;
+	ON UPDATE RESTRICT;
 -- a PK update that succeeds because the numeric id isn't referenced:
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') WHERE id = '[5,5]';
 -- a PK update that succeeds even though the numeric id is referenced because the range isn't:
 DELETE FROM temporal_rng WHERE id = '[5,5]';
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 UPDATE temporal_rng SET valid_at = tsrange('2016-02-01', '2016-03-01')
 WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
@@ -611,8 +629,9 @@ ALTER TABLE temporal_fk_rng2rng
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 DELETE FROM temporal_rng WHERE id = '[5,5]';
 -- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
 -- a PK delete that fails because both are referenced:
@@ -635,8 +654,9 @@ ALTER TABLE temporal_fk_rng2rng
 INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
 DELETE FROM temporal_rng WHERE id = '[5,5]';
 -- a PK delete that succeeds even though the numeric id is referenced because the range isn't:
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-01-01', '2018-02-01'));
-INSERT INTO temporal_rng VALUES ('[5,5]', tsrange('2018-02-01', '2018-03-01'));
+INSERT INTO temporal_rng VALUES 
+	('[5,5]', tsrange('2018-01-01', '2018-02-01')),
+	('[5,5]', tsrange('2018-02-01', '2018-03-01'));
 INSERT INTO temporal_fk_rng2rng VALUES ('[3,3]', tsrange('2018-01-05', '2018-01-10'), '[5,5]');
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-02-01', '2018-03-01');
 -- a PK delete that fails because both are referenced:
@@ -646,12 +666,10 @@ DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]';
 DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01');
 
 --
--- test ON UPDATE/DELETE options
+-- test ON UPDATE/DELETE {set null | set default | CASCADE}
 --
 
 -- test FK parent updates CASCADE
-INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]');
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -660,8 +678,6 @@ ALTER TABLE temporal_fk_rng2rng
 		ON DELETE CASCADE ON UPDATE CASCADE;
 
 -- test FK parent updates SET NULL
-INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]');
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
@@ -670,9 +686,6 @@ ALTER TABLE temporal_fk_rng2rng
 		ON DELETE SET NULL ON UPDATE SET NULL;
 
 -- test FK parent updates SET DEFAULT
-INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null));
-INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]');
 ALTER TABLE temporal_fk_rng2rng
   ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
 	DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -796,8 +809,6 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE CASCADE ON UPDATE CASCADE;
 
--- partitioned FK parent deletes CASCADE
-
 -- partitioned FK parent updates SET NULL
 
 ALTER TABLE temporal_partitioned_fk_rng2rng
@@ -807,10 +818,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE SET NULL ON UPDATE SET NULL;
 
--- partitioned FK parent deletes SET NULL
-
 -- partitioned FK parent updates SET DEFAULT
-
 ALTER TABLE temporal_partitioned_fk_rng2rng
   ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
@@ -819,7 +827,5 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 		REFERENCES temporal_partitioned_rng
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 
--- partitioned FK parent deletes SET DEFAULT
-
 DROP TABLE temporal_partitioned_fk_rng2rng;
 DROP TABLE temporal_partitioned_rng;
-- 
2.34.1