v32-0002-Support-multiranges-in-temporal-FKs.patch

From 4c6c9cdc8cd48008c3755ce06e82aed0be93c0fb Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Fri, 15 Mar 2024 17:43:16 -0700
Subject: [PATCH v32 2/6] Support multiranges in temporal FKs

Allow multiranges as the PERIOD part of a FOREIGN KEY. We already allow
range types, and adding multiranges is straightforward.
---
 doc/src/sgml/ref/create_table.sgml            |   2 +-
 src/backend/catalog/pg_constraint.c           |  19 +-
 src/backend/commands/tablecmds.c              |   6 +-
 src/backend/utils/adt/ri_triggers.c           |   8 +-
 .../regress/expected/without_overlaps.out     | 485 ++++++++++++++++++
 src/test/regress/sql/without_overlaps.sql     | 407 +++++++++++++++
 6 files changed, 917 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 512b1db3878..84a520e983f 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1184,7 +1184,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       whose combined <literal>PERIOD</literal> values completely cover
       the referencing record's.
       In other words, the reference must have a referent for its entire duration.
-      This column must be a column with a range type.
+      This column must be a range or multirange type.
       In addition the referenced table must have a primary key
       or unique constraint declared with <literal>WITHOUT OVERLAPS</literal>.
       Finally, if one side of the foreign key uses <literal>PERIOD</literal>,
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b2e5c3a6876..acaccd26fdb 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1657,8 +1657,8 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
  * The opclass should be the opclass of that PERIOD element.
  * Everything else is an output: containedbyoperoid is the ContainedBy operator for
  * types matching the PERIOD element.
- * And aggedcontainedbyoperoid is also a ContainedBy operator,
- * but one whose rhs is anymultirange.
+ * aggedcontainedbyoperoid is also a ContainedBy operator,
+ * but one whose rhs is a multirange.
  * That way foreign keys can compare fkattr <@ range_agg(pkattr).
  */
 void
@@ -1666,8 +1666,23 @@ FindFKPeriodOpers(Oid opclass,
 				  Oid *containedbyoperoid,
 				  Oid *aggedcontainedbyoperoid)
 {
+	Oid		opfamily = InvalidOid;
+	Oid		opcintype = InvalidOid;
 	StrategyNumber strat;
 
+	/* Make sure we have a range or multirange. */
+	if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype))
+	{
+		if (opcintype != ANYRANGEOID && opcintype != ANYMULTIRANGEOID)
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("Invalid type for PERIOD part in FOREIGN KEY"),
+					 errhint("Use a range or multirange type.")));
+
+	}
+	else
+		elog(ERROR, "cache lookup failed for opclass %u", opclass);
+
 	/*
 	 * Look up the ContainedBy operator whose lhs and rhs are the opclass's type.
 	 * We use this to optimize RI checks: if the new value includes all
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8cb263199b2..77e6b09a9de 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10250,10 +10250,12 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	}
 
 	/*
-	 * For FKs with PERIOD we need an operator and aggregate function
+	 * For FKs with PERIOD we need additional operators
 	 * to check whether the referencing row's range is contained
 	 * by the aggregated ranges of the referenced row(s).
-	 * For rangetypes this is fk.periodatt <@ range_agg(pk.periodatt).
+	 * For rangetypes and multirangetypes this is
+	 * fk.periodatt <@ range_agg(pk.periodatt).
+	 * Those are the only types we support for now.
 	 * FKs will look these up at "runtime", but we should make sure
 	 * the lookup works here, even if we don't use the values.
 	 */
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 2f9c868e9c3..624f9b788ff 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -128,7 +128,7 @@ typedef struct RI_ConstraintInfo
 	Oid			pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */
 	Oid			ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
 	Oid			period_contained_by_oper;	/* anyrange <@ anyrange */
-	Oid			agged_period_contained_by_oper;	/* anyrange <@ anymultirange */
+	Oid			agged_period_contained_by_oper;	/* fkattr <@ range_agg(pkattr) */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -425,14 +425,13 @@ RI_FKey_check(TriggerData *trigdata)
 		if (riinfo->hasperiod)
 		{
 			Oid		fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]);
