From 9a41d1b670252028104cf40fd3e050556a14776e Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 3 Jun 2023 21:41:11 -0400 Subject: [PATCH v26 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 | 24 +- src/backend/commands/tablecmds.c | 53 +- src/backend/utils/adt/ri_triggers.c | 575 ++++++++++- src/include/catalog/pg_constraint.h | 7 +- src/include/catalog/pg_proc.dat | 20 + src/test/regress/expected/btree_index.out | 18 +- .../regress/expected/without_overlaps.out | 941 +++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 496 ++++++++- 9 files changed, 2073 insertions(+), 75 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 0c39bad6c76..3a791c89c33 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1269,7 +1269,9 @@ WITH ( MODULUS numeric_literal, REM - In a temporal foreign key, this option is not supported. + In a temporal foreign key, the delete/update will use + FOR PORTION OF semantics to constrain the + effect to the bounds being deleted/updated in the referenced row. @@ -1284,7 +1286,10 @@ WITH ( MODULUS numeric_literal, REM - In a temporal foreign key, this option is not supported. + In a temporal foreign key, the change will use FOR PORTION + OF semantics to constrain the effect to the bounds being + deleted/updated in the referenced row. The column maked with + PERIOD will not be set to null. @@ -1301,7 +1306,10 @@ WITH ( MODULUS numeric_literal, REM - In a temporal foreign key, this option is not supported. + In a temporal foreign key, the change will use FOR PORTION + OF semantics to constrain the effect to the bounds being + deleted/updated in the referenced row. The column marked with + PERIOD with not be set to a default value. diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index bac1f8fc916..efba05920b6 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -1615,15 +1615,16 @@ DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, */ void FindFKPeriodOpersAndProcs(Oid opclass, - Oid *periodoperoid, - Oid *aggedperiodoperoid, - Oid *periodprocoid) + Oid *containedbyoperoid, + Oid *aggedcontainedbyoperoid, + Oid *aggprocoid, + Oid *intersectprocoid) { Oid opfamily; Oid opcintype; Oid aggrettype; Oid funcid = InvalidOid; - StrategyNumber strat = RTContainedByStrategyNumber; + StrategyNumber strat; /* * Look up the ContainedBy operator with symmetric types. @@ -1631,9 +1632,10 @@ FindFKPeriodOpersAndProcs(Oid opclass, * of the old value, then we can treat the attribute as if it didn't change, * and skip the RI check. */ + strat = RTContainedByStrategyNumber; GetOperatorFromWellKnownStrategy(opclass, InvalidOid, - periodoperoid, + containedbyoperoid, &strat); /* Now look up the support proc for aggregation. */ @@ -1646,7 +1648,7 @@ FindFKPeriodOpersAndProcs(Oid opclass, errmsg("no support func %u found for FOREIGN KEY constraint", GIST_REFERENCED_AGG_PROC), errhint("Define a referencedagg support function for your GiST opclass."))); - *periodprocoid = funcid; + *aggprocoid = funcid; /* Look up the function's rettype. */ aggrettype = get_func_rettype(funcid); @@ -1659,8 +1661,16 @@ FindFKPeriodOpersAndProcs(Oid opclass, strat = RTContainedByStrategyNumber; GetOperatorFromWellKnownStrategy(opclass, aggrettype, - aggedperiodoperoid, + 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. + */ + *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 04bd325e7ac..e4bf02b5163 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -513,7 +513,7 @@ static ObjectAddress addFkRecurseReferenced(List **wqueue, Constraint *fkconstra bool old_check_ok, Oid parentDelTrigger, Oid parentUpdTrigger, bool is_temporal); -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, @@ -9832,6 +9832,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; bool is_temporal; bool pk_with_period; + int16 fkperiodattnum = InvalidOid; int i; int numfks, numpks, @@ -9933,12 +9934,13 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, 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); @@ -10056,9 +10058,14 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { Oid periodoperoid; Oid aggedperiodoperoid; - Oid periodprocoid; + Oid aggprocoid; + Oid intersectprocoid; - FindFKPeriodOpersAndProcs(opclasses[numpks - 1], &periodoperoid, &aggedperiodoperoid, &periodprocoid); + FindFKPeriodOpersAndProcs(opclasses[numpks - 1], + &periodoperoid, + &aggedperiodoperoid, + &aggprocoid, + &intersectprocoid); } /* @@ -10112,6 +10119,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) { @@ -10122,6 +10130,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; @@ -12697,12 +12712,19 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->funcname = SystemFuncName("RI_FKey_period_restrict_del"); break; case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_del"); + break; case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_del"); + break; case FKCONSTR_ACTION_SETDEFAULT: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("invalid %s action for foreign key constraint using PERIOD", - "ON DELETE"))); + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setdefault_del"); break; default: elog(ERROR, "unrecognized FK action type: %d", @@ -12789,12 +12811,19 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->funcname = SystemFuncName("RI_FKey_period_restrict_upd"); break; case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_upd"); + break; case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_upd"); + break; case FKCONSTR_ACTION_SETDEFAULT: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("invalid %s action for foreign key constraint using PERIOD", - "ON UPDATE"))); + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("RI_FKey_period_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 6a9d35f35fd..6bdc261f274 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -83,6 +83,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) @@ -131,6 +137,7 @@ typedef struct RI_ConstraintInfo Oid agged_period_contained_by_oper; /* operator for PERIOD SQL */ Oid period_referenced_agg_proc; /* proc for PERIOD SQL */ Oid period_referenced_agg_rettype; /* rettype for previous */ + Oid period_intersect_proc; /* operator for PERIOD SQL */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; @@ -194,6 +201,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, @@ -242,7 +250,7 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, static void lookupPeriodRIProc(const RI_ConstraintInfo *riinfo, char **aggname); 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); @@ -826,7 +834,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, @@ -1409,7 +1417,128 @@ RI_FKey_period_restrict_del(PG_FUNCTION_ARGS) } /* - * TRI_FKey_noaction_upd - + * 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] + * FOR PORTION OF $fkatt FROM lower(${n+1}) TO upper(${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 FROM pg_catalog.lower($%d) TO pg_catalog.upper($%d)", + fk_only, fkrelname, attname, riinfo->nkeys + 1, 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_noaction_upd - * * Give an error and roll back the current transaction if the * update has resulted in a violation of the given referential @@ -1445,6 +1574,415 @@ RI_FKey_period_restrict_upd(PG_FUNCTION_ARGS) return ri_restrict((TriggerData *) fcinfo->context, false); } +/* + * 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] + * FOR PORTION OF $fkatt FROM lower(${2n+1}) TO upper(${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 FROM pg_catalog.lower($%d) TO pg_catalog.upper($%d) SET", + fk_only, fkrelname, attname, 2 * riinfo->nkeys + 1, 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] + * FOR PORTION OF $fkatt FROM lower(${n+1}) TO upper(${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 FROM pg_catalog.lower($%d) TO pg_catalog.upper($%d) SET", + fk_only, fkrelname, attname, riinfo->nkeys + 1, 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 - * @@ -2426,7 +2964,8 @@ ri_LoadConstraintInfo(Oid constraintOid) FindFKPeriodOpersAndProcs(opclass, &riinfo->period_contained_by_oper, &riinfo->agged_period_contained_by_oper, - &riinfo->period_referenced_agg_proc); + &riinfo->period_referenced_agg_proc, + &riinfo->period_intersect_proc); riinfo->period_referenced_agg_rettype = get_func_rettype(riinfo->period_referenced_agg_proc); } @@ -2582,8 +3121,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 @@ -2618,8 +3157,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 { @@ -3288,8 +3829,14 @@ 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_RESTRICT_DEL: case F_RI_FKEY_PERIOD_RESTRICT_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: case F_RI_FKEY_PERIOD_NOACTION_DEL: case F_RI_FKEY_PERIOD_NOACTION_UPD: return RI_TRIGGER_PK; @@ -3360,7 +3907,7 @@ fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintIn /* * restrict_cascading_range - * - * Returns a Datum of RangeTypeP holding the appropriate timespan + * Returns a Datum holding the appropriate timespan * to target child records when we CASCADE/SET NULL/SET DEFAULT. * * In a normal UPDATE/DELETE this should be the parent's own valid time, @@ -3368,22 +3915,22 @@ fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintIn * trim down the parent's 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 e4f4c2ffaf1..538e8e64466 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -284,9 +284,10 @@ extern void DeconstructFkConstraintRow(HeapTuple tuple, int *numfks, 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 FindFKPeriodOpersAndProcs(Oid opclass, - Oid *periodoperoid, - Oid *aggedperiodoperoid, - Oid *periodprocoid); + Oid *containedbyodoperoid, + Oid *aggedcontainedbyoperoid, + Oid *aggprocoid, + 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 7fe2542f058..fe01f213349 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3952,6 +3952,12 @@ { oid => '6123', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', proname => 'RI_FKey_period_check_upd', provolatile => 'v', prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_period_check_upd' }, +{ 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 => '6126', descr => 'temporal referential integrity ON DELETE RESTRICT', proname => 'RI_FKey_period_restrict_del', provolatile => 'v', prorettype => 'trigger', proargtypes => '', @@ -3960,6 +3966,20 @@ proname => 'RI_FKey_period_restrict_upd', provolatile => 'v', prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_period_restrict_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 => '6132', descr => 'temporal referential integrity ON DELETE NO ACTION', proname => 'RI_FKey_period_noaction_del', provolatile => 'v', prorettype => 'trigger', proargtypes => '', diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index f3595d4ea3b..11002e91709 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -206,16 +206,19 @@ 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_noaction_del RI_FKey_period_restrict_del + RI_FKey_period_setdefault_del + RI_FKey_period_setnull_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(7 rows) +(10 rows) explain (costs off) select proname from pg_proc where proname ilike '00%foo' order by 1; @@ -254,16 +257,19 @@ 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_noaction_del RI_FKey_period_restrict_del + RI_FKey_period_setdefault_del + RI_FKey_period_setnull_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(7 rows) +(10 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 0c8224a35f5..15f35dfe952 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -872,6 +872,21 @@ SET id = '[7,7]' WHERE id = '[5,5]'; 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), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; + id | valid_at +-------+--------------------------------------------------------- + [5,6) | ["Mon Feb 01 00:00:00 2016","Tue Mar 01 00:00:00 2016") + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 02 00:00:00 2018") + [5,6) | ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018") + [7,8) | ["Tue Jan 02 00:00:00 2018","Wed Jan 03 00:00:00 2018") +(4 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [3,4) | ["Fri Jan 05 00:00:00 2018","Wed Jan 10 00:00:00 2018") | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') @@ -922,6 +937,21 @@ SET id = '[7,7]' WHERE id = '[5,5]'; 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), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; + id | valid_at +-------+--------------------------------------------------------- + [5,6) | ["Mon Feb 01 00:00:00 2016","Tue Mar 01 00:00:00 2016") + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 02 00:00:00 2018") + [5,6) | ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018") + [7,8) | ["Tue Jan 02 00:00:00 2018","Wed Jan 03 00:00:00 2018") +(4 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [3,4) | ["Fri Jan 05 00:00:00 2018","Wed Jan 10 00:00:00 2018") | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') @@ -961,6 +991,19 @@ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' WHERE id = '[5,5]'; 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), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; + id | valid_at +-------+--------------------------------------------------------- + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 02 00:00:00 2018") + [5,6) | ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018") +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [3,4) | ["Fri Jan 05 00:00:00 2018","Wed Jan 10 00:00:00 2018") | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); @@ -999,6 +1042,19 @@ FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' WHERE id = '[5,5]'; 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), ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018")) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; + id | valid_at +-------+--------------------------------------------------------- + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 02 00:00:00 2018") + [5,6) | ["Wed Jan 03 00:00:00 2018","Thu Feb 01 00:00:00 2018") +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [3,4) | ["Fri Jan 05 00:00:00 2018","Wed Jan 10 00:00:00 2018") | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,3]'; DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); @@ -1008,30 +1064,184 @@ DELETE FROM temporal_rng WHERE id IN ('[5,5]'); -- -- test ON UPDATE/DELETE options -- +-- TOC: +-- parent updates CASCADE +-- parent deletes CASCADE +-- parent updates SET NULL +-- parent deletes SET NULL +-- parent updates SET DEFAULT +-- parent deletes SET DEFAULT +-- parent updates CASCADE (two scalar cols) +-- parent deletes CASCADE (two scalar cols) +-- parent updates SET NULL (two scalar cols) +-- parent deletes SET NULL (two scalar cols) +-- parent deletes SET NULL (two scalar cols, SET NULL subset) +-- parent updates SET DEFAULT (two scalar cols) +-- parent deletes SET DEFAULT (two scalar cols) +-- parent deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) -- test FK parent updates CASCADE INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); 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: invalid ON DELETE 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [7,8) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [7,8) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [9,10) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes CASCADE +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(1 row) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- test FK parent updates SET NULL -INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]'); +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); 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: invalid ON DELETE 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET NULL +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- test FK parent updates SET DEFAULT INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null)); -INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]'); +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', DROP CONSTRAINT temporal_fk_rng2rng_fk, @@ -1039,7 +1249,433 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: invalid ON DELETE 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET DEFAULT +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id +-----------+---------------------------------------------------------+----------- + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent updates CASCADE (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [7,8) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [7,8) | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [9,10) | [8,9) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes CASCADE (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +----+----------+------------+------------ +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(1 row) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent updates SET NULL (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | | + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | | + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET NULL (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | | + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | | + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET NULL (two scalar cols, SET NULL subset) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- 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,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | | [8,9) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent updates SET DEFAULT (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[-1,-1]', tsrange(null, null)); +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]', id2 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]', id2 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) | [-1,0) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]', id2 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET DEFAULT (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) | [-1,0) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) | [-1,0) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); +-- test FK parent deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[6,6]', tsrange(null, null)); +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- 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,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [100,101) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) | [6,7) + [100,101) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) | [6,7) + [100,101) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[8,8]', tsrange(null, null)); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------------+------------+------------ + [200,201) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) | [8,9) + [200,201) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) | [8,9) +(2 rows) + +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- -- test FOREIGN KEY, multirange references multirange -- @@ -1315,6 +1951,22 @@ UPDATE temporal_mltrng SET id = '[7,7]' WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) 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,7]' +WHERE id = '[5,5]'; +-- changing just a part fails: +UPDATE temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +SET id = '[7,7]' +WHERE id = '[5,5]'; +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), {[01-01-2018,01-02-2018),[01-03-2018,02-01-2018)}) 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,3]'; +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); @@ -1349,6 +2001,22 @@ UPDATE temporal_mltrng SET id = '[7,7]' WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) 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,7]' +WHERE id = '[5,5]'; +-- changing just a part fails: +UPDATE temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +SET id = '[7,7]' +WHERE id = '[5,5]'; +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), {[01-01-2018,01-02-2018),[01-03-2018,02-01-2018)}) 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,3]'; +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); @@ -1374,6 +2042,19 @@ DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(dat DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) 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,5]'; +-- 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,5]'; +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), {[01-01-2018,01-02-2018),[01-03-2018,02-01-2018)}) 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,3]'; +DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); @@ -1399,9 +2080,50 @@ DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(dat DELETE FROM temporal_mltrng WHERE id = '[5,5]' 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), {[01-01-2018,02-01-2018)}) 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,5]'; +-- 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,5]'; +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), {[01-01-2018,01-02-2018),[01-03-2018,02-01-2018)}) 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,3]'; +DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); +-- 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 VALUES ('[8,8]', mydaterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk3_rng2rng VALUES ('[5,5]', mydaterange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,5]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [5,6) | [01-01-2018,01-01-2019) | [8,9) + [5,6) | [01-01-2020,01-01-2021) | [8,9) +(2 rows) + +DROP TABLE temporal_fk3_rng2rng; +DROP TABLE temporal_rng3; +DROP TYPE mydaterange; -- FK between partitioned tables CREATE TABLE temporal_partitioned_rng ( id int4range, @@ -1409,8 +2131,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,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]', '[13,13]', '[15,15]', '[17,17]', '[19,19]', '[21,21]', '[23,23]'); +CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,0]', '[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]', '[14,14]', '[16,16]', '[18,18]', '[20,20]', '[22,22]', '[24,24]'); INSERT INTO temporal_partitioned_rng VALUES ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -1423,8 +2145,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,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]', '[13,13]', '[15,15]', '[17,17]', '[19,19]', '[21,21]', '[23,23]'); +CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,0]', '[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]', '[14,14]', '[16,16]', '[18,18]', '[20,20]', '[22,22]', '[24,24]'); -- partitioned FK child inserts INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[1,1]', daterange('2000-01-01', '2000-02-15'), '[1,1]'), @@ -1459,7 +2181,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,5]' 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), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- clean up: DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; @@ -1471,7 +2193,7 @@ INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01- DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); -- should fail: DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' 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), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- clean up: DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; @@ -1493,7 +2215,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,5]' 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), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- clean up: DELETE FROM temporal_partitioned_fk_rng2rng WHERE parent_id = '[5,5]'; @@ -1505,35 +2227,214 @@ INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[3,3]', daterange('2018-01- DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange('2018-02-01', '2018-03-01'); -- should fail: DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' 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), [01-01-2018,02-01-2018)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- partitioned FK parent updates CASCADE +INSERT INTO temporal_partitioned_rng VALUES ('[6,6]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[4,4]', daterange('2018-01-01', '2021-01-01'), '[6,6]'); 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: invalid ON DELETE 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,4]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [4,5) | [01-01-2019,01-01-2020) | [7,8) + [4,5) | [01-01-2018,01-01-2019) | [6,7) + [4,5) | [01-01-2020,01-01-2021) | [6,7) +(3 rows) + +UPDATE temporal_partitioned_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,4]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [4,5) | [01-01-2019,01-01-2020) | [7,8) + [4,5) | [01-01-2018,01-01-2019) | [7,8) + [4,5) | [01-01-2020,01-01-2021) | [7,8) +(3 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[15,15]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[15,15]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[10,10]', daterange('2018-01-01', '2021-01-01'), '[15,15]'); +UPDATE temporal_partitioned_rng SET id = '[16,16]' WHERE id = '[15,15]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,10]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [10,11) | [01-01-2018,01-01-2020) | [16,17) + [10,11) | [01-01-2020,01-01-2021) | [15,16) +(2 rows) + -- partitioned FK parent deletes CASCADE +INSERT INTO temporal_partitioned_rng VALUES ('[8,8]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[5,5]', daterange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,5]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [5,6) | [01-01-2018,01-01-2019) | [8,9) + [5,6) | [01-01-2020,01-01-2021) | [8,9) +(2 rows) + +DELETE FROM temporal_partitioned_rng WHERE id = '[8,8]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,5]'; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[17,17]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[17,17]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[11,11]', daterange('2018-01-01', '2021-01-01'), '[17,17]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[17,17]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,11]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [11,12) | [01-01-2020,01-01-2021) | [17,18) +(1 row) + -- partitioned FK parent updates SET NULL +INSERT INTO temporal_partitioned_rng VALUES ('[9,9]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[6,6]', daterange('2018-01-01', '2021-01-01'), '[9,9]'); 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: invalid ON DELETE 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,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,6]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [6,7) | [01-01-2019,01-01-2020) | + [6,7) | [01-01-2018,01-01-2019) | [9,10) + [6,7) | [01-01-2020,01-01-2021) | [9,10) +(3 rows) + +UPDATE temporal_partitioned_rng SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,6]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [6,7) | [01-01-2019,01-01-2020) | + [6,7) | [01-01-2018,01-01-2019) | + [6,7) | [01-01-2020,01-01-2021) | +(3 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[18,18]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[18,18]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[12,12]', daterange('2018-01-01', '2021-01-01'), '[18,18]'); +UPDATE temporal_partitioned_rng SET id = '[19,19]' WHERE id = '[18,18]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,12]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [12,13) | [01-01-2018,01-01-2020) | + [12,13) | [01-01-2020,01-01-2021) | [18,19) +(2 rows) + -- partitioned FK parent deletes SET NULL +INSERT INTO temporal_partitioned_rng VALUES ('[11,11]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[7,7]', daterange('2018-01-01', '2021-01-01'), '[11,11]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,11]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,7]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [7,8) | [01-01-2019,01-01-2020) | + [7,8) | [01-01-2018,01-01-2019) | [11,12) + [7,8) | [01-01-2020,01-01-2021) | [11,12) +(3 rows) + +DELETE FROM temporal_partitioned_rng WHERE id = '[11,11]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,7]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [7,8) | [01-01-2019,01-01-2020) | + [7,8) | [01-01-2018,01-01-2019) | + [7,8) | [01-01-2020,01-01-2021) | +(3 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[20,20]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[20,20]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[13,13]', daterange('2018-01-01', '2021-01-01'), '[20,20]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[20,20]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,13]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [13,14) | [01-01-2018,01-01-2020) | + [13,14) | [01-01-2020,01-01-2021) | [20,21) +(2 rows) + -- partitioned FK parent updates SET DEFAULT +INSERT INTO temporal_partitioned_rng VALUES ('[0,0]', daterange(null, null)); +INSERT INTO temporal_partitioned_rng VALUES ('[12,12]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[8,8]', daterange('2018-01-01', '2021-01-01'), '[12,12]'); ALTER TABLE temporal_partitioned_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id SET DEFAULT '[0,0]', 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: invalid ON DELETE 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,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,8]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [8,9) | [01-01-2019,01-01-2020) | [0,1) + [8,9) | [01-01-2018,01-01-2019) | [12,13) + [8,9) | [01-01-2020,01-01-2021) | [12,13) +(3 rows) + +UPDATE temporal_partitioned_rng SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,8]'; + id | valid_at | parent_id +-------+-------------------------+----------- + [8,9) | [01-01-2019,01-01-2020) | [0,1) + [8,9) | [01-01-2018,01-01-2019) | [0,1) + [8,9) | [01-01-2020,01-01-2021) | [0,1) +(3 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[22,22]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[22,22]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[14,14]', daterange('2018-01-01', '2021-01-01'), '[22,22]'); +UPDATE temporal_partitioned_rng SET id = '[23,23]' WHERE id = '[22,22]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,14]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [14,15) | [01-01-2018,01-01-2020) | [0,1) + [14,15) | [01-01-2020,01-01-2021) | [22,23) +(2 rows) + -- partitioned FK parent deletes SET DEFAULT +INSERT INTO temporal_partitioned_rng VALUES ('[14,14]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[9,9]', daterange('2018-01-01', '2021-01-01'), '[14,14]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,14]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,9]'; + id | valid_at | parent_id +--------+-------------------------+----------- + [9,10) | [01-01-2019,01-01-2020) | [0,1) + [9,10) | [01-01-2018,01-01-2019) | [14,15) + [9,10) | [01-01-2020,01-01-2021) | [14,15) +(3 rows) + +DELETE FROM temporal_partitioned_rng WHERE id = '[14,14]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,9]'; + id | valid_at | parent_id +--------+-------------------------+----------- + [9,10) | [01-01-2019,01-01-2020) | [0,1) + [9,10) | [01-01-2018,01-01-2019) | [0,1) + [9,10) | [01-01-2020,01-01-2021) | [0,1) +(3 rows) + +INSERT INTO temporal_partitioned_rng VALUES ('[24,24]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[24,24]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[15,15]', daterange('2018-01-01', '2021-01-01'), '[24,24]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[24,24]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,15]'; + id | valid_at | parent_id +---------+-------------------------+----------- + [15,16) | [01-01-2018,01-01-2020) | [0,1) + [15,16) | [01-01-2020,01-01-2021) | [24,25) +(2 rows) + DROP TABLE temporal_partitioned_fk_rng2rng; DROP TABLE temporal_partitioned_rng; diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index c111f6a6bca..959e0b64267 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -664,6 +664,8 @@ UPDATE temporal_rng FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' SET id = '[7,7]' WHERE id = '[5,5]'; +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') 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,3]'; UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') @@ -710,6 +712,8 @@ UPDATE temporal_rng FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' SET id = '[7,7]' WHERE id = '[5,5]'; +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') 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,3]'; UPDATE temporal_rng SET valid_at = tsrange('2016-01-01', '2016-02-01') @@ -745,6 +749,8 @@ WHERE id = '[5,5]'; DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' WHERE id = '[5,5]'; +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') 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,3]'; DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); @@ -780,6 +786,8 @@ WHERE id = '[5,5]'; DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' WHERE id = '[5,5]'; +SELECT * FROM temporal_rng WHERE id in ('[5,5]', '[7,7]') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,3]') 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,3]'; DELETE FROM temporal_rng WHERE id = '[5,5]' AND valid_at = tsrange('2018-01-01', '2018-02-01'); @@ -790,31 +798,114 @@ DELETE FROM temporal_rng WHERE id IN ('[5,5]'); -- -- test ON UPDATE/DELETE options -- +-- TOC: +-- parent updates CASCADE +-- parent deletes CASCADE +-- parent updates SET NULL +-- parent deletes SET NULL +-- parent updates SET DEFAULT +-- parent deletes SET DEFAULT +-- parent updates CASCADE (two scalar cols) +-- parent deletes CASCADE (two scalar cols) +-- parent updates SET NULL (two scalar cols) +-- parent deletes SET NULL (two scalar cols) +-- parent deletes SET NULL (two scalar cols, SET NULL subset) +-- parent updates SET DEFAULT (two scalar cols) +-- parent deletes SET DEFAULT (two scalar cols) +-- parent deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) -- test FK parent updates CASCADE INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[4,4]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes CASCADE +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- test FK parent updates SET NULL -INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]'); +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET NULL +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- test FK parent updates SET DEFAULT INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null)); -INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01'), '[12,12]'); +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', DROP CONSTRAINT temporal_fk_rng2rng_fk, @@ -822,6 +913,245 @@ 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +UPDATE temporal_rng SET id = '[9,9]' WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET DEFAULT +INSERT INTO temporal_rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +DELETE FROM temporal_rng WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng WHERE id = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng WHERE id IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent updates CASCADE (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes CASCADE (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent updates SET NULL (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET NULL (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET NULL (two scalar cols, SET NULL subset) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- 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,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent updates SET DEFAULT (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[-1,-1]', tsrange(null, null)); +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +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,7]', id2 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,7]', id2 = '[7,7]' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +UPDATE temporal_rng2 SET id1 = '[9,9]', id2 = '[9,9]' WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET DEFAULT (two scalar cols) +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); + +-- test FK parent deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[6,6]', tsrange(null, null)); +INSERT INTO temporal_rng2 VALUES ('[6,6]', '[6,6]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng VALUES ('[100,100]', tsrange('2018-01-01', '2021-01-01'), '[6,6]', '[6,6]'); +-- 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,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,6]'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,100]'; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 VALUES ('[8,8]', '[8,8]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_rng2 VALUES ('[-1,-1]', '[8,8]', tsrange(null, null)); +INSERT INTO temporal_fk2_rng2rng VALUES ('[200,200]', tsrange('2018-01-01', '2021-01-01'), '[8,8]', '[8,8]'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,8]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,200]'; +-- clean up +DELETE FROM temporal_fk2_rng2rng WHERE id IN ('[100,100]', '[200,200]'); +DELETE FROM temporal_rng2 WHERE id1 IN ('[6,6]', '[7,7]', '[8,8]', '[9,9]'); -- -- test FOREIGN KEY, multirange references multirange @@ -1068,6 +1398,20 @@ WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-0 -- changing the scalar part fails: UPDATE temporal_mltrng SET id = '[7,7]' WHERE id = '[5,5]' 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,7]' +WHERE id = '[5,5]'; +-- changing just a part fails: +UPDATE temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +SET id = '[7,7]' +WHERE id = '[5,5]'; +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,3]'; +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); @@ -1100,6 +1444,20 @@ WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-0 -- changing the scalar part fails: UPDATE temporal_mltrng SET id = '[7,7]' WHERE id = '[5,5]' 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,7]' +WHERE id = '[5,5]'; +-- changing just a part fails: +UPDATE temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +SET id = '[7,7]' +WHERE id = '[5,5]'; +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,3]'; +UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02-01')) +WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]', '[7,7]'); @@ -1123,6 +1481,17 @@ INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange( DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); -- a PK delete that fails because both are referenced: DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- 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,5]'; +-- 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,5]'; +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,3]'; +DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); @@ -1147,10 +1516,47 @@ INSERT INTO temporal_fk_mltrng2mltrng VALUES ('[3,3]', datemultirange(daterange( DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); -- a PK delete that fails because both are referenced: DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- 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,5]'; +-- 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,5]'; +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,3]'; +DELETE FROM temporal_mltrng WHERE id = '[5,5]' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- clean up: DELETE FROM temporal_fk_mltrng2mltrng WHERE parent_id = '[5,5]'; DELETE FROM temporal_mltrng WHERE id IN ('[5,5]'); +-- 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 VALUES ('[8,8]', mydaterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk3_rng2rng VALUES ('[5,5]', mydaterange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,5]'; + +DROP TABLE temporal_fk3_rng2rng; +DROP TABLE temporal_rng3; +DROP TYPE mydaterange; + -- FK between partitioned tables CREATE TABLE temporal_partitioned_rng ( @@ -1159,8 +1565,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,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]', '[13,13]', '[15,15]', '[17,17]', '[19,19]', '[21,21]', '[23,23]'); +CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,0]', '[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]', '[14,14]', '[16,16]', '[18,18]', '[20,20]', '[22,22]', '[24,24]'); INSERT INTO temporal_partitioned_rng VALUES ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -1174,8 +1580,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,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]'); +CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,1]', '[3,3]', '[5,5]', '[7,7]', '[9,9]', '[11,11]', '[13,13]', '[15,15]', '[17,17]', '[19,19]', '[21,21]', '[23,23]'); +CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,0]', '[2,2]', '[4,4]', '[6,6]', '[8,8]', '[10,10]', '[12,12]', '[14,14]', '[16,16]', '[18,18]', '[20,20]', '[22,22]', '[24,24]'); -- partitioned FK child inserts @@ -1259,37 +1665,107 @@ DELETE FROM temporal_partitioned_rng WHERE id = '[5,5]' AND valid_at = daterange -- partitioned FK parent updates CASCADE +INSERT INTO temporal_partitioned_rng VALUES ('[6,6]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[4,4]', daterange('2018-01-01', '2021-01-01'), '[6,6]'); 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,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,4]'; +UPDATE temporal_partitioned_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,4]'; +INSERT INTO temporal_partitioned_rng VALUES ('[15,15]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[15,15]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[10,10]', daterange('2018-01-01', '2021-01-01'), '[15,15]'); +UPDATE temporal_partitioned_rng SET id = '[16,16]' WHERE id = '[15,15]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,10]'; -- partitioned FK parent deletes CASCADE +INSERT INTO temporal_partitioned_rng VALUES ('[8,8]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[5,5]', daterange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,5]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[8,8]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,5]'; +INSERT INTO temporal_partitioned_rng VALUES ('[17,17]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[17,17]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[11,11]', daterange('2018-01-01', '2021-01-01'), '[17,17]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[17,17]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,11]'; + -- partitioned FK parent updates SET NULL +INSERT INTO temporal_partitioned_rng VALUES ('[9,9]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[6,6]', daterange('2018-01-01', '2021-01-01'), '[9,9]'); 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,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,6]'; +UPDATE temporal_partitioned_rng SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,6]'; +INSERT INTO temporal_partitioned_rng VALUES ('[18,18]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[18,18]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[12,12]', daterange('2018-01-01', '2021-01-01'), '[18,18]'); +UPDATE temporal_partitioned_rng SET id = '[19,19]' WHERE id = '[18,18]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,12]'; -- partitioned FK parent deletes SET NULL +INSERT INTO temporal_partitioned_rng VALUES ('[11,11]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[7,7]', daterange('2018-01-01', '2021-01-01'), '[11,11]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,11]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,7]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[11,11]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,7]'; +INSERT INTO temporal_partitioned_rng VALUES ('[20,20]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[20,20]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[13,13]', daterange('2018-01-01', '2021-01-01'), '[20,20]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[20,20]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,13]'; + -- partitioned FK parent updates SET DEFAULT +INSERT INTO temporal_partitioned_rng VALUES ('[0,0]', daterange(null, null)); +INSERT INTO temporal_partitioned_rng VALUES ('[12,12]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[8,8]', daterange('2018-01-01', '2021-01-01'), '[12,12]'); ALTER TABLE temporal_partitioned_fk_rng2rng - ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id SET DEFAULT '[0,0]', 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,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,8]'; +UPDATE temporal_partitioned_rng SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,8]'; +INSERT INTO temporal_partitioned_rng VALUES ('[22,22]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[22,22]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[14,14]', daterange('2018-01-01', '2021-01-01'), '[22,22]'); +UPDATE temporal_partitioned_rng SET id = '[23,23]' WHERE id = '[22,22]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,14]'; -- partitioned FK parent deletes SET DEFAULT +INSERT INTO temporal_partitioned_rng VALUES ('[14,14]', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[9,9]', daterange('2018-01-01', '2021-01-01'), '[14,14]'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,14]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,9]'; +DELETE FROM temporal_partitioned_rng WHERE id = '[14,14]'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,9]'; +INSERT INTO temporal_partitioned_rng VALUES ('[24,24]', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng VALUES ('[24,24]', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng VALUES ('[15,15]', daterange('2018-01-01', '2021-01-01'), '[24,24]'); +DELETE FROM temporal_partitioned_rng WHERE id = '[24,24]' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,15]'; + DROP TABLE temporal_partitioned_fk_rng2rng; DROP TABLE temporal_partitioned_rng; -- 2.42.0