From 923ae978765a8c820f12e7282fff74a432cbc19d Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 3 Jun 2023 21:41:11 -0400 Subject: [PATCH v55 09/11] Add CASCADE/SET NULL/SET DEFAULT for temporal foreign keys Previously we raised an error for these options, because their implementations require FOR PORTION OF. Now that we have temporal UPDATE/DELETE, we can implement foreign keys that use it. Author: Paul A. Jungwirth --- doc/src/sgml/ref/create_table.sgml | 14 +- doc/src/sgml/temporal.sgml | 7 +- src/backend/commands/tablecmds.c | 65 +- src/backend/utils/adt/ri_triggers.c | 617 ++++++- src/include/catalog/pg_proc.dat | 22 + src/test/regress/expected/btree_index.out | 18 +- .../regress/expected/without_overlaps.out | 1594 ++++++++++++++++- src/test/regress/sql/without_overlaps.sql | 900 +++++++++- 8 files changed, 3185 insertions(+), 52 deletions(-) diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index dc000e913c1..389e95a2a6d 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1315,7 +1315,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. @@ -1330,7 +1332,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. @@ -1347,7 +1352,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/doc/src/sgml/temporal.sgml b/doc/src/sgml/temporal.sgml index 4c8fa93d9a0..9d3f3fddf47 100644 --- a/doc/src/sgml/temporal.sgml +++ b/doc/src/sgml/temporal.sgml @@ -252,9 +252,10 @@ ALTER TABLE variants - PostgreSQL supports NO ACTION temporal foreign keys, - but not RESTRICT, CASCADE, SET NULL, - or SET DEFAULT. + PostgreSQL supports NO ACTION + CASCADE, SET NULL, and + SET DEFAULT temporal foreign keys, + but not RESTRICT. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index c03f7b9c090..6b889230bc7 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -562,7 +562,7 @@ static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo * Relation rel, Constraint *fkconstraint, bool recurse, bool recursing, LOCKMODE lockmode); -static int validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, +static int validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, const int16 fkperiodattnum, int numfksetcols, int16 *fksetcolsattnums, List *fksetcols); static ObjectAddress addFkConstraint(addFkConstraintSides fkside, @@ -10050,6 +10050,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, int16 fkdelsetcols[INDEX_MAX_KEYS] = {0}; bool with_period; bool pk_has_without_overlaps; + int16 fkperiodattnum = InvalidAttrNumber; int i; int numfks, numpks, @@ -10135,15 +10136,20 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, fkconstraint->fk_attrs, fkattnum, fktypoid, fkcolloid); with_period = fkconstraint->fk_with_period || fkconstraint->pk_with_period; - if (with_period && !fkconstraint->fk_with_period) - ereport(ERROR, - errcode(ERRCODE_INVALID_FOREIGN_KEY), - errmsg("foreign key uses PERIOD on the referenced table but not the referencing table")); + if (with_period) + { + if (!fkconstraint->fk_with_period) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FOREIGN_KEY), + errmsg("foreign key uses PERIOD on the referenced table but not the referencing table"))); + fkperiodattnum = fkattnum[numfks - 1]; + } numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel), fkconstraint->fk_del_set_cols, fkdelsetcols, NULL, NULL); numfkdelsetcols = validateFkOnDeleteSetColumns(numfks, fkattnum, + fkperiodattnum, numfkdelsetcols, fkdelsetcols, fkconstraint->fk_del_set_cols); @@ -10245,19 +10251,13 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, */ if (fkconstraint->fk_with_period) { - if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_RESTRICT || - fkconstraint->fk_upd_action == FKCONSTR_ACTION_CASCADE || - fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETNULL || - fkconstraint->fk_upd_action == FKCONSTR_ACTION_SETDEFAULT) + if (fkconstraint->fk_upd_action == FKCONSTR_ACTION_RESTRICT) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("unsupported %s action for foreign key constraint using PERIOD", "ON UPDATE")); - if (fkconstraint->fk_del_action == FKCONSTR_ACTION_RESTRICT || - fkconstraint->fk_del_action == FKCONSTR_ACTION_CASCADE || - fkconstraint->fk_del_action == FKCONSTR_ACTION_SETNULL || - fkconstraint->fk_del_action == FKCONSTR_ACTION_SETDEFAULT) + if (fkconstraint->fk_del_action == FKCONSTR_ACTION_RESTRICT) ereport(ERROR, errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("unsupported %s action for foreign key constraint using PERIOD", @@ -10614,6 +10614,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, */ static int validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, + const int16 fkperiodattnum, int numfksetcols, int16 *fksetcolsattnums, List *fksetcols) { @@ -10627,6 +10628,14 @@ validateFkOnDeleteSetColumns(int numfks, const int16 *fkattnums, /* Make sure it's in 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; @@ -13866,17 +13875,26 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr case FKCONSTR_ACTION_CASCADE: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_del"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_del"); break; case FKCONSTR_ACTION_SETNULL: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_del"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_del"); break; case FKCONSTR_ACTION_SETDEFAULT: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setdefault_del"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_del"); break; default: elog(ERROR, "unrecognized FK action type: %d", @@ -13926,17 +13944,26 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr case FKCONSTR_ACTION_CASCADE: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_cascade_upd"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_cascade_upd"); break; case FKCONSTR_ACTION_SETNULL: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setnull_upd"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_setnull_upd"); break; case FKCONSTR_ACTION_SETDEFAULT: fk_trigger->deferrable = false; fk_trigger->initdeferred = false; - fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd"); + if (fkconstraint->fk_with_period) + fk_trigger->funcname = SystemFuncName("RI_FKey_period_setdefault_upd"); + else + fk_trigger->funcname = SystemFuncName("RI_FKey_setdefault_upd"); break; default: elog(ERROR, "unrecognized FK action type: %d", diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index fc8d59e0f47..4f2d62011a7 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -79,6 +79,12 @@ #define RI_PLAN_SETNULL_ONUPDATE 8 #define RI_PLAN_SETDEFAULT_ONDELETE 9 #define RI_PLAN_SETDEFAULT_ONUPDATE 10 +#define RI_PLAN_PERIOD_CASCADE_ONDELETE 11 +#define RI_PLAN_PERIOD_CASCADE_ONUPDATE 12 +#define RI_PLAN_PERIOD_SETNULL_ONUPDATE 13 +#define RI_PLAN_PERIOD_SETNULL_ONDELETE 14 +#define RI_PLAN_PERIOD_SETDEFAULT_ONUPDATE 15 +#define RI_PLAN_PERIOD_SETDEFAULT_ONDELETE 16 #define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3) #define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2) @@ -194,6 +200,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, @@ -230,6 +237,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int periodParam, Datum period, bool is_restrict, bool detectNewRows, int expect_OK); static void ri_ExtractValues(Relation rel, TupleTableSlot *slot, @@ -239,6 +247,11 @@ pg_noreturn static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool is_restrict, bool partgone); +static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo); +static Datum restrict_enforced_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo, + TupleTableSlot *oldslot); /* @@ -452,6 +465,7 @@ RI_FKey_check(TriggerData *trigdata) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, NULL, newslot, + -1, (Datum) 0, false, pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE, SPI_OK_SELECT); @@ -617,6 +631,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, result = ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, false, true, /* treat like update */ SPI_OK_SELECT); @@ -893,6 +908,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, !is_no_action, true, /* must detect new rows */ SPI_OK_SELECT); @@ -995,6 +1011,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, false, true, /* must detect new rows */ SPI_OK_DELETE); @@ -1112,6 +1129,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, newslot, + -1, (Datum) 0, false, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1340,6 +1358,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, false, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1371,6 +1390,540 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) } +/* + * RI_FKey_period_cascade_del - + * + * Cascaded delete foreign key references at delete event on temporal PK table. + */ +Datum +RI_FKey_period_cascade_del(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + const RI_ConstraintInfo *riinfo; + Relation fk_rel; + Relation pk_rel; + TupleTableSlot *oldslot; + RI_QueryKey qkey; + SPIPlanPtr qplan; + Datum targetRange; + + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_cascade_del", RI_TRIGTYPE_DELETE); + + riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, + trigdata->tg_relation, true); + + /* + * Get the relation descriptors of the FK and PK tables and the old tuple. + * + * fk_rel is opened in RowExclusiveLock mode since that's what our + * eventual DELETE will get on it. + */ + fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock); + pk_rel = trigdata->tg_relation; + oldslot = trigdata->tg_trigslot; + + /* + * Don't delete than more than the PK's duration, trimmed by an original + * FOR PORTION OF if necessary. + */ + targetRange = restrict_enforced_range(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 (${n+1}) + * WHERE $1 = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. + * ---------- + */ + initStringInfo(&querybuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "DELETE FROM %s%s FOR PORTION OF %s ($%d)", + fk_only, fkrelname, attname, riinfo->nkeys + 1); + querysep = "WHERE"; + for (int i = 0; i < riinfo->nkeys; i++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + sprintf(paramname, "$%d", i + 1); + ri_GenerateQual(&querybuf, querysep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + querysep = "AND"; + queryoids[i] = pk_type; + } + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Build up the arguments from the key values in the + * deleted PK tuple and delete the referencing rows + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, NULL, + riinfo->nkeys + 1, targetRange, + false, + true, /* must detect new rows */ + SPI_OK_DELETE); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + table_close(fk_rel, RowExclusiveLock); + + return PointerGetDatum(NULL); +} + +/* + * RI_FKey_period_cascade_upd - + * + * Cascaded update foreign key references at update event on temporal PK table. + */ +Datum +RI_FKey_period_cascade_upd(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + const RI_ConstraintInfo *riinfo; + Relation fk_rel; + Relation pk_rel; + TupleTableSlot *oldslot; + TupleTableSlot *newslot; + RI_QueryKey qkey; + SPIPlanPtr qplan; + Datum targetRange; + + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "RI_FKey_period_cascade_upd", RI_TRIGTYPE_UPDATE); + + riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, + trigdata->tg_relation, true); + + /* + * Get the relation descriptors of the FK and PK tables and the new and + * old tuple. + * + * fk_rel is opened in RowExclusiveLock mode since that's what our + * eventual UPDATE will get on it. + */ + fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock); + pk_rel = trigdata->tg_relation; + newslot = trigdata->tg_newslot; + oldslot = trigdata->tg_trigslot; + + /* + * Don't delete than more than the PK's duration, trimmed by an original + * FOR PORTION OF if necessary. + */ + targetRange = restrict_enforced_range(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 (${2n+1}) + * SET fkatt1 = $1, [, ...] + * WHERE $n = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. Note that we are assuming + * there is an assignment cast from the PK to the FK type; + * else the parser will fail. + * ---------- + */ + initStringInfo(&querybuf); + initStringInfo(&qualbuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s ($%d) SET", + fk_only, fkrelname, attname, 2 * riinfo->nkeys + 1); + + querysep = ""; + qualsep = "WHERE"; + for (int i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + + /* + * Don't set the temporal column(s). FOR PORTION OF will take care + * of that. + */ + if (i < riinfo->nkeys - 1) + appendStringInfo(&querybuf, + "%s %s = $%d", + querysep, attname, i + 1); + + sprintf(paramname, "$%d", j + 1); + ri_GenerateQual(&qualbuf, qualsep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + querysep = ","; + qualsep = "AND"; + queryoids[i] = pk_type; + queryoids[j] = pk_type; + } + appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len); + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[2 * riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, 2 * riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Run it to update the existing references. + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, newslot, + riinfo->nkeys * 2 + 1, targetRange, + false, + 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 more than the PK's duration, trimmed by an + * original FOR PORTION OF if necessary. + */ + targetRange = restrict_enforced_range(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 (${n+1}) + * SET fkatt1 = {NULL|DEFAULT} [, ...] + * WHERE $1 = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. + * ---------- + */ + initStringInfo(&querybuf); + initStringInfo(&qualbuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s ($%d) SET", + fk_only, fkrelname, attname, riinfo->nkeys + 1); + + /* + * Add assignment clauses + */ + querysep = ""; + for (int i = 0; i < num_cols_to_set; i++) + { + quoteOneName(attname, RIAttName(fk_rel, set_cols[i])); + appendStringInfo(&querybuf, + "%s %s = %s", + querysep, attname, + is_set_null ? "NULL" : "DEFAULT"); + querysep = ","; + } + + /* + * Add WHERE clause + */ + qualsep = "WHERE"; + for (int i = 0; i < riinfo->nkeys; i++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + + sprintf(paramname, "$%d", i + 1); + ri_GenerateQual(&querybuf, qualsep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + qualsep = "AND"; + queryoids[i] = pk_type; + } + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Run it to update the existing references. + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, NULL, + riinfo->nkeys + 1, targetRange, + false, + 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 - * @@ -2487,6 +3040,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int periodParam, Datum period, bool is_restrict, bool detectNewRows, int expect_OK) { @@ -2499,8 +3053,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 @@ -2543,6 +3097,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk, vals, nulls); } + /* Add/replace a query param for the PERIOD if needed */ + if (period) + { + vals[periodParam - 1] = period; + nulls[periodParam - 1] = ' '; + } /* * In READ COMMITTED mode, we just need to use an up-to-date regular @@ -3223,6 +3783,12 @@ RI_FKey_trigger_type(Oid tgfoid) case F_RI_FKEY_SETDEFAULT_UPD: case F_RI_FKEY_NOACTION_DEL: case F_RI_FKEY_NOACTION_UPD: + case F_RI_FKEY_PERIOD_CASCADE_DEL: + case F_RI_FKEY_PERIOD_CASCADE_UPD: + case F_RI_FKEY_PERIOD_SETNULL_DEL: + case F_RI_FKEY_PERIOD_SETNULL_UPD: + case F_RI_FKEY_PERIOD_SETDEFAULT_DEL: + case F_RI_FKEY_PERIOD_SETDEFAULT_UPD: return RI_TRIGGER_PK; case F_RI_FKEY_CHECK_INS: @@ -3232,3 +3798,50 @@ RI_FKey_trigger_type(Oid tgfoid) return RI_TRIGGER_NONE; } + +/* + * fpo_targets_pk_range + * + * Returns true iff the primary key referenced by riinfo includes the range + * column targeted by the FOR PORTION OF clause (according to tg_temporal). + */ +static bool +fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo) +{ + if (tg_temporal == NULL) + return false; + + return riinfo->pk_attnums[riinfo->nkeys - 1] == tg_temporal->fp_rangeAttno; +} + +/* + * restrict_enforced_range - + * + * Returns a Datum of RangeTypeP holding the appropriate timespan + * to target child records when we RESTRICT/CASCADE/SET NULL/SET DEFAULT. + * + * In a normal UPDATE/DELETE this should be the referenced row's own valid time, + * but if there was a FOR PORTION OF clause, then we should use that to + * trim down the span further. + */ +static Datum +restrict_enforced_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot) +{ + Datum pkRecordRange; + bool isnull; + AttrNumber attno = riinfo->pk_attnums[riinfo->nkeys - 1]; + + pkRecordRange = slot_getattr(oldslot, attno, &isnull); + if (isnull) + elog(ERROR, "application time should not be null"); + + if (fpo_targets_pk_range(tg_temporal, riinfo)) + { + 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_proc.dat b/src/include/catalog/pg_proc.dat index 7c47d06dc10..0e23a45fb5d 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -4120,6 +4120,28 @@ prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_noaction_upd' }, +# Temporal referential integrity constraint triggers +{ oid => '6124', descr => 'temporal referential integrity ON DELETE CASCADE', + proname => 'RI_FKey_period_cascade_del', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_cascade_del' }, +{ oid => '6125', descr => 'temporal referential integrity ON UPDATE CASCADE', + proname => 'RI_FKey_period_cascade_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_cascade_upd' }, +{ oid => '6128', descr => 'temporal referential integrity ON DELETE SET NULL', + proname => 'RI_FKey_period_setnull_del', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_setnull_del' }, +{ oid => '6129', descr => 'temporal referential integrity ON UPDATE SET NULL', + proname => 'RI_FKey_period_setnull_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'RI_FKey_period_setnull_upd' }, +{ oid => '6130', descr => 'temporal referential integrity ON DELETE SET DEFAULT', + proname => 'RI_FKey_period_setdefault_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_setdefault_del' }, +{ oid => '6131', descr => 'temporal referential integrity ON UPDATE SET DEFAULT', + proname => 'RI_FKey_period_setdefault_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'RI_FKey_period_setdefault_upd' }, + { oid => '1666', proname => 'varbiteq', proleakproof => 't', prorettype => 'bool', proargtypes => 'varbit varbit', prosrc => 'biteq' }, diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 21dc9b5783a..c3bf94797e7 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -454,14 +454,17 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; (3 rows) select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; - proname ------------------------- + proname +------------------------------- RI_FKey_cascade_del RI_FKey_noaction_del + RI_FKey_period_cascade_del + RI_FKey_period_setdefault_del + RI_FKey_period_setnull_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(5 rows) +(8 rows) explain (costs off) select proname from pg_proc where proname ilike '00%foo' order by 1; @@ -500,14 +503,17 @@ select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; (6 rows) select proname from pg_proc where proname like E'RI\\_FKey%del' order by 1; - proname ------------------------- + proname +------------------------------- RI_FKey_cascade_del RI_FKey_noaction_del + RI_FKey_period_cascade_del + RI_FKey_period_setdefault_del + RI_FKey_period_setnull_del RI_FKey_restrict_del RI_FKey_setdefault_del RI_FKey_setnull_del -(5 rows) +(8 rows) explain (costs off) select proname from pg_proc where proname ilike '00%foo' order by 1; diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 401550b5482..4d9f25ac405 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -1947,7 +1947,24 @@ ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD -- -- rng2rng test ON UPDATE/DELETE options -- +-- TOC: +-- referenced updates CASCADE +-- referenced deletes CASCADE +-- referenced updates SET NULL +-- referenced deletes SET NULL +-- referenced updates SET DEFAULT +-- referenced deletes SET DEFAULT +-- referenced updates CASCADE (two scalar cols) +-- referenced deletes CASCADE (two scalar cols) +-- referenced updates SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- referenced updates SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- -- test FK referenced updates CASCADE +-- TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); @@ -1956,29 +1973,593 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [7,8) + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [7,8) + [100,101) | [2019-01-01,2020-01-01) | [7,8) + [100,101) | [2020-01-01,2021-01-01) | [7,8) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2018-01-01,2020-01-01) | [9,10) + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes CASCADE +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(1 row) + +-- -- test FK referenced updates SET NULL +-- TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | + [100,101) | [2019-01-01,2020-01-01) | + [100,101) | [2020-01-01,2021-01-01) | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2018-01-01,2020-01-01) | + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | + [100,101) | [2019-01-01,2020-01-01) | + [100,101) | [2020-01-01,2021-01-01) | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2018-01-01,2020-01-01) | + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +-- -- test FK referenced updates SET DEFAULT +-- TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [-1,0) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2018-01-01,2020-01-01) | [-1,0) + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT +-- +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [100,101) | [2018-01-01,2019-01-01) | [-1,0) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+-------------------------+----------- + [200,201) | [2018-01-01,2020-01-01) | [-1,0) + [200,201) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +-- +-- test FK referenced updates CASCADE (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [7,8) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [7,8) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [7,8) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [7,8) | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | [9,10) | [8,9) + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes CASCADE (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +----+----------+------------+------------ +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(1 row) + +-- +-- test FK referenced updates SET NULL (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | | + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | | + [100,101) | [2019-01-01,2020-01-01) | | + [100,101) | [2020-01-01,2021-01-01) | | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | | + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | | + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | | + [100,101) | [2019-01-01,2020-01-01) | | + [100,101) | [2020-01-01,2021-01-01) | | +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | | + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL (valid_at) ON UPDATE SET NULL; +ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD +-- ok: +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL; +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | | [6,7) + [100,101) | [2019-01-01,2020-01-01) | | [6,7) + [100,101) | [2020-01-01,2021-01-01) | | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | | [8,9) + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced updates SET DEFAULT (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [-1,0) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [-1,0) + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [-1,0) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [-1,0) + [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [-1,0) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [-1,0) + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT; +ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD +-- ok: +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT; +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [6,7) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [6,7) | [6,7) +(3 rows) + +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [100,101) | [2018-01-01,2019-01-01) | [-1,0) | [6,7) + [100,101) | [2019-01-01,2020-01-01) | [-1,0) | [6,7) + [100,101) | [2020-01-01,2021-01-01) | [-1,0) | [6,7) +(3 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', daterange(null, null)); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+-------------------------+------------+------------ + [200,201) | [2018-01-01,2020-01-01) | [-1,0) | [8,9) + [200,201) | [2020-01-01,2021-01-01) | [8,9) | [8,9) +(2 rows) + -- -- test FOREIGN KEY, multirange references multirange -- @@ -2413,6 +2994,626 @@ DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02 DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- +-- +-- mltrng2mltrng test ON UPDATE/DELETE options +-- +-- TOC: +-- referenced updates CASCADE +-- referenced deletes CASCADE +-- referenced updates SET NULL +-- referenced deletes SET NULL +-- referenced updates SET DEFAULT +-- referenced deletes SET DEFAULT +-- referenced updates CASCADE (two scalar cols) +-- referenced deletes CASCADE (two scalar cols) +-- referenced updates SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- referenced updates SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- +-- test FK referenced updates CASCADE +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [7,8) +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [7,8) + [100,101) | {[2019-01-01,2020-01-01)} | [7,8) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2018-01-01,2020-01-01)} | [9,10) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(2 rows) + +-- +-- test FK referenced deletes CASCADE +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) +(1 row) + +-- non-FPO delete: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(1 row) + +-- +-- test FK referenced updates SET NULL +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | + [100,101) | {[2019-01-01,2020-01-01)} | +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2018-01-01,2020-01-01)} | + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | + [100,101) | {[2019-01-01,2020-01-01)} | +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2018-01-01,2020-01-01)} | + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(2 rows) + +-- +-- test FK referenced updates SET DEFAULT +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT +-- +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) +(2 rows) + +-- non-FPO update: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------------------------------+----------- + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id +-----------+---------------------------+----------- + [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) +(2 rows) + +-- +-- test FK referenced updates CASCADE (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [7,8) | [6,7) +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [7,8) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [7,8) | [6,7) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | [9,10) | [8,9) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes CASCADE (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) +(1 row) + +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +----+----------+------------+------------ +(0 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(1 row) + +-- +-- test FK referenced updates SET NULL (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | | +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | | + [100,101) | {[2019-01-01,2020-01-01)} | | +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | | + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | | +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | | + [100,101) | {[2019-01-01,2020-01-01)} | | +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | | + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL (valid_at) ON UPDATE SET NULL; +ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD +-- ok: +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL; +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | | [6,7) +(2 rows) + +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | | [6,7) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | | [8,9) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced updates SET DEFAULT (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0) +(2 rows) + +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [-1,0) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [-1,0) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0) +(2 rows) + +-- non-FPO update: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [-1,0) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [-1,0) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [-1,0) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT; +ERROR: column "valid_at" referenced in ON DELETE SET action cannot be PERIOD +-- ok: +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT; +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [6,7) +(2 rows) + +-- non-FPO update: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------------------------------+------------+------------ + [100,101) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [-1,0) | [6,7) + [100,101) | {[2019-01-01,2020-01-01)} | [-1,0) | [6,7) +(2 rows) + +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', datemultirange(daterange(null, null))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + id | valid_at | parent_id1 | parent_id2 +-----------+---------------------------+------------+------------ + [200,201) | {[2018-01-01,2020-01-01)} | [-1,0) | [8,9) + [200,201) | {[2020-01-01,2021-01-01)} | [8,9) | [8,9) +(2 rows) + +-- FK with a custom range type +CREATE TYPE mydaterange AS range(subtype=date); +CREATE TABLE temporal_rng3 ( + id int4range, + valid_at mydaterange, + CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_fk3_rng2rng ( + id int4range, + valid_at mydaterange, + parent_id int4range, + CONSTRAINT temporal_fk3_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk3_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng3 (id, PERIOD valid_at) ON DELETE CASCADE +); +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[8,9)', mydaterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk3_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', mydaterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)'; +SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,6)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [5,6) | [2018-01-01,2019-01-01) | [8,9) + [5,6) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +DROP TABLE temporal_fk3_rng2rng; +DROP TABLE temporal_rng3; +DROP TYPE mydaterange; +-- -- FK between partitioned tables: ranges -- CREATE TABLE temporal_partitioned_rng ( @@ -2421,8 +3622,8 @@ CREATE TABLE temporal_partitioned_rng ( name text, CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); -CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)'); +CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)'); INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -2435,8 +3636,8 @@ CREATE TABLE temporal_partitioned_fk_rng2rng ( CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) ) PARTITION BY LIST (id); -CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)'); +CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)'); -- -- partitioned FK referencing inserts -- @@ -2478,7 +3679,7 @@ UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03- -- should fail: UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_1" on table "temporal_partitioned_fk_rng2rng" +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_2" on table "temporal_partitioned_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- -- partitioned FK referenced deletes NO ACTION @@ -2490,37 +3691,162 @@ INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[ DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); -- should fail: DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_1" on table "temporal_partitioned_fk_rng2rng" +ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk_2" on table "temporal_partitioned_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng". -- -- partitioned FK referenced updates CASCADE -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [4,5) | [2019-01-01,2020-01-01) | [7,8) + [4,5) | [2018-01-01,2019-01-01) | [6,7) + [4,5) | [2020-01-01,2021-01-01) | [6,7) +(3 rows) + +UPDATE temporal_partitioned_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [4,5) | [2019-01-01,2020-01-01) | [7,8) + [4,5) | [2018-01-01,2019-01-01) | [7,8) + [4,5) | [2020-01-01,2021-01-01) | [7,8) +(3 rows) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[10,11)', daterange('2018-01-01', '2021-01-01'), '[15,16)'); +UPDATE temporal_partitioned_rng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,11)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [10,11) | [2018-01-01,2020-01-01) | [16,17) + [10,11) | [2020-01-01,2021-01-01) | [15,16) +(2 rows) + -- -- partitioned FK referenced deletes CASCADE -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [5,6) | [2018-01-01,2019-01-01) | [8,9) + [5,6) | [2020-01-01,2021-01-01) | [8,9) +(2 rows) + +DELETE FROM temporal_partitioned_rng WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)'; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'), '[17,18)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,12)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [11,12) | [2020-01-01,2021-01-01) | [17,18) +(1 row) + -- -- partitioned FK referenced updates SET NULL -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [6,7) | [2019-01-01,2020-01-01) | + [6,7) | [2018-01-01,2019-01-01) | [9,10) + [6,7) | [2020-01-01,2021-01-01) | [9,10) +(3 rows) + +UPDATE temporal_partitioned_rng SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [6,7) | [2019-01-01,2020-01-01) | + [6,7) | [2018-01-01,2019-01-01) | + [6,7) | [2020-01-01,2021-01-01) | +(3 rows) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'), '[18,19)'); +UPDATE temporal_partitioned_rng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,13)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [12,13) | [2018-01-01,2020-01-01) | + [12,13) | [2020-01-01,2021-01-01) | [18,19) +(2 rows) + -- -- partitioned FK referenced deletes SET NULL -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[7,8)', daterange('2018-01-01', '2021-01-01'), '[11,12)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [7,8) | [2019-01-01,2020-01-01) | + [7,8) | [2018-01-01,2019-01-01) | [11,12) + [7,8) | [2020-01-01,2021-01-01) | [11,12) +(3 rows) + +DELETE FROM temporal_partitioned_rng WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [7,8) | [2019-01-01,2020-01-01) | + [7,8) | [2018-01-01,2019-01-01) | + [7,8) | [2020-01-01,2021-01-01) | +(3 rows) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[13,14)', daterange('2018-01-01', '2021-01-01'), '[20,21)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,14)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [13,14) | [2018-01-01,2020-01-01) | + [13,14) | [2020-01-01,2021-01-01) | [20,21) +(2 rows) + -- -- partitioned FK referenced updates SET DEFAULT -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null)); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); ALTER TABLE temporal_partitioned_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, @@ -2528,10 +3854,73 @@ ALTER TABLE temporal_partitioned_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,14)' WHERE id = '[12,13)'; +ERROR: insert or update on table "tfkp2" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2019-01-01,2020-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [8,9) | [2018-01-01,2021-01-01) | [12,13) +(1 row) + +UPDATE temporal_partitioned_rng SET id = '[13,14)' WHERE id = '[12,13)'; +ERROR: insert or update on table "tfkp2" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2018-01-01,2021-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)'; + id | valid_at | parent_id +-------+-------------------------+----------- + [8,9) | [2018-01-01,2021-01-01) | [12,13) +(1 row) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'), '[22,23)'); +UPDATE temporal_partitioned_rng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date; +ERROR: insert or update on table "tfkp2" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2018-01-01,2020-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,15)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [14,15) | [2018-01-01,2021-01-01) | [22,23) +(1 row) + -- -- partitioned FK referenced deletes SET DEFAULT -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null)); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'), '[14,15)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,15)'; +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2019-01-01,2020-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)'; + id | valid_at | parent_id +--------+-------------------------+----------- + [9,10) | [2018-01-01,2021-01-01) | [14,15) +(1 row) + +DELETE FROM temporal_partitioned_rng WHERE id = '[14,15)'; +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2018-01-01,2021-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)'; + id | valid_at | parent_id +--------+-------------------------+----------- + [9,10) | [2018-01-01,2021-01-01) | [14,15) +(1 row) + +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[15,16)', daterange('2018-01-01', '2021-01-01'), '[24,25)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date; +ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitioned_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=([-1,0), [2018-01-01,2020-01-01)) is not present in table "temporal_partitioned_rng". +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,16)'; + id | valid_at | parent_id +---------+-------------------------+----------- + [15,16) | [2018-01-01,2021-01-01) | [24,25) +(1 row) + DROP TABLE temporal_partitioned_fk_rng2rng; DROP TABLE temporal_partitioned_rng; -- @@ -2617,32 +4006,150 @@ DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenc -- -- partitioned FK referenced updates CASCADE -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[4,5)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[4,5)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [4,5) | {[2019-01-01,2020-01-01)} | [7,8) + [4,5) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [6,7) +(2 rows) + +UPDATE temporal_partitioned_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[4,5)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [4,5) | {[2019-01-01,2020-01-01)} | [7,8) + [4,5) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [7,8) +(2 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[15,16)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[15,16)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[10,11)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[15,16)'); +UPDATE temporal_partitioned_mltrng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[10,11)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [10,11) | {[2018-01-01,2020-01-01)} | [16,17) + [10,11) | {[2020-01-01,2021-01-01)} | [15,16) +(2 rows) + -- -- partitioned FK referenced deletes CASCADE -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[5,6)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [5,6) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [8,9) +(1 row) + +DELETE FROM temporal_partitioned_mltrng WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[5,6)'; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[17,18)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[17,18)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[11,12)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[17,18)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[11,12)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [11,12) | {[2020-01-01,2021-01-01)} | [17,18) +(1 row) + -- -- partitioned FK referenced updates SET NULL -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[9,10)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[9,10)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[6,7)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [6,7) | {[2019-01-01,2020-01-01)} | + [6,7) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [9,10) +(2 rows) + +UPDATE temporal_partitioned_mltrng SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[6,7)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [6,7) | {[2019-01-01,2020-01-01)} | + [6,7) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | +(2 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[18,19)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[18,19)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[12,13)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[18,19)'); +UPDATE temporal_partitioned_mltrng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[12,13)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [12,13) | {[2018-01-01,2020-01-01)} | + [12,13) | {[2020-01-01,2021-01-01)} | [18,19) +(2 rows) + -- -- partitioned FK referenced deletes SET NULL -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[11,12)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[7,8)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[11,12)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[7,8)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [7,8) | {[2019-01-01,2020-01-01)} | + [7,8) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [11,12) +(2 rows) + +DELETE FROM temporal_partitioned_mltrng WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[7,8)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [7,8) | {[2019-01-01,2020-01-01)} | + [7,8) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | +(2 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[20,21)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[20,21)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[13,14)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[20,21)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[13,14)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [13,14) | {[2018-01-01,2020-01-01)} | + [13,14) | {[2020-01-01,2021-01-01)} | [20,21) +(2 rows) + -- -- partitioned FK referenced updates SET DEFAULT -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[0,1)', datemultirange(daterange(null, null))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[12,13)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[12,13)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng ALTER COLUMN parent_id SET DEFAULT '[0,1)', DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, @@ -2650,10 +4157,67 @@ ALTER TABLE temporal_partitioned_fk_mltrng2mltrng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[8,9)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [8,9) | {[2019-01-01,2020-01-01)} | [0,1) + [8,9) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [12,13) +(2 rows) + +UPDATE temporal_partitioned_mltrng SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[8,9)'; + id | valid_at | parent_id +-------+---------------------------------------------------+----------- + [8,9) | {[2019-01-01,2020-01-01)} | [0,1) + [8,9) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [0,1) +(2 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[22,23)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[22,23)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[14,15)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[22,23)'); +UPDATE temporal_partitioned_mltrng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[14,15)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [14,15) | {[2018-01-01,2020-01-01)} | [0,1) + [14,15) | {[2020-01-01,2021-01-01)} | [22,23) +(2 rows) + -- -- partitioned FK referenced deletes SET DEFAULT -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[0,1)', datemultirange(daterange(null, null))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[14,15)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[9,10)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[14,15)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[9,10)'; + id | valid_at | parent_id +--------+---------------------------------------------------+----------- + [9,10) | {[2019-01-01,2020-01-01)} | [0,1) + [9,10) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [14,15) +(2 rows) + +DELETE FROM temporal_partitioned_mltrng WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[9,10)'; + id | valid_at | parent_id +--------+---------------------------------------------------+----------- + [9,10) | {[2019-01-01,2020-01-01)} | [0,1) + [9,10) | {[2018-01-01,2019-01-01),[2020-01-01,2021-01-01)} | [0,1) +(2 rows) + +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[24,25)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[24,25)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[15,16)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[24,25)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[15,16)'; + id | valid_at | parent_id +---------+---------------------------+----------- + [15,16) | {[2018-01-01,2020-01-01)} | [0,1) + [15,16) | {[2020-01-01,2021-01-01)} | [24,25) +(2 rows) + DROP TABLE temporal_partitioned_fk_mltrng2mltrng; DROP TABLE temporal_partitioned_mltrng; RESET datestyle; diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index c5c89fe40ab..224ddef8430 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -1424,8 +1424,26 @@ ALTER TABLE temporal_fk_rng2rng -- -- rng2rng test ON UPDATE/DELETE options -- +-- TOC: +-- referenced updates CASCADE +-- referenced deletes CASCADE +-- referenced updates SET NULL +-- referenced deletes SET NULL +-- referenced updates SET DEFAULT +-- referenced deletes SET DEFAULT +-- referenced updates CASCADE (two scalar cols) +-- referenced deletes CASCADE (two scalar cols) +-- referenced updates SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- referenced updates SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- -- test FK referenced updates CASCADE +-- + TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); @@ -1434,28 +1452,346 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes CASCADE +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; +-- -- test FK referenced updates SET NULL +-- + TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng + DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; +-- -- test FK referenced updates SET DEFAULT +-- + TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_rng2rng_fk, ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +UPDATE temporal_rng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT +-- + +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_rng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates CASCADE (two scalar cols) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes CASCADE (two scalar cols) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET NULL (two scalar cols) +-- +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL (two scalar cols) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL (valid_at) ON UPDATE SET NULL; +-- ok: +ALTER TABLE temporal_fk2_rng2rng + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL; +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET DEFAULT (two scalar cols) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_rng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +UPDATE temporal_rng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- + +TRUNCATE temporal_rng2, temporal_fk2_rng2rng; +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', daterange(null, null)); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT; +-- ok: +ALTER TABLE temporal_fk2_rng2rng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_rng2rng_fk, + ADD CONSTRAINT temporal_fk2_rng2rng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_rng2 + ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT; +-- leftovers on both sides: +DELETE FROM temporal_rng2 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_rng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_rng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', daterange(null, null)); +INSERT INTO temporal_fk2_rng2rng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', daterange('2018-01-01', '2021-01-01'), '[8,9)', '[8,9)'); +DELETE FROM temporal_rng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_rng2rng WHERE id = '[200,201)' ORDER BY id, valid_at; -- -- test FOREIGN KEY, multirange references multirange @@ -1855,6 +2191,408 @@ WHERE id = '[5,6)'; DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- + +-- +-- mltrng2mltrng test ON UPDATE/DELETE options +-- +-- TOC: +-- referenced updates CASCADE +-- referenced deletes CASCADE +-- referenced updates SET NULL +-- referenced deletes SET NULL +-- referenced updates SET DEFAULT +-- referenced deletes SET DEFAULT +-- referenced updates CASCADE (two scalar cols) +-- referenced deletes CASCADE (two scalar cols) +-- referenced updates SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols) +-- referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- referenced updates SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols) +-- referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) + +-- +-- test FK referenced updates CASCADE +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes CASCADE +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET NULL +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET DEFAULT +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +ALTER TABLE temporal_fk_mltrng2mltrng + ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk + FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_mltrng + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +UPDATE temporal_mltrng SET id = '[9,10)' WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT +-- + +TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_mltrng WHERE id = '[6,7)'; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_mltrng WHERE id = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates CASCADE (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE CASCADE ON UPDATE CASCADE; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes CASCADE (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET NULL (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL ON UPDATE SET NULL; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET NULL (two scalar cols, SET NULL subset) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL (valid_at) ON UPDATE SET NULL; +-- ok: +ALTER TABLE temporal_fk2_mltrng2mltrng + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET NULL (parent_id1) ON UPDATE SET NULL; +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO delete: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced updates SET DEFAULT (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + ALTER COLUMN parent_id2 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +-- leftovers on both sides: +UPDATE temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +UPDATE temporal_mltrng2 SET id1 = '[7,8)', id2 = '[7,8)' WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +UPDATE temporal_mltrng2 SET id1 = '[9,10)', id2 = '[9,10)' WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[-1,-1]', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- +-- test FK referenced deletes SET DEFAULT (two scalar cols, SET DEFAULT subset) +-- + +TRUNCATE temporal_mltrng2, temporal_fk2_mltrng2mltrng; +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[6,7)', datemultirange(daterange(null, null))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[6,7)', '[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[100,101)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)', '[6,7)'); +-- fails because you can't set the PERIOD column: +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT (valid_at) ON UPDATE SET DEFAULT; +-- ok: +ALTER TABLE temporal_fk2_mltrng2mltrng + ALTER COLUMN parent_id1 SET DEFAULT '[-1,-1]', + DROP CONSTRAINT temporal_fk2_mltrng2mltrng_fk, + ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk + FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) + REFERENCES temporal_mltrng2 + ON DELETE SET DEFAULT (parent_id1) ON UPDATE SET DEFAULT; +-- leftovers on both sides: +DELETE FROM temporal_mltrng2 FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- non-FPO update: +DELETE FROM temporal_mltrng2 WHERE id1 = '[6,7)'; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[100,101)' ORDER BY id, valid_at; +-- FK across two referenced rows: +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[8,9)', '[8,9)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_mltrng2 (id1, id2, valid_at) VALUES ('[-1,-1]', '[8,9)', datemultirange(daterange(null, null))); +INSERT INTO temporal_fk2_mltrng2mltrng (id, valid_at, parent_id1, parent_id2) VALUES ('[200,201)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)', '[8,9)'); +DELETE FROM temporal_mltrng2 WHERE id1 = '[8,9)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_fk2_mltrng2mltrng WHERE id = '[200,201)' ORDER BY id, valid_at; + +-- FK with a custom range type + +CREATE TYPE mydaterange AS range(subtype=date); + +CREATE TABLE temporal_rng3 ( + id int4range, + valid_at mydaterange, + CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +CREATE TABLE temporal_fk3_rng2rng ( + id int4range, + valid_at mydaterange, + parent_id int4range, + CONSTRAINT temporal_fk3_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk3_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng3 (id, PERIOD valid_at) ON DELETE CASCADE +); +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[8,9)', mydaterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk3_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', mydaterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_rng3 FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)'; +SELECT * FROM temporal_fk3_rng2rng WHERE id = '[5,6)'; + +DROP TABLE temporal_fk3_rng2rng; +DROP TABLE temporal_rng3; +DROP TYPE mydaterange; + -- -- FK between partitioned tables: ranges -- @@ -1865,8 +2603,8 @@ CREATE TABLE temporal_partitioned_rng ( name text, CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); -CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +CREATE TABLE tp1 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)'); +CREATE TABLE tp2 PARTITION OF temporal_partitioned_rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)'); INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), @@ -1880,8 +2618,8 @@ CREATE TABLE temporal_partitioned_fk_rng2rng ( CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) ) PARTITION BY LIST (id); -CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)'); -CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)'); +CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)'); +CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)'); -- -- partitioned FK referencing inserts @@ -1940,36 +2678,90 @@ DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange -- partitioned FK referenced updates CASCADE -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE CASCADE ON UPDATE CASCADE; +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)'; +UPDATE temporal_partitioned_rng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[4,5)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[15,16)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[10,11)', daterange('2018-01-01', '2021-01-01'), '[15,16)'); +UPDATE temporal_partitioned_rng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[10,11)'; -- -- partitioned FK referenced deletes CASCADE -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', daterange('2018-01-01', '2021-01-01'), '[8,9)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)'; +DELETE FROM temporal_partitioned_rng WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[17,18)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01'), '[17,18)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[11,12)'; + -- -- partitioned FK referenced updates SET NULL -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET NULL ON UPDATE SET NULL; +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)'; +UPDATE temporal_partitioned_rng SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[6,7)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[18,19)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01'), '[18,19)'); +UPDATE temporal_partitioned_rng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[12,13)'; -- -- partitioned FK referenced deletes SET NULL -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[11,12)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[7,8)', daterange('2018-01-01', '2021-01-01'), '[11,12)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)'; +DELETE FROM temporal_partitioned_rng WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[7,8)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[20,21)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[13,14)', daterange('2018-01-01', '2021-01-01'), '[20,21)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[13,14)'; + -- -- partitioned FK referenced updates SET DEFAULT -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null)); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); ALTER TABLE temporal_partitioned_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, @@ -1977,11 +2769,34 @@ ALTER TABLE temporal_partitioned_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +UPDATE temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)'; +UPDATE temporal_partitioned_rng SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[8,9)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[22,23)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01'), '[22,23)'); +UPDATE temporal_partitioned_rng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[14,15)'; -- -- partitioned FK referenced deletes SET DEFAULT -- +TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[0,1)', daterange(null, null)); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[14,15)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01'), '[14,15)'); +DELETE FROM temporal_partitioned_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)'; +DELETE FROM temporal_partitioned_rng WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[9,10)'; +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[24,25)', daterange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[15,16)', daterange('2018-01-01', '2021-01-01'), '[24,25)'); +DELETE FROM temporal_partitioned_rng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_rng2rng WHERE id = '[15,16)'; + DROP TABLE temporal_partitioned_fk_rng2rng; DROP TABLE temporal_partitioned_rng; @@ -2070,36 +2885,90 @@ DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemu -- partitioned FK referenced updates CASCADE -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[4,5)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[6,7)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE CASCADE ON UPDATE CASCADE; +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[4,5)'; +UPDATE temporal_partitioned_mltrng SET id = '[7,8)' WHERE id = '[6,7)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[4,5)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[15,16)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[15,16)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[10,11)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[15,16)'); +UPDATE temporal_partitioned_mltrng SET id = '[16,17)' WHERE id = '[15,16)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[10,11)'; -- -- partitioned FK referenced deletes CASCADE -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[8,9)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[5,6)'; +DELETE FROM temporal_partitioned_mltrng WHERE id = '[8,9)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[5,6)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[17,18)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[17,18)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[11,12)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[17,18)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[17,18)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[11,12)'; + -- -- partitioned FK referenced updates SET NULL -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[9,10)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[9,10)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET NULL ON UPDATE SET NULL; +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[6,7)'; +UPDATE temporal_partitioned_mltrng SET id = '[10,11)' WHERE id = '[9,10)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[6,7)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[18,19)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[18,19)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[12,13)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[18,19)'); +UPDATE temporal_partitioned_mltrng SET id = '[19,20)' WHERE id = '[18,19)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[12,13)'; -- -- partitioned FK referenced deletes SET NULL -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[11,12)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[7,8)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[11,12)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[7,8)'; +DELETE FROM temporal_partitioned_mltrng WHERE id = '[11,12)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[7,8)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[20,21)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[20,21)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[13,14)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[20,21)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[20,21)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[13,14)'; + -- -- partitioned FK referenced updates SET DEFAULT -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[0,1)', datemultirange(daterange(null, null))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[12,13)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[8,9)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[12,13)'); ALTER TABLE temporal_partitioned_fk_mltrng2mltrng ALTER COLUMN parent_id SET DEFAULT '[0,1)', DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, @@ -2107,11 +2976,34 @@ ALTER TABLE temporal_partitioned_fk_mltrng2mltrng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +UPDATE temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[8,9)'; +UPDATE temporal_partitioned_mltrng SET id = '[13,14)' WHERE id = '[12,13)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[8,9)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[22,23)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[22,23)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[14,15)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[22,23)'); +UPDATE temporal_partitioned_mltrng SET id = '[23,24)' WHERE id = '[22,23)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[14,15)'; -- -- partitioned FK referenced deletes SET DEFAULT -- +TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[0,1)', datemultirange(daterange(null, null))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[14,15)', datemultirange(daterange('2018-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[9,10)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[14,15)'); +DELETE FROM temporal_partitioned_mltrng FOR PORTION OF valid_at (datemultirange(daterange('2019-01-01', '2020-01-01'))) WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[9,10)'; +DELETE FROM temporal_partitioned_mltrng WHERE id = '[14,15)'; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[9,10)'; +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[24,25)', datemultirange(daterange('2018-01-01', '2020-01-01'))); +INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[24,25)', datemultirange(daterange('2020-01-01', '2021-01-01'))); +INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[15,16)', datemultirange(daterange('2018-01-01', '2021-01-01')), '[24,25)'); +DELETE FROM temporal_partitioned_mltrng WHERE id = '[24,25)' AND valid_at @> '2019-01-01'::date; +SELECT * FROM temporal_partitioned_fk_mltrng2mltrng WHERE id = '[15,16)'; + DROP TABLE temporal_partitioned_fk_mltrng2mltrng; DROP TABLE temporal_partitioned_mltrng; -- 2.39.5