v29-0003-Don-t-infer-PERIOD-on-PK-side-of-temporal-FK.patch

From a1b031371470b5d097344d8b736607eef12d2471 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Fri, 15 Mar 2024 10:51:55 -0700
Subject: [PATCH v29 3/9] Don't infer PERIOD on PK side of temporal FK

---
 doc/src/sgml/ref/create_table.sgml            |  3 +-
 src/backend/commands/tablecmds.c              | 41 ++++++------
 .../regress/expected/without_overlaps.out     | 63 +++++++++----------
 src/test/regress/sql/without_overlaps.sql     | 60 ++++++++----------
 4 files changed, 79 insertions(+), 88 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index eaf3c4b705a..e4778a4079e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1167,7 +1167,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       column(s) of some row of the referenced table.  If the <replaceable
       class="parameter">refcolumn</replaceable> list is omitted, the
       primary key of the <replaceable class="parameter">reftable</replaceable>
-      is used.  Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
+      is used (omitting any part declared with <literal>WITHOUT OVERLAPS</literal>).
+      Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
       list must refer to the columns of a non-deferrable unique or primary key
       constraint or be the columns of a non-partial unique index.
      </para>
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0fa164f3ac2..b289c029226 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -386,7 +386,7 @@ static int	transformColumnNameList(Oid relId, List *colList,
 static int	transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
 									   List **attnamelist,
 									   int16 *attnums, Oid *atttypids,
-									   Oid *opclasses, bool *pk_period);
+									   Oid *opclasses);
 static Oid	transformFkeyCheckAttrs(Relation pkrel,
 									int numattrs, int16 *attnums,
 									bool with_period, Oid *opclasses);
@@ -9820,7 +9820,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	Oid			ffeqoperators[INDEX_MAX_KEYS] = {0};
 	int16		fkdelsetcols[INDEX_MAX_KEYS] = {0};
 	bool		with_period;
-	bool		pk_with_period;
 	int			i;
 	int			numfks,
 				numpks,
@@ -9922,6 +9921,16 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_FOREIGN_KEY),
 					errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")));
+		/*
+		 * We never infer a PERIOD on the PK side
+		 * (Cf. 11.8 syntax rule 4b of the standard),
+		 * so if we don't have one already we won't ever get one.
+		 */
+		if (!fkconstraint->pk_with_period)
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_FOREIGN_KEY),
+					errmsg("foreign key uses PERIOD on the referencing table but not the referenced table")));
+
 	}
 
 	numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
@@ -9942,13 +9951,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
 											&fkconstraint->pk_attrs,
 											pkattnum, pktypoid,
-											opclasses, &pk_with_period);
-
-		/* If the primary key uses WITHOUT OVERLAPS, the fk must use PERIOD */
-		if (pk_with_period && !fkconstraint->fk_with_period)
-			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_FOREIGN_KEY),
-					errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")));
+											opclasses);
 	}
 	else
 	{
@@ -9956,15 +9959,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 										 fkconstraint->pk_attrs,
 										 pkattnum, pktypoid);
 
-		if (with_period)
-		{
-			if (!fkconstraint->pk_with_period)
-				/* Since we got pk_attrs, one should be a period. */
-				ereport(ERROR,
-						(errcode(ERRCODE_INVALID_FOREIGN_KEY),
-						errmsg("foreign key uses PERIOD on the referencing table but not the referenced table")));
-		}
-
 		/* Look for an index matching the column list */
 		indexOid = transformFkeyCheckAttrs(pkrel, numpks, pkattnum,
 										   with_period, opclasses);
@@ -12144,7 +12138,7 @@ static int
 transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
 						   List **attnamelist,
 						   int16 *attnums, Oid *atttypids,
-						   Oid *opclasses, bool *pk_period)
+						   Oid *opclasses)
 {
 	List	   *indexoidlist;
 	ListCell   *indexoidscan;
@@ -12215,6 +12209,13 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
 	{
 		int			pkattno = indexStruct->indkey.values[i];
 
+		/*
+		 * We must omit WITHOUT OVERLAPs parts of the key per the SQL standard 11.8 syntax rule 4b.
+		 * This should cause an error downstream if the FK uses PERIOD---and also if it doesn't!
+		 */
+		if (indexStruct->indisexclusion && i == indexStruct->indnatts - 1)
+			break; /* don't include this item in the number of attributes returned */
+
 		attnums[i] = pkattno;
 		atttypids[i] = attnumTypeId(pkrel, pkattno);
 		opclasses[i] = indclass->values[i];
@@ -12222,8 +12223,6 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
 							   makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))));
 	}
 