-			Oid		agg_rettype = ANYMULTIRANGEOID;
 
 			appendStringInfo(&querybuf, ") x1 HAVING ");
 			sprintf(paramname, "$%d", riinfo->nkeys);
 			ri_GenerateQual(&querybuf, "",
 							paramname, fk_type,
 							riinfo->agged_period_contained_by_oper,
-							"pg_catalog.range_agg", agg_rettype);
+							"pg_catalog.range_agg", ANYMULTIRANGEOID);
 			appendStringInfo(&querybuf, "(x1.r)");
 		}
 
@@ -595,14 +594,13 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
 		if (riinfo->hasperiod)
 		{
 			Oid		fk_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]);
-			Oid		agg_rettype = ANYMULTIRANGEOID;
 
 			appendStringInfo(&querybuf, ") x1 HAVING ");
 			sprintf(paramname, "$%d", riinfo->nkeys);
 			ri_GenerateQual(&querybuf, "",
 							paramname, fk_type,
 							riinfo->agged_period_contained_by_oper,
-							"pg_catalog.range_agg", agg_rettype);
+							"pg_catalog.range_agg", ANYMULTIRANGEOID);
 			appendStringInfo(&querybuf, "(x1.r)");
 		}
 
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 869b4418a90..ca26acb8527 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -99,6 +99,51 @@ CREATE TABLE temporal_rng3 (
 ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
 DROP TYPE textrange2;
+-- PK with one column plus a multirange:
+CREATE TABLE temporal_mltrng (
+  id int4range,
+  valid_at datemultirange,
+  CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_mltrng
+               Table "public.temporal_mltrng"
+  Column  |      Type      | Collation | Nullable | Default 
+----------+----------------+-----------+----------+---------
+ id       | int4range      |           | not null | 
+ valid_at | datemultirange |           | not null | 
+Indexes:
+    "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+
+-- PK with two columns plus a multirange:
+-- We don't drop this table because tests below also need multiple scalar columns.
+CREATE TABLE temporal_mltrng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at datemultirange,
+	CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_mltrng2
+              Table "public.temporal_mltrng2"
+  Column  |      Type      | Collation | Nullable | Default 
+----------+----------------+-----------+----------+---------
+ id1      | int4range      |           | not null | 
+ id2      | int4range      |           | not null | 
+ valid_at | datemultirange |           | not null | 
+Indexes:
+    "temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
+               pg_get_constraintdef                
+---------------------------------------------------
+ PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+(1 row)
+
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
+                                       pg_get_indexdef                                       
+---------------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at)
+(1 row)
+
 -- UNIQUE with no columns just WITHOUT OVERLAPS:
 CREATE TABLE temporal_rng3 (
 	valid_at daterange,
@@ -275,6 +320,30 @@ DETAIL:  Failing row contains (null, [2018-01-01,2018-01-05)).
 INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL);
 ERROR:  null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
 DETAIL:  Failing row contains ([3,4), null).
+-- okay:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+-- should fail:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+ERROR:  conflicting key value violates exclusion constraint "temporal_mltrng_pk"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}).
+INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+ERROR:  null value in column "id" of relation "temporal_mltrng" violates not-null constraint
+DETAIL:  Failing row contains (null, {[2018-01-01,2018-01-05)}).
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL);
+ERROR:  null value in column "valid_at" of relation "temporal_mltrng" violates not-null constraint
+DETAIL:  Failing row contains ([3,4), null).
+SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
+  id   |         valid_at          
+-------+---------------------------
+ [1,2) | {[2018-01-02,2018-02-03)}
+ [1,2) | {[2018-03-03,2018-04-04)}
+ [2,3) | {[2018-01-01,2018-01-05)}
+ [3,4) | {[2018-01-01,)}
+(4 rows)
+
 --
 -- test a range with both a PK and a UNIQUE constraint
 --
@@ -879,6 +948,422 @@ ALTER TABLE temporal_fk_rng2rng
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 ERROR:  invalid ON DELETE action for foreign key constraint using PERIOD
 --
+-- test FOREIGN KEY, multirange references multirange
+--
+-- Can't create a FK with a mismatched multirange type
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at int4multirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+ERROR:  foreign key constraint "temporal_fk_mltrng2mltrng_fk2" cannot be implemented
+DETAIL:  Key columns "valid_at" and "valid_at" are of incompatible types: int4multirange and datemultirange.
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+DROP TABLE temporal_fk_mltrng2mltrng;
+-- with mismatched PERIOD columns:
+-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at)
+-- REFERENCES part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, valid_at)
+);
+ERROR:  foreign key uses PERIOD on the referencing table but not the referenced table
+-- (parent_id, valid_at) REFERENCES (id, valid_at)
+-- both should specify PERIOD:
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng (id, valid_at)
+);
+ERROR:  foreign key must use PERIOD when referencing a WITHOUT OVERLAPS index
+-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at)
+-- FOREIGN KEY part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng (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 part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng
+);
+ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
+-- (parent_id, PERIOD valid_at) REFERENCES (id)
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id)
+);
+ERROR:  foreign key uses PERIOD on the referencing table but not the referenced table
+-- (parent_id) REFERENCES (id, PERIOD valid_at)
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id)
+		REFERENCES temporal_mltrng (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:
+-- (parent_id, PERIOD valid_at) REFERENCES [implicit]
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng
+);
+DROP TABLE temporal_fk_mltrng2mltrng;
+-- (parent_id) REFERENCES [implicit]
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id)
+		REFERENCES temporal_mltrng
+);
+ERROR:  foreign key uses PERIOD on the referenced table but not the referencing table
+-- should fail because of duplicate referenced columns:
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id)
+		REFERENCES temporal_mltrng (id, PERIOD id)
+);
+ERROR:  foreign key referenced-columns list must not contain duplicates
+-- Two scalar columns
+CREATE TABLE temporal_fk2_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id1 int4range,
+	parent_id2 int4range,
+	CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+		REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at)
+);
+\d temporal_fk2_mltrng2mltrng
+          Table "public.temporal_fk2_mltrng2mltrng"
+   Column   |      Type      | Collation | Nullable | Default 
+------------+----------------+-----------+----------+---------
+ id         | int4range      |           | not null | 
+ valid_at   | datemultirange |           | not null | 
+ parent_id1 | int4range      |           |          | 
+ parent_id2 | int4range      |           |          | 
+Indexes:
+    "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Foreign-key constraints:
+    "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
+
+DROP TABLE temporal_fk2_mltrng2mltrng;
+--
+-- test ALTER TABLE ADD CONSTRAINT
+--
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+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);
+-- Two scalar columns:
+CREATE TABLE temporal_fk2_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id1 int4range,
+	parent_id2 int4range,
+	CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_fk2_mltrng2mltrng
+	ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+	FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+	REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at);
+\d temporal_fk2_mltrng2mltrng
+          Table "public.temporal_fk2_mltrng2mltrng"
+   Column   |      Type      | Collation | Nullable | Default 
+------------+----------------+-----------+----------+---------
+ id         | int4range      |           | not null | 
+ valid_at   | datemultirange |           | not null | 
+ parent_id1 | int4range      |           |          | 
+ parent_id2 | int4range      |           |          | 
+Indexes:
+    "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+Foreign-key constraints:
+    "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)
+
+-- should fail because of duplicate referenced columns:
+ALTER TABLE temporal_fk_mltrng2mltrng
+	ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2
+	FOREIGN KEY (parent_id, PERIOD parent_id)
+	REFERENCES temporal_mltrng (id, PERIOD id);
+ERROR:  foreign key referenced-columns list must not contain duplicates
+--
+-- test with rows already
+--
+DELETE FROM temporal_fk_mltrng2mltrng;
+ALTER TABLE temporal_fk_mltrng2mltrng
+	DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk;
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
+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);
+ALTER TABLE temporal_fk_mltrng2mltrng
+	DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk;
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+-- should fail:
+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);
+ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
+DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng".
+-- okay again:
+DELETE FROM temporal_fk_mltrng2mltrng;
+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);
+--
+-- test pg_get_constraintdef
+--
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk';
+                                   pg_get_constraintdef                                   
+------------------------------------------------------------------------------------------
+ FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng(id, PERIOD valid_at)
+(1 row)
+
+--
+-- test FK referencing inserts
+--
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
+-- should fail:
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
+DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng".
+-- now it should work:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+--
+-- test FK referencing updates
+--
+UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)';
+-- should fail:
+UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)';
+ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
+DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-05-01)}) is not present in table "temporal_mltrng".
+UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)';
+ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
+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;
+  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".
+--
+-- test FK referenced updates 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;
+-- 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)';
+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:
+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".
+-- a PK update that fails because both are referenced, but not 'til commit:
+BEGIN;
+  ALTER TABLE temporal_fk_mltrng2mltrng
+    ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
+    DEFERRABLE INITIALLY DEFERRED;
+  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'));
+COMMIT;
+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".
+--
+-- test FK referenced updates RESTRICT
+--
+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 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)';
+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:
+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".
+--
+-- 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".
+-- a PK delete that fails because both are referenced, but not 'til commit:
+BEGIN;
+  ALTER TABLE temporal_fk_mltrng2mltrng
+    ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
+    DEFERRABLE INITIALLY DEFERRED;
+  DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
+COMMIT;
+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 RESTRICT
+--
+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 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:
+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".
+--
+-- test FOREIGN KEY, box references box
+-- (not allowed: PERIOD part must be a range or multirange)
+--
+CREATE TABLE temporal_box (
+  id int4range,
+  valid_at box,
+  CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_box
+              Table "public.temporal_box"
+  Column  |   Type    | Collation | Nullable | Default 
+----------+-----------+-----------+----------+---------
+ id       | int4range |           | not null | 
+ valid_at | box       |           | not null | 
+Indexes:
+    "temporal_box_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+
+CREATE TABLE temporal_fk_box2box (
+  id int4range,
+  valid_at box,
+  parent_id int4range,
+  CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+  CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_box (id, PERIOD valid_at)
+);
+ERROR:  Invalid type for PERIOD part in FOREIGN KEY
+HINT:  Use a range or multirange type.
+--
 -- FK between partitioned tables
 --
 CREATE TABLE temporal_partitioned_rng (
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 0346d99594b..e5fec3fc071 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -69,6 +69,26 @@ ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk;
 DROP TABLE temporal_rng3;
 DROP TYPE textrange2;
 
+-- PK with one column plus a multirange:
+CREATE TABLE temporal_mltrng (
+  id int4range,
+  valid_at datemultirange,
+  CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_mltrng
+
+-- PK with two columns plus a multirange:
+-- We don't drop this table because tests below also need multiple scalar columns.
+CREATE TABLE temporal_mltrng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at datemultirange,
+	CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_mltrng2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk';
+
 -- UNIQUE with no columns just WITHOUT OVERLAPS:
 
 CREATE TABLE temporal_rng3 (
@@ -207,6 +227,19 @@ INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01',
 INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
 INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL);
 
+-- okay:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
+
+-- should fail:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL);
+
+SELECT * FROM temporal_mltrng ORDER BY id, valid_at;
+
 --
 -- test a range with both a PK and a UNIQUE constraint
 --
@@ -748,6 +781,380 @@ ALTER TABLE temporal_fk_rng2rng
 		REFERENCES temporal_rng
 		ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
 
+--
+-- test FOREIGN KEY, multirange references multirange
+--
+
+-- Can't create a FK with a mismatched multirange type
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at int4multirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+DROP TABLE temporal_fk_mltrng2mltrng;
+
+-- with mismatched PERIOD columns:
+
+-- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at)
+-- REFERENCES part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id, valid_at)
+);
+-- (parent_id, valid_at) REFERENCES (id, valid_at)
+-- both should specify PERIOD:
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng (id, valid_at)
+);
+-- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at)
+-- FOREIGN KEY part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+-- (parent_id, valid_at) REFERENCES [implicit]
+-- FOREIGN KEY part should specify PERIOD
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at)
+		REFERENCES temporal_mltrng
+);
+-- (parent_id, PERIOD valid_at) REFERENCES (id)
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng (id)
+);
+-- (parent_id) REFERENCES (id, PERIOD valid_at)
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id)
+		REFERENCES temporal_mltrng (id, PERIOD valid_at)
+);
+-- with inferred PK on the referenced table:
+-- (parent_id, PERIOD valid_at) REFERENCES [implicit]
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+		REFERENCES temporal_mltrng
+);
+DROP TABLE temporal_fk_mltrng2mltrng;
+-- (parent_id) REFERENCES [implicit]
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id)
+		REFERENCES temporal_mltrng
+);
+
+-- should fail because of duplicate referenced columns:
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id)
+		REFERENCES temporal_mltrng (id, PERIOD id)
+);
+
+-- Two scalar columns
+CREATE TABLE temporal_fk2_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id1 int4range,
+	parent_id2 int4range,
+	CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+	CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+		REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at)
+);
+\d temporal_fk2_mltrng2mltrng
+DROP TABLE temporal_fk2_mltrng2mltrng;
+
+--
+-- test ALTER TABLE ADD CONSTRAINT
+--
+
+CREATE TABLE temporal_fk_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id int4range,
+	CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+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);
+-- Two scalar columns:
+CREATE TABLE temporal_fk2_mltrng2mltrng (
+	id int4range,
+	valid_at datemultirange,
+	parent_id1 int4range,
+	parent_id2 int4range,
+	CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+ALTER TABLE temporal_fk2_mltrng2mltrng
+	ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+	FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+	REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at);
+\d temporal_fk2_mltrng2mltrng
+
+-- should fail because of duplicate referenced columns:
+ALTER TABLE temporal_fk_mltrng2mltrng
+	ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2
+	FOREIGN KEY (parent_id, PERIOD parent_id)
+	REFERENCES temporal_mltrng (id, PERIOD id);
+
+--
+-- test with rows already
+--
+
+DELETE FROM temporal_fk_mltrng2mltrng;
+ALTER TABLE temporal_fk_mltrng2mltrng
+	DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk;
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
+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);
+ALTER TABLE temporal_fk_mltrng2mltrng
+	DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk;
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+-- should fail:
+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);
+-- okay again:
+DELETE FROM temporal_fk_mltrng2mltrng;
+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);
+
+--
+-- test pg_get_constraintdef
+--
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng2mltrng_fk';
+
+--
+-- test FK referencing inserts
+--
+
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
+-- should fail:
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+-- now it should work:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
+
+--
+-- test FK referencing updates
+--
+
+UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-03-01')) WHERE id = '[1,2)';
+-- should fail:
+UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)';
+UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)';
+
+-- ALTER FK DEFERRABLE
+
+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)');
+  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.
+
+--
+-- test FK referenced updates 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;
+-- 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)';
+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:
+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'));
+-- a PK update that fails because both are referenced, but not 'til commit:
+BEGIN;
+  ALTER TABLE temporal_fk_mltrng2mltrng
+    ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
+    DEFERRABLE INITIALLY DEFERRED;
+
+  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'));
+COMMIT;
+-- 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'));
+
+--
+-- test FK referenced updates RESTRICT
+--
+
+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 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)';
+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:
+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'));
+
+--
+-- 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'));
+-- a PK delete that fails because both are referenced, but not 'til commit:
+BEGIN;
+  ALTER TABLE temporal_fk_mltrng2mltrng
+    ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk
+    DEFERRABLE INITIALLY DEFERRED;
+
+  DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
+COMMIT;
+
+--
+-- test FK referenced deletes RESTRICT
+--
+
+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 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:
+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'));
+
+--
+-- test FOREIGN KEY, box references box
+-- (not allowed: PERIOD part must be a range or multirange)
+--
+
+CREATE TABLE temporal_box (
+  id int4range,
+  valid_at box,
+  CONSTRAINT temporal_box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_box
+
+CREATE TABLE temporal_fk_box2box (
+  id int4range,
+  valid_at box,
+  parent_id int4range,
+  CONSTRAINT temporal_fk_box2box_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+  CONSTRAINT temporal_fk_box2box_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+    REFERENCES temporal_box (id, PERIOD valid_at)
+);
+
 --
 -- FK between partitioned tables
 --
-- 
2.42.0