v46-0003-Fix-RESTRICT-temporal-foreign-keys-when-the-refe.patch

From 61641714fbf27ae13415de3ff8c66ca1ef8299f2 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 12 Nov 2024 09:19:44 -0800
Subject: [PATCH v46 3/8] Fix RESTRICT temporal foreign keys when the
 referenced endpoints change

A RESTICT foreign key must fail if the referenced key changes. A
temporal RESTRICT foreign key should fail if the key changes for a span
that is referenced. Changing the key for an unreferenced span is okay,
even if that row is referenced during some portion of its span that
didn't change. Therefore we have to compute which part(s) were lost, and
fail if we find any references overlapping those parts. We can still use
the same SQL as normal, because we need to adjust the PERIOD parameter
we search for. We can call without_portion to get the lost parts: they
are oldslot.pkperiod - newslot.pkperiod. This commit adds code for that
to ri_restrict, along with tests.

Author: Paul Jungwirth
---
 src/backend/catalog/pg_constraint.c           |  25 ++-
 src/backend/commands/tablecmds.c              |   6 +-
 src/backend/utils/adt/ri_triggers.c           | 158 +++++++++++++++---
 src/include/catalog/pg_constraint.h           |   9 +-
 .../regress/expected/without_overlaps.out     |  68 +++++++-
 src/test/regress/sql/without_overlaps.sql     |  60 ++++++-
 6 files changed, 291 insertions(+), 35 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index ca00569c40b..fbdbdb80879 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1600,9 +1600,9 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 }
 
 /*
- * FindFKPeriodOpers -
+ * FindFkPeriodOpersAndProcs -
  *
- * Looks up the operator oids used for the PERIOD part of a temporal foreign key.
+ * Looks up the operator and procedure oids used for the PERIOD part of a temporal foreign key.
  * 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.
@@ -1611,12 +1611,16 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
  * That way foreign keys can compare fkattr <@ range_agg(pkattr).
  * intersectoperoid is used by NO ACTION constraints to trim the range being considered
  * to just what was updated/deleted.
+ * withoutportionoid is a set-returning function computing
+ * the difference between one range and another,
+ * returning each result range in a separate row.
  */
 void
-FindFKPeriodOpers(Oid opclass,
-				  Oid *containedbyoperoid,
-				  Oid *aggedcontainedbyoperoid,
-				  Oid *intersectoperoid)
+FindFKPeriodOpersAndProcs(Oid opclass,
+						  Oid *containedbyoperoid,
+						  Oid *aggedcontainedbyoperoid,
+						  Oid *intersectoperoid,
+						  Oid *withoutportionoid)
 {
 	Oid			opfamily = InvalidOid;
 	Oid			opcintype = InvalidOid;
@@ -1668,6 +1672,15 @@ FindFKPeriodOpers(Oid opclass,
 		default:
 			elog(ERROR, "Unexpected opcintype: %u", opcintype);
 	}
+
+	*withoutportionoid = InvalidOid;
+	*withoutportionoid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_WITHOUT_PORTION_PROC);
+	if (!OidIsValid(*withoutportionoid))
+		ereport(ERROR,
+				errcode(ERRCODE_UNDEFINED_OBJECT),
+				errmsg("could not identify a without_overlaps support function for type %s", format_type_be(opcintype)),
+				errhint("Define a without_overlaps support function for operator class \"%d\" for access method \"%s\".",
+						opclass, "gist"));
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7e2270d87f6..356fb6d1ea6 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10229,9 +10229,11 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		Oid			periodoperoid;
 		Oid			aggedperiodoperoid;
 		Oid			intersectoperoid;
+		Oid			withoutoverlapsoid;
 
-		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
-						  &intersectoperoid);
+		FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid,
+								  &aggedperiodoperoid, &intersectoperoid,
+								  &withoutoverlapsoid);
 	}
 
 	/* First, create the constraint catalog entry itself. */
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 89fd84b7b9f..17b671450df 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -130,6 +130,7 @@ typedef struct RI_ConstraintInfo
 	Oid			period_contained_by_oper;	/* anyrange <@ anyrange */
 	Oid			agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */
 	Oid			period_intersect_oper;	/* anyrange * anyrange */