-	*pk_period = (indexStruct->indisexclusion);
-
 	ReleaseSysCache(indexTuple);
 
 	return i;
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index cc8e74275db..6d3f6fdfdea 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -469,7 +469,7 @@ CREATE TABLE temporal_fk_rng2rng (
 );
 ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
 -- (parent_id, valid_at) REFERENCES [implicit]
--- FOREIGN KEY part should specify PERIOD
+-- FOREIGN KEY part should specify PERIOD, REFERENCES must be explicit
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at daterange,
@@ -478,7 +478,7 @@ CREATE TABLE temporal_fk_rng2rng (
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at)
 		REFERENCES temporal_rng
 );
-ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
+ERROR:  number of referencing and referenced columns for foreign key disagree
 -- (parent_id, PERIOD valid_at) REFERENCES (id)
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
@@ -500,6 +500,7 @@ CREATE TABLE temporal_fk_rng2rng (
 );
 ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
 -- with inferred PK on the referenced table:
+-- (This is not permitted by the SQL standard. See 11.8 syntax rule 4b.)
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at daterange,
@@ -508,7 +509,20 @@ CREATE TABLE temporal_fk_rng2rng (
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng
 );
-DROP TABLE temporal_fk_rng2rng;
+ERROR:  foreign key uses PERIOD on the referencing table but not the referenced table
+-- (parent_id) REFERENCES [implicit]
+-- This finds the PK (omitting the WITHOUT OVERLAPS element),
+-- but it's not a b-tree index, so it fails anyway.
+-- Anyway it must fail because the two sides have a different definition of "unique".
+CREATE TABLE temporal_fk_rng2rng (
+	id int4range,
+	valid_at daterange,
+	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
+);
+ERROR:  only b-tree indexes are supported for non-PERIOD foreign keys
 -- should fail because of duplicate referenced columns:
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
@@ -588,23 +602,6 @@ Indexes:
 Foreign-key constraints:
     "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)
 
--- with inferred PK on the referenced table, and wrong column type:
-ALTER TABLE temporal_fk_rng2rng
-	DROP CONSTRAINT temporal_fk_rng2rng_fk,
-	ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at));
-ALTER TABLE temporal_fk_rng2rng
-	ADD CONSTRAINT temporal_fk_rng2rng_fk
-	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
-ERROR:  foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented
-DETAIL:  Key columns "valid_at" and "valid_at" are of incompatible types: tsrange and daterange.
-ALTER TABLE temporal_fk_rng2rng
-	ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date);
--- with inferred PK on the referenced table:
-ALTER TABLE temporal_fk_rng2rng
-	ADD CONSTRAINT temporal_fk_rng2rng_fk
-	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
 -- should fail because of duplicate referenced columns:
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk2
@@ -627,7 +624,7 @@ INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', dater
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk;
 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)');
@@ -635,7 +632,7 @@ INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', dater
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 ERROR:  insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk"
 DETAIL:  Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in table "temporal_rng".
 -- okay again:
@@ -643,7 +640,7 @@ DELETE FROM temporal_fk_rng2rng;
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 --
 -- test pg_get_constraintdef
 --
@@ -727,7 +724,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng
+	REFERENCES temporal_rng (id, PERIOD valid_at)
 	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'));
@@ -763,7 +760,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 -- a PK delete 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'));
 DELETE FROM temporal_rng WHERE id = '[5,6)';
@@ -789,7 +786,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng
+	REFERENCES temporal_rng (id, PERIOD valid_at)
 	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)';
@@ -816,7 +813,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE CASCADE ON UPDATE CASCADE;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 -- test FK referenced updates SET NULL
@@ -826,7 +823,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE SET NULL ON UPDATE SET NULL;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 -- test FK referenced updates SET DEFAULT
@@ -838,7 +835,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 --
@@ -930,7 +927,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 ALTER TABLE temporal_partitioned_fk_rng2rng
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_partitioned_rng
+	REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 	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)';
@@ -962,7 +959,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE CASCADE ON UPDATE CASCADE;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 --
@@ -975,7 +972,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE SET NULL ON UPDATE SET NULL;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 --
@@ -989,7 +986,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 --
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 3310980329f..5eabafe0b4e 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -370,7 +370,7 @@ CREATE TABLE temporal_fk_rng2rng (
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
 -- (parent_id, valid_at) REFERENCES [implicit]
--- FOREIGN KEY part should specify PERIOD
+-- FOREIGN KEY part should specify PERIOD, REFERENCES must be explicit
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at daterange,
@@ -397,8 +397,8 @@ CREATE TABLE temporal_fk_rng2rng (
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id)
 		REFERENCES temporal_rng (id, PERIOD valid_at)
 );
