v53-0008-Look-up-more-temporal-foreign-key-helper-procs.patch

From 2a36a771905359ad75b3b70f66bd37dd45261465 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Fri, 13 Jun 2025 16:11:47 -0700
Subject: [PATCH v53 08/10] Look up more temporal foreign key helper procs

To implement CASCADE/SET NULL/SET DEFAULT on temporal foreign keys, we
need an intersect function and a minus set-returning function. We can
look them up when we look up the operators already needed for temporal
foreign keys (including NO ACTION constraints).

Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
 src/backend/catalog/pg_constraint.c | 36 +++++++++++++++++++++++++----
 src/backend/commands/tablecmds.c    |  6 +++--
 src/backend/parser/analyze.c        |  2 +-
 src/backend/utils/adt/ri_triggers.c | 12 ++++++----
 src/include/catalog/pg_constraint.h | 10 ++++----
 5 files changed, 50 insertions(+), 16 deletions(-)

diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 6002fd0002f..4c8e242ad4a 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1633,7 +1633,7 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
 }
 
 /*
- * FindFKPeriodOpers -
+ * FindFKPeriodOpersAndProcs -
  *
  * Looks up the operator oids used for the PERIOD part of a temporal foreign key.
  * The opclass should be the opclass of that PERIOD element.
@@ -1644,12 +1644,19 @@ 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.
+ * intersectprocoid is used to limit the effect of CASCADE/SET NULL/SET DEFAULT
+ * when the PK record is changed with FOR PORTION OF.
+ * 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 *intersectprocoid,
+						  Oid *withoutportionoid)
 {
 	Oid			opfamily = InvalidOid;
 	Oid			opcintype = InvalidOid;
@@ -1691,6 +1698,17 @@ FindFKPeriodOpers(Oid opclass,
 							   aggedcontainedbyoperoid,
 							   &strat);
 
+	/*
+	 * Hardcode intersect operators for ranges and multiranges, because we
+	 * don't have a better way to look up operators that aren't used in
+	 * indexes.
+	 *
+	 * If you change this code, you must change the code in
+	 * transformForPortionOfClause.
+	 *
+	 * XXX: Find a more extensible way to look up the operator, permitting
+	 * user-defined types.
+	 */
 	switch (opcintype)
 	{
 		case ANYRANGEOID:
@@ -1702,6 +1720,14 @@ FindFKPeriodOpers(Oid opclass,
 		default:
 			elog(ERROR, "unexpected opcintype: %u", opcintype);
 	}
+
+	/*
+	 * Look up the intersect proc. We use this for FOR PORTION OF (both the
+	 * operation itself and when checking foreign keys). If this is missing we
+	 * don't need to complain here, because FOR PORTION OF will not be
+	 * allowed.
+	 */
+	*intersectprocoid = get_opcode(*intersectoperoid);
 }
 
 /*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6f5d7648ade..adcfc9ee8aa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -10536,9 +10536,11 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		Oid			periodoperoid;
 		Oid			aggedperiodoperoid;
 		Oid			intersectoperoid;
+		Oid			intersectprocoid;
+		Oid			withoutoverlapsoid;
 
-		FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
-						  &intersectoperoid);
+		FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid,
+								  &intersectoperoid, &intersectprocoid, &withoutoverlapsoid);
 	}
 
 	/* First, create the constraint catalog entry itself. */
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 39199c9f032..cca38df31da 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1434,7 +1434,7 @@ transformForPortionOfClause(ParseState *pstate,
 		/*
 		 * Whatever operator is used for intersect by temporal foreign keys,
 		 * we can use its backing procedure for intersects in FOR PORTION OF.
-		 * XXX: Share code with FindFKPeriodOpers?
+		 * XXX: Share code with FindFKPeriodOpersAndProcs?
 		 */
 		switch (opcintype)
 		{
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index d6b1eb57a48..fc8d59e0f47 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -129,6 +129,8 @@ 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 (or multirange) */
+	Oid			period_intersect_proc;	/* anyrange * anyrange (or multirange) */
+	Oid			without_portion_proc;	/* anyrange - anyrange SRF */
 	dlist_node	valid_link;		/* Link in list of valid entries */
 } RI_ConstraintInfo;
 
@@ -2337,10 +2339,12 @@ 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->period_intersect_proc,
+								  &riinfo->without_portion_proc);
 	}
 
 	ReleaseSysCache(tup);
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 4afceb5c692..f8a01d89617 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -288,10 +288,12 @@ 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 *intersectprocoid,
+									  Oid *withoutportionoid);
 
 extern bool check_functional_grouping(Oid relid,
 									  Index varno, Index varlevelsup,
-- 
2.45.0