From df302feef6f5ffd05e96600f931a77831526b859 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Sat, 3 Jun 2023 21:41:11 -0400 Subject: [PATCH v12 4/5] Add CASCADE/SET NULL/SET DEFAULT for temporal foreign keys --- src/backend/commands/tablecmds.c | 30 +- src/backend/utils/adt/ri_triggers.c | 612 +++++++++++++++++- src/include/catalog/pg_proc.dat | 20 + .../regress/expected/without_overlaps.out | 181 +++++- src/test/regress/sql/without_overlaps.sql | 66 ++ 5 files changed, 895 insertions(+), 14 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f819fe0fcb..409e470fbd 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -11990,11 +11990,20 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->funcname = SystemFuncName("TRI_FKey_restrict_del"); break; case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_cascade_del"); + break; case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_setnull_del"); + break; case FKCONSTR_ACTION_SETDEFAULT: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("action not supported for temporal foreign keys"))); + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_setdefault_del"); + break; default: elog(ERROR, "unrecognized FK action type: %d", (int) fkconstraint->fk_del_action); @@ -12080,11 +12089,20 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr fk_trigger->funcname = SystemFuncName("TRI_FKey_restrict_upd"); break; case FKCONSTR_ACTION_CASCADE: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_cascade_upd"); + break; case FKCONSTR_ACTION_SETNULL: + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_setnull_upd"); + break; case FKCONSTR_ACTION_SETDEFAULT: - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("action not supported for temporal foreign keys"))); + fk_trigger->deferrable = false; + fk_trigger->initdeferred = false; + fk_trigger->funcname = SystemFuncName("TRI_FKey_setdefault_upd"); + break; default: elog(ERROR, "unrecognized FK action type: %d", (int) fkconstraint->fk_upd_action); diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 08c6e4c1c4..85441af42b 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -85,9 +85,9 @@ #define TRI_PLAN_CASCADE_ONDELETE 10 #define TRI_PLAN_CASCADE_ONUPDATE 11 #define TRI_PLAN_SETNULL_ONUPDATE 12 -// TODO: Need TRI SETNULL_ONDELETE -#define TRI_PLAN_SETDEFAULT_ONUPDATE 13 -// TODO: Need TRI SETDEFAULT_ONDELETE +#define TRI_PLAN_SETNULL_ONDELETE 13 +#define TRI_PLAN_SETDEFAULT_ONUPDATE 14 +#define TRI_PLAN_SETDEFAULT_ONDELETE 15 #define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3) #define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2) @@ -195,6 +195,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, @@ -231,6 +232,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + bool hasForPortionOf, Datum forPortionOf, bool detectNewRows, int expect_OK); static void ri_ExtractValues(Relation rel, TupleTableSlot *slot, const RI_ConstraintInfo *riinfo, bool rel_is_pk, @@ -239,6 +241,11 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool partgone) pg_attribute_noreturn(); +static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo); +static Datum restrict_cascading_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo, + TupleTableSlot *oldslot); /* @@ -447,6 +454,7 @@ RI_FKey_check(TriggerData *trigdata) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, NULL, newslot, + false, 0, pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE, SPI_OK_SELECT); @@ -576,6 +584,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, result = ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + false, 0, true, /* treat like update */ SPI_OK_SELECT); @@ -774,6 +783,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + false, 0, true, /* must detect new rows */ SPI_OK_SELECT); @@ -880,6 +890,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + false, 0, true, /* must detect new rows */ SPI_OK_DELETE); @@ -1001,6 +1012,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, newslot, + false, 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1233,6 +1245,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + false, 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1346,6 +1359,127 @@ TRI_FKey_restrict_del(PG_FUNCTION_ARGS) return ri_restrict((TriggerData *) fcinfo->context, false); } +/* + * TRI_FKey_cascade_del - + * + * Cascaded delete foreign key references at delete event on temporal PK table. + */ +Datum +TRI_FKey_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, "TRI_FKey_cascade_del", RI_TRIGTYPE_DELETE); + + riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, + trigdata->tg_relation, true); + + /* + * Get the relation descriptors of the FK and PK tables and the old tuple. + * + * fk_rel is opened in RowExclusiveLock mode since that's what our + * eventual DELETE will get on it. + */ + fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock); + pk_rel = trigdata->tg_relation; + oldslot = trigdata->tg_trigslot; + + /* + * Don't delete than more than the PK's duration, + * trimmed by an original FOR PORTION OF if necessary. + */ + targetRange = restrict_cascading_range(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, TRI_PLAN_CASCADE_ONDELETE); + + if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) + { + StringInfoData querybuf; + char fkrelname[MAX_QUOTED_REL_NAME_LEN]; + char attname[MAX_QUOTED_NAME_LEN]; + char paramname[16]; + const char *querysep; + Oid queryoids[RI_MAX_NUMKEYS + 1]; + const char *fk_only; + + /* ---------- + * The query string built is + * DELETE FROM [ONLY] + * FOR PORTION OF $fkatt FROM lower(${n+1}) TO upper(${n+1}) + * WHERE $1 = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. + * ---------- + */ + initStringInfo(&querybuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "DELETE FROM %s%s FOR PORTION OF %s FROM lower($%d) TO upper($%d)", + fk_only, fkrelname, attname, riinfo->nkeys + 1, riinfo->nkeys + 1); + querysep = "WHERE"; + for (int i = 0; i < riinfo->nkeys; i++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + sprintf(paramname, "$%d", i + 1); + ri_GenerateQual(&querybuf, querysep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + querysep = "AND"; + queryoids[i] = pk_type; + } + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Build up the arguments from the key values in the + * deleted PK tuple and delete the referencing rows + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, NULL, + true, targetRange, + true, /* must detect new rows */ + SPI_OK_DELETE); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + table_close(fk_rel, RowExclusiveLock); + + return PointerGetDatum(NULL); +} + + /* * TRI_FKey_noaction_upd - * @@ -1383,6 +1517,415 @@ TRI_FKey_restrict_upd(PG_FUNCTION_ARGS) return ri_restrict((TriggerData *) fcinfo->context, false); } +/* + * TRI_FKey_cascade_upd - + * + * Cascaded update foreign key references at update event on temporal PK table. + */ +Datum +TRI_FKey_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, "TRI_FKey_cascade_upd", RI_TRIGTYPE_UPDATE); + + riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, + trigdata->tg_relation, true); + + /* + * Get the relation descriptors of the FK and PK tables and the new and + * old tuple. + * + * fk_rel is opened in RowExclusiveLock mode since that's what our + * eventual UPDATE will get on it. + */ + fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock); + pk_rel = trigdata->tg_relation; + newslot = trigdata->tg_newslot; + oldslot = trigdata->tg_trigslot; + + /* + * Don't delete than more than the PK's duration, + * trimmed by an original FOR PORTION OF if necessary. + */ + targetRange = restrict_cascading_range(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, TRI_PLAN_CASCADE_ONUPDATE); + + if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) + { + StringInfoData querybuf; + StringInfoData qualbuf; + char fkrelname[MAX_QUOTED_REL_NAME_LEN]; + char attname[MAX_QUOTED_NAME_LEN]; + char paramname[16]; + const char *querysep; + const char *qualsep; + Oid queryoids[2 * RI_MAX_NUMKEYS + 1]; + const char *fk_only; + + /* ---------- + * The query string built is + * UPDATE [ONLY] + * FOR PORTION OF $fkatt FROM lower(${2n+1}) TO upper(${2n+1}) + * SET fkatt1 = $1, [, ...] + * WHERE $n = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. Note that we are assuming + * there is an assignment cast from the PK to the FK type; + * else the parser will fail. + * ---------- + */ + initStringInfo(&querybuf); + initStringInfo(&qualbuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s FROM lower($%d) TO upper($%d) SET", + fk_only, fkrelname, attname, 2 * riinfo->nkeys + 1, 2 * riinfo->nkeys + 1); + + querysep = ""; + qualsep = "WHERE"; + for (int i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + /* + * Don't set the temporal column(s). + * FOR PORTION OF will take care of that. + */ + if (i < riinfo->nkeys - 1) + appendStringInfo(&querybuf, + "%s %s = $%d", + querysep, attname, i + 1); + + sprintf(paramname, "$%d", j + 1); + ri_GenerateQual(&qualbuf, qualsep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + querysep = ","; + qualsep = "AND"; + queryoids[i] = pk_type; + queryoids[j] = pk_type; + } + appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len); + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[2 * riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, 2 * riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Run it to update the existing references. + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, newslot, + true, targetRange, + true, /* must detect new rows */ + SPI_OK_UPDATE); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + table_close(fk_rel, RowExclusiveLock); + + return PointerGetDatum(NULL); +} + +/* + * TRI_FKey_setnull_del - + * + * Set foreign key references to NULL values at delete event on PK table. + */ +Datum +TRI_FKey_setnull_del(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_setnull_del", RI_TRIGTYPE_DELETE); + + /* Share code with UPDATE case */ + return tri_set((TriggerData *) fcinfo->context, true, RI_TRIGTYPE_DELETE); +} + +/* + * TRI_FKey_setnull_upd - + * + * Set foreign key references to NULL at update event on PK table. + */ +Datum +TRI_FKey_setnull_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_setnull_upd", RI_TRIGTYPE_UPDATE); + + /* Share code with DELETE case */ + return tri_set((TriggerData *) fcinfo->context, true, RI_TRIGTYPE_UPDATE); +} + +/* + * TRI_FKey_setdefault_del - + * + * Set foreign key references to defaults at delete event on PK table. + */ +Datum +TRI_FKey_setdefault_del(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_setdefault_del", RI_TRIGTYPE_DELETE); + + /* Share code with UPDATE case */ + return tri_set((TriggerData *) fcinfo->context, false, RI_TRIGTYPE_DELETE); +} + +/* + * TRI_FKey_setdefault_upd - + * + * Set foreign key references to defaults at update event on PK table. + */ +Datum +TRI_FKey_setdefault_upd(PG_FUNCTION_ARGS) +{ + /* Check that this is a valid trigger call on the right time and event. */ + ri_CheckTrigger(fcinfo, "TRI_FKey_setdefault_upd", RI_TRIGTYPE_UPDATE); + + /* Share code with DELETE case */ + return tri_set((TriggerData *) fcinfo->context, false, RI_TRIGTYPE_UPDATE); +} + +/* + * tri_set - + * + * Common code for temporal ON DELETE SET NULL, ON DELETE SET DEFAULT, ON + * UPDATE SET NULL, and ON UPDATE SET DEFAULT. + */ +static Datum +tri_set(TriggerData *trigdata, bool is_set_null, int tgkind) +{ + const RI_ConstraintInfo *riinfo; + Relation fk_rel; + Relation pk_rel; + TupleTableSlot *oldslot; + RI_QueryKey qkey; + SPIPlanPtr qplan; + Datum targetRange; + int32 queryno; + + riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, + trigdata->tg_relation, true); + + /* + * Get the relation descriptors of the FK and PK tables and the old tuple. + * + * fk_rel is opened in RowExclusiveLock mode since that's what our + * eventual UPDATE will get on it. + */ + fk_rel = table_open(riinfo->fk_relid, RowExclusiveLock); + pk_rel = trigdata->tg_relation; + oldslot = trigdata->tg_trigslot; + + /* + * Don't SET NULL/DEFAULT than more than the PK's duration, + * trimmed by an original FOR PORTION OF if necessary. + */ + targetRange = restrict_cascading_range(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 + ? TRI_PLAN_SETNULL_ONUPDATE + : TRI_PLAN_SETDEFAULT_ONUPDATE; + break; + case RI_TRIGTYPE_DELETE: + queryno = is_set_null + ? TRI_PLAN_SETNULL_ONDELETE + : TRI_PLAN_SETDEFAULT_ONDELETE; + break; + default: + elog(ERROR, "invalid tgkind passed to ri_set"); + } + + ri_BuildQueryKey(&qkey, riinfo, queryno); + + if ((qplan = ri_FetchPreparedPlan(&qkey)) == NULL) + { + StringInfoData querybuf; + StringInfoData qualbuf; + char fkrelname[MAX_QUOTED_REL_NAME_LEN]; + char attname[MAX_QUOTED_NAME_LEN]; + char paramname[16]; + const char *querysep; + const char *qualsep; + Oid queryoids[RI_MAX_NUMKEYS + 1]; /* +1 for FOR PORTION OF */ + const char *fk_only; + int num_cols_to_set; + const int16 *set_cols; + + switch (tgkind) + { + case RI_TRIGTYPE_UPDATE: + /* -1 so we let FOR PORTION OF set the range. */ + num_cols_to_set = riinfo->nkeys - 1; + set_cols = riinfo->fk_attnums; + break; + case RI_TRIGTYPE_DELETE: + /* + * If confdelsetcols are present, then we only update the + * columns specified in that array, otherwise we update all + * the referencing columns. + */ + if (riinfo->ndelsetcols != 0) + { + num_cols_to_set = riinfo->ndelsetcols; + set_cols = riinfo->confdelsetcols; + } + else + { + /* -1 so we let FOR PORTION OF set the range. */ + num_cols_to_set = riinfo->nkeys - 1; + set_cols = riinfo->fk_attnums; + } + break; + default: + elog(ERROR, "invalid tgkind passed to ri_set"); + } + + /* ---------- + * The query string built is + * UPDATE [ONLY] + * FOR PORTION OF $fkatt FROM lower(${n+1}) TO upper(${n+1}) + * SET fkatt1 = {NULL|DEFAULT} [, ...] + * WHERE $1 = fkatt1 [AND ...] + * The type id's for the $ parameters are those of the + * corresponding PK attributes. + * ---------- + */ + initStringInfo(&querybuf); + initStringInfo(&qualbuf); + fk_only = fk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ? + "" : "ONLY "; + quoteRelationName(fkrelname, fk_rel); + quoteOneName(attname, RIAttName(fk_rel, riinfo->fk_attnums[riinfo->nkeys - 1])); + + appendStringInfo(&querybuf, "UPDATE %s%s FOR PORTION OF %s FROM lower($%d) TO upper($%d) SET", + fk_only, fkrelname, attname, riinfo->nkeys + 1, riinfo->nkeys + 1); + + /* + * Add assignment clauses + */ + querysep = ""; + for (int i = 0; i < num_cols_to_set; i++) + { + quoteOneName(attname, RIAttName(fk_rel, set_cols[i])); + appendStringInfo(&querybuf, + "%s %s = %s", + querysep, attname, + is_set_null ? "NULL" : "DEFAULT"); + querysep = ","; + } + + /* + * Add WHERE clause + */ + qualsep = "WHERE"; + for (int i = 0; i < riinfo->nkeys; i++) + { + Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]); + Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]); + Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]); + Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]); + + quoteOneName(attname, + RIAttName(fk_rel, riinfo->fk_attnums[i])); + + sprintf(paramname, "$%d", i + 1); + ri_GenerateQual(&querybuf, qualsep, + paramname, pk_type, + riinfo->pf_eq_oprs[i], + attname, fk_type); + if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll)) + ri_GenerateQualCollation(&querybuf, pk_coll); + qualsep = "AND"; + queryoids[i] = pk_type; + } + + /* Set a param for FOR PORTION OF TO/FROM */ + queryoids[riinfo->nkeys] = RIAttType(pk_rel, riinfo->pk_attnums[riinfo->nkeys - 1]); + + /* Prepare and save the plan */ + qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys + 1, queryoids, + &qkey, fk_rel, pk_rel); + } + + /* + * We have a plan now. Run it to update the existing references. + */ + ri_PerformCheck(riinfo, &qkey, qplan, + fk_rel, pk_rel, + oldslot, NULL, + true, targetRange, + true, /* must detect new rows */ + SPI_OK_UPDATE); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + table_close(fk_rel, RowExclusiveLock); + + if (is_set_null) + return PointerGetDatum(NULL); + else + { + /* + * If we just deleted or updated the PK row whose key was equal to the + * FK columns' default values, and a referencing row exists in the FK + * table, we would have updated that row to the same values it already + * had --- and RI_FKey_fk_upd_check_required would hence believe no + * check is necessary. So we need to do another lookup now and in + * case a reference still exists, abort the operation. That is + * already implemented in the NO ACTION trigger, so just run it. (This + * recheck is only needed in the SET DEFAULT case, since CASCADE would + * remove such rows in case of a DELETE operation or would change the + * FK key values in case of an UPDATE, while SET NULL is certain to + * result in rows that satisfy the FK constraint.) + */ + return ri_restrict(trigdata, true); + } +} + /* * RI_FKey_pk_upd_check_required - * @@ -2495,6 +3038,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + bool hasForPortionOf, Datum forPortionOf, bool detectNewRows, int expect_OK) { Relation query_rel, @@ -2506,8 +3050,9 @@ 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]; + int last_param; /* * Use the query type code to determine whether the query is run against @@ -2537,19 +3082,29 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, } /* Extract the parameters to be passed into the query */ + last_param = riinfo->nkeys; if (newslot) { ri_ExtractValues(source_rel, newslot, riinfo, source_is_pk, vals, nulls); if (oldslot) + { ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk, vals + riinfo->nkeys, nulls + riinfo->nkeys); + last_param = 2 * riinfo->nkeys; + } } else { ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk, vals, nulls); } + /* Add one more query param if we are using FOR PORTION OF */ + if (hasForPortionOf) + { + vals[last_param] = forPortionOf; + nulls[last_param] = ' '; + } /* * In READ COMMITTED mode, we just need to use an up-to-date regular @@ -3206,3 +3761,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_cascading_range - + * + * Returns a Datum of RangeTypeP holding the appropriate timespan + * to target child records when we CASCADE/SET NULL/SET DEFAULT. + * + * In a normal UPDATE/DELETE this should be the parent's own valid time, + * but if there was a FOR PORTION OF clause, then we should use that to + * trim down the parent's span further. + */ +static Datum +restrict_cascading_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot) +{ + Datum pkRecordRange; + bool isnull; + + pkRecordRange = slot_getattr(oldslot, riinfo->pk_attnums[riinfo->nkeys - 1], &isnull); + if (isnull) + elog(ERROR, "application time should not be null"); + + if (fpo_targets_pk_range(tg_temporal, riinfo)) + { + RangeType *r1 = DatumGetRangeTypeP(pkRecordRange); + RangeType *r2 = DatumGetRangeTypeP(tg_temporal->fp_targetRange); + Oid rngtypid = RangeTypeGetOid(r1); + TypeCacheEntry *typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO); + return RangeTypePGetDatum(range_intersect_internal(typcache, r1, r2)); + } + else + return pkRecordRange; +} diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index eac76d2af8..2dcadb2236 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3937,6 +3937,12 @@ { oid => '6123', descr => 'temporal referential integrity FOREIGN KEY ... REFERENCES', proname => 'TRI_FKey_check_upd', provolatile => 'v', prorettype => 'trigger', proargtypes => '', prosrc => 'TRI_FKey_check_upd' }, +{ oid => '6124', descr => 'temporal referential integrity ON DELETE CASCADE', + proname => 'TRI_FKey_cascade_del', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_cascade_del' }, +{ oid => '6125', descr => 'temporal referential integrity ON UPDATE CASCADE', + proname => 'TRI_FKey_cascade_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_cascade_upd' }, { oid => '6126', descr => 'temporal referential integrity ON DELETE RESTRICT', proname => 'TRI_FKey_restrict_del', provolatile => 'v', prorettype => 'trigger', proargtypes => '', @@ -3945,6 +3951,20 @@ proname => 'TRI_FKey_restrict_upd', provolatile => 'v', prorettype => 'trigger', proargtypes => '', prosrc => 'TRI_FKey_restrict_upd' }, +{ oid => '6128', descr => 'temporal referential integrity ON DELETE SET NULL', + proname => 'TRI_FKey_setnull_del', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_setnull_del' }, +{ oid => '6129', descr => 'temporal referential integrity ON UPDATE SET NULL', + proname => 'TRI_FKey_setnull_upd', provolatile => 'v', prorettype => 'trigger', + proargtypes => '', prosrc => 'TRI_FKey_setnull_upd' }, +{ oid => '6130', descr => 'temporal referential integrity ON DELETE SET DEFAULT', + proname => 'TRI_FKey_setdefault_del', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_setdefault_del' }, +{ oid => '6131', descr => 'temporal referential integrity ON UPDATE SET DEFAULT', + proname => 'TRI_FKey_setdefault_upd', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'TRI_FKey_setdefault_upd' }, { oid => '6132', descr => 'temporal referential integrity ON DELETE NO ACTION', proname => 'TRI_FKey_noaction_del', provolatile => 'v', prorettype => 'trigger', proargtypes => '', diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 143f19981f..8926888a37 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -593,7 +593,62 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE CASCADE ON UPDATE CASCADE; -ERROR: action not supported for temporal foreign keys +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[4,4]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [4,5) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) + [4,5) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [6,7) + [4,5) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [6,7) +(3 rows) + +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[4,4]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [4,5) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [7,8) + [4,5) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [7,8) + [4,5) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [7,8) +(3 rows) + +INSERT INTO temporal_rng VALUES ('[15,15]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[15,15]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[10,10]', tsrange('2018-01-01', '2021-01-01'), '[15,15]'); +UPDATE temporal_rng SET id = '[16,16]' WHERE id = '[15,15]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[10,10]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [10,11) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [16,17) + [10,11) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [15,16) +(2 rows) + +-- test FK parent deletes CASCADE +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[5,5]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[5,5]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [8,9) + [5,6) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [8,9) +(2 rows) + +DELETE FROM temporal_rng WHERE id = '[8,8]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[5,5]'; + id | valid_at | parent_id +----+----------+----------- +(0 rows) + +INSERT INTO temporal_rng VALUES ('[17,17]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[17,17]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[11,11]', tsrange('2018-01-01', '2021-01-01'), '[17,17]'); +DELETE FROM temporal_rng WHERE id = '[17,17]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[11,11]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [11,12) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [17,18) +(1 row) + -- test FK parent updates SET NULL INSERT INTO temporal_rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01')); INSERT INTO temporal_fk_rng2rng VALUES ('[6,6]', tsrange('2018-01-01', '2021-01-01'), '[9,9]'); @@ -603,7 +658,67 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET NULL ON UPDATE SET NULL; -ERROR: action not supported for temporal foreign keys +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[6,6]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [6,7) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [6,7) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [9,10) + [6,7) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [9,10) +(3 rows) + +UPDATE temporal_rng SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[6,6]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [6,7) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [6,7) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | + [6,7) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | +(3 rows) + +INSERT INTO temporal_rng VALUES ('[18,18]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[18,18]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'), '[18,18]'); +UPDATE temporal_rng SET id = '[19,19]' WHERE id = '[18,18]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[12,12]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [12,13) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | + [12,13) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [18,19) +(2 rows) + +-- test FK parent deletes SET NULL +INSERT INTO temporal_rng VALUES ('[11,11]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[7,7]', tsrange('2018-01-01', '2021-01-01'), '[11,11]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,11]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[7,7]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [7,8) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [7,8) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [11,12) + [7,8) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [11,12) +(3 rows) + +DELETE FROM temporal_rng WHERE id = '[11,11]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[7,7]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [7,8) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | + [7,8) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | + [7,8) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | +(3 rows) + +INSERT INTO temporal_rng VALUES ('[20,20]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[20,20]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[13,13]', tsrange('2018-01-01', '2021-01-01'), '[20,20]'); +DELETE FROM temporal_rng WHERE id = '[20,20]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[13,13]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [13,14) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | + [13,14) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [20,21) +(2 rows) + -- test FK parent updates SET DEFAULT INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null)); INSERT INTO temporal_rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01')); @@ -615,4 +730,64 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; -ERROR: action not supported for temporal foreign keys +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[8,8]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [8,9) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [8,9) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [12,13) + [8,9) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [12,13) +(3 rows) + +UPDATE temporal_rng SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[8,8]'; + id | valid_at | parent_id +-------+---------------------------------------------------------+----------- + [8,9) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [8,9) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) + [8,9) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) +(3 rows) + +INSERT INTO temporal_rng VALUES ('[22,22]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[22,22]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[14,14]', tsrange('2018-01-01', '2021-01-01'), '[22,22]'); +UPDATE temporal_rng SET id = '[23,23]' WHERE id = '[22,22]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[14,14]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [14,15) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) + [14,15) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [22,23) +(2 rows) + +-- test FK parent deletes SET DEFAULT +INSERT INTO temporal_rng VALUES ('[14,14]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'), '[14,14]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,14]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[9,9]'; + id | valid_at | parent_id +--------+---------------------------------------------------------+----------- + [9,10) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [9,10) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [14,15) + [9,10) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [14,15) +(3 rows) + +DELETE FROM temporal_rng WHERE id = '[14,14]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[9,9]'; + id | valid_at | parent_id +--------+---------------------------------------------------------+----------- + [9,10) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | [-1,0) + [9,10) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | [-1,0) + [9,10) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [-1,0) +(3 rows) + +INSERT INTO temporal_rng VALUES ('[24,24]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[24,24]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[15,15]', tsrange('2018-01-01', '2021-01-01'), '[24,24]'); +DELETE FROM temporal_rng WHERE id = '[24,24]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[15,15]'; + id | valid_at | parent_id +---------+---------------------------------------------------------+----------- + [15,16) | ["Mon Jan 01 00:00:00 2018","Wed Jan 01 00:00:00 2020") | [-1,0) + [15,16) | ["Wed Jan 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | [24,25) +(2 rows) + diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 0dd9370a50..a1d69a9d99 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -510,6 +510,28 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE CASCADE ON UPDATE CASCADE; +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[4,4]'; +UPDATE temporal_rng SET id = '[7,7]' WHERE id = '[6,6]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[4,4]'; +INSERT INTO temporal_rng VALUES ('[15,15]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[15,15]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[10,10]', tsrange('2018-01-01', '2021-01-01'), '[15,15]'); +UPDATE temporal_rng SET id = '[16,16]' WHERE id = '[15,15]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[10,10]'; + +-- test FK parent deletes CASCADE +INSERT INTO temporal_rng VALUES ('[8,8]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[5,5]', tsrange('2018-01-01', '2021-01-01'), '[8,8]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[8,8]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[5,5]'; +DELETE FROM temporal_rng WHERE id = '[8,8]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[5,5]'; +INSERT INTO temporal_rng VALUES ('[17,17]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[17,17]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[11,11]', tsrange('2018-01-01', '2021-01-01'), '[17,17]'); +DELETE FROM temporal_rng WHERE id = '[17,17]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[11,11]'; -- test FK parent updates SET NULL @@ -521,6 +543,28 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET NULL ON UPDATE SET NULL; +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[6,6]'; +UPDATE temporal_rng SET id = '[10,10]' WHERE id = '[9,9]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[6,6]'; +INSERT INTO temporal_rng VALUES ('[18,18]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[18,18]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[12,12]', tsrange('2018-01-01', '2021-01-01'), '[18,18]'); +UPDATE temporal_rng SET id = '[19,19]' WHERE id = '[18,18]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[12,12]'; + +-- test FK parent deletes SET NULL +INSERT INTO temporal_rng VALUES ('[11,11]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[7,7]', tsrange('2018-01-01', '2021-01-01'), '[11,11]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[11,11]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[7,7]'; +DELETE FROM temporal_rng WHERE id = '[11,11]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[7,7]'; +INSERT INTO temporal_rng VALUES ('[20,20]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[20,20]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[13,13]', tsrange('2018-01-01', '2021-01-01'), '[20,20]'); +DELETE FROM temporal_rng WHERE id = '[20,20]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[13,13]'; -- test FK parent updates SET DEFAULT INSERT INTO temporal_rng VALUES ('[-1,-1]', tsrange(null, null)); @@ -533,3 +577,25 @@ ALTER TABLE temporal_fk_rng2rng FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; +UPDATE temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[8,8]'; +UPDATE temporal_rng SET id = '[13,13]' WHERE id = '[12,12]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[8,8]'; +INSERT INTO temporal_rng VALUES ('[22,22]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[22,22]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[14,14]', tsrange('2018-01-01', '2021-01-01'), '[22,22]'); +UPDATE temporal_rng SET id = '[23,23]' WHERE id = '[22,22]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[14,14]'; + +-- test FK parent deletes SET DEFAULT +INSERT INTO temporal_rng VALUES ('[14,14]', tsrange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[9,9]', tsrange('2018-01-01', '2021-01-01'), '[14,14]'); +DELETE FROM temporal_rng FOR PORTION OF valid_at FROM '2019-01-01' TO '2020-01-01' WHERE id = '[14,14]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[9,9]'; +DELETE FROM temporal_rng WHERE id = '[14,14]'; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[9,9]'; +INSERT INTO temporal_rng VALUES ('[24,24]', tsrange('2018-01-01', '2020-01-01')); +INSERT INTO temporal_rng VALUES ('[24,24]', tsrange('2020-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng VALUES ('[15,15]', tsrange('2018-01-01', '2021-01-01'), '[24,24]'); +DELETE FROM temporal_rng WHERE id = '[24,24]' AND valid_at @> '2019-01-01'::timestamp; +SELECT * FROM temporal_fk_rng2rng WHERE id = '[15,15]'; -- 2.32.0 (Apple Git-132)