-
 -- with inferred PK on the referenced table:
+-- (This is not permitted by the SQL standard. See 11.8 syntax rule 4b.)
 CREATE TABLE temporal_fk_rng2rng (
 	id int4range,
 	valid_at daterange,
@@ -407,7 +407,18 @@ CREATE TABLE temporal_fk_rng2rng (
 	CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
 		REFERENCES temporal_rng
 );
-DROP TABLE temporal_fk_rng2rng;
+-- (parent_id) REFERENCES [implicit]
+-- This finds the PK (omitting the WITHOUT OVERLAPS element),
+-- but it's not a b-tree index, so it fails anyway.
+-- Anyway it must fail because the two sides have a different definition of "unique".
+CREATE TABLE temporal_fk_rng2rng (
+	id int4range,
+	valid_at daterange,
+	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
+);
 
 -- should fail because of duplicate referenced columns:
 CREATE TABLE temporal_fk_rng2rng (
@@ -468,23 +479,6 @@ ALTER TABLE temporal_fk2_rng2rng
 	REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at);
 \d temporal_fk2_rng2rng
 
--- with inferred PK on the referenced table, and wrong column type:
-ALTER TABLE temporal_fk_rng2rng
-	DROP CONSTRAINT temporal_fk_rng2rng_fk,
-	ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at));
-ALTER TABLE temporal_fk_rng2rng
-	ADD CONSTRAINT temporal_fk_rng2rng_fk
-	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
-ALTER TABLE temporal_fk_rng2rng
-	ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date);
-
--- with inferred PK on the referenced table:
-ALTER TABLE temporal_fk_rng2rng
-	ADD CONSTRAINT temporal_fk_rng2rng_fk
-	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
-
 -- should fail because of duplicate referenced columns:
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk2
@@ -509,7 +503,7 @@ INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', dater
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 ALTER TABLE temporal_fk_rng2rng
 	DROP CONSTRAINT temporal_fk_rng2rng_fk;
 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)');
@@ -517,13 +511,13 @@ INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', dater
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 -- okay again:
 DELETE FROM temporal_fk_rng2rng;
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 
 --
 -- test pg_get_constraintdef
@@ -603,7 +597,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng
+	REFERENCES temporal_rng (id, PERIOD valid_at)
 	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'));
@@ -637,7 +631,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng;
+	REFERENCES temporal_rng (id, PERIOD valid_at);
 -- a PK delete 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'));
 DELETE FROM temporal_rng WHERE id = '[5,6)';
@@ -663,7 +657,7 @@ ALTER TABLE temporal_fk_rng2rng
 ALTER TABLE temporal_fk_rng2rng
 	ADD CONSTRAINT temporal_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_rng
+	REFERENCES temporal_rng (id, PERIOD valid_at)
 	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)';
@@ -690,7 +684,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE CASCADE ON UPDATE CASCADE;
 
 -- test FK referenced updates SET NULL
@@ -700,7 +694,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE SET NULL ON UPDATE SET NULL;
 
 -- test FK referenced updates SET DEFAULT
@@ -712,7 +706,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
+		REFERENCES temporal_rng (id, PERIOD valid_at)
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 
 --
@@ -806,7 +800,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 ALTER TABLE temporal_partitioned_fk_rng2rng
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 	FOREIGN KEY (parent_id, PERIOD valid_at)
-	REFERENCES temporal_partitioned_rng
+	REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 	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)';
@@ -838,7 +832,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE CASCADE ON UPDATE CASCADE;
 
 --
@@ -853,7 +847,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE SET NULL ON UPDATE SET NULL;
 
 --
@@ -869,7 +863,7 @@ ALTER TABLE temporal_partitioned_fk_rng2rng
 	DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
 	ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
 		FOREIGN KEY (parent_id, PERIOD valid_at)
-		REFERENCES temporal_partitioned_rng
+		REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 
 --
-- 
2.42.0