v44-0003-Fix-NOACTION-temporal-foreign-keys-when-the-refe.patch

From bb8fd8f4458f36e3ba218971321b4bd85652f873 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 9 Nov 2024 14:05:48 -0800
Subject: [PATCH v44 3/4] Fix NOACTION temporal foreign keys when the
 referenced endpoints change

If a referenced UPDATE changes the temporal start/end times, shrinking
the span the row is valid, we get a false return from ri_Check_Pk_Match,
but overlapping references may still be valid, if their reference didn't
overlap with the removed span.

We need to consider what span(s) are still provided in the referenced
table. Instead of returning that from ri_Check_Pk_Match, we can just
we look it up in the main SQL query.

Reported-by: Sam Gabrielsson
Author: Paul Jungwirth
---
 src/backend/catalog/pg_constraint.c           | 14 ++-
 src/backend/commands/tablecmds.c              |  4 +-
 src/backend/utils/adt/ri_triggers.c           | 99 ++++++++++++++++++-
 src/include/catalog/pg_constraint.h           |  3 +-
 src/include/catalog/pg_operator.dat           |  6 +-
 .../regress/expected/without_overlaps.out     | 68 ++++++++++++-
 src/test/regress/sql/without_overlaps.sql     | 60 ++++++++++-
 7 files changed, 243 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 9c05a98d28c..c7fdbeedc0a 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1613,7 +1613,8 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 void
 FindFKPeriodOpers(Oid opclass,
 				  Oid *containedbyoperoid,
-				  Oid *aggedcontainedbyoperoid)
+				  Oid *aggedcontainedbyoperoid,
+				  Oid *intersectoperoid)
 {
 	Oid			opfamily = InvalidOid;
 	Oid			opcintype = InvalidOid;
@@ -1654,6 +1655,17 @@ FindFKPeriodOpers(Oid opclass,
 									 ANYMULTIRANGEOID,
 									 aggedcontainedbyoperoid,
 									 &strat);
+
+	switch (opcintype) {
+		case ANYRANGEOID:
+			*intersectoperoid = OID_RANGE_INTERSECT_RANGE_OP;
+			break;
+		case ANYMULTIRANGEOID:
+			*intersectoperoid = OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP;
+			break;
+		default:
+			elog(ERROR, "Unexpected opcintype: %u", opcintype);
+	}
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cbd1d9c56fc..dc1e7d4f439 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10176,8 +10176,10 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 	{
 		Oid			periodoperoid;
 		Oid			aggedperiodoperoid;
+		Oid			intersectoperoid;
 
-		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid);
+		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
+						  &intersectoperoid);
 	}
 
 	/* 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 6896e1ae638..a06fcafdb7e 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -129,6 +129,7 @@ typedef struct RI_ConstraintInfo
 	Oid			ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
 	Oid			period_contained_by_oper;	/* anyrange <@ anyrange */
 	Oid			agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */
+	Oid			period_intersect_oper;	/* anyrange * anyrange */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -731,8 +732,11 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	 * not do anything.  However, this check should only be made in the NO
 	 * ACTION case; in RESTRICT cases we don't wish to allow another row to be
 	 * substituted.
+	 *
+	 * If the foreign key has PERIOD, we incorporate looking for replacement
+	 * rows in the main SQL query below, so we needn't do it here.
 	 */
-	if (is_no_action &&
+	if (is_no_action && !riinfo->hasperiod &&
 		ri_Check_Pk_Match(pk_rel, fk_rel, oldslot, riinfo))
 	{
 		table_close(fk_rel, RowShareLock);
@@ -750,8 +754,10 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 	if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
 	{
 		StringInfoData querybuf;
+		char		pkrelname[MAX_QUOTED_REL_NAME_LEN];
 		char		fkrelname[MAX_QUOTED_REL_NAME_LEN];
 		char		attname[MAX_QUOTED_NAME_LEN];
+		char		periodattname[MAX_QUOTED_NAME_LEN];
 		char		paramname[16];
 		const char *querysep;
 		Oid			queryoids[RI_MAX_NUMKEYS];
@@ -763,6 +769,26 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 		 *		   FOR KEY SHARE OF x
 		 * The type id's for the $ parameters are those of the
 		 * corresponding PK attributes.
+		 *
+		 * For temporal foreign keys a reference could still be valid,
+		 * if the referenced range didn't change too much.
+		 * Also if the referencing time span extends past the current PK row,
+		 * we don't want to check that part: some other PK row should fulfill it.
+		 * We only want to validate the part matching the PK record we've changed.
+		 * Therefore to find invalid records we do this:
+		 * SELECT 1
+		 * FROM [ONLY] <fktable> x
+		 * WHERE $1 = x.fkatt1 [AND ...] AND $n && x.fkperiod
+		 * AND NOT coalesce((x.fkperiod * $n) <@
+		 *  (SELECT range_agg(r)
+		 *   FROM (SELECT y.pkperiod r
+		 *         FROM [ONLY] <pktable> y
+		 *         WHERE $1 = y.pkatt1 [AND ...] AND $n && y.pkperiod
+		 *         FOR KEY SHARE OF y), false)
+		 * FOR KEY SHARE OF x
+		 * 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.
 		 * ----------
 		 */
 		initStringInfo(&querybuf);
@@ -791,6 +817,74 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
 			querysep = "AND";
 			queryoids[i] = pk_type;
 		}
+
+		/*
+		 * Don't fail if the remaining history still fulfills the references.
+		 * Only check the part of the references intersecting with oldslot,
+		 * since the rest would be fulfilled by some other pk record.
+		 *
+		 * For NOACTION we can query the pk table and use whatever we find
+		 * (instead of calling ri_Check_Pk_Match above).
+		 */
+		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]);
+			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]));
+			sprintf(paramname, "$%d", riinfo->nkeys);
+
+			appendStringInfoString(&querybuf, " AND NOT coalesce(");
+
+			/* Intersect the fk with the old pk range */
+			initStringInfo(&intersectbuf);
+			appendStringInfoString(&intersectbuf, "(");
+			ri_GenerateQual(&intersectbuf, "",
+							attname, fk_period_type,
+							riinfo->period_intersect_oper,
+							paramname, pk_period_type);
+			appendStringInfoString(&intersectbuf, ")");
+
+			/* Find the remaining history */
+			initStringInfo(&replacementsbuf);
+			appendStringInfoString(&replacementsbuf, "(SELECT pg_catalog.range_agg(r) FROM ");
+
+			quoteOneName(periodattname, RIAttName(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]));
+			quoteRelationName(pkrelname, pk_rel);
+			appendStringInfo(&replacementsbuf, "(SELECT y.%s r FROM %s%s y",
+							 periodattname, pk_only, pkrelname);
+
+			/* Restrict pk rows to what matches */
+			querysep = "WHERE";
+			for (int i = 0; i < riinfo->nkeys; i++)
+			{
+				Oid		pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+
+				quoteOneName(attname,
+							 RIAttName(pk_rel, riinfo->pk_attnums[i]));
+				sprintf(paramname, "$%d", i + 1);
+				ri_GenerateQual(&replacementsbuf, querysep,
+								paramname, pk_type,
+								riinfo->pp_eq_oprs[i],
+								attname, pk_type);
+				querysep = "AND";
+				queryoids[i] = pk_type;
+			}
+			appendStringInfoString(&replacementsbuf, " FOR KEY SHARE OF y)");
+			appendStringInfoString(&replacementsbuf, " y2)");
+
+			ri_GenerateQual(&querybuf, "",
+							intersectbuf.data, fk_period_type,
+							riinfo->agged_period_contained_by_oper,
+							replacementsbuf.data, ANYMULTIRANGEOID);
+			/* end of coalesce: */
+			appendStringInfoString(&querybuf, ", false)");
+		}
+
 		appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
 
 		/* Prepare and save the plan */
@@ -2262,7 +2356,8 @@ ri_LoadConstraintInfo(Oid constraintOid)
 
 		FindFKPeriodOpers(opclass,
 						  &riinfo->period_contained_by_oper,
-						  &riinfo->agged_period_contained_by_oper);
+						  &riinfo->agged_period_contained_by_oper,
+						  &riinfo->period_intersect_oper);
 	}
 
 	ReleaseSysCache(tup);
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 4b4476738a2..5edd4eb8a33 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -288,7 +288,8 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 									   int *num_fk_del_set_cols, AttrNumber *fk_del_set_cols);
 extern void FindFKPeriodOpers(Oid opclass,
 							  Oid *containedbyoperoid,
-							  Oid *aggedcontainedbyoperoid);
+							  Oid *aggedcontainedbyoperoid,
+							  Oid *intersectoperoid);
 
 extern bool check_functional_grouping(Oid relid,
 									  Index varno, Index varlevelsup,
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index 0e7511dde1c..aeddd292e2c 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -3150,7 +3150,8 @@
 { oid => '3899', descr => 'range difference',
   oprname => '-', oprleft => 'anyrange', oprright => 'anyrange',
   oprresult => 'anyrange', oprcode => 'range_minus' },
-{ oid => '3900', descr => 'range intersection',
+{ oid => '3900', oid_symbol => 'OID_RANGE_INTERSECT_RANGE_OP',
+  descr => 'range intersection',
   oprname => '*', oprleft => 'anyrange', oprright => 'anyrange',
   oprresult => 'anyrange', oprcom => '*(anyrange,anyrange)',
   oprcode => 'range_intersect' },
@@ -3417,7 +3418,8 @@
 { oid => '4393', descr => 'multirange minus',
   oprname => '-', oprleft => 'anymultirange', oprright => 'anymultirange',
   oprresult => 'anymultirange', oprcode => 'multirange_minus' },
-{ oid => '4394', descr => 'multirange intersect',
+{ oid => '4394', oid_symbol => 'OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP',
+  descr => 'multirange intersect',
   oprname => '*', oprleft => 'anymultirange', oprright => 'anymultirange',
   oprresult => 'anymultirange', oprcom => '*(anymultirange,anymultirange)',
   oprcode => 'multirange_intersect' },
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 319662bd58d..df955bd1b3c 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -1681,7 +1681,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:
@@ -1694,6 +1695,37 @@ 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;
+ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
+DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still 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 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 still 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:
 UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
@@ -2169,7 +2201,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:
@@ -2182,6 +2215,37 @@ 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;
+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)=([1,2), {[2018-01-01,2018-03-01)}) is still 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 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 still 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:
 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'));
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index ebee2f2932e..9b844d1fa3b 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -1234,7 +1234,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:
@@ -1247,6 +1248,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:
 UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
 WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
@@ -1691,7 +1719,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:
@@ -1704,6 +1733,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:
 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'));
-- 
2.42.0