+	Oid			without_portion_proc;	/* anyrange - anyrange SRF */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -207,7 +208,7 @@ static void ri_BuildQueryKey(RI_QueryKey *key,
 							 const RI_ConstraintInfo *riinfo,
 							 int32 constr_queryno);
 static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
-						 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
+						 const RI_ConstraintInfo *riinfo, bool rel_is_pk, bool skip_period);
 static bool ri_CompareWithCast(Oid eq_opr, Oid typeid, Oid collid,
 							   Datum lhs, Datum rhs);
 
@@ -229,6 +230,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 							RI_QueryKey *qkey, SPIPlanPtr qplan,
 							Relation fk_rel, Relation pk_rel,
 							TupleTableSlot *oldslot, TupleTableSlot *newslot,
+							int periodParam, Datum period,
 							bool is_restrict,
 							bool detectNewRows, int expect_OK);
 static void ri_ExtractValues(Relation rel, TupleTableSlot *slot,
@@ -451,6 +453,7 @@ RI_FKey_check(TriggerData *trigdata)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					NULL, newslot,
+					-1, (Datum) 0,
 					false,
 					pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE,
 					SPI_OK_SELECT);
@@ -616,6 +619,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
 	result = ri_PerformCheck(riinfo, &qkey, qplan,
 							 fk_rel, pk_rel,
 							 oldslot, NULL,
+							 -1, (Datum) 0,
 							 false,
 							 true,	/* treat like update */
 							 SPI_OK_SELECT);
@@ -714,8 +718,18 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	Relation	fk_rel;
 	Relation	pk_rel;
 	TupleTableSlot *oldslot;
+	TupleTableSlot *newslot;
 	RI_QueryKey qkey;
 	SPIPlanPtr	qplan;
+	AttrNumber	pkperiodattno = InvalidAttrNumber;
+	AttrNumber	fkperiodattno = InvalidAttrNumber;
+	int			targetRangeParam = -1;
+	Datum		targetRange = (Datum) 0;
+	FmgrInfo	flinfo;
+	ReturnSetInfo	rsi;
+	LOCAL_FCINFO(fcinfo, 2);
+	bool		multiplelost = false;
+	bool		finished = false;
 
 	riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
 									trigdata->tg_relation, true);
@@ -729,6 +743,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	fk_rel = table_open(riinfo->fk_relid, RowShareLock);
 	pk_rel = trigdata->tg_relation;
 	oldslot = trigdata->tg_trigslot;
+	newslot = trigdata->tg_newslot;
 
 	/*
 	 * If another PK row now exists providing the old key values, we should
@@ -746,6 +761,12 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		return PointerGetDatum(NULL);
 	}
 
+	if (riinfo->hasperiod)
+	{
+		pkperiodattno = riinfo->pk_attnums[riinfo->nkeys - 1];
+		fkperiodattno = riinfo->fk_attnums[riinfo->nkeys - 1];
+	}
+
 	SPI_connect();
 
 	/*
@@ -792,6 +813,16 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 * We need the coalesce in case the first subquery returns no rows.
 		 * We need the second subquery because FOR KEY SHARE doesn't support
 		 * aggregate queries.
+		 *
+		 * For RESTRICT keys we can't query pktable, so instead we use the old
+		 * and new periods to see what was removed, and look for references
+		 * matching that. If the scalar key part changed, then this is
+		 * (where $n is the old period and $2n the new):
+		 *   $n && x.fkperiod
+		 * But if the scalar key part didn't change, then we only lost part of
+		 * the time span, so we should look for:
+		 *   (SELECT range_agg(r) FROM without_portion($n, $2n) wo(r))
+		 *     && x.fkperiod
 		 * ----------
 		 */
 		initStringInfo(&querybuf);
@@ -827,14 +858,14 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 */
 		if (riinfo->hasperiod && is_no_action)
 		{
-			Oid				pk_period_type = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
-			Oid				fk_period_type = RIAttType(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]);
+			Oid				pk_period_type = RIAttType(pk_rel, pkperiodattno);
+			Oid				fk_period_type = RIAttType(fk_rel, fkperiodattno);
 			StringInfoData	intersectbuf;
 			StringInfoData	replacementsbuf;
 			char *pk_only = pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
 				"" : "ONLY ";
 
