v37-0007-Add-CASCADE-SET-NULL-SET-DEFAULT-for-temporal-fo.patch
text/x-patch
Filename: v37-0007-Add-CASCADE-SET-NULL-SET-DEFAULT-for-temporal-fo.patch
Type: text/x-patch
Part: 6
Message:
Re: SQL:2011 application time
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v37-0007
Subject: Add CASCADE/SET NULL/SET DEFAULT for temporal foreign keys
| File | + | − |
|---|---|---|
| doc/src/sgml/ref/create_table.sgml | 11 | 3 |
| src/backend/catalog/pg_constraint.c | 16 | 4 |
| src/backend/commands/tablecmds.c | 48 | 35 |
| src/backend/utils/adt/ri_triggers.c | 565 | 19 |
| src/include/catalog/pg_constraint.h | 4 | 3 |
| src/include/catalog/pg_proc.dat | 22 | 0 |
| src/test/regress/expected/btree_index.out | 12 | 6 |
| src/test/regress/expected/without_overlaps.out | 1523 | 28 |
| src/test/regress/sql/without_overlaps.sql | 884 | 18 |
From e3813f04877827abf29c14fdb8953c2f4c0ffedc Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Sat, 3 Jun 2023 21:41:11 -0400
Subject: [PATCH v37 7/8] Add CASCADE/SET NULL/SET DEFAULT for temporal foreign
keys
---
doc/src/sgml/ref/create_table.sgml | 14 +-
src/backend/catalog/pg_constraint.c | 20 +-
src/backend/commands/tablecmds.c | 83 +-
src/backend/utils/adt/ri_triggers.c | 584 ++++++-
src/include/catalog/pg_constraint.h | 7 +-
src/include/catalog/pg_proc.dat | 22 +
src/test/regress/expected/btree_index.out | 18 +-
.../regress/expected/without_overlaps.out | 1551 ++++++++++++++++-
src/test/regress/sql/without_overlaps.sql | 902 +++++++++-
9 files changed, 3085 insertions(+), 116 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 30150eebfa0..c6bc37d6e81 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1265,7 +1265,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- In a temporal foreign key, this option is not supported.
+ In a temporal foreign key, the delete/update will use
+ <literal>FOR PORTION OF</literal> semantics to constrain the
+ effect to the bounds being deleted/updated in the referenced row.
</para>
</listitem>
</varlistentry>
@@ -1280,7 +1282,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- In a temporal foreign key, this option is not supported.
+ In a temporal foreign key, the change will use <literal>FOR PORTION
+ OF</literal> semantics to constrain the effect to the bounds being
+ deleted/updated in the referenced row. The column maked with
+ <literal>PERIOD</literal> will not be set to null.
</para>
</listitem>
</varlistentry>
@@ -1297,7 +1302,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- In a temporal foreign key, this option is not supported.
+ In a temporal foreign key, the change will use <literal>FOR PORTION
+ OF</literal> semantics to constrain the effect to the bounds being
+ deleted/updated in the referenced row. The column marked with
+ <literal>PERIOD</literal> with not be set to a default value.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index 1e2df031a84..1d7badbf34e 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -1351,7 +1351,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.
@@ -1360,11 +1360,14 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks,
* aggedcontainedbyoperoid is also a ContainedBy operator,
* but one whose rhs is a multirange.
* That way foreign keys can compare fkattr <@ range_agg(pkattr).
+ * intersectprocoid is used to limit the effect of CASCADE/SET NULL/SET DEFAULT
+ * when the PK record is changed with FOR PORTION OF.
*/
void
-FindFKPeriodOpers(Oid opclass,
- Oid *containedbyoperoid,
- Oid *aggedcontainedbyoperoid)
+FindFKPeriodOpersAndProcs(Oid opclass,
+ Oid *containedbyoperoid,
+ Oid *aggedcontainedbyoperoid,
+ Oid *intersectprocoid)
{
Oid opfamily = InvalidOid;
Oid opcintype = InvalidOid;
@@ -1405,6 +1408,15 @@ FindFKPeriodOpers(Oid opclass,
ANYMULTIRANGEOID,
aggedcontainedbyoperoid,
&strat);
+
+ /*
+ * If the command uses FOR PORTION OF,
+ * we will also need an intersect support proc.
+ * If this is missing we don't need to complain here,
+ * because FOR PORTION OF will not be allowed.
+ */
+ if (opfamily != InvalidOid && opcintype != InvalidOid)
+ *intersectprocoid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_INTERSECT_PROC);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8da83125262..d7bb1df259b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -515,7 +515,7 @@ static ObjectAddress addFkRecurseReferenced(List **wqueue, Constraint *fkconstra
bool old_check_ok,
Oid parentDelTrigger, Oid parentUpdTrigger,
bool with_period);
-static void validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
+static void validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, const int16 fkperiodattnum,
int numfksetcols, const int16 *fksetcolsattnums,
List *fksetcols);
static void addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint,
@@ -9595,6 +9595,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
int16 fkdelsetcols[INDEX_MAX_KEYS] = {0};
bool with_period;
bool pk_has_without_overlaps;
+ int16 fkperiodattnum = InvalidAttrNumber;
int i;
int numfks,
numpks,
@@ -9690,15 +9691,19 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
fkconstraint->fk_attrs,
fkattnum, fktypoid);
with_period = fkconstraint->fk_with_period || fkconstraint->pk_with_period;
- if (with_period && !fkconstraint->fk_with_period)
- ereport(ERROR,
- errcode(ERRCODE_INVALID_FOREIGN_KEY),
- errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"));
+ if (with_period)
+ {
+ if (!fkconstraint->fk_with_period)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_FOREIGN_KEY),
+ errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")));
+ fkperiodattnum = fkattnum[numfks - 1];
+ }
numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
fkconstraint->fk_del_set_cols,
fkdelsetcols, NULL);
- validateFkOnDeleteSetColumns(numfks, fkattnum,
+ validateFkOnDeleteSetColumns(numfks, fkattnum, fkperiodattnum,
numfkdelsetcols, fkdelsetcols,
fkconstraint->fk_del_set_cols);
@@ -9780,28 +9785,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
}
- /*
- * Some actions are currently unsupported for foreign keys using PERIOD.
- */
- if (fkconstraint->fk_with_period)
- {
- if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE ||
- fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL ||
- fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT)
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("unsupported %s action for foreign key constraint using PERIOD",
- "ON UPDATE"));
-
- if (fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE ||
- fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL ||
- fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT)
- ereport(ERROR,
- errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("unsupported %s action for foreign key constraint using PERIOD",
- "ON DELETE"));
- }
-
/*
* Look up the equality operators to use in the constraint.
*
@@ -10059,8 +10042,12 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
{
Oid periodoperoid;
Oid aggedperiodoperoid;
+ Oid intersectprocoid;
- FindFKPeriodOpers(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid);
+ FindFKPeriodOpersAndProcs(opclasses[numpks - 1],
+ &periodoperoid,
+ &aggedperiodoperoid,
+ &intersectprocoid);
}
/*
@@ -10114,6 +10101,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
void
validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
+ const int16 fkperiodattnum,
int numfksetcols, const int16 *fksetcolsattnums,
List *fksetcols)
{
@@ -10124,6 +10112,13 @@ validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums,
for (int j = 0; j < numfks; j++)
{
+ if (fkperiodattnum == setcol_attnum)
+ {
+ char *col = strVal(list_nth(fksetcols, i));
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column \"%s\" referenced in ON DELETE SET action cannot be PERIOD", col)));
+ }
if (fkattnums[j] == setcol_attnum)
{
seen = true;
@@ -12452,17 +12447,26 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
case FKCONSTR_ACTION_CASCADE:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_del");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del");
break;
case FKCONSTR_ACTION_SETNULL:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_del");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del");
break;
case FKCONSTR_ACTION_SETDEFAULT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_setdefault_del");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del");
break;
default:
elog(ERROR, "unrecognized FK action type: %d",
@@ -12513,17 +12517,26 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr
case FKCONSTR_ACTION_CASCADE:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_upd");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd");
break;
case FKCONSTR_ACTION_SETNULL:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_upd");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd");
break;
case FKCONSTR_ACTION_SETDEFAULT:
fk_trigger->deferrable = false;
fk_trigger->initdeferred = false;
- fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd");
+ if (fkconstraint->fk_with_period)
+ fk_trigger->funcname = SystemFuncName("RI_FKey_period_setdefault_upd");
+ else
+ fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd");
break;
default:
elog(ERROR, "unrecognized FK action type: %d",
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index d5b8666c216..9613bfaa71b 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -80,6 +80,12 @@
#define RI_PLAN_SETNULL_ONUPDATE 7
#define RI_PLAN_SETDEFAULT_ONDELETE 8
#define RI_PLAN_SETDEFAULT_ONUPDATE 9
+#define RI_PLAN_PERIOD_CASCADE_ONDELETE 10
+#define RI_PLAN_PERIOD_CASCADE_ONUPDATE 11
+#define RI_PLAN_PERIOD_SETNULL_ONUPDATE 12
+#define RI_PLAN_PERIOD_SETNULL_ONDELETE 13
+#define RI_PLAN_PERIOD_SETDEFAULT_ONUPDATE 14
+#define RI_PLAN_PERIOD_SETDEFAULT_ONDELETE 15
#define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3)
#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2)
@@ -128,7 +134,8 @@ 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; /* fkattr <@ range_agg(pkattr) */
+ Oid agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */
+ Oid period_intersect_proc; /* anyrange * anyrange (or multirange) */
dlist_node valid_link; /* Link in list of valid entries */
} RI_ConstraintInfo;
@@ -192,6 +199,7 @@ static bool ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
const RI_ConstraintInfo *riinfo);
static Datum ri_restrict(TriggerData *trigdata, bool is_no_action);
static Datum ri_set(TriggerData *trigdata, bool is_set_null, int tgkind);
+static Datum tri_set(TriggerData *trigdata, bool is_set_null, int tgkind);
static void quoteOneName(char *buffer, const char *name);
static void quoteRelationName(char *buffer, Relation rel);
static void ri_GenerateQual(StringInfo buf,
@@ -239,7 +247,7 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo,
int queryno, bool partgone) pg_attribute_noreturn();
static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal,
const RI_ConstraintInfo *riinfo);
-static Datum restrict_cascading_range(const ForPortionOfState *tg_temporal,
+static Datum restrict_cascading_range(Relation rel, const ForPortionOfState *tg_temporal,
const RI_ConstraintInfo *riinfo,
TupleTableSlot *oldslot);
@@ -816,7 +824,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
if (trigdata->tg_temporal)
{
targetRangeParam = riinfo->nkeys - 1;
- targetRange = restrict_cascading_range(trigdata->tg_temporal, riinfo, oldslot);
+ targetRange = restrict_cascading_range(pk_rel, trigdata->tg_temporal, riinfo, oldslot);
}
ri_PerformCheck(riinfo, &qkey, qplan,
@@ -1315,6 +1323,535 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
}
+/*
+ * RI_FKey_period_cascade_del -
+ *
+ * Cascaded delete foreign key references at delete event on temporal PK table.
+ */
+Datum
+RI_FKey_period_cascade_del(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ const RI_ConstraintInfo *riinfo;
+ Relation fk_rel;
+ Relation pk_rel;
+ TupleTableSlot *oldslot;
+ RI_QueryKey qkey;
+ SPIPlanPtr qplan;
+ Datum targetRange;
+
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_cascade_del", RI_TRIGTYPE_DELETE);
+
+ riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
+ trigdata->tg_relation, true);
+
+ /*
+ * Get the relation descriptors of the FK and PK tables and the old tuple.
+ *
+ * fk_rel is opened in RowExclusiveLock mode since that's what our
+ * eventual DELETE will get on it.
+ */
+ fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock);
+ pk_rel = trigdata->tg_relation;
+ oldslot = trigdata->tg_trigslot;
+
+ /*
+ * Don't delete than more than the PK's duration,
+ * trimmed by an original FOR PORTION OF if necessary.
+ */
+ targetRange = restrict_cascading_range(pk_rel, trigdata->tg_temporal, riinfo, oldslot);
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* Fetch or prepare a saved plan for the cascaded delete */
+ ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_PERIOD_CASCADE_ONDELETE);
+
+ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
+ {
+ StringInfoData querybuf;
+ char fkrelname[MAX_QUOTED_REL_NAME_LEN];
+ char attname[MAX_QUOTED_NAME_LEN];
+ char paramname[16];
+ const char *querysep;
+ Oid queryoids[RI_MAX_NUMKEYS + 1];
+ const char *fk_only;
+
+ /* ----------
+ * The query string built is
+ * DELETE FROM [ONLY] <fktable>
+ * FOR PORTION OF $fkatt (${n+1})
+ * WHERE $1 = fkatt1 [AND ...]
+ * The type id's for the $ parameters are those of the
+ * corresponding PK attributes.
+ * ----------
+ */
+ initStringInfo(&querybuf);
+ fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
+ quoteRelationName(fkrelname, fk_rel);
+ quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+
+ appendStringInfo(&querybuf, "DELETE FROM %s%s FOR PORTION OF %s ($%d)",
+ fk_only, fkrelname, attname, riinfo->nkeys + 1);
+ querysep = "WHERE";
+ for (int i = 0; i < riinfo->nkeys; i++)
+ {
+ Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+
+ quoteOneName(attname,
+ RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ sprintf(paramname, "$%d", i + 1);
+ ri_GenerateQual(&querybuf, querysep,
+ paramname, pk_type,
+ riinfo->pf_eq_oprs[i],
+ attname, fk_type);
+ if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
+ ri_GenerateQualCollation(&querybuf, pk_coll);
+ querysep = "AND";
+ queryoids[i] = pk_type;
+ }
+
+ /* Set a param for FOR PORTION OF TO/FROM */
+ queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
+
+ /* Prepare and save the plan */
+ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids,
+ &qkey, fk_rel, pk_rel);
+ }
+
+ /*
+ * We have a plan now. Build up the arguments from the key values in the
+ * deleted PK tuple and delete the referencing rows
+ */
+ ri_PerformCheck(riinfo, &qkey, qplan,
+ fk_rel, pk_rel,
+ oldslot, NULL,
+ riinfo->nkeys, targetRange,
+ true, /* must detect new rows */
+ SPI_OK_DELETE);
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+
+ table_close(fk_rel, RowExclusiveLock);
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * RI_FKey_period_cascade_upd -
+ *
+ * Cascaded update foreign key references at update event on temporal PK table.
+ */
+Datum
+RI_FKey_period_cascade_upd(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ const RI_ConstraintInfo *riinfo;
+ Relation fk_rel;
+ Relation pk_rel;
+ TupleTableSlot *oldslot;
+ TupleTableSlot *newslot;
+ RI_QueryKey qkey;
+ SPIPlanPtr qplan;
+ Datum targetRange;
+
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_cascade_upd", RI_TRIGTYPE_UPDATE);
+
+ riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
+ trigdata->tg_relation, true);
+
+ /*
+ * Get the relation descriptors of the FK and PK tables and the new and
+ * old tuple.
+ *
+ * fk_rel is opened in RowExclusiveLock mode since that's what our
+ * eventual UPDATE will get on it.
+ */
+ fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock);
+ pk_rel = trigdata->tg_relation;
+ newslot = trigdata->tg_newslot;
+ oldslot = trigdata->tg_trigslot;
+
+ /*
+ * Don't delete than more than the PK's duration,
+ * trimmed by an original FOR PORTION OF if necessary.
+ */
+ targetRange = restrict_cascading_range(pk_rel, trigdata->tg_temporal, riinfo, oldslot);
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* Fetch or prepare a saved plan for the cascaded update */
+ ri_BuildQueryKey(&qkey, riinfo, RI_PLAN_PERIOD_CASCADE_ONUPDATE);
+
+ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
+ {
+ StringInfoData querybuf;
+ StringInfoData qualbuf;
+ char fkrelname[MAX_QUOTED_REL_NAME_LEN];
+ char attname[MAX_QUOTED_NAME_LEN];
+ char paramname[16];
+ const char *querysep;
+ const char *qualsep;
+ Oid queryoids[2 * RI_MAX_NUMKEYS + 1];
+ const char *fk_only;
+
+ /* ----------
+ * The query string built is
+ * UPDATE [ONLY] <fktable>
+ * FOR PORTION OF $fkatt (${2n+1})
+ * SET fkatt1 = $1, [, ...]
+ * WHERE $n = fkatt1 [AND ...]
+ * The type id's for the $ parameters are those of the
+ * corresponding PK attributes. Note that we are assuming
+ * there is an assignment cast from the PK to the FK type;
+ * else the parser will fail.
+ * ----------
+ */
+ initStringInfo(&querybuf);
+ initStringInfo(&qualbuf);
+ fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
+ quoteRelationName(fkrelname, fk_rel);
+ quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+
+ appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s ($%d) SET",
+ fk_only, fkrelname, attname, 2 * riinfo->nkeys + 1);
+
+ querysep = "";
+ qualsep = "WHERE";
+ for (int i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
+ {
+ Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+
+ quoteOneName(attname,
+ RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ /*
+ * Don't set the temporal column(s).
+ * FOR PORTION OF will take care of that.
+ */
+ if (i < riinfo->nkeys - 1)
+ appendStringInfo(&querybuf,
+ "%s %s = $%d",
+ querysep, attname, i + 1);
+
+ sprintf(paramname, "$%d", j + 1);
+ ri_GenerateQual(&qualbuf, qualsep,
+ paramname, pk_type,
+ riinfo->pf_eq_oprs[i],
+ attname, fk_type);
+ if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
+ ri_GenerateQualCollation(&querybuf, pk_coll);
+ querysep = ",";
+ qualsep = "AND";
+ queryoids[i] = pk_type;
+ queryoids[j] = pk_type;
+ }
+ appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len);
+
+ /* Set a param for FOR PORTION OF TO/FROM */
+ queryoids[2 * riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
+
+ /* Prepare and save the plan */
+ qplan = ri_PlanCheck(querybuf.data, 2 * riinfo->nkeys + 1, queryoids,
+ &qkey, fk_rel, pk_rel);
+ }
+
+ /*
+ * We have a plan now. Run it to update the existing references.
+ */
+ ri_PerformCheck(riinfo, &qkey, qplan,
+ fk_rel, pk_rel,
+ oldslot, newslot,
+ riinfo->nkeys * 2, targetRange,
+ true, /* must detect new rows */
+ SPI_OK_UPDATE);
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+
+ table_close(fk_rel, RowExclusiveLock);
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * RI_FKey_period_setnull_del -
+ *
+ * Set foreign key references to NULL values at delete event on PK table.
+ */
+Datum
+RI_FKey_period_setnull_del(PG_FUNCTION_ARGS)
+{
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_setnull_del", RI_TRIGTYPE_DELETE);
+
+ /* Share code with UPDATE case */
+ return tri_set((TriggerData *) fcinfo->context, true, RI_TRIGTYPE_DELETE);
+}
+
+/*
+ * RI_FKey_period_setnull_upd -
+ *
+ * Set foreign key references to NULL at update event on PK table.
+ */
+Datum
+RI_FKey_period_setnull_upd(PG_FUNCTION_ARGS)
+{
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_setnull_upd", RI_TRIGTYPE_UPDATE);
+
+ /* Share code with DELETE case */
+ return tri_set((TriggerData *) fcinfo->context, true, RI_TRIGTYPE_UPDATE);
+}
+
+/*
+ * RI_FKey_period_setdefault_del -
+ *
+ * Set foreign key references to defaults at delete event on PK table.
+ */
+Datum
+RI_FKey_period_setdefault_del(PG_FUNCTION_ARGS)
+{
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_setdefault_del", RI_TRIGTYPE_DELETE);
+
+ /* Share code with UPDATE case */
+ return tri_set((TriggerData *) fcinfo->context, false, RI_TRIGTYPE_DELETE);
+}
+
+/*
+ * RI_FKey_period_setdefault_upd -
+ *
+ * Set foreign key references to defaults at update event on PK table.
+ */
+Datum
+RI_FKey_period_setdefault_upd(PG_FUNCTION_ARGS)
+{
+ /* Check that this is a valid trigger call on the right time and event. */
+ ri_CheckTrigger(fcinfo, "RI_FKey_period_setdefault_upd", RI_TRIGTYPE_UPDATE);
+
+ /* Share code with DELETE case */
+ return tri_set((TriggerData *) fcinfo->context, false, RI_TRIGTYPE_UPDATE);
+}
+
+/*
+ * tri_set -
+ *
+ * Common code for temporal ON DELETE SET NULL, ON DELETE SET DEFAULT, ON
+ * UPDATE SET NULL, and ON UPDATE SET DEFAULT.
+ */
+static Datum
+tri_set(TriggerData *trigdata, bool is_set_null, int tgkind)
+{
+ const RI_ConstraintInfo *riinfo;
+ Relation fk_rel;
+ Relation pk_rel;
+ TupleTableSlot *oldslot;
+ RI_QueryKey qkey;
+ SPIPlanPtr qplan;
+ Datum targetRange;
+ int32 queryno;
+
+ riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger,
+ trigdata->tg_relation, true);
+
+ /*
+ * Get the relation descriptors of the FK and PK tables and the old tuple.
+ *
+ * fk_rel is opened in RowExclusiveLock mode since that's what our
+ * eventual UPDATE will get on it.
+ */
+ fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock);
+ pk_rel = trigdata->tg_relation;
+ oldslot = trigdata->tg_trigslot;
+
+ /*
+ * Don't SET NULL/DEFAULT than more than the PK's duration,
+ * trimmed by an original FOR PORTION OF if necessary.
+ */
+ targetRange = restrict_cascading_range(pk_rel, trigdata->tg_temporal, riinfo, oldslot);
+
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /*
+ * Fetch or prepare a saved plan for the trigger.
+ */
+ switch (tgkind)
+ {
+ case RI_TRIGTYPE_UPDATE:
+ queryno = is_set_null
+ ? RI_PLAN_PERIOD_SETNULL_ONUPDATE
+ : RI_PLAN_PERIOD_SETDEFAULT_ONUPDATE;
+ break;
+ case RI_TRIGTYPE_DELETE:
+ queryno = is_set_null
+ ? RI_PLAN_PERIOD_SETNULL_ONDELETE
+ : RI_PLAN_PERIOD_SETDEFAULT_ONDELETE;
+ break;
+ default:
+ elog(ERROR, "invalid tgkind passed to ri_set");
+ }
+
+ ri_BuildQueryKey(&qkey, riinfo, queryno);
+
+ if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL)
+ {
+ StringInfoData querybuf;
+ StringInfoData qualbuf;
+ char fkrelname[MAX_QUOTED_REL_NAME_LEN];
+ char attname[MAX_QUOTED_NAME_LEN];
+ char paramname[16];
+ const char *querysep;
+ const char *qualsep;
+ Oid queryoids[RI_MAX_NUMKEYS + 1]; /* +1 for FOR PORTION OF */
+ const char *fk_only;
+ int num_cols_to_set;
+ const int16 *set_cols;
+
+ switch (tgkind)
+ {
+ case RI_TRIGTYPE_UPDATE:
+ /* -1 so we let FOR PORTION OF set the range. */
+ num_cols_to_set = riinfo->nkeys - 1;
+ set_cols = riinfo->fk_attnums;
+ break;
+ case RI_TRIGTYPE_DELETE:
+ /*
+ * If confdelsetcols are present, then we only update the
+ * columns specified in that array, otherwise we update all
+ * the referencing columns.
+ */
+ if (riinfo->ndelsetcols != 0)
+ {
+ num_cols_to_set = riinfo->ndelsetcols;
+ set_cols = riinfo->confdelsetcols;
+ }
+ else
+ {
+ /* -1 so we let FOR PORTION OF set the range. */
+ num_cols_to_set = riinfo->nkeys - 1;
+ set_cols = riinfo->fk_attnums;
+ }
+ break;
+ default:
+ elog(ERROR, "invalid tgkind passed to ri_set");
+ }
+
+ /* ----------
+ * The query string built is
+ * UPDATE [ONLY] <fktable>
+ * FOR PORTION OF $fkatt (${n+1})
+ * SET fkatt1 = {NULL|DEFAULT} [, ...]
+ * WHERE $1 = fkatt1 [AND ...]
+ * The type id's for the $ parameters are those of the
+ * corresponding PK attributes.
+ * ----------
+ */
+ initStringInfo(&querybuf);
+ initStringInfo(&qualbuf);
+ fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?
+ "" : "ONLY ";
+ quoteRelationName(fkrelname, fk_rel);
+ quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1]));
+
+ appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s ($%d) SET",
+ fk_only, fkrelname, attname, riinfo->nkeys + 1);
+
+ /*
+ * Add assignment clauses
+ */
+ querysep = "";
+ for (int i = 0; i < num_cols_to_set; i++)
+ {
+ quoteOneName(attname, RIAttName(fk_rel, set_cols[i]));
+ appendStringInfo(&querybuf,
+ "%s %s = %s",
+ querysep, attname,
+ is_set_null ? "NULL" : "DEFAULT");
+ querysep = ",";
+ }
+
+ /*
+ * Add WHERE clause
+ */
+ qualsep = "WHERE";
+ for (int i = 0; i < riinfo->nkeys; i++)
+ {
+ Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+
+ quoteOneName(attname,
+ RIAttName(fk_rel, riinfo->fk_attnums[i]));
+
+ sprintf(paramname, "$%d", i + 1);
+ ri_GenerateQual(&querybuf, qualsep,
+ paramname, pk_type,
+ riinfo->pf_eq_oprs[i],
+ attname, fk_type);
+ if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
+ ri_GenerateQualCollation(&querybuf, pk_coll);
+ qualsep = "AND";
+ queryoids[i] = pk_type;
+ }
+
+ /* Set a param for FOR PORTION OF TO/FROM */
+ queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]);
+
+ /* Prepare and save the plan */
+ qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids,
+ &qkey, fk_rel, pk_rel);
+ }
+
+ /*
+ * We have a plan now. Run it to update the existing references.
+ */
+ ri_PerformCheck(riinfo, &qkey, qplan,
+ fk_rel, pk_rel,
+ oldslot, NULL,
+ riinfo->nkeys, targetRange,
+ true, /* must detect new rows */
+ SPI_OK_UPDATE);
+
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+
+ table_close(fk_rel, RowExclusiveLock);
+
+ if (is_set_null)
+ return PointerGetDatum(NULL);
+ else
+ {
+ /*
+ * If we just deleted or updated the PK row whose key was equal to the
+ * FK columns' default values, and a referencing row exists in the FK
+ * table, we would have updated that row to the same values it already
+ * had --- and RI_FKey_fk_upd_check_required would hence believe no
+ * check is necessary. So we need to do another lookup now and in
+ * case a reference still exists, abort the operation. That is
+ * already implemented in the NO ACTION trigger, so just run it. (This
+ * recheck is only needed in the SET DEFAULT case, since CASCADE would
+ * remove such rows in case of a DELETE operation or would change the
+ * FK key values in case of an UPDATE, while SET NULL is certain to
+ * result in rows that satisfy the FK constraint.)
+ */
+ return ri_restrict(trigdata, true);
+ }
+}
+
/*
* RI_FKey_pk_upd_check_required -
*
@@ -2287,9 +2824,10 @@ 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);
+ FindFKPeriodOpersAndProcs(opclass,
+ &riinfo->period_contained_by_oper,
+ &riinfo->agged_period_contained_by_oper,
+ &riinfo->period_intersect_proc);
}
ReleaseSysCache(tup);
@@ -2444,8 +2982,8 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
int spi_result;
Oid save_userid;
int save_sec_context;
- Datum vals[RI_MAX_NUMKEYS * 2];
- char nulls[RI_MAX_NUMKEYS * 2];
+ Datum vals[RI_MAX_NUMKEYS * 2 + 1];
+ char nulls[RI_MAX_NUMKEYS * 2 + 1];
/*
* Use the query type code to determine whether the query is run against
@@ -2480,8 +3018,10 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
ri_ExtractValues(source_rel, newslot, riinfo, source_is_pk,
vals, nulls);
if (oldslot)
+ {
ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
vals + riinfo->nkeys, nulls + riinfo->nkeys);
+ }
}
else
{
@@ -3161,6 +3701,12 @@ RI_FKey_trigger_type(Oid tgfoid)
case F_RI_FKEY_SETDEFAULT_UPD:
case F_RI_FKEY_NOACTION_DEL:
case F_RI_FKEY_NOACTION_UPD:
+ case F_RI_FKEY_PERIOD_CASCADE_DEL:
+ case F_RI_FKEY_PERIOD_CASCADE_UPD:
+ case F_RI_FKEY_PERIOD_SETNULL_DEL:
+ case F_RI_FKEY_PERIOD_SETNULL_UPD:
+ case F_RI_FKEY_PERIOD_SETDEFAULT_DEL:
+ case F_RI_FKEY_PERIOD_SETDEFAULT_UPD:
return RI_TRIGGER_PK;
case F_RI_FKEY_CHECK_INS:
@@ -3189,30 +3735,30 @@ fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintIn
/*
* restrict_cascading_range -
*
- * Returns a Datum of RangeTypeP holding the appropriate timespan
- * to target child records when we CASCADE/SET NULL/SET DEFAULT.
+ * Returns a Datum holding the appropriate timespan
+ * to target referencing records when we CASCADE/SET NULL/SET DEFAULT.
*
- * In a normal UPDATE/DELETE this should be the parent's own valid time,
+ * In a normal UPDATE/DELETE this should be the referenced row's own valid time,
* but if there was a FOR PORTION OF clause, then we should use that to
- * trim down the parent's span further.
+ * trim down the span further.
*/
static Datum
-restrict_cascading_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot)
+restrict_cascading_range(Relation rel, const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot)
{
Datum pkRecordRange;
bool isnull;
+ int attno = riinfo->pk_attnums[riinfo->nkeys - 1];
- pkRecordRange = slot_getattr(oldslot, riinfo->pk_attnums[riinfo->nkeys - 1], &isnull);
+ pkRecordRange = slot_getattr(oldslot, attno, &isnull);
if (isnull)
elog(ERROR, "application time should not be null");
if (fpo_targets_pk_range(tg_temporal, riinfo))
{
- RangeType *r1 = DatumGetRangeTypeP(pkRecordRange);
- RangeType *r2 = DatumGetRangeTypeP(tg_temporal->fp_targetRange);
- Oid rngtypid = RangeTypeGetOid(r1);
- TypeCacheEntry *typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO);
- return RangeTypePGetDatum(range_intersect_internal(typcache, r1, r2));
+ if (!OidIsValid(riinfo->period_intersect_proc))
+ elog(ERROR, "invalid intersect support function");
+
+ return OidFunctionCall2(riinfo->period_intersect_proc, pkRecordRange, tg_temporal->fp_targetRange);
}
else
return pkRecordRange;
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 115217a6162..a56f665e149 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -279,9 +279,10 @@ 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);
+extern void FindFKPeriodOpersAndProcs(Oid opclass,
+ Oid *containedbyoperoid,
+ Oid *aggedcontainedbyoperoid,
+ Oid *intersectprocoid);
extern bool check_functional_grouping(Oid relid,
Index varno, Index varlevelsup,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34674686ad3..57347ec260e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3960,6 +3960,28 @@
prorettype => 'trigger', proargtypes => '',
prosrc => 'RI_FKey_noaction_upd' },
+# Temporal referential integrity constraint triggers
+{ oid => '6124', descr => 'temporal referential integrity ON DELETE CASCADE',
+ proname => 'RI_FKey_period_cascade_del', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'RI_FKey_period_cascade_del' },
+{ oid => '6125', descr => 'temporal referential integrity ON UPDATE CASCADE',
+ proname => 'RI_FKey_period_cascade_upd', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'RI_FKey_period_cascade_upd' },
+{ oid => '6128', descr => 'temporal referential integrity ON DELETE SET NULL',
+ proname => 'RI_FKey_period_setnull_del', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'RI_FKey_period_setnull_del' },
+{ oid => '6129', descr => 'temporal referential integrity ON UPDATE SET NULL',
+ proname => 'RI_FKey_period_setnull_upd', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'RI_FKey_period_setnull_upd' },
+{ oid => '6130', descr => 'temporal referential integrity ON DELETE SET DEFAULT',
+ proname => 'RI_FKey_period_setdefault_del', provolatile => 'v',
+ prorettype => 'trigger', proargtypes => '',
+ prosrc => 'RI_FKey_period_setdefault_del' },
+{ oid => '6131', descr => 'temporal referential integrity ON UPDATE SET DEFAULT',
+ proname => 'RI_FKey_period_setdefault_upd', provolatile => 'v',
+ prorettype => 'trigger', proargtypes => '',
+ prosrc => 'RI_FKey_period_setdefault_upd' },
+
{ oid => '1666',
proname => 'varbiteq', proleakproof => 't', prorettype => 'bool',
proargtypes => 'varbit varbit', prosrc => 'biteq' },
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out
index 510646cbce7..5796333f4f0 100644
--- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -258,14 +258,17 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
(3 rows)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
- proname
-------------------------
+ proname
+-------------------------------
RI_FKey_cascade_del
RI_FKey_noaction_del
+ RI_FKey_period_cascade_del
+ RI_FKey_period_setdefault_del
+ RI_FKey_period_setnull_del
RI_FKey_restrict_del
RI_FKey_setdefault_del
RI_FKey_setnull_del
-(5 rows)
+(8 rows)
explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
@@ -304,14 +307,17 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
(6 rows)
select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1;
- proname
-------------------------
+ proname
+-------------------------------
RI_FKey_cascade_del
RI_FKey_noaction_del
+ RI_FKey_period_cascade_del
+ RI_FKey_period_setdefault_del
+ RI_FKey_period_setnull_del
RI_FKey_restrict_del
RI_FKey_setdefault_del
RI_FKey_setnull_del
-(5 rows)
+(8 rows)
explain (costs off)
select proname from pg_proc where proname ilike '00%foo' order by 1;
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index 902594ef4b8..bbd222db361 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -2,7 +2,7 @@
--
-- We leave behind several tables to test pg_dump etc:
-- temporal_rng, temporal_rng2,
--- temporal_fk_rng2rng.
+-- temporal_fk_rng2rng, temporal_fk2_rng2rng.
SET datestyle TO ISO, YMD;
--
-- test input parser
@@ -1622,6 +1622,21 @@ SET id = '[7,8)'
WHERE id = '[5,6)';
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)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2016-02-01,2016-03-01)
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+ [7,8) | [2018-01-02,2018-01-03)
+(4 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1675,6 +1690,21 @@ SET id = '[7,8)'
WHERE id = '[5,6)';
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)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2016-02-01,2016-03-01)
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+ [7,8) | [2018-01-02,2018-01-03)
+(4 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1721,12 +1751,22 @@ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
WHERE id = '[5,6)';
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)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+(2 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
--- clean up:
-DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)';
-DELETE FROM temporal_rng WHERE id IN ('[5,6)');
--
-- test FK referenced deletes RESTRICT
--
@@ -1765,39 +1805,206 @@ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
WHERE id = '[5,6)';
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)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+ id | valid_at
+-------+-------------------------
+ [5,6) | [2018-01-01,2018-01-02)
+ [5,6) | [2018-01-03,2018-02-01)
+(2 rows)
+
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [3,4) | [2018-01-05,2018-01-10) | [5,6)
+(1 row)
+
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
--- clean up:
-DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)';
-DELETE FROM temporal_rng WHERE id IN ('[5,6)');
--
--- test ON UPDATE/DELETE options
+-- rng2rng test ON UPDATE/DELETE options
+--
+-- TOC:
+-- referenced updates CASCADE
+-- referenced deletes CASCADE
+-- referenced updates SET NULL
+-- referenced deletes SET NULL
+-- referenced updates SET DEFAULT
+-- referenced deletes SET DEFAULT
+-- referenced updates CASCADE (two scalar cols)
+-- referenced deletes CASCADE (two scalar cols)
+-- referenced updates SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols, SET NULL subset)
+-- referenced updates SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
--
-- test FK referenced updates CASCADE
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE CASCADE ON UPDATE CASCADE;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [7,8)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [7,8)
+ [100,101) | [2019-01-01,2020-01-01) | [7,8)
+ [100,101) | [2020-01-01,2021-01-01) | [7,8)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2018-01-01,2020-01-01) | [9,10)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes CASCADE
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(2 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+----+----------+-----------
+(0 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(1 row)
+
+--
-- test FK referenced updates SET NULL
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE SET NULL ON UPDATE SET NULL;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) |
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) |
+ [100,101) | [2019-01-01,2020-01-01) |
+ [100,101) | [2020-01-01,2021-01-01) |
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2018-01-01,2020-01-01) |
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) |
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) |
+ [100,101) | [2019-01-01,2020-01-01) |
+ [100,101) | [2020-01-01,2021-01-01) |
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2018-01-01,2020-01-01) |
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+--
-- test FK referenced updates SET DEFAULT
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -1805,7 +2012,432 @@ ALTER TABLE temporal_fk_rng2rng
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [-1,0)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [-1,0)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2018-01-01,2020-01-01) | [-1,0)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT
+--
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [100,101) | [2018-01-01,2019-01-01) | [-1,0)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [-1,0)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+-------------------------+-----------
+ [200,201) | [2018-01-01,2020-01-01) | [-1,0)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced updates CASCADE (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [7,8) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [7,8) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [7,8) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [7,8) | [6,7)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | [9,10) | [8,9)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes CASCADE (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(2 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+----+----------+------------+------------
+(0 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(1 row)
+
+--
+-- test FK referenced updates SET NULL (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | |
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | |
+ [100,101) | [2019-01-01,2020-01-01) | |
+ [100,101) | [2020-01-01,2021-01-01) | |
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | |
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | |
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | |
+ [100,101) | [2019-01-01,2020-01-01) | |
+ [100,101) | [2020-01-01,2021-01-01) | |
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | |
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL (valid_at) ON UPDATE SET NULL;
+ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD
+-- ok:
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL;
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | | [6,7)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | | [8,9)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced updates SET DEFAULT (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [-1,0)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [-1,0)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [-1,0)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT;
+ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD
+-- ok:
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7)
+(3 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [6,7)
+ [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [6,7)
+ [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [6,7)
+(3 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', daterange(null, null));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+-------------------------+------------+------------
+ [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [8,9)
+ [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9)
+(2 rows)
+
--
-- test FOREIGN KEY, multirange references multirange
--
@@ -2125,6 +2757,22 @@ 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".
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+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-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+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'));
--
-- test FK referenced updates RESTRICT
--
@@ -2162,6 +2810,22 @@ 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".
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+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-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+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'));
--
-- test FK referenced deletes NO ACTION
--
@@ -2194,6 +2858,19 @@ BEGIN;
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".
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+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-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- test FK referenced deletes RESTRICT
--
@@ -2222,6 +2899,638 @@ BEGIN;
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".
ROLLBACK;
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+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-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
+--
+-- mltrng2mltrng test ON UPDATE/DELETE options
+--
+-- TOC:
+-- referenced updates CASCADE
+-- referenced deletes CASCADE
+-- referenced updates SET NULL
+-- referenced deletes SET NULL
+-- referenced updates SET DEFAULT
+-- referenced deletes SET DEFAULT
+-- referenced updates CASCADE (two scalar cols)
+-- referenced deletes CASCADE (two scalar cols)
+-- referenced updates SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols, SET NULL subset)
+-- referenced updates SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
+-- test FK referenced updates CASCADE
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [7,8)
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [7,8)
+ [100,101) | {[2019-01-01,2020-01-01)} | [7,8)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2018-01-01,2020-01-01)} | [9,10)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes CASCADE
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+(1 row)
+
+-- non-FPO delete:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+----+----------+-----------
+(0 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(1 row)
+
+--
+-- test FK referenced updates SET NULL
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} |
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} |
+ [100,101) | {[2019-01-01,2020-01-01)} |
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2018-01-01,2020-01-01)} |
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} |
+(2 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} |
+ [100,101) | {[2019-01-01,2020-01-01)} |
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2018-01-01,2020-01-01)} |
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(2 rows)
+
+--
+-- test FK referenced updates SET DEFAULT
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0)
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2018-01-01,2020-01-01)} | [-1,0)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT
+--
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0)
+(2 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------------------------------+-----------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id
+-----------+---------------------------+-----------
+ [200,201) | {[2018-01-01,2020-01-01)} | [-1,0)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9)
+(2 rows)
+
+--
+-- test FK referenced updates CASCADE (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [7,8) | [6,7)
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [7,8) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [7,8) | [6,7)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | [9,10) | [8,9)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes CASCADE (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+(1 row)
+
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+----+----------+------------+------------
+(0 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(1 row)
+
+--
+-- test FK referenced updates SET NULL (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | |
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | |
+ [100,101) | {[2019-01-01,2020-01-01)} | |
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | |
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | |
+(2 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | |
+ [100,101) | {[2019-01-01,2020-01-01)} | |
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | |
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL (valid_at) ON UPDATE SET NULL;
+ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD
+-- ok:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL;
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | | [6,7)
+(2 rows)
+
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | | [6,7)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | | [8,9)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced updates SET DEFAULT (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0)
+(2 rows)
+
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [-1,0)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [-1,0)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0)
+(2 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [-1,0)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [-1,0)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT;
+ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD
+-- ok:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [6,7)
+(2 rows)
+
+-- non-FPO update:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------------------------------+------------+------------
+ [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [6,7)
+ [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [6,7)
+(2 rows)
+
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', datemultirange(daterange(null, null)));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+ id | valid_at | parent_id1 | parent_id2
+-----------+---------------------------+------------+------------
+ [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [8,9)
+ [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9)
+(2 rows)
+
+-- FK with a custom range type
+CREATE TYPE mydaterange AS range(subtype=date);
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at mydaterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_fk3_rng2rng (
+ id int4range,
+ valid_at mydaterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk3_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_fk3_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at) ON DELETE CASCADE
+);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[8,9)', mydaterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk3_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', mydaterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)';
+SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,6)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [5,6) | [2018-01-01,2019-01-01) | [8,9)
+ [5,6) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+DROP TABLE temporal_fk3_rng2rng;
+DROP TABLE temporal_rng3;
+DROP TYPE mydaterange;
--
-- FK between partitioned tables
--
@@ -2231,8 +3540,8 @@ CREATE TABLE temporal_partitioned_rng (
name text,
CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
-CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
-CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
+CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
@@ -2245,8 +3554,8 @@ CREATE TABLE temporal_partitioned_fk_rng2rng (
CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
) PARTITION BY LIST (id);
-CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
-CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
+CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
+CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
--
-- partitioned FK referencing inserts
--
@@ -2288,7 +3597,7 @@ UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-
-- should fail:
UPDATE temporal_partitioned_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');
-ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng"
+ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- partitioned FK referenced deletes NO ACTION
@@ -2300,7 +3609,7 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
-- should fail:
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng"
+ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- partitioned FK referenced updates RESTRICT
@@ -2322,7 +3631,7 @@ UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-
-- should fail:
UPDATE temporal_partitioned_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');
-ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng"
+ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- partitioned FK referenced deletes RESTRICT
@@ -2334,48 +3643,234 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
-- should fail:
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
-ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey" on table "temporal_partitioned_fk_rng2rng"
+ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_parent_id_valid_at_fkey1" on table "temporal_partitioned_fk_rng2rng"
DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- partitioned FK referenced updates CASCADE
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_partitioned_fk_rng2rng
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE CASCADE ON UPDATE CASCADE;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [4,5) | [2019-01-01,2020-01-01) | [7,8)
+ [4,5) | [2018-01-01,2019-01-01) | [6,7)
+ [4,5) | [2020-01-01,2021-01-01) | [6,7)
+(3 rows)
+
+UPDATE temporal_partitioned_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [4,5) | [2019-01-01,2020-01-01) | [7,8)
+ [4,5) | [2018-01-01,2019-01-01) | [7,8)
+ [4,5) | [2020-01-01,2021-01-01) | [7,8)
+(3 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[10,11)', daterange('2018-01-01', '2021-01-01'), '[15,16)');
+UPDATE temporal_partitioned_rng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,11)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [10,11) | [2018-01-01,2020-01-01) | [16,17)
+ [10,11) | [2020-01-01,2021-01-01) | [15,16)
+(2 rows)
+
--
-- partitioned FK referenced deletes CASCADE
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [5,6) | [2018-01-01,2019-01-01) | [8,9)
+ [5,6) | [2020-01-01,2021-01-01) | [8,9)
+(2 rows)
+
+DELETE FROM temporal_partitioned_rng WHERE id = '[8,9)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)';
+ id | valid_at | parent_id
+----+----------+-----------
+(0 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'), '[17,18)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,12)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [11,12) | [2020-01-01,2021-01-01) | [17,18)
+(1 row)
+
--
-- partitioned FK referenced updates SET NULL
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
ALTER TABLE temporal_partitioned_fk_rng2rng
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE SET NULL ON UPDATE SET NULL;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,11)' WHERE id = '[9,10)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [6,7) | [2019-01-01,2020-01-01) |
+ [6,7) | [2018-01-01,2019-01-01) | [9,10)
+ [6,7) | [2020-01-01,2021-01-01) | [9,10)
+(3 rows)
+
+UPDATE temporal_partitioned_rng SET id = '[10,11)' WHERE id = '[9,10)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [6,7) | [2019-01-01,2020-01-01) |
+ [6,7) | [2018-01-01,2019-01-01) |
+ [6,7) | [2020-01-01,2021-01-01) |
+(3 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'), '[18,19)');
+UPDATE temporal_partitioned_rng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,13)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [12,13) | [2018-01-01,2020-01-01) |
+ [12,13) | [2020-01-01,2021-01-01) | [18,19)
+(2 rows)
+
--
-- partitioned FK referenced deletes SET NULL
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[7,8)', daterange('2018-01-01', '2021-01-01'), '[11,12)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,12)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [7,8) | [2019-01-01,2020-01-01) |
+ [7,8) | [2018-01-01,2019-01-01) | [11,12)
+ [7,8) | [2020-01-01,2021-01-01) | [11,12)
+(3 rows)
+
+DELETE FROM temporal_partitioned_rng WHERE id = '[11,12)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [7,8) | [2019-01-01,2020-01-01) |
+ [7,8) | [2018-01-01,2019-01-01) |
+ [7,8) | [2020-01-01,2021-01-01) |
+(3 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[13,14)', daterange('2018-01-01', '2021-01-01'), '[20,21)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,14)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [13,14) | [2018-01-01,2020-01-01) |
+ [13,14) | [2020-01-01,2021-01-01) | [20,21)
+(2 rows)
+
--
-- partitioned FK referenced updates SET DEFAULT
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
ALTER TABLE temporal_partitioned_fk_rng2rng
- ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id SET DEFAULT '[0,1)',
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
-ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,14)' WHERE id = '[12,13)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [8,9) | [2019-01-01,2020-01-01) | [0,1)
+ [8,9) | [2018-01-01,2019-01-01) | [12,13)
+ [8,9) | [2020-01-01,2021-01-01) | [12,13)
+(3 rows)
+
+UPDATE temporal_partitioned_rng SET id = '[13,14)' WHERE id = '[12,13)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)';
+ id | valid_at | parent_id
+-------+-------------------------+-----------
+ [8,9) | [2019-01-01,2020-01-01) | [0,1)
+ [8,9) | [2018-01-01,2019-01-01) | [0,1)
+ [8,9) | [2020-01-01,2021-01-01) | [0,1)
+(3 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'), '[22,23)');
+UPDATE temporal_partitioned_rng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,15)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [14,15) | [2018-01-01,2020-01-01) | [0,1)
+ [14,15) | [2020-01-01,2021-01-01) | [22,23)
+(2 rows)
+
--
-- partitioned FK referenced deletes SET DEFAULT
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'), '[14,15)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,15)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)';
+ id | valid_at | parent_id
+--------+-------------------------+-----------
+ [9,10) | [2019-01-01,2020-01-01) | [0,1)
+ [9,10) | [2018-01-01,2019-01-01) | [14,15)
+ [9,10) | [2020-01-01,2021-01-01) | [14,15)
+(3 rows)
+
+DELETE FROM temporal_partitioned_rng WHERE id = '[14,15)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)';
+ id | valid_at | parent_id
+--------+-------------------------+-----------
+ [9,10) | [2019-01-01,2020-01-01) | [0,1)
+ [9,10) | [2018-01-01,2019-01-01) | [0,1)
+ [9,10) | [2020-01-01,2021-01-01) | [0,1)
+(3 rows)
+
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[15,16)', daterange('2018-01-01', '2021-01-01'), '[24,25)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,16)';
+ id | valid_at | parent_id
+---------+-------------------------+-----------
+ [15,16) | [2018-01-01,2020-01-01) | [0,1)
+ [15,16) | [2020-01-01,2021-01-01) | [24,25)
+(2 rows)
+
DROP TABLE temporal_partitioned_fk_rng2rng;
DROP TABLE temporal_partitioned_rng;
RESET datestyle;
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index de7186ab455..c8241c27929 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -2,7 +2,7 @@
--
-- We leave behind several tables to test pg_dump etc:
-- temporal_rng, temporal_rng2,
--- temporal_fk_rng2rng.
+-- temporal_fk_rng2rng, temporal_fk2_rng2rng.
SET datestyle TO ISO, YMD;
@@ -1194,6 +1194,8 @@ UPDATE temporal_rng
FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
SET id = '[7,8)'
WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1243,6 +1245,8 @@ UPDATE temporal_rng
FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
SET id = '[7,8)'
WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01')
@@ -1286,12 +1290,11 @@ WHERE id = '[5,6)';
DELETE FROM temporal_rng
FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
--- clean up:
-DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)';
-DELETE FROM temporal_rng WHERE id IN ('[5,6)');
--
-- test FK referenced deletes RESTRICT
@@ -1328,41 +1331,131 @@ WHERE id = '[5,6)';
DELETE FROM temporal_rng
FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
WHERE id = '[5,6)';
+SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
+SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)';
DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01');
--- clean up:
-DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)';
-DELETE FROM temporal_rng WHERE id IN ('[5,6)');
--
--- test ON UPDATE/DELETE options
+-- rng2rng test ON UPDATE/DELETE options
--
+-- TOC:
+-- referenced updates CASCADE
+-- referenced deletes CASCADE
+-- referenced updates SET NULL
+-- referenced deletes SET NULL
+-- referenced updates SET DEFAULT
+-- referenced deletes SET DEFAULT
+-- referenced updates CASCADE (two scalar cols)
+-- referenced deletes CASCADE (two scalar cols)
+-- referenced updates SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols, SET NULL subset)
+-- referenced updates SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
-- test FK referenced updates CASCADE
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+--
+-- test FK referenced deletes CASCADE
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
-- test FK referenced updates SET NULL
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
DROP CONSTRAINT temporal_fk_rng2rng_fk,
ADD CONSTRAINT temporal_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+--
+-- test FK referenced deletes SET NULL
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
-- test FK referenced updates SET DEFAULT
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
-INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
-INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_fk_rng2rng
ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
DROP CONSTRAINT temporal_fk_rng2rng_fk,
@@ -1370,6 +1463,252 @@ ALTER TABLE temporal_fk_rng2rng
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT
+--
+
+TRUNCATE temporal_rng, temporal_fk_rng2rng;
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_rng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates CASCADE (two scalar cols)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes CASCADE (two scalar cols)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET NULL (two scalar cols)
+--
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL (valid_at) ON UPDATE SET NULL;
+-- ok:
+ALTER TABLE temporal_fk2_rng2rng
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL;
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET DEFAULT (two scalar cols)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_rng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+UPDATE temporal_rng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
+
+TRUNCATE temporal_rng2, temporal_fk2_rng2rng;
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', daterange(null, null));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT;
+-- ok:
+ALTER TABLE temporal_fk2_rng2rng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_rng2rng_fk,
+ ADD CONSTRAINT temporal_fk2_rng2rng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_rng2
+ ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_rng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', daterange(null, null));
+INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)');
+DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at;
--
-- test FOREIGN KEY, multirange references multirange
@@ -1662,6 +2001,20 @@ 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'));
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+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'));
--
-- test FK referenced updates RESTRICT
@@ -1698,6 +2051,20 @@ ROLLBACK;
-- 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'));
+-- changing an unreferenced part is okay:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- changing just a part fails:
+UPDATE temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+SET id = '[7,8)'
+WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK update succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+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'));
--
-- test FK referenced deletes NO ACTION
@@ -1729,6 +2096,17 @@ BEGIN;
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
COMMIT;
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- test FK referenced deletes RESTRICT
@@ -1758,6 +2136,417 @@ BEGIN;
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
ROLLBACK;
+-- deleting an unreferenced part is okay:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
+WHERE id = '[5,6)';
+-- deleting just a part fails:
+DELETE FROM temporal_mltrng
+FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
+WHERE id = '[5,6)';
+-- then delete the objecting FK record and the same PK delete succeeds:
+DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
+DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
+
+--
+-- mltrng2mltrng test ON UPDATE/DELETE options
+--
+-- TOC:
+-- referenced updates CASCADE
+-- referenced deletes CASCADE
+-- referenced updates SET NULL
+-- referenced deletes SET NULL
+-- referenced updates SET DEFAULT
+-- referenced deletes SET DEFAULT
+-- referenced updates CASCADE (two scalar cols)
+-- referenced deletes CASCADE (two scalar cols)
+-- referenced updates SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols)
+-- referenced deletes SET NULL (two scalar cols, SET NULL subset)
+-- referenced updates SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols)
+-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+
+--
+-- test FK referenced updates CASCADE
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes CASCADE
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET NULL
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET NULL
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET DEFAULT
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+ALTER TABLE temporal_fk_mltrng2mltrng
+ ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_mltrng
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT
+--
+
+TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng;
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_mltrng WHERE id = '[6,7)';
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)');
+DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates CASCADE (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE CASCADE ON UPDATE CASCADE;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes CASCADE (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET NULL (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL ON UPDATE SET NULL;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL (valid_at) ON UPDATE SET NULL;
+-- ok:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL;
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO delete:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced updates SET DEFAULT (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+UPDATE temporal_mltrng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+UPDATE temporal_mltrng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+--
+-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset)
+--
+
+TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng;
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', datemultirange(daterange(null, null)));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)');
+-- fails because you can't set the PERIOD column:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT;
+-- ok:
+ALTER TABLE temporal_fk2_mltrng2mltrng
+ ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]',
+ DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk,
+ ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk
+ FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at)
+ REFERENCES temporal_mltrng2
+ ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT;
+-- leftovers on both sides:
+DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- non-FPO update:
+DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)';
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at;
+-- FK across two referenced rows:
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01')));
+INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', datemultirange(daterange(null, null)));
+INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)');
+DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at;
+
+-- FK with a custom range type
+
+CREATE TYPE mydaterange AS range(subtype=date);
+
+CREATE TABLE temporal_rng3 (
+ id int4range,
+ valid_at mydaterange,
+ CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+CREATE TABLE temporal_fk3_rng2rng (
+ id int4range,
+ valid_at mydaterange,
+ parent_id int4range,
+ CONSTRAINT temporal_fk3_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS),
+ CONSTRAINT temporal_fk3_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
+ REFERENCES temporal_rng3 (id, PERIOD valid_at) ON DELETE CASCADE
+);
+INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[8,9)', mydaterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_fk3_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', mydaterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)';
+SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,6)';
+
+DROP TABLE temporal_fk3_rng2rng;
+DROP TABLE temporal_rng3;
+DROP TYPE mydaterange;
--
-- FK between partitioned tables
@@ -1769,8 +2558,8 @@ CREATE TABLE temporal_partitioned_rng (
name text,
CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
) PARTITION BY LIST (id);
-CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
-CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
+CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES
('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'),
('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'),
@@ -1784,8 +2573,8 @@ CREATE TABLE temporal_partitioned_fk_rng2rng (
CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng (id, PERIOD valid_at)
) PARTITION BY LIST (id);
-CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
-CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
+CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
+CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
--
-- partitioned FK referencing inserts
@@ -1878,48 +2667,125 @@ DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange
-- partitioned FK referenced updates CASCADE
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
ALTER TABLE temporal_partitioned_fk_rng2rng
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE CASCADE ON UPDATE CASCADE;
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)';
+UPDATE temporal_partitioned_rng SET id = '[7,8)' WHERE id = '[6,7)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[10,11)', daterange('2018-01-01', '2021-01-01'), '[15,16)');
+UPDATE temporal_partitioned_rng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,11)';
--
-- partitioned FK referenced deletes CASCADE
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', daterange('2018-01-01', '2021-01-01'), '[8,9)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)';
+DELETE FROM temporal_partitioned_rng WHERE id = '[8,9)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'), '[17,18)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,12)';
+
--
-- partitioned FK referenced updates SET NULL
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)');
ALTER TABLE temporal_partitioned_fk_rng2rng
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE SET NULL ON UPDATE SET NULL;
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,11)' WHERE id = '[9,10)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)';
+UPDATE temporal_partitioned_rng SET id = '[10,11)' WHERE id = '[9,10)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'), '[18,19)');
+UPDATE temporal_partitioned_rng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,13)';
--
-- partitioned FK referenced deletes SET NULL
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[7,8)', daterange('2018-01-01', '2021-01-01'), '[11,12)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,12)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)';
+DELETE FROM temporal_partitioned_rng WHERE id = '[11,12)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[13,14)', daterange('2018-01-01', '2021-01-01'), '[20,21)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,14)';
+
--
-- partitioned FK referenced updates SET DEFAULT
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)');
ALTER TABLE temporal_partitioned_fk_rng2rng
- ALTER COLUMN parent_id SET DEFAULT '[-1,-1]',
+ ALTER COLUMN parent_id SET DEFAULT '[0,1)',
DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk,
ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk
FOREIGN KEY (parent_id, PERIOD valid_at)
REFERENCES temporal_partitioned_rng
ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
+UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,14)' WHERE id = '[12,13)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)';
+UPDATE temporal_partitioned_rng SET id = '[13,14)' WHERE id = '[12,13)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'), '[22,23)');
+UPDATE temporal_partitioned_rng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,15)';
--
-- partitioned FK referenced deletes SET DEFAULT
--
+TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng;
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'), '[14,15)');
+DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,15)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)';
+DELETE FROM temporal_partitioned_rng WHERE id = '[14,15)';
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)';
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2018-01-01', '2020-01-01'));
+INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2020-01-01', '2021-01-01'));
+INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[15,16)', daterange('2018-01-01', '2021-01-01'), '[24,25)');
+DELETE FROM temporal_partitioned_rng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date;
+SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,16)';
+
DROP TABLE temporal_partitioned_fk_rng2rng;
DROP TABLE temporal_partitioned_rng;
--
2.42.0