From a16353f34b6c09087a7a5a5120c5a061833c7797 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 13 Jun 2025 16:11:47 -0700 Subject: [PATCH v57 08/11] 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 --- 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 9f138815970..de0f3af46c5 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10539,9 +10539,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 6890a67fb3c..2d22a6ee5c0 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1446,7 +1446,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.39.5