-			quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+			quoteOneName(attname, RIAttName(fk_rel, fkperiodattno));
 			sprintf(paramname, "$%d", riinfo->nkeys);
 
 			appendStringInfoString(&querybuf, " AND NOT coalesce(");
@@ -852,7 +883,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 			initStringInfo(&replacementsbuf);
 			appendStringInfoString(&replacementsbuf, "(SELECT pg_catalog.range_agg(r) FROM ");
 
-			quoteOneName(periodattname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]));
+			quoteOneName(periodattname, RIAttName(pk_rel, pkperiodattno));
 			quoteRelationName(pkrelname, pk_rel);
 			appendStringInfo(&replacementsbuf, "(SELECT y.%s r FROM %s%s y",
 							 periodattname, pk_only, pkrelname);
@@ -892,13 +923,86 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 
 	/*
 	 * We have a plan now. Run it to check for existing references.
+	 *
+	 * Normally we only loop once here. But if
+	 * we have a RESTRICT constraint with a PERIOD,
+	 * we must only consider the timespan that was lost.
+	 *
+	 * If the scalar key part was UPDATEd,
+	 * then all of oldslot.pkperiod was lost
+	 * (whether the endpoints changed or not).
+	 * That's what we already check by default.
+	 *
+	 * Otherwise only oldslot.pkperiod - newslot.pkperiod was lost.
+	 * That may be more than one range, so we use the
+	 * without_portion set-returning function and loop
+	 * over its results. It also may be empty,
+	 * meaning nothing was lost, and no check is required.
+	 * We shouldn't be here if neither the scalar nor PERIOD part changed,
+	 * but it's easy to support anyway.
+	 *
+	 * For a DELETE, oldslot.pkperiod was lost,
+	 * which is what we check for by default.
 	 */
-	ri_PerformCheck(riinfo, &qkey, qplan,
-					fk_rel, pk_rel,
-					oldslot, NULL,
-					!is_no_action,
-					true,		/* must detect new rows */
-					SPI_OK_SELECT);
+	if (riinfo->hasperiod && !is_no_action)
+	{
+		if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)
+			&& ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true, true))
+		{
+			multiplelost = true;
+
+			fmgr_info(riinfo->without_portion_proc, &flinfo);
+			rsi.type = T_ReturnSetInfo;
+			rsi.econtext = CreateStandaloneExprContext();
+			rsi.expectedDesc = NULL;
+			rsi.allowedModes = (int) (SFRM_ValuePerCall);
+			rsi.returnMode = SFRM_ValuePerCall;
+			rsi.setResult = NULL;
+			rsi.setDesc = NULL;
+
+			InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi);
+			fcinfo->args[0].value = slot_getattr(oldslot, pkperiodattno,
+												 &fcinfo->args[0].isnull);
+			fcinfo->args[1].value = slot_getattr(newslot, pkperiodattno,
+												 &fcinfo->args[1].isnull);
+
+			targetRangeParam = riinfo->nkeys;
+		}
+	}
+
+	while (!finished)
+	{
+		if (multiplelost)
+		{
+			/* Compute a span that was actually lost. */
+			targetRange = FunctionCallInvoke(fcinfo);
+
+			/*
+			 * If we have no more lost spans to check, we're done.
+			 * If no span was lost, we don't even need to check the foreign key.
+			 */
+			if (rsi.isDone == ExprEndResult)
+				break;
+
+			if (fcinfo->isnull)
+				elog(ERROR, "Get a null from without_portion function");
+		}
+		else
+			finished = true;
+
+		ri_PerformCheck(riinfo, &qkey, qplan,
+						fk_rel, pk_rel,
+						oldslot, NULL,
+						targetRangeParam, targetRange,
+						!is_no_action,
+						true,		/* must detect new rows */
+						SPI_OK_SELECT);
+
+	}
+
+	/* Free this before we shut down SPI since our memctx is a child */
+	if (multiplelost)
+		FreeExprContext(rsi.econtext, false);
 
 	if (SPI_finish() != SPI_OK_FINISH)
 		elog(ERROR, "SPI_finish failed");
@@ -998,6 +1102,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, NULL,
+					-1, (Datum) 0,
 					false,
 					true,		/* must detect new rows */
 					SPI_OK_DELETE);
@@ -1115,6 +1220,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, newslot,
+					-1, (Datum) 0,
 					false,
 					true,		/* must detect new rows */
 					SPI_OK_UPDATE);
@@ -1343,6 +1449,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
 	ri_PerformCheck(riinfo, &qkey, qplan,
 					fk_rel, pk_rel,
 					oldslot, NULL,
+					-1, (Datum) 0,
 					false,
 					true,		/* must detect new rows */
 					SPI_OK_UPDATE);
@@ -1401,7 +1508,7 @@ RI_FKey_pk_upd_check_required(Trigger *trigger, Relation pk_rel,
 		return false;
 
 	/* If all old and new key values are equal, no check is needed */
-	if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true))
+	if (newslot && ri_KeysEqual(pk_rel, oldslot, newslot, riinfo, true, false))
 		return false;
 
 	/* Else we need to fire the trigger. */
@@ -1494,7 +1601,7 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
 		return true;
 
 	/* If all old and new key values are equal, no check is needed */
-	if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false))
+	if (ri_KeysEqual(fk_rel, oldslot, newslot, riinfo, false, false))
 		return false;
 
 	/* Else we need to fire the trigger. */
@@ -2342,10 +2449,11 @@ ri_LoadConstraintInfo(Oid constraintOid)
 	{
 		Oid			opclass = get_index_column_opclass(conForm->conindid, riinfo->nkeys);
 
-		FindFKPeriodOpers(opclass,
-						  &riinfo->period_contained_by_oper,
-						  &riinfo->agged_period_contained_by_oper,
-						  &riinfo->period_intersect_oper);
+		FindFKPeriodOpersAndProcs(opclass,
+								  &riinfo->period_contained_by_oper,
+								  &riinfo->agged_period_contained_by_oper,
+								  &riinfo->period_intersect_oper,
+								  &riinfo->without_portion_proc);
 	}
 
 	ReleaseSysCache(tup);
@@ -2488,6 +2596,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 				RI_QueryKey *qkey, SPIPlanPtr qplan,
 				Relation fk_rel, Relation pk_rel,
 				TupleTableSlot *oldslot, TupleTableSlot *newslot,
+				int periodParam, Datum period,
 				bool is_restrict,
 				bool detectNewRows, int expect_OK)
 {
@@ -2544,6 +2653,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
 		ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
 						 vals, nulls);
 	}
+	/* Add/replace a query param for the PERIOD if needed */
+	if (period)
+	{
+		vals[periodParam - 1] = period;
+		nulls[periodParam - 1] = ' ';
+	}
 
 	/*
 	 * In READ COMMITTED mode, we just need to use an up-to-date regular
@@ -2978,6 +3093,9 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan)
  * For normal FKs we check for equality.
  * For temporal FKs we check that the PK side is a superset of its old value,
  * or the FK side is a subset of its old value.
+ * If skip_period is set, we ignore the last key element.
+ * This lets us ask if the scalar key parts changed,
+ * ignoring the PERIOD.
  *
  * Note: at some point we might wish to redefine this as checking for
  * "IS NOT DISTINCT" rather than "=", that is, allow two nulls to be
@@ -2986,17 +3104,19 @@ ri_HashPreparedPlan(RI_QueryKey *key, SPIPlanPtr plan)
  */
 static bool
 ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
-			 const RI_ConstraintInfo *riinfo, bool rel_is_pk)
+			 const RI_ConstraintInfo *riinfo, bool rel_is_pk, bool skip_period)
 {
 	const int16 *attnums;
 
+	Assert(skip_period ? riinfo->hasperiod : true);
+
 	if (rel_is_pk)
 		attnums = riinfo->pk_attnums;
 	else
 		attnums = riinfo->fk_attnums;
 
 	/* XXX: could be worthwhile to fetch all necessary attrs at once */
-	for (int i = 0; i < riinfo->nkeys; i++)
+	for (int i = 0; i < riinfo->nkeys - (skip_period ? 1 : 0); i++)
 	{
 		Datum		oldvalue;
 		Datum		newvalue;
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 1c286997b80..0d90dd0c179 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -286,10 +286,11 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 									   AttrNumber *conkey, AttrNumber *confkey,
 									   Oid *pf_eq_oprs, Oid *pp_eq_oprs, Oid *ff_eq_oprs,
 									   int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols);
-extern void FindFKPeriodOpers(Oid opclass,
-							  Oid *containedbyoperoid,
-							  Oid *aggedcontainedbyoperoid,
-							  Oid *intersectoperoid);
+extern void FindFKPeriodOpersAndProcs(Oid opclass,
+									  Oid *containedbyoperoid,
+									  Oid *aggedcontainedbyoperoid,
+									  Oid *intersectoperoid,
+									  Oid *withoutportionoid);
 
 extern bool check_functional_grouping(Oid relid,
 									  Index varno, Index varlevelsup,
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 92b9c29917f..3bcae04a269 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1778,7 +1778,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1793,6 +1794,37 @@ SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-
 WHERE id = '[6,7)';
 ERROR:  update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
 DETAIL:  Key (id, valid_at)=([6,7), [2018-01-01,2018-02-01)) is referenced from table "temporal_fk_rng2rng".
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is referenced from table "temporal_fk_rng2rng".
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_rng" violates RESTRICT setting of foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is referenced from table "temporal_fk_rng2rng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_rng2rng
@@ -2294,7 +2326,8 @@ 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)');
+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 sliding the edge between two referenced rows:
@@ -2309,6 +2342,37 @@ SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(date
 WHERE id = '[6,7)';
 ERROR:  update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
 DETAIL:  Key (id, valid_at)=([6,7), {[2018-01-01,2018-02-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+ERROR:  update or delete on table "temporal_mltrng" violates RESTRICT setting of foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
+DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is referenced from table "temporal_fk_mltrng2mltrng".
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_mltrng2mltrng
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 8f472867e28..f7de3871093 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1316,7 +1316,8 @@ DELETE FROM temporal_rng WHERE id = '[5,6)';
 INSERT INTO temporal_rng (id, valid_at) VALUES
   ('[5,6)', daterange('2018-01-01', '2018-02-01')),
   ('[5,6)', daterange('2018-02-01', '2018-03-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
 UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
 -- A PK update sliding the edge between two referenced rows:
@@ -1329,6 +1330,33 @@ UPDATE temporal_rng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018-01-05')
                     WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced range but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[1,2)', daterange('2018-01-01', '2018-03-01')),
+  ('[1,2)', daterange('2018-03-01', '2018-06-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'),
+  ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced range is fine:
+UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced range and changing the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01')
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole range:
+UPDATE temporal_rng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_rng (id, valid_at) VALUES
+  ('[2,3)', daterange('2018-01-01', '2018-03-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES
+  ('[3,4)', daterange('2018-01-15', '2018-02-01'), '[2,3)');
+UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15')
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_rng2rng
@@ -1797,7 +1825,8 @@ 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)');
+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 sliding the edge between two referenced rows:
@@ -1810,6 +1839,33 @@ UPDATE temporal_mltrng
 SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('2018-01-01', '2018-01-05'))
                     WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
 WHERE id = '[6,7)';
+-- a PK update shrinking the referenced multirange but still valid:
+-- There are two references: one fulfilled by the first pk row,
+-- the other fulfilled by both pk rows combined.
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))),
+  ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'),
+  ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update growing the referenced multirange is fine:
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date;
+-- a PK update shrinking the referenced multirange and changing the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15', '2018-03-01'))
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update changing only the id invalidates the whole multirange:
+UPDATE temporal_mltrng SET id = '[2,3)'
+WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date;
+-- a PK update that loses time from both ends, but is still valid:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES
+  ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES
+  ('[3,4)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)');
+UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02-15'))
+WHERE id = '[2,3)';
 -- a PK update that fails because both are referenced (even before commit):
 BEGIN;
   ALTER TABLE temporal_fk_mltrng2mltrng
-- 
2.42.0