From 65131972fe5b3cd02c2b740a6c14509a551c3b15 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 25 Jun 2021 18:54:35 -0700 Subject: [PATCH v9 3/4] Add UPDATE/DELETE FOR PORTION OF - Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. But we permit the magic MINVALUE and MAXVALUE keywords (to match the terms for partitions) to perform an unbounded update/delete. We also accept functions like NOW(). - Added AFTER ROW triggers to insert new rows for the "leftover" part of a record touched by a FOR PORTION OF query. We install these triggers on any table with a temporal primary key. - Added tg_temporal descriptor to the TriggerData struct that we pass to trigger functions. Our triggers use this to learn what bounds were given in the FOR PORTION OF clause. - Documented FOR PORTION OF. - Documented tg_temporal struct. - Added tests. --- doc/src/sgml/ddl.sgml | 4 + doc/src/sgml/ref/delete.sgml | 46 ++ doc/src/sgml/ref/update.sgml | 47 ++ doc/src/sgml/trigger.sgml | 60 +- src/backend/access/brin/brin_minmax_multi.c | 28 +- src/backend/commands/indexcmds.c | 6 + src/backend/commands/tablecmds.c | 65 ++ src/backend/commands/trigger.c | 55 ++ src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 33 + src/backend/nodes/copyfuncs.c | 47 ++ src/backend/nodes/equalfuncs.c | 35 + src/backend/nodes/nodeFuncs.c | 10 + src/backend/nodes/outfuncs.c | 23 + src/backend/nodes/readfuncs.c | 25 + src/backend/optimizer/plan/createplan.c | 8 +- src/backend/optimizer/plan/planner.c | 1 + src/backend/optimizer/util/pathnode.c | 3 +- src/backend/parser/analyze.c | 384 ++++++++++- src/backend/parser/gram.y | 47 +- src/backend/parser/parse_agg.c | 10 + src/backend/parser/parse_collate.c | 1 + src/backend/parser/parse_expr.c | 8 + src/backend/parser/parse_func.c | 3 + src/backend/rewrite/rewriteHandler.c | 24 + src/backend/tcop/utility.c | 2 +- src/backend/utils/adt/Makefile | 1 + src/backend/utils/adt/fp_triggers.c | 574 ++++++++++++++++ src/backend/utils/adt/rangetypes.c | 42 ++ src/include/catalog/pg_proc.dat | 6 + src/include/commands/tablecmds.h | 2 + src/include/commands/trigger.h | 1 + src/include/nodes/execnodes.h | 22 + src/include/nodes/nodes.h | 3 + src/include/nodes/parsenodes.h | 43 +- src/include/nodes/pathnodes.h | 1 + src/include/nodes/plannodes.h | 2 + src/include/nodes/primnodes.h | 27 + src/include/optimizer/pathnode.h | 2 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/utils/rangetypes.h | 3 + src/test/regress/expected/for_portion_of.out | 674 +++++++++++++++++++ src/test/regress/expected/sanity_check.out | 1 + src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/for_portion_of.sql | 562 ++++++++++++++++ 46 files changed, 2888 insertions(+), 58 deletions(-) create mode 100644 src/backend/utils/adt/fp_triggers.c create mode 100644 src/test/regress/expected/for_portion_of.out create mode 100644 src/test/regress/sql/for_portion_of.sql diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8bc60eb843..57e0dc1363 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1188,6 +1188,10 @@ CREATE TABLE billing_addresses ( and second a period (or range column) that qualifies when the row applies. So a temporal primary permits multiple rows with equal values in the ordinary key parts, as long as those rows don't have overlapping periods. Each row makes a statement about the entity identified by the ordinary key parts, but applying only to the span given by the period. Temporal primary keys are essentially exclusion constraints where the first key parts are compared for equality and the last part for overlaps. + + + Any table with a temporal primary key supports temporal UPDATE and DELETE using FOR PORTION OF syntax. See and for details. + diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 1b81b4e7d7..2f052f80ae 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] + [ FOR PORTION OF range_or_period_name FROM start_time TO end_time ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -54,6 +55,15 @@ DELETE FROM [ ONLY ] table_name [ * circumstances. + + If the table has a temporal + primary key, you may supply a + FOR PORTION OF clause, and your delete will only affect rows + that overlap the given interval. Furthermore, if a row's span extends outside + the FOR PORTION OF bounds, then new rows spanning the "cut + off" duration will be inserted to preserve the old values. + + The optional RETURNING clause causes DELETE to compute and return value(s) based on each row actually deleted. @@ -116,6 +126,42 @@ DELETE FROM [ ONLY ] table_name [ * + + range_or_period_name + + + The range column or period to use when performing a temporal delete. This + must match the range or period used in the table's temporal primary key. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal delete. + This must be a value matching the base type of the range or period from + range_or_period_name. It may also + be the special value MINVALUE to indicate a delete whose + beginning is unbounded. + + + + + + end_time + + + The latest time (exclusive) to change in a temporal delete. + This must be a value matching the base type of the range or period from + range_or_period_name. It may also + be the special value MAXVALUE to indicate a delete whose + end is unbounded. + + + + from_item diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 3fa54e5f70..8baa13daf9 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] + [ FOR PORTION OF range_or_period_name FROM start_time TO end_time ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -51,6 +52,16 @@ UPDATE [ ONLY ] table_name [ * ] [ circumstances. + + If the table has a temporal + primary key, you may supply a + FOR PORTION OF clause, and your update will only affect rows + that overlap the given interval. Furthermore, if a row's span extends outside + the FOR PORTION OF bounds, then it will be truncated to fit + within the bounds, and new rows spanning the "cut off" duration will be + inserted to preserve the old values. + + The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. @@ -114,6 +125,42 @@ UPDATE [ ONLY ] table_name [ * ] [ + + range_or_period_name + + + The range column or period to use when performing a temporal update. This + must match the range or period used in the table's temporal primary key. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal update. + This must be a value matching the base type of the range or period from + range_or_period_name. It may also + be the special value MINVALUE to indicate an update whose + beginning is unbounded. + + + + + + end_time + + + The latest time (exclusive) to change in a temporal update. + This must be a value matching the base type of the range or period from + range_or_period_name. It may also + be the special value MAXVALUE to indicate an update whose + end is unbounded. + + + + column_name diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 7e2654493b..072152980d 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -515,17 +515,18 @@ CALLED_AS_TRIGGER(fcinfo) typedef struct TriggerData { - NodeTag type; - TriggerEvent tg_event; - Relation tg_relation; - HeapTuple tg_trigtuple; - HeapTuple tg_newtuple; - Trigger *tg_trigger; - TupleTableSlot *tg_trigslot; - TupleTableSlot *tg_newslot; - Tuplestorestate *tg_oldtable; - Tuplestorestate *tg_newtable; - const Bitmapset *tg_updatedcols; + NodeTag type; + TriggerEvent tg_event; + Relation tg_relation; + HeapTuple tg_trigtuple; + HeapTuple tg_newtuple; + Trigger *tg_trigger; + TupleTableSlot *tg_trigslot; + TupleTableSlot *tg_newslot; + Tuplestorestate *tg_oldtable; + Tuplestorestate *tg_newtable; + const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; @@ -792,6 +793,43 @@ typedef struct Trigger + + + tg_temporal + + + Set for UPDATE and DELETE queries + that use FOR PORTION OF, otherwise NULL. + Contains a pointer to a structure of type + ForPortionOfState, defined in + nodes/execnodes.h: + + +typedef struct ForPortionOfState +{ + NodeTag type; + + char *fp_rangeName; /* the column/PERIOD named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + bool fp_hasPeriod; /* true iff this is a PERIOD not a range */ + char *fp_periodStartName; /* the PERIOD's start column */ + char *fp_periodEndName; /* the PERIOD's end column */ + Datum fp_targetRange; /* the range from FOR PORTION OF */ +} ForPortionOfState; + + + where fp_rangeName is the period or range + column named in the FOR PORTION OF clause, + fp_rangeType is its range type, + fp_hasPeriod indicates whether a period was used + or a range column, fp_periodStartName and + fp_periodEndName are the names of the period's + start and end columns (or NULL if a range column was used), + and fp_targetRange is a rangetype value created + by evaluating the FOR PORTION OF bounds. + + + diff --git a/src/backend/access/brin/brin_minmax_multi.c b/src/backend/access/brin/brin_minmax_multi.c index a85dfdfec4..b4f5a058b7 100644 --- a/src/backend/access/brin/brin_minmax_multi.c +++ b/src/backend/access/brin/brin_minmax_multi.c @@ -198,7 +198,7 @@ typedef struct Ranges * with basic metadata, followed by the boundary values. It has a varlena * header, so can be treated as varlena directly. * - * See range_serialize/range_deserialize for serialization details. + * See brin_range_serialize/brin_range_deserialize for serialization details. */ typedef struct SerializedRanges { @@ -217,9 +217,9 @@ typedef struct SerializedRanges char data[FLEXIBLE_ARRAY_MEMBER]; } SerializedRanges; -static SerializedRanges *range_serialize(Ranges *range); +static SerializedRanges *brin_range_serialize(Ranges *range); -static Ranges *range_deserialize(int maxvalues, SerializedRanges *range); +static Ranges *brin_range_deserialize(int maxvalues, SerializedRanges *range); /* @@ -566,14 +566,14 @@ range_deduplicate_values(Ranges *range) /* - * range_serialize + * brin_range_serialize * Serialize the in-memory representation into a compact varlena value. * * Simply copy the header and then also the individual values, as stored * in the in-memory value array. */ static SerializedRanges * -range_serialize(Ranges *range) +brin_range_serialize(Ranges *range) { Size len; int nvalues; @@ -712,14 +712,14 @@ range_serialize(Ranges *range) } /* - * range_deserialize + * brin_range_deserialize * Serialize the in-memory representation into a compact varlena value. * * Simply copy the header and then also the individual values, as stored * in the in-memory value array. */ static Ranges * -range_deserialize(int maxvalues, SerializedRanges *serialized) +brin_range_deserialize(int maxvalues, SerializedRanges *serialized) { int i, nvalues; @@ -2405,7 +2405,7 @@ brin_minmax_multi_serialize(BrinDesc *bdesc, Datum src, Datum *dst) /* At this point everything has to be fully sorted. */ Assert(ranges->nsorted == ranges->nvalues); - s = range_serialize(ranges); + s = brin_range_serialize(ranges); dst[0] = PointerGetDatum(s); } @@ -2528,7 +2528,7 @@ brin_minmax_multi_add_value(PG_FUNCTION_ARGS) maxvalues = Max(maxvalues, MINMAX_BUFFER_MIN); maxvalues = Min(maxvalues, MINMAX_BUFFER_MAX); - ranges = range_deserialize(maxvalues, serialized); + ranges = brin_range_deserialize(maxvalues, serialized); ranges->attno = attno; ranges->colloid = colloid; @@ -2581,7 +2581,7 @@ brin_minmax_multi_consistent(PG_FUNCTION_ARGS) attno = column->bv_attno; serialized = (SerializedRanges *) PG_DETOAST_DATUM(column->bv_values[0]); - ranges = range_deserialize(serialized->maxvalues, serialized); + ranges = brin_range_deserialize(serialized->maxvalues, serialized); /* inspect the ranges, and for each one evaluate the scan keys */ for (rangeno = 0; rangeno < ranges->nranges; rangeno++) @@ -2776,8 +2776,8 @@ brin_minmax_multi_union(PG_FUNCTION_ARGS) serialized_a = (SerializedRanges *) PG_DETOAST_DATUM(col_a->bv_values[0]); serialized_b = (SerializedRanges *) PG_DETOAST_DATUM(col_b->bv_values[0]); - ranges_a = range_deserialize(serialized_a->maxvalues, serialized_a); - ranges_b = range_deserialize(serialized_b->maxvalues, serialized_b); + ranges_a = brin_range_deserialize(serialized_a->maxvalues, serialized_a); + ranges_b = brin_range_deserialize(serialized_b->maxvalues, serialized_b); /* make sure neither of the ranges is NULL */ Assert(ranges_a && ranges_b); @@ -2859,7 +2859,7 @@ brin_minmax_multi_union(PG_FUNCTION_ARGS) /* cleanup and update the serialized value */ pfree(serialized_a); - col_a->bv_values[0] = PointerGetDatum(range_serialize(ranges_a)); + col_a->bv_values[0] = PointerGetDatum(brin_range_serialize(ranges_a)); PG_RETURN_VOID(); } @@ -3041,7 +3041,7 @@ brin_minmax_multi_summary_out(PG_FUNCTION_ARGS) fmgr_info(outfunc, &fmgrinfo); /* deserialize the range info easy-to-process pieces */ - ranges_deserialized = range_deserialize(ranges->maxvalues, ranges); + ranges_deserialized = brin_range_deserialize(ranges->maxvalues, ranges); appendStringInfo(&str, "nranges: %d nvalues: %d maxvalues: %d", ranges_deserialized->nranges, diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 670024762e..91455261ce 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1161,6 +1161,12 @@ DefineIndex(Oid relationId, ObjectAddressSet(address, RelationRelationId, indexRelationId); + /* + * If we created a temporal PK, create triggers for FOR PORTION OF queries. + */ + if (stmt->primary && stmt->istemporal) + CreateTemporalPrimaryKeyTriggers(rel, createdConstraintId, indexRelationId); + /* * Revert to original default_tablespace. Must do this before any return * from this function, but after index_create, so this is a good time. diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 469f2042f1..88b692bd8d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -4247,6 +4247,67 @@ AlterTableInternal(Oid relid, List *cmds, bool recurse, AlterTableUtilityContext ATController(NULL, rel, cmds, recurse, lockmode, context); } +/* + * CreateTemporalPrimaryKeyTriggers + * Create the triggers to perform implicit INSERTs in FOR PORTION OF + * queries. + */ +void +CreateTemporalPrimaryKeyTriggers(Relation rel, Oid constraintOid, Oid indexOid) +{ + CreateTrigStmt *pk_trigger; + + /* + * Build and execute a CREATE TRIGGER statement AFTER UPDATE. + */ + pk_trigger = makeNode(CreateTrigStmt); + pk_trigger->trigname = "PK_TemporalTrigger"; + pk_trigger->relation = NULL; + pk_trigger->row = true; + pk_trigger->timing = TRIGGER_TYPE_AFTER; + pk_trigger->events = TRIGGER_TYPE_UPDATE; + pk_trigger->columns = NIL; + pk_trigger->transitionRels = NIL; + pk_trigger->whenClause = NULL; + pk_trigger->isconstraint = false; + pk_trigger->constrrel = NULL; + pk_trigger->deferrable = false; + pk_trigger->initdeferred = false; + pk_trigger->funcname = SystemFuncName("FP_insert_leftovers"); + pk_trigger->args = NIL; + + (void) CreateTrigger(pk_trigger, NULL, RelationGetRelid(rel), InvalidOid, + constraintOid, + indexOid, InvalidOid, InvalidOid, NULL, true, false); + + /* Make changes-so-far visible */ + CommandCounterIncrement(); + + /* + * Build and execute a CREATE TRIGGER statement AFTER DELETE. + */ + pk_trigger = makeNode(CreateTrigStmt); + pk_trigger->trigname = "PK_TemporalTrigger"; + pk_trigger->relation = NULL; + pk_trigger->row = true; + pk_trigger->timing = TRIGGER_TYPE_AFTER; + pk_trigger->events = TRIGGER_TYPE_DELETE; + pk_trigger->columns = NIL; + pk_trigger->transitionRels = NIL; + pk_trigger->whenClause = NULL; + pk_trigger->isconstraint = false; + pk_trigger->constrrel = NULL; + pk_trigger->deferrable = false; + pk_trigger->initdeferred = false; + pk_trigger->funcname = SystemFuncName("FP_insert_leftovers"); + pk_trigger->args = NIL; + + (void) CreateTrigger(pk_trigger, NULL, RelationGetRelid(rel), InvalidOid, + constraintOid, + indexOid, InvalidOid, InvalidOid, NULL, true, false); + +} + /* * AlterTableGetLockLevel * @@ -9162,6 +9223,9 @@ ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel, allowSystemTableMods, false); /* is_internal */ + if (stmt->primary && stmt->istemporal) + CreateTemporalPrimaryKeyTriggers(rel, address.objectId, index_oid); + index_close(indexRel, NoLock); return address; @@ -11698,6 +11762,7 @@ validateForeignKeyConstraint(char *conname, trigdata.tg_trigtuple = ExecFetchSlotHeapTuple(slot, false, NULL); trigdata.tg_trigslot = slot; trigdata.tg_trigger = &trig; + trigdata.tg_temporal = NULL; fcinfo->context = (Node *) &trigdata; diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 313ba9cdf6..a503d638be 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -54,6 +54,7 @@ #include "utils/acl.h" #include "utils/builtins.h" #include "utils/bytea.h" +#include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/inval.h" #include "utils/lsyscache.h" @@ -2558,6 +2559,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2653,6 +2655,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { HeapTuple newtuple; @@ -2734,6 +2737,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, slot, false); @@ -2797,6 +2801,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; LocTriggerData.tg_updatedcols = updatedCols; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2909,6 +2914,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; updatedCols = ExecGetAllUpdatedCols(relinfo, estate); LocTriggerData.tg_updatedcols = updatedCols; for (i = 0; i < trigdesc->numtriggers; i++) @@ -3032,6 +3038,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, oldslot, false); @@ -3470,6 +3477,7 @@ typedef struct AfterTriggerSharedData Oid ats_tgoid; /* the trigger's ID */ Oid ats_relid; /* the relation it's on */ CommandId ats_firing_id; /* ID for firing cycle */ + ForPortionOfState *for_portion_of; /* the FOR PORTION OF clause */ struct AfterTriggersTableData *ats_table; /* transition table access */ Bitmapset *ats_modifiedcols; /* modified columns */ } AfterTriggerSharedData; @@ -3698,6 +3706,7 @@ static SetConstraintState SetConstraintStateCreate(int numalloc); static SetConstraintState SetConstraintStateCopy(SetConstraintState state); static SetConstraintState SetConstraintStateAddItem(SetConstraintState state, Oid tgoid, bool tgisdeferred); +static ForPortionOfState *CopyForPortionOfState(ForPortionOfState *src); static void cancel_prior_stmt_triggers(Oid relid, CmdType cmdType, int tgevent); @@ -4175,6 +4184,7 @@ AfterTriggerExecute(EState *estate, LocTriggerData.tg_relation = rel; if (TRIGGER_FOR_UPDATE(LocTriggerData.tg_trigger->tgtype)) LocTriggerData.tg_updatedcols = evtshared->ats_modifiedcols; + LocTriggerData.tg_temporal = evtshared->for_portion_of; MemoryContextReset(per_tuple_context); @@ -5571,6 +5581,50 @@ AfterTriggerPendingOnRel(Oid relid) return false; } +/* ---------- + * ForPortionOfState() + * + * Copies a ForPortionOfState into the current memory context. + */ +static ForPortionOfState * +CopyForPortionOfState(ForPortionOfState *src) +{ + ForPortionOfState *dst = NULL; + if (src) { + MemoryContext oldctx; + RangeType *r; + TypeCacheEntry *typcache; + + /* + * Need to lift the FOR PORTION OF details into a higher memory context + * because cascading foreign key update/deletes can cause triggers to fire + * triggers (both other TRI triggers and the insert_leftovers trigger), + * and the AfterTriggerEvents will outlive the FPO details of the original + * query. + */ + oldctx = MemoryContextSwitchTo(TopTransactionContext); + dst = makeNode(ForPortionOfState); + dst->fp_rangeName = pstrdup(src->fp_rangeName); + dst->fp_rangeType = src->fp_rangeType; + dst->fp_hasPeriod = src->fp_hasPeriod; + + if (src->fp_periodStartName) + dst->fp_periodStartName = pstrdup(src->fp_periodStartName); + else + dst->fp_periodStartName = NULL; + + if (src->fp_periodEndName) + dst->fp_periodEndName = pstrdup(src->fp_periodEndName); + else + dst->fp_periodEndName = NULL; + + r = DatumGetRangeTypeP(src->fp_targetRange); + typcache = lookup_type_cache(RangeTypeGetOid(r), TYPECACHE_RANGE_INFO); + dst->fp_targetRange = datumCopy(src->fp_targetRange, typcache->typbyval, typcache->typlen); + MemoryContextSwitchTo(oldctx); + } + return dst; +} /* ---------- * AfterTriggerSaveEvent() @@ -5890,6 +5944,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, else new_shared.ats_table = NULL; new_shared.ats_modifiedcols = modifiedCols; + new_shared.for_portion_of = CopyForPortionOfState(relinfo->ri_forPortionOf); afterTriggerAddEvent(&afterTriggers.query_stack[afterTriggers.query_depth].events, &new_event, &new_shared); diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index b3ce4bae53..80a3cdfc22 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1240,6 +1240,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_projectReturning = NULL; resultRelInfo->ri_onConflictArbiterIndexes = NIL; resultRelInfo->ri_onConflict = NULL; + resultRelInfo->ri_forPortionOf = NULL; resultRelInfo->ri_ReturningSlot = NULL; resultRelInfo->ri_TrigOldSlot = NULL; resultRelInfo->ri_TrigNewSlot = NULL; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index d328856ae5..e7b9d88d39 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -51,6 +51,8 @@ #include "utils/builtins.h" #include "utils/datum.h" #include "utils/memutils.h" +#include "utils/rangetypes.h" +#include "utils/lsyscache.h" #include "utils/rel.h" @@ -3048,6 +3050,37 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } } + /* + * If needed, initialize the target range for FOR PORTION OF. + */ + if (node->forPortionOf) + { + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + Datum targetRange; + bool isNull; + ExprContext *econtext; + ExprState *exprState; + + /* Eval the FOR PORTION OF target */ + if (mtstate->ps.ps_ExprContext == NULL) + ExecAssignExprContext(estate, &mtstate->ps); + econtext = mtstate->ps.ps_ExprContext; + + exprState = ExecPrepareExpr((Expr *) forPortionOf->targetRange, estate); + targetRange = ExecEvalExpr(exprState, econtext, &isNull); + + if (isNull) elog(ERROR, "Got a NULL FOR PORTION OF target range"); + + resultRelInfo->ri_forPortionOf = makeNode(ForPortionOfState); + resultRelInfo->ri_forPortionOf->fp_rangeName = forPortionOf->range_name; + resultRelInfo->ri_forPortionOf->fp_periodStartName = forPortionOf->period_start_name; + resultRelInfo->ri_forPortionOf->fp_periodEndName = forPortionOf->period_end_name; + resultRelInfo->ri_forPortionOf->fp_targetRange = targetRange; + resultRelInfo->ri_forPortionOf->fp_rangeType = forPortionOf->rangeType; + + /* Don't free the ExprContext here because the result must last for the whole query */ + } + /* * If we have any secondary relations in an UPDATE or DELETE, they need to * be treated like non-locked relations in SELECT FOR UPDATE, ie, the diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index f476009d37..833212fb7f 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -222,6 +222,7 @@ _copyModifyTable(const ModifyTable *from) COPY_NODE_FIELD(rowMarks); COPY_SCALAR_FIELD(epqParam); COPY_SCALAR_FIELD(onConflictAction); + COPY_NODE_FIELD(forPortionOf); COPY_NODE_FIELD(arbiterIndexes); COPY_NODE_FIELD(onConflictSet); COPY_NODE_FIELD(onConflictCols); @@ -2295,6 +2296,33 @@ _copyOnConflictExpr(const OnConflictExpr *from) return newnode; } +/* + * _copyForPortionOfExpr + */ +static ForPortionOfExpr * +_copyForPortionOfExpr(const ForPortionOfExpr *from) +{ + ForPortionOfExpr *newnode = makeNode(ForPortionOfExpr); + + COPY_SCALAR_FIELD(range_attno); + COPY_SCALAR_FIELD(start_attno); + COPY_SCALAR_FIELD(end_attno); + COPY_STRING_FIELD(range_name); + COPY_STRING_FIELD(period_start_name); + COPY_STRING_FIELD(period_end_name); + COPY_NODE_FIELD(range); + COPY_NODE_FIELD(startCol); + COPY_NODE_FIELD(endCol); + COPY_NODE_FIELD(targetStart); + COPY_NODE_FIELD(targetEnd); + COPY_NODE_FIELD(targetRange); + COPY_SCALAR_FIELD(rangeType); + COPY_NODE_FIELD(overlapsExpr); + COPY_NODE_FIELD(rangeSet); + + return newnode; +} + /* **************************************************************** * pathnodes.h copy functions * @@ -2671,6 +2699,19 @@ _copyCTECycleClause(const CTECycleClause *from) return newnode; } +static ForPortionOfClause * +_copyForPortionOfClause(const ForPortionOfClause *from) +{ + ForPortionOfClause *newnode = makeNode(ForPortionOfClause); + + COPY_STRING_FIELD(range_name); + COPY_SCALAR_FIELD(range_name_location); + COPY_NODE_FIELD(target_start); + COPY_NODE_FIELD(target_end); + + return newnode; +} + static CommonTableExpr * _copyCommonTableExpr(const CommonTableExpr *from) { @@ -5338,6 +5379,9 @@ copyObjectImpl(const void *from) case T_OnConflictExpr: retval = _copyOnConflictExpr(from); break; + case T_ForPortionOfExpr: + retval = _copyForPortionOfExpr(from); + break; /* * RELATION NODES @@ -5889,6 +5933,9 @@ copyObjectImpl(const void *from) case T_CTECycleClause: retval = _copyCTECycleClause(from); break; + case T_ForPortionOfClause: + retval = _copyForPortionOfClause(from); + break; case T_CommonTableExpr: retval = _copyCommonTableExpr(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 8c8e4c5ffc..9e23b8cc43 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -841,6 +841,24 @@ _equalOnConflictExpr(const OnConflictExpr *a, const OnConflictExpr *b) return true; } +static bool +_equalForPortionOfExpr(const ForPortionOfExpr *a, const ForPortionOfExpr *b) +{ + COMPARE_SCALAR_FIELD(range_attno); + COMPARE_STRING_FIELD(range_name); + COMPARE_NODE_FIELD(range); + COMPARE_NODE_FIELD(startCol); + COMPARE_NODE_FIELD(endCol); + COMPARE_NODE_FIELD(targetStart); + COMPARE_NODE_FIELD(targetEnd); + COMPARE_NODE_FIELD(targetRange); + COMPARE_SCALAR_FIELD(rangeType); + COMPARE_NODE_FIELD(overlapsExpr); + COMPARE_NODE_FIELD(rangeSet); + + return true; +} + /* * Stuff from pathnodes.h */ @@ -2964,6 +2982,17 @@ _equalCTECycleClause(const CTECycleClause *a, const CTECycleClause *b) return true; } +static bool +_equalForPortionOfClause(const ForPortionOfClause *a, const ForPortionOfClause *b) +{ + COMPARE_STRING_FIELD(range_name); + COMPARE_SCALAR_FIELD(range_name_location); + COMPARE_NODE_FIELD(target_start); + COMPARE_NODE_FIELD(target_end); + + return true; +} + static bool _equalCommonTableExpr(const CommonTableExpr *a, const CommonTableExpr *b) { @@ -3349,6 +3378,9 @@ equal(const void *a, const void *b) case T_OnConflictExpr: retval = _equalOnConflictExpr(a, b); break; + case T_ForPortionOfExpr: + retval = _equalForPortionOfExpr(a, b); + break; case T_JoinExpr: retval = _equalJoinExpr(a, b); break; @@ -3890,6 +3922,9 @@ equal(const void *a, const void *b) case T_CTECycleClause: retval = _equalCTECycleClause(a, b); break; + case T_ForPortionOfClause: + retval = _equalForPortionOfClause(a, b); + break; case T_CommonTableExpr: retval = _equalCommonTableExpr(a, b); break; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d4c4e90c29..38512058ca 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2237,6 +2237,14 @@ expression_tree_walker(Node *node, return true; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node; + + if (walker((Node *) forPortionOf->targetRange, context)) + return true; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -2373,6 +2381,8 @@ query_tree_walker(Query *query, return true; if (walker((Node *) query->withCheckOptions, context)) return true; + if (walker((Node *) query->forPortionOf, context)) + return true; if (walker((Node *) query->onConflict, context)) return true; if (walker((Node *) query->returningList, context)) diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index ee03b54ddc..51a26fadae 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -423,10 +423,12 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_NODE_FIELD(rowMarks); WRITE_INT_FIELD(epqParam); WRITE_ENUM_FIELD(onConflictAction, OnConflictAction); + WRITE_NODE_FIELD(forPortionOf); WRITE_NODE_FIELD(arbiterIndexes); WRITE_NODE_FIELD(onConflictSet); WRITE_NODE_FIELD(onConflictCols); WRITE_NODE_FIELD(onConflictWhere); + // TODO: add things for ForPortionOf WRITE_UINT_FIELD(exclRelRTI); WRITE_NODE_FIELD(exclRelTlist); } @@ -1748,6 +1750,24 @@ _outOnConflictExpr(StringInfo str, const OnConflictExpr *node) WRITE_NODE_FIELD(exclRelTlist); } +static void +_outForPortionOfExpr(StringInfo str, const ForPortionOfExpr *node) +{ + WRITE_NODE_TYPE("FORPORTIONOFEXPR"); + + WRITE_INT_FIELD(range_attno); + WRITE_STRING_FIELD(range_name); + WRITE_NODE_FIELD(range); + WRITE_NODE_FIELD(startCol); + WRITE_NODE_FIELD(endCol); + WRITE_NODE_FIELD(targetStart); + WRITE_NODE_FIELD(targetEnd); + WRITE_NODE_FIELD(targetRange); + WRITE_OID_FIELD(rangeType); + WRITE_NODE_FIELD(overlapsExpr); + WRITE_NODE_FIELD(rangeSet); +} + /***************************************************************************** * * Stuff from pathnodes.h. @@ -4198,6 +4218,9 @@ outNode(StringInfo str, const void *obj) case T_OnConflictExpr: _outOnConflictExpr(str, obj); break; + case T_ForPortionOfExpr: + _outForPortionOfExpr(str, obj); + break; case T_Path: _outPath(str, obj); break; diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index abf08b7a2f..8f0f5a0f0d 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1422,6 +1422,28 @@ _readAppendRelInfo(void) * Stuff from parsenodes.h. */ +/* + * _readForPortionOfExpr + */ +static ForPortionOfExpr * +_readForPortionOfExpr(void) +{ + READ_LOCALS(ForPortionOfExpr); + + READ_INT_FIELD(range_attno); + READ_STRING_FIELD(range_name); + READ_NODE_FIELD(range); + READ_NODE_FIELD(startCol); + READ_NODE_FIELD(endCol); + READ_NODE_FIELD(targetStart); + READ_NODE_FIELD(targetEnd); + READ_NODE_FIELD(targetRange); + READ_OID_FIELD(rangeType); + READ_NODE_FIELD(overlapsExpr); + READ_NODE_FIELD(rangeSet); + READ_DONE(); +} + /* * _readRangeTblEntry */ @@ -1696,6 +1718,7 @@ _readModifyTable(void) READ_NODE_FIELD(rowMarks); READ_INT_FIELD(epqParam); READ_ENUM_FIELD(onConflictAction, OnConflictAction); + READ_NODE_FIELD(forPortionOf); READ_NODE_FIELD(arbiterIndexes); READ_NODE_FIELD(onConflictSet); READ_NODE_FIELD(onConflictCols); @@ -2847,6 +2870,8 @@ parseNodeString(void) return_value = _readAppendRelInfo(); else if (MATCH("RANGETBLENTRY", 13)) return_value = _readRangeTblEntry(); + else if (MATCH("FORPORTIONOFEXPR", 16)) + return_value = _readForPortionOfExpr(); else if (MATCH("RANGETBLFUNCTION", 16)) return_value = _readRangeTblFunction(); else if (MATCH("TABLESAMPLECLAUSE", 17)) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 3dc0176a51..a25c5ad51a 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -308,7 +308,8 @@ static ModifyTable *make_modifytable(PlannerInfo *root, Plan *subplan, List *resultRelations, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, - List *rowMarks, OnConflictExpr *onconflict, int epqParam); + List *rowMarks, OnConflictExpr *onconflict, + ForPortionOfExpr *forPortionOf, int epqParam); static GatherMerge *create_gather_merge_plan(PlannerInfo *root, GatherMergePath *best_path); @@ -2753,6 +2754,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->returningLists, best_path->rowMarks, best_path->onconflict, + best_path->forPortionOf, best_path->epqParam); copy_generic_path_info(&plan->plan, &best_path->path); @@ -6877,7 +6879,8 @@ make_modifytable(PlannerInfo *root, Plan *subplan, List *resultRelations, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, - List *rowMarks, OnConflictExpr *onconflict, int epqParam) + List *rowMarks, OnConflictExpr *onconflict, + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTable *node = makeNode(ModifyTable); List *fdw_private_list; @@ -6942,6 +6945,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, node->exclRelTlist = onconflict->exclRelTlist; } node->updateColnosLists = updateColnosLists; + node->forPortionOf = (Node *) forPortionOf; node->withCheckOptionLists = withCheckOptionLists; node->returningLists = returningLists; node->rowMarks = rowMarks; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index bd01ec0526..5e2d09e3eb 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1859,6 +1859,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction) returningLists, rowMarks, parse->onConflict, + parse->forPortionOf, assign_special_exec_param(root)); } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index e53d381e19..591528dfd1 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3630,7 +3630,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTablePath *pathnode = makeNode(ModifyTablePath); @@ -3695,6 +3695,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->returningLists = returningLists; pathnode->rowMarks = rowMarks; pathnode->onconflict = onconflict; + pathnode->forPortionOf = forPortionOf; pathnode->epqParam = epqParam; return pathnode; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 146ee8dd1e..230226fbc8 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -25,6 +25,7 @@ #include "postgres.h" #include "access/sysattr.h" +#include "catalog/pg_period.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "miscadmin.h" @@ -44,12 +45,14 @@ #include "parser/parse_relation.h" #include "parser/parse_target.h" #include "parser/parse_type.h" +#include "parser/parser.h" #include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "utils/backend_status.h" #include "utils/builtins.h" #include "utils/guc.h" #include "utils/queryjumble.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -65,6 +68,10 @@ static List *transformInsertRow(ParseState *pstate, List *exprlist, bool strip_indirection); static OnConflictExpr *transformOnConflictClause(ParseState *pstate, OnConflictClause *onConflictClause); +static Node *transformForPortionOfBound(Node *n, bool isLowerBound); +static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOfClause); static int count_rowexpr_columns(ParseState *pstate, Node *expr); static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt); static Query *transformValuesClause(ParseState *pstate, SelectStmt *stmt); @@ -77,7 +84,8 @@ static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt); static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt); static List *transformReturningList(ParseState *pstate, List *returningList); static List *transformUpdateTargetList(ParseState *pstate, - List *targetList); + List *targetList, + ForPortionOfExpr *forPortionOf); static Query *transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt); static Query *transformDeclareCursorStmt(ParseState *pstate, @@ -430,6 +438,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_DELETE; @@ -468,7 +477,20 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; - qual = transformWhereClause(pstate, stmt->whereClause, + if (stmt->forPortionOf) + qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf); + + // TODO: DRY with UPDATE + if (stmt->forPortionOf) + { + if (stmt->whereClause) + whereClause = (Node *) makeBoolExpr(AND_EXPR, list_make2(qry->forPortionOf->overlapsExpr, stmt->whereClause), -1); + else + whereClause = qry->forPortionOf->overlapsExpr; + } + else + whereClause = stmt->whereClause; + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList); @@ -1085,7 +1107,7 @@ transformOnConflictClause(ParseState *pstate, * Now transform the UPDATE subexpressions. */ onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, @@ -1115,6 +1137,339 @@ transformOnConflictClause(ParseState *pstate, return result; } +/* + * transformForPortionOfBound + * transforms MINVALUE and MAXVALUE pseudo-column references to NULL + * (which represent "unbounded" in a range type, otherwise returns + * its input unchanged. + */ +static Node * +transformForPortionOfBound(Node *n, bool isLowerBound) +{ + if (nodeTag(n) == T_ColumnRef) + { + ColumnRef *cref = (ColumnRef *) n; + char *cname = ""; + A_Const *n2; + + if (list_length(cref->fields) == 1 && + IsA(linitial(cref->fields), String)) + cname = strVal(linitial(cref->fields)); + + if (strcmp("minvalue", cname) == 0) + { + if (!isLowerBound) + ereport(ERROR, (errmsg("MINVALUE can only be used for the lower bound"))); + } + else if (strcmp("maxvalue", cname) == 0) + { + if (isLowerBound) + ereport(ERROR, (errmsg("MAXVALUE can only be used for the upper bound"))); + } + else + return n; + + n2 = makeNode(A_Const); + n2->isnull = true; + n2->location = ((ColumnRef *)n)->location; + + return (Node *)n2; + } + else + return n; +} + +/* + * transformForPortionOfClause + * transforms a ForPortionOfClause in an UPDATE/DELETE statement + */ +static ForPortionOfExpr * +transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOf) +{ + Relation targetrel = pstate->p_target_relation; + RangeTblEntry *target_rte = pstate->p_target_nsitem->p_rte; + char *range_name = forPortionOf->range_name; + char *range_type_name = NULL; + int range_attno = InvalidOid; + int start_attno = InvalidOid; + int end_attno = InvalidOid; + char *startcolname = NULL; + char *endcolname = NULL; + ForPortionOfExpr *result; + List *targetList; + Oid pkoid; + HeapTuple indexTuple; + Form_pg_index pk; + Node *target_start, *target_end; + FuncCall *fc; + + result = makeNode(ForPortionOfExpr); + result->range = NULL; + + /* Make sure the table has a primary key */ + pkoid = RelationGetPrimaryKeyIndex(targetrel); + if (pkoid == InvalidOid) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("relation \"%s\" does not have a temporal primary key", + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + + /* Make sure the primary key is a temporal key */ + // TODO: need a lock here? + indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(pkoid)); + if (!HeapTupleIsValid(indexTuple)) /* should not happen */ + elog(ERROR, "cache lookup failed for index %u", pkoid); + pk = (Form_pg_index) GETSTRUCT(indexTuple); + + /* + * Only temporal pkey indexes have both isprimary and isexclusion. + * Checking those saves us from scanning pg_constraint + * like in RelationGetExclusionInfo. + */ + if (!(pk->indisprimary && pk->indisexclusion)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("relation \"%s\" does not have a temporal primary key", + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + + /* + * First look for a range column, then look for a period. + */ + range_attno = attnameAttNum(targetrel, range_name, false); + if (range_attno != InvalidAttrNumber) + { + Form_pg_attribute attr = TupleDescAttr(targetrel->rd_att, range_attno - 1); + Var *v; + + // TODO: check attr->attisdropped ? + + /* Make sure it's a range column */ + if (!type_is_range(attr->atttypid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" of relation \"%s\" is not a range type", + range_name, + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + + /* Make sure the range attribute is the last part of the pkey. */ + if (range_attno != pk->indkey.values[pk->indnkeyatts - 1]) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" is not the temporal part of the primary key for relation \"%s\"", + range_name, + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + } + + v = makeVar( + rtindex, + range_attno, + attr->atttypid, + attr->atttypmod, + attr->attcollation, + 0); + v->location = forPortionOf->range_name_location; + result->range = (Expr *) v; + result->rangeType = attr->atttypid; + range_type_name = get_typname(attr->atttypid); + + } else { + Oid relid = RelationGetRelid(targetrel); + HeapTuple perTuple = SearchSysCache2(PERIODNAME, + ObjectIdGetDatum(relid), + PointerGetDatum(range_name)); + if (HeapTupleIsValid(perTuple)) + { + Form_pg_period per = (Form_pg_period) GETSTRUCT(perTuple); + Form_pg_attribute startattr, endattr; + bool pkeyIncludesPeriod = false; + Var *startvar, *endvar; + FuncCall *periodRange; + + Type rngtype = typeidType(per->perrngtype); + range_type_name = typeTypeName(rngtype); + ReleaseSysCache(rngtype); + start_attno = per->perstart; + end_attno = per->perend; + + startattr = TupleDescAttr(targetrel->rd_att, start_attno - 1); + endattr = TupleDescAttr(targetrel->rd_att, end_attno - 1); + + startcolname = NameStr(startattr->attname); + endcolname = NameStr(endattr->attname); + result->period_start_name = startcolname; + result->period_end_name = endcolname; + + /* Make sure the period is the last part of the pkey. */ + if (pk->indkey.values[pk->indnkeyatts - 1] == InvalidOid) + { + /* + * The PK ends with an expression. Make sure it's for our period. + * There should be only one entry in indexprs, and it should be ours. + */ + pkeyIncludesPeriod = pk->indperiod == per->oid; + } + + if (!pkeyIncludesPeriod) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" is not the temporal part of the primary key for relation \"%s\"", + range_name, + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + + startvar = makeVar( + rtindex, + per->perstart, + startattr->atttypid, + startattr->atttypmod, + startattr->attcollation, + 0); + startvar->location = forPortionOf->range_name_location; + + endvar = makeVar( + rtindex, + per->perend, + endattr->atttypid, + endattr->atttypmod, + endattr->attcollation, + 0); + endvar->location = forPortionOf->range_name_location; + + ReleaseSysCache(perTuple); + + periodRange = makeFuncCall(SystemFuncName(range_type_name), + list_make2(startvar, endvar), + COERCE_EXPLICIT_CALL, + forPortionOf->range_name_location); + result->range = (Expr *) periodRange; + result->rangeType = typenameTypeId(pstate, typeStringToTypeName(range_type_name)); + } + } + ReleaseSysCache(indexTuple); + + if (result->range == NULL) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column or period \"%s\" of relation \"%s\" does not exist", + range_name, + RelationGetRelationName(pstate->p_target_relation)), + parser_errposition(pstate, forPortionOf->range_name_location))); + + /* + * Build a range from the FROM ... TO .... bounds. + * This should give a constant result, so we accept functions like NOW() + * but not column references, subqueries, etc. + * + * It also permits MINVALUE and MAXVALUE like declarative partitions. + */ + target_start = transformForPortionOfBound(forPortionOf->target_start, true); + target_end = transformForPortionOfBound(forPortionOf->target_end, false); + fc = makeFuncCall(SystemFuncName(range_type_name), + list_make2(target_start, target_end), + COERCE_EXPLICIT_CALL, + forPortionOf->range_name_location); + result->targetRange = transformExpr(pstate, (Node *) fc, EXPR_KIND_UPDATE_PORTION); + + /* overlapsExpr is something we can add to the whereClause */ + result->overlapsExpr = (Node *) makeSimpleA_Expr(AEXPR_OP, "&&", + // TODO: Maybe need a copy here?: + (Node *) result->range, (Node *) fc, + forPortionOf->range_name_location); + + /* + * Now make sure we update the start/end time of the record. + * For a range col (r) this is `r = r * targetRange`. + * For a PERIOD with cols (s, e) this is `s = lower(tsrange(s, e) * targetRange)` + * and `e = upper(tsrange(s, e) * targetRange` (of course not necessarily with + * tsrange, but with whatever range type is used there)). + * + * We also compute the possible left-behind bits at the start and end of the tuple, + * so that we can INSERT them if necessary. + */ + // TODO: Only do this for UPDATE, not DELETE: + targetList = NIL; + if (range_attno != InvalidAttrNumber) + { + Expr *rangeSetExpr = (Expr *) makeSimpleA_Expr(AEXPR_OP, "*", + // TODO: Maybe need a copy here?: + (Node *) result->range, (Node *) fc, + forPortionOf->range_name_location); + TargetEntry *tle; + + rangeSetExpr = (Expr *) transformExpr(pstate, (Node *) rangeSetExpr, EXPR_KIND_UPDATE_PORTION); + tle = makeTargetEntry(rangeSetExpr, + range_attno, + range_name, + false); + + targetList = lappend(targetList, tle); + + /* Mark the range column as requiring update permissions */ + target_rte->updatedCols = bms_add_member(target_rte->updatedCols, + range_attno - FirstLowInvalidHeapAttributeNumber); + + } else { + Expr *intersectExpr; + Expr *boundSetExpr; + TargetEntry *tle; + + /* Set up targetList for the PERIOD start column */ + + intersectExpr = (Expr *) makeSimpleA_Expr(AEXPR_OP, "*", + // TODO: copy? + (Node *) result->range, (Node *) fc, + forPortionOf->range_name_location); + + boundSetExpr = (Expr *) makeFuncCall(SystemFuncName("lower"), + list_make1(intersectExpr), + COERCE_EXPLICIT_CALL, + forPortionOf->range_name_location); + boundSetExpr = (Expr *) transformExpr(pstate, (Node *) boundSetExpr, EXPR_KIND_UPDATE_PORTION); + + tle = makeTargetEntry(boundSetExpr, + start_attno, + startcolname, + false); + + targetList = lappend(targetList, tle); + + /* Set up targetList for the PERIOD end column */ + + boundSetExpr = (Expr *) makeFuncCall(SystemFuncName("upper"), + list_make1(intersectExpr), + COERCE_EXPLICIT_CALL, + forPortionOf->range_name_location); + boundSetExpr = (Expr *) transformExpr(pstate, (Node *) boundSetExpr, EXPR_KIND_UPDATE_PORTION); + + tle = makeTargetEntry(boundSetExpr, + end_attno, + endcolname, + false); + + targetList = lappend(targetList, tle); + + /* Mark the bound columns as requiring update permissions */ + target_rte->updatedCols = bms_add_member(target_rte->updatedCols, + start_attno - FirstLowInvalidHeapAttributeNumber); + target_rte->updatedCols = bms_add_member(target_rte->updatedCols, + end_attno - FirstLowInvalidHeapAttributeNumber); + } + + result->rangeSet = targetList; + result->range_name = range_name; + result->range_attno = range_attno; + result->start_attno = start_attno; + result->end_attno = end_attno; + + return result; +} /* * BuildOnConflictExcludedTargetlist @@ -2320,6 +2675,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_UPDATE; @@ -2337,6 +2693,10 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) stmt->relation->inh, true, ACL_UPDATE); + + if (stmt->forPortionOf) + qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf); + nsitem = pstate->p_target_nsitem; /* subqueries in FROM cannot access the result relation */ @@ -2353,7 +2713,16 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; - qual = transformWhereClause(pstate, stmt->whereClause, + if (stmt->forPortionOf) + { + if (stmt->whereClause) + whereClause = (Node *) makeBoolExpr(AND_EXPR, list_make2(qry->forPortionOf->overlapsExpr, stmt->whereClause), -1); + else + whereClause = qry->forPortionOf->overlapsExpr; + } + else + whereClause = stmt->whereClause; + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList); @@ -2362,7 +2731,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) * Now we are done with SELECT-like processing, and can get on with * transforming the target list to match the UPDATE target columns. */ - qry->targetList = transformUpdateTargetList(pstate, stmt->targetList); + qry->targetList = transformUpdateTargetList(pstate, stmt->targetList, qry->forPortionOf); qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); @@ -2380,7 +2749,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) * handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE */ static List * -transformUpdateTargetList(ParseState *pstate, List *origTlist) +transformUpdateTargetList(ParseState *pstate, List *origTlist, ForPortionOfExpr *forPortionOf) { List *tlist = NIL; RangeTblEntry *target_rte; @@ -2430,6 +2799,9 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) RelationGetRelationName(pstate->p_target_relation)), parser_errposition(pstate, origTarget->location))); + /* TODO: Make sure user isn't trying to SET the range attribute directly --- TODO or permit it?? */ + + updateTargetListEntry(pstate, tle, origTarget->name, attrno, origTarget->indirection, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 62c545f8fc..d7ffb1d7bb 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -248,6 +248,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RangeVar *range; IntoClause *into; WithClause *with; + ForPortionOfClause *forportionof; InferClause *infer; OnConflictClause *onconflict; A_Indices *aind; @@ -525,6 +526,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type relation_expr %type extended_relation_expr %type relation_expr_opt_alias +%type for_portion_of_clause %type tablesample_clause opt_repeatable_clause %type target_el set_target insert_column_item @@ -700,7 +702,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PERIOD PLACING PLANS POLICY - POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY + PORTION POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION QUOTE @@ -773,6 +775,16 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %nonassoc '<' '>' '=' LESS_EQUALS GREATER_EQUALS NOT_EQUALS %nonassoc BETWEEN IN_P LIKE ILIKE SIMILAR NOT_LA %nonassoc ESCAPE /* ESCAPE must be just above LIKE/ILIKE/SIMILAR */ +/* + * We need to handle this shift/reduce conflict: + * FOR PORTION OF valid_at FROM INTERVAL YEAR TO MONTH TO foo. + * This is basically the classic "dangling else" problem, and we want a + * similar resolution: treat the TO as part of the INTERVAL, not as part of + * the FROM ... TO .... Users can add parentheses if that's a problem. + * TO just needs to be higher precedence than YEAR_P etc. + */ +%nonassoc YEAR_P MONTH_P DAY_P HOUR_P MINUTE_P +%nonassoc TO /* * To support target_el without AS, it used to be necessary to assign IDENT an * explicit precedence just less than Op. While that's not really necessary @@ -11255,13 +11267,15 @@ returning_clause: *****************************************************************************/ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias + for_portion_of_clause using_clause where_or_current_clause returning_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $4; - n->usingClause = $5; - n->whereClause = $6; - n->returningList = $7; + n->forPortionOf = $5; + n->usingClause = $6; + n->whereClause = $7; + n->returningList = $8; n->withClause = $1; $$ = (Node *)n; } @@ -11324,6 +11338,7 @@ opt_nowait_or_skip: *****************************************************************************/ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias + for_portion_of_clause SET set_clause_list from_clause where_or_current_clause @@ -11331,10 +11346,11 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias { UpdateStmt *n = makeNode(UpdateStmt); n->relation = $3; - n->targetList = $5; - n->fromClause = $6; - n->whereClause = $7; - n->returningList = $8; + n->forPortionOf = $4; + n->targetList = $6; + n->fromClause = $7; + n->whereClause = $8; + n->returningList = $9; n->withClause = $1; $$ = (Node *)n; } @@ -12619,6 +12635,19 @@ relation_expr_opt_alias: relation_expr %prec UMINUS } ; +for_portion_of_clause: + FOR PORTION OF ColId FROM a_expr TO a_expr + { + ForPortionOfClause *n = makeNode(ForPortionOfClause); + n->range_name = $4; + n->range_name_location = @4; + n->target_start = $6; + n->target_end = $8; + $$ = n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + /* * TABLESAMPLE decoration in a FROM item */ @@ -15786,6 +15815,7 @@ unreserved_keyword: | PASSWORD | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -16367,6 +16397,7 @@ bare_label_keyword: | PLACING | PLANS | POLICY + | PORTION | POSITION | PRECEDING | PREPARE diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 7d829a05a9..41c1a8fd7c 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -555,6 +555,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + if (isAgg) + err = _("aggregate functions are not allowed in FOR PORTION OF expressions"); + else + err = _("grouping operations are not allowed in FOR PORTION OF expressions"); + + break; /* * There is intentionally no default: case here, so that the @@ -943,6 +950,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + err = _("window functions are not allowed in FOR PORTION OF expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c index 4133526f04..9991da3b3e 100644 --- a/src/backend/parser/parse_collate.c +++ b/src/backend/parser/parse_collate.c @@ -484,6 +484,7 @@ assign_collations_walker(Node *node, assign_collations_context *context) case T_JoinExpr: case T_FromExpr: case T_OnConflictExpr: + case T_ForPortionOfExpr: case T_SortGroupClause: (void) expression_tree_walker(node, assign_collations_walker, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 2d1a477154..cef7f3f4c9 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -513,6 +513,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_PARTITION_BOUND: err = _("cannot use column reference in partition bound expression"); break; + case EXPR_KIND_UPDATE_PORTION: + err = _("cannot use column reference in FOR PORTION OF expression"); + break; /* * There is intentionally no default: case here, so that the @@ -1764,6 +1767,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_GENERATED_COLUMN: err = _("cannot use subquery in column generation expression"); break; + case EXPR_KIND_UPDATE_PORTION: + err = _("cannot use subquery in FOR PORTION OF expression"); + break; /* * There is intentionally no default: case here, so that the @@ -3039,6 +3045,8 @@ ParseExprKindName(ParseExprKind exprKind) case EXPR_KIND_UPDATE_SOURCE: case EXPR_KIND_UPDATE_TARGET: return "UPDATE"; + case EXPR_KIND_UPDATE_PORTION: + return "FOR PORTION OF"; case EXPR_KIND_GROUP_BY: return "GROUP BY"; case EXPR_KIND_ORDER_BY: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 542f9167aa..65f1cf4f98 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2655,6 +2655,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + err = _("set-returning functions are not allowed in FOR PORTION OF expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 9521e81100..503c50e098 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -1591,6 +1591,7 @@ rewriteValuesRTE(Query *parsetree, RangeTblEntry *rte, int rti, /* * Record in target_rte->extraUpdatedCols the indexes of any generated columns * that depend on any columns mentioned in target_rte->updatedCols. + * If the update uses FOR PORTION OF, include the PK range. */ void fill_extraUpdatedCols(RangeTblEntry *target_rte, Relation target_relation) @@ -3122,6 +3123,19 @@ rewriteTargetView(Query *parsetree, Relation view) } } + // TODO: This doesn't look like it's getting used: + if (parsetree->forPortionOf) + { + foreach(lc, parsetree->forPortionOf->rangeSet) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + + if (!tle->resjunk) + modified_cols = bms_add_member(modified_cols, + tle->resno - FirstLowInvalidHeapAttributeNumber); + } + } + auto_update_detail = view_cols_are_auto_updatable(viewquery, modified_cols, NULL, @@ -3764,6 +3778,16 @@ RewriteQuery(Query *parsetree, List *rewrite_events) } else if (event == CMD_UPDATE) { + /* Update FOR PORTION OF column(s) automatically */ + if (parsetree->forPortionOf) + { + ListCell *tl; + foreach(tl, parsetree->forPortionOf->rangeSet) + { + TargetEntry *tle = (TargetEntry *) lfirst(tl); + parsetree->targetList = lappend(parsetree->targetList, tle); + } + } parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 1fbc387d47..8f6e80b23d 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1542,7 +1542,7 @@ ProcessUtilitySlow(ParseState *pstate, true, /* check_rights */ true, /* check_not_in_use */ false, /* skip_build */ - false); /* quiet */ + false); /* quiet */ /* * Add the CREATE INDEX node itself to stash right away; diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index 41b486bcef..dc741f2fc9 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -38,6 +38,7 @@ OBJS = \ float.o \ format_type.o \ formatting.o \ + fp_triggers.o \ genfile.o \ geo_ops.o \ geo_selfuncs.o \ diff --git a/src/backend/utils/adt/fp_triggers.c b/src/backend/utils/adt/fp_triggers.c new file mode 100644 index 0000000000..d00021ace1 --- /dev/null +++ b/src/backend/utils/adt/fp_triggers.c @@ -0,0 +1,574 @@ +/*------------------------------------------------------------------------- + * + * fp_triggers.c + * + * Generic trigger procedures for temporal update and delete commands. + * + * Note about memory management: the private hashtables kept here live + * across query and transaction boundaries, in fact they live as long as + * the backend does. This works because the hashtable structures + * themselves are allocated by dynahash.c in its permanent DynaHashCxt, + * and the SPI plans they point to are saved using SPI_keepplan(). + * There is not currently any provision for throwing away a no-longer-needed + * plan --- consider improving this someday. + * + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * + * src/backend/utils/adt/fp_triggers.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/htup_details.h" +#include "access/sysattr.h" +#include "access/table.h" +#include "access/tableam.h" +#include "access/xact.h" +#include "catalog/pg_collation.h" +#include "catalog/pg_constraint.h" +#include "catalog/pg_operator.h" +#include "catalog/pg_type.h" +#include "commands/trigger.h" +#include "executor/executor.h" +#include "executor/spi.h" +#include "lib/ilist.h" +#include "miscadmin.h" +#include "parser/parse_coerce.h" +#include "parser/parse_relation.h" +#include "storage/bufmgr.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/datum.h" +#include "utils/fmgroids.h" +#include "utils/guc.h" +#include "utils/inval.h" +#include "utils/lsyscache.h" +#include "utils/memutils.h" +#include "utils/rel.h" +#include "utils/rls.h" +#include "utils/ruleutils.h" +#include "utils/snapmgr.h" +#include "utils/syscache.h" + +/* + * Local definitions + */ + +/* Need a little more than the possible number of columns in a table */ +#define FP_MAX_ATTS 1650 + +#define FP_INIT_CONSTRAINTHASHSIZE 64 +#define FP_INIT_QUERYHASHSIZE (FP_INIT_CONSTRAINTHASHSIZE * 4) + +#define MAX_QUOTED_NAME_LEN (NAMEDATALEN*2+3) +#define MAX_QUOTED_REL_NAME_LEN (MAX_QUOTED_NAME_LEN*2) + +#define FPAttName(rel, attnum) NameStr(*attnumAttName(rel, attnum)) + + +/* + * FP_QueryKey + * + * The key identifying a prepared SPI plan in our query hashtable + */ +typedef struct FP_QueryKey +{ + Oid relation_id; /* OID of Relation */ +} FP_QueryKey; + +/* + * FP_QueryHashEntry + */ +typedef struct FP_QueryHashEntry +{ + FP_QueryKey key; + SPIPlanPtr plan; +} FP_QueryHashEntry; + +/* + * FP_CompareKey + * + * The key identifying an entry showing how to compare two values + */ +typedef struct FP_CompareKey +{ + Oid eq_opr; /* the equality operator to apply */ + Oid typeid; /* the data type to apply it to */ +} FP_CompareKey; + + +/* + * Local data + */ +static HTAB *fp_query_cache = NULL; + + +/* + * Local function prototypes + */ +static void quoteOneName(char *buffer, const char *name); +static void quoteRelationName(char *buffer, Relation rel); +static void fp_BuildQueryKey(FP_QueryKey *key, + const Relation rel); + +static void fp_InitHashTables(void); +static SPIPlanPtr fp_FetchPreparedPlan(FP_QueryKey *key); +static void fp_HashPreparedPlan(FP_QueryKey *key, SPIPlanPtr plan); + +static SPIPlanPtr fp_PlanInserts(const char *querystr, int nargs, Oid *argtypes, + FP_QueryKey *qkey, Relation query_rel); +static bool fp_PerformInserts(FP_QueryKey *qkey, SPIPlanPtr qplan, + Relation query_rel, + TupleTableSlot *oldslot, Datum targetRange); +static void fp_ExtractValues(TupleTableSlot *slot, + Datum targetRange, + Datum *vals, char *nulls); + + +/* + * FP_insert_leftovers - + * + * Insert leftovers from a temporal UPDATE/DELETE + */ +Datum +FP_insert_leftovers(PG_FUNCTION_ARGS) +{ + TriggerData *trigdata = (TriggerData *) fcinfo->context; + Relation rel = trigdata->tg_relation; + FP_QueryKey qkey; + SPIPlanPtr qplan; + + /* Check that this is a valid trigger call on the right time and event. */ + if (!CALLED_AS_TRIGGER(fcinfo)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" was not called by trigger manager", "FP_insert_leftovers"))); + + if (!TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event) && + !TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)) + ereport(ERROR, + (errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED), + errmsg("function \"%s\" must be fired for UPDATE or DELETE", "FP_insert_leftovers"))); + + /* Only do something if the statement has FOR PORTION OF */ + if (!trigdata->tg_temporal) + return PointerGetDatum(NULL); + + if (!trigdata->tg_temporal->fp_targetRange) + elog(ERROR, "No target range found for temporal query"); + + if (SPI_connect() != SPI_OK_CONNECT) + elog(ERROR, "SPI_connect failed"); + + /* Fetch or prepare a saved plan for the inserts */ + fp_BuildQueryKey(&qkey, rel); + + if ((qplan = fp_FetchPreparedPlan(&qkey)) == NULL) + { + RangeType *targetRange = DatumGetRangeTypeP(trigdata->tg_temporal->fp_targetRange); + char *rangeTypeName = get_typname(RangeTypeGetOid(targetRange)); + StringInfoData querybuf; + int natts = rel->rd_att->natts; + char relname[MAX_QUOTED_REL_NAME_LEN]; + char attname[MAX_QUOTED_NAME_LEN]; + Oid queryoids[FP_MAX_ATTS]; + int rangeAttNum = InvalidAttrNumber; + int periodStartAttNum = InvalidAttrNumber; + int periodEndAttNum = InvalidAttrNumber; + bool usingPeriod; + + /* ---------- + * The query string built is + * INSERT INTO + * (rangeatt, otheratt1, ...) + * SELECT x.r, $1, ... $n + * FROM (VALUES + * (rangetype(lower($x), upper($n+1)) - $n+1), + * (rangetype(lower($n+1), upper($x)) - $n+1) + * ) x (r) + * WHERE x.r <> 'empty' + * The SELECT list "$1, ... $n" includes every attribute except the rangeatt. + * The "$x" is whichever attribute is the range column. + * The $n+1 param has the FOR PORTION OF target range. + * The $1...$n params are the values of the pre-UPDATE/DELETE tuple. + * If there is a PERIOD instead of a range, + * then instead of rangeatt we use startatt and endatt. + * ---------- + */ + initStringInfo(&querybuf); + + usingPeriod = trigdata->tg_temporal->fp_periodStartName != NULL; + quoteRelationName(relname, rel); + appendStringInfo(&querybuf, "INSERT INTO %s (", relname); + if (usingPeriod) + { + quoteOneName(attname, trigdata->tg_temporal->fp_periodStartName); + appendStringInfo(&querybuf, "%s", attname); + quoteOneName(attname, trigdata->tg_temporal->fp_periodEndName); + appendStringInfo(&querybuf, ", %s", attname); + } + else + { + quoteOneName(attname, trigdata->tg_temporal->fp_rangeName); + appendStringInfo(&querybuf, "%s", attname); + } + + /* INSERT into every attribute but the range column */ + for (int i = 0; i < natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i); + const char *colname = NameStr(attr->attname); + if (!usingPeriod && strcmp(colname, trigdata->tg_temporal->fp_rangeName) == 0) + rangeAttNum = i + 1; + else if (usingPeriod && strcmp(colname, trigdata->tg_temporal->fp_periodStartName) == 0) + periodStartAttNum = i + 1; + else if (usingPeriod && strcmp(colname, trigdata->tg_temporal->fp_periodEndName) == 0) + periodEndAttNum = i + 1; + else + { + quoteOneName(attname, colname); + appendStringInfo(&querybuf, ", %s", attname); + } + queryoids[i] = attr->atttypid; + } + queryoids[natts] = trigdata->tg_temporal->fp_rangeType; + if (!usingPeriod && rangeAttNum == InvalidAttrNumber) + elog(ERROR, "range column %s not found", trigdata->tg_temporal->fp_rangeName); + else if (usingPeriod && periodStartAttNum == InvalidAttrNumber) + elog(ERROR, "period start column %s not found", trigdata->tg_temporal->fp_periodStartName); + else if (usingPeriod && periodEndAttNum == InvalidAttrNumber) + elog(ERROR, "period end column %s not found", trigdata->tg_temporal->fp_periodEndName); + + if (!usingPeriod) + appendStringInfo(&querybuf, ") SELECT x.r"); + else + appendStringInfo(&querybuf, ") SELECT lower(x.r), upper(x.r)"); + + /* SELECT all the attributes but the range/start/end columns */ + for (int i = 0; i < natts; i++) + if (!((!usingPeriod && i == rangeAttNum - 1) || + (usingPeriod && i == periodStartAttNum - 1) || + (usingPeriod && i == periodEndAttNum - 1))) + appendStringInfo(&querybuf, ", $%d", i + 1); + + appendStringInfo(&querybuf, " FROM (VALUES"); + // TODO: Why use `- $n+1` instead of setting the bound to the edge of $n+1 directly? + // (where $n+1 is the range build from FOR PORTION OF) + if (!usingPeriod) + { + appendStringInfo(&querybuf, " (%s(lower($%d), upper($%d)) - $%d),", rangeTypeName, rangeAttNum, natts+1, natts+1); + appendStringInfo(&querybuf, " (%s(lower($%d), upper($%d)) - $%d)", rangeTypeName, natts+1, rangeAttNum, natts+1); + } + else + { + appendStringInfo(&querybuf, " (%s($%d, upper($%d)) - $%d),", rangeTypeName, periodStartAttNum, natts+1, natts+1); + appendStringInfo(&querybuf, " (%s(lower($%d), $%d) - $%d)", rangeTypeName, natts+1, periodEndAttNum, natts+1); + } + appendStringInfo(&querybuf, ") x(r) WHERE x.r <> 'empty'"); + + /* Prepare and save the plan */ + qplan = fp_PlanInserts(querybuf.data, natts + (usingPeriod ? 2 : 1), queryoids, &qkey, rel); + } + + /* + * We have a plan now. Run it. + */ + fp_PerformInserts(&qkey, qplan, + rel, + trigdata->tg_trigslot, + trigdata->tg_temporal->fp_targetRange); + + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + return PointerGetDatum(NULL); +} + + +/* ---------- + * Local functions below + * ---------- + */ + + +/* + * quoteOneName --- safely quote a single SQL name + * + * buffer must be MAX_QUOTED_NAME_LEN long (includes room for \0) + */ +static void +quoteOneName(char *buffer, const char *name) +{ + /* Rather than trying to be smart, just always quote it. */ + *buffer++ = '"'; + while (*name) + { + if (*name == '"') + *buffer++ = '"'; + *buffer++ = *name++; + } + *buffer++ = '"'; + *buffer = '\0'; +} + +/* + * quoteRelationName --- safely quote a fully qualified relation name + * + * buffer must be MAX_QUOTED_REL_NAME_LEN long (includes room for \0) + */ +static void +quoteRelationName(char *buffer, Relation rel) +{ + quoteOneName(buffer, get_namespace_name(RelationGetNamespace(rel))); + buffer += strlen(buffer); + *buffer++ = '.'; + quoteOneName(buffer, RelationGetRelationName(rel)); +} + +/* ---------- + * fp_BuildQueryKey - + * + * Construct a hashtable key for a prepared SPI plan of a temporal leftovers insert + * + * key: output argument, *key is filled in based on the other arguments + * Relation: info from pg_constraint entry + * ---------- + */ +static void +fp_BuildQueryKey(FP_QueryKey *key, const Relation rel) +{ + /* + * We assume struct FP_QueryKey contains no padding bytes, else we'd need + * to use memset to clear them. + */ + key->relation_id = RelationGetRelid(rel); +} + +/* + * Prepare execution plan for a query to insert temporal leftovers + */ +static SPIPlanPtr +fp_PlanInserts(const char *querystr, int nargs, Oid *argtypes, + FP_QueryKey *qkey, Relation query_rel) +{ + SPIPlanPtr qplan; + Oid save_userid; + int save_sec_context; + + /* Switch to proper UID to perform check as */ + GetUserIdAndSecContext(&save_userid, &save_sec_context); + SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner, + save_sec_context | SECURITY_LOCAL_USERID_CHANGE | + SECURITY_NOFORCE_RLS); + + /* Create the plan */ + qplan = SPI_prepare(querystr, nargs, argtypes); + + if (qplan == NULL) + elog(ERROR, "SPI_prepare returned %s for %s", SPI_result_code_string(SPI_result), querystr); + + /* Restore UID and security context */ + SetUserIdAndSecContext(save_userid, save_sec_context); + + /* Save the plan */ + SPI_keepplan(qplan); + fp_HashPreparedPlan(qkey, qplan); + + return qplan; +} + +/* + * Perform a query to enforce a temporal PK restriction + */ +static bool +fp_PerformInserts(FP_QueryKey *qkey, SPIPlanPtr qplan, + Relation query_rel, + TupleTableSlot *oldslot, Datum targetRange) +{ + Snapshot test_snapshot; + Snapshot crosscheck_snapshot; + int spi_result; + Oid save_userid; + int save_sec_context; + Datum vals[FP_MAX_ATTS]; + char nulls[FP_MAX_ATTS]; + bool detectNewRows = true; // TODO: need this? + + /* Extract the parameters to be passed into the query */ + fp_ExtractValues(oldslot, targetRange, vals, nulls); + + /* + * In READ COMMITTED mode, we just need to use an up-to-date regular + * snapshot, and we will see all rows that could be interesting. But in + * transaction-snapshot mode, we can't change the transaction snapshot. If + * the caller passes detectNewRows == false then it's okay to do the query + * with the transaction snapshot; otherwise we use a current snapshot, and + * tell the executor to error out if it finds any rows under the current + * snapshot that wouldn't be visible per the transaction snapshot. Note + * that SPI_execute_snapshot will register the snapshots, so we don't need + * to bother here. + */ + if (IsolationUsesXactSnapshot() && detectNewRows) + { + CommandCounterIncrement(); /* be sure all my own work is visible */ + test_snapshot = GetLatestSnapshot(); + crosscheck_snapshot = GetTransactionSnapshot(); + } + else + { + /* the default SPI behavior is okay */ + test_snapshot = InvalidSnapshot; + crosscheck_snapshot = InvalidSnapshot; + } + + /* Switch to proper UID to perform check as */ + GetUserIdAndSecContext(&save_userid, &save_sec_context); + SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner, + save_sec_context | SECURITY_LOCAL_USERID_CHANGE | + SECURITY_NOFORCE_RLS); + + /* Finally we can run the query. */ + spi_result = SPI_execute_snapshot(qplan, + vals, nulls, + test_snapshot, crosscheck_snapshot, + false, true, 0); + + /* Restore UID and security context */ + SetUserIdAndSecContext(save_userid, save_sec_context); + + /* Check result */ + if (spi_result < 0) + elog(ERROR, "SPI_execute_snapshot returned %s", SPI_result_code_string(spi_result)); + + if (spi_result != SPI_OK_INSERT) + ereport(ERROR, + (errcode(ERRCODE_INTERNAL_ERROR), + errmsg("temporal leftovers query on \"%s\" gave unexpected result", + RelationGetRelationName(query_rel)), + errhint("This is most likely due to a rule having rewritten the query."))); + + return SPI_processed != 0; +} + +/* + * Extract fields from a tuple into Datum/nulls arrays + */ +static void +fp_ExtractValues(TupleTableSlot *slot, + Datum targetRange, + Datum *vals, char *nulls) +{ + int natts = slot->tts_tupleDescriptor->natts; + bool isnull; + + for (int i = 0; i < natts; i++) + { + vals[i] = slot_getattr(slot, i + 1, &isnull); + nulls[i] = isnull ? 'n' : ' '; + } + vals[natts] = targetRange; + nulls[natts] = false; +} + +/* + * fp_InitHashTables - + * + * Initialize our internal hash tables. + */ +static void +fp_InitHashTables(void) +{ + HASHCTL ctl; + + memset(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(FP_QueryKey); + ctl.entrysize = sizeof(FP_QueryHashEntry); + fp_query_cache = hash_create("FP query cache", + FP_INIT_QUERYHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); +} + + +/* + * fp_FetchPreparedPlan - + * + * Lookup for a query key in our private hash table of prepared + * and saved SPI execution plans. Return the plan if found or NULL. + */ +static SPIPlanPtr +fp_FetchPreparedPlan(FP_QueryKey *key) +{ + FP_QueryHashEntry *entry; + SPIPlanPtr plan; + + /* + * On the first call initialize the hashtable + */ + if (!fp_query_cache) + fp_InitHashTables(); + + /* + * Lookup for the key + */ + entry = (FP_QueryHashEntry *) hash_search(fp_query_cache, + (void *) key, + HASH_FIND, NULL); + if (entry == NULL) + return NULL; + + /* + * Check whether the plan is still valid. If it isn't, we don't want to + * simply rely on plancache.c to regenerate it; rather we should start + * from scratch and rebuild the query text too. This is to cover cases + * such as table/column renames. We depend on the plancache machinery to + * detect possible invalidations, though. + * + * CAUTION: this check is only trustworthy if the caller has already + * locked the rel. + */ + plan = entry->plan; + if (plan && SPI_plan_is_valid(plan)) + return plan; + + /* + * Otherwise we might as well flush the cached plan now, to free a little + * memory space before we make a new one. + */ + entry->plan = NULL; + if (plan) + SPI_freeplan(plan); + + return NULL; +} + + +/* + * fp_HashPreparedPlan - + * + * Add another plan to our private SPI query plan hashtable. + */ +static void +fp_HashPreparedPlan(FP_QueryKey *key, SPIPlanPtr plan) +{ + FP_QueryHashEntry *entry; + bool found; + + /* + * On the first call initialize the hashtable + */ + if (!fp_query_cache) + fp_InitHashTables(); + + /* + * Add the new plan. We might be overwriting an entry previously found + * invalid by fp_FetchPreparedPlan. + */ + entry = (FP_QueryHashEntry *) hash_search(fp_query_cache, + (void *) key, + HASH_ENTER, &found); + Assert(!found || entry->plan == NULL); + entry->plan = plan; +} diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index 815175a654..5a38178485 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -1195,6 +1195,48 @@ range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeT return false; } +/* + * range_leftover_internal - Sets output1 and output2 to the remaining parts of r1 + * after subtracting r2, or if nothing is left then to the empty range. + * output1 will always be "before" r2 and output2 "after". + */ +void +range_leftover_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2, RangeType **output1, RangeType **output2) +{ + RangeBound lower1, + lower2; + RangeBound upper1, + upper2; + bool empty1, + empty2; + + range_deserialize(typcache, r1, &lower1, &upper1, &empty1); + range_deserialize(typcache, r2, &lower2, &upper2, &empty2); + + if (range_cmp_bounds(typcache, &lower1, &lower2) < 0) + { + lower2.inclusive = !lower2.inclusive; + lower2.lower = false; + *output1 = make_range(typcache, &lower1, &lower2, false); + } + else + { + *output1 = make_empty_range(typcache); + } + + if (range_cmp_bounds(typcache, &upper1, &upper2) > 0) + { + upper2.inclusive = !upper2.inclusive; + upper2.lower = true; + *output2 = make_range(typcache, &upper2, &upper1, false); + } + else + { + *output2 = make_empty_range(typcache); + } +} + /* range -> range aggregate functions */ Datum diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d068d6532e..5537740256 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3883,6 +3883,12 @@ prorettype => 'trigger', proargtypes => '', prosrc => 'RI_FKey_noaction_upd' }, +# Temporal leftovers triggers +{ oid => '8157', descr => 'temporal leftovers trigger function', + proname => 'FP_insert_leftovers', provolatile => 'v', + prorettype => 'trigger', proargtypes => '', + prosrc => 'FP_insert_leftovers' }, + { oid => '1666', proname => 'varbiteq', proleakproof => 't', prorettype => 'bool', proargtypes => 'varbit varbit', prosrc => 'biteq' }, diff --git a/src/include/commands/tablecmds.h b/src/include/commands/tablecmds.h index 89239205d2..8b35a427ae 100644 --- a/src/include/commands/tablecmds.h +++ b/src/include/commands/tablecmds.h @@ -41,6 +41,8 @@ extern void ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, L extern void AlterTableInternal(Oid relid, List *cmds, bool recurse, struct AlterTableUtilityContext *context); +extern void CreateTemporalPrimaryKeyTriggers(Relation rel, Oid constraintOid, Oid indexOid); + extern Oid AlterTableMoveAll(AlterTableMoveAllStmt *stmt); extern ObjectAddress AlterTableNamespace(AlterObjectSchemaStmt *stmt, diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 9ef7f6d768..e6792b7281 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -40,6 +40,7 @@ typedef struct TriggerData Tuplestorestate *tg_oldtable; Tuplestorestate *tg_newtable; const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 62cb6f5054..fafa03c425 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -26,12 +26,14 @@ #include "storage/condition_variable.h" #include "utils/hsearch.h" #include "utils/queryenvironment.h" +#include "utils/rangetypes.h" #include "utils/reltrigger.h" #include "utils/sharedtuplestore.h" #include "utils/snapshot.h" #include "utils/sortsupport.h" #include "utils/tuplesort.h" #include "utils/tuplestore.h" +#include "utils/typcache.h" struct PlanState; /* forward references in this file */ struct ParallelHashJoinState; @@ -388,6 +390,23 @@ typedef struct OnConflictSetState ExprState *oc_WhereClause; /* state for the WHERE clause */ } OnConflictSetState; +/* + * ForPortionOfState + * + * Executor state of a FOR PORTION OF operation. + */ +typedef struct ForPortionOfState +{ + NodeTag type; + + char *fp_rangeName; /* the column/PERIOD named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + bool fp_hasPeriod; /* true iff this is a PERIOD not a range */ + char *fp_periodStartName; /* the PERIOD's start column */ + char *fp_periodEndName; /* the PERIOD's end column */ + Datum fp_targetRange; /* the range from FOR PORTION OF */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -499,6 +518,9 @@ typedef struct ResultRelInfo /* ON CONFLICT evaluation state */ OnConflictSetState *ri_onConflict; + /* FOR PORTION OF evaluation state */ + ForPortionOfState *ri_forPortionOf; + /* partition check expression state (NULL if not set up yet) */ ExprState *ri_PartitionCheckExpr; diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 8cb6def639..84546ac9c6 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -35,6 +35,7 @@ typedef enum NodeTag T_ProjectionInfo, T_JunkFilter, T_OnConflictSetState, + T_ForPortionOfState, T_ResultRelInfo, T_EState, T_TupleTableSlot, @@ -200,6 +201,7 @@ typedef enum NodeTag T_JoinExpr, T_FromExpr, T_OnConflictExpr, + T_ForPortionOfExpr, T_IntoClause, /* @@ -479,6 +481,7 @@ typedef enum NodeTag T_OnConflictClause, T_CTESearchClause, T_CTECycleClause, + T_ForPortionOfClause, T_CommonTableExpr, T_RoleSpec, T_TriggerTransition, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 647dfdc144..d2a59ca755 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -130,6 +130,8 @@ typedef struct Query int resultRelation; /* rtable index of target relation for * INSERT/UPDATE/DELETE; 0 for SELECT */ + ForPortionOfExpr *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */ + bool hasAggs; /* has aggregates in tlist or havingQual */ bool hasWindowFuncs; /* has window functions in tlist */ bool hasTargetSRFs; /* has set-returning functions in tlist */ @@ -1423,6 +1425,19 @@ typedef struct RowMarkClause bool pushedDown; /* pushed down from higher query level? */ } RowMarkClause; +/* + * ForPortionOfClause + * representation of FOR PORTION OF FROM TO + */ +typedef struct ForPortionOfClause +{ + NodeTag type; + char *range_name; + int range_name_location; + Node *target_start; + Node *target_end; +} ForPortionOfClause; + /* * WithClause - * representation of WITH clause @@ -1604,12 +1619,13 @@ typedef struct InsertStmt */ typedef struct DeleteStmt { - NodeTag type; - RangeVar *relation; /* relation to delete from */ - List *usingClause; /* optional using clause for more tables */ - Node *whereClause; /* qualifications */ - List *returningList; /* list of expressions to return */ - WithClause *withClause; /* WITH clause */ + NodeTag type; + RangeVar *relation; /* relation to delete from */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ + List *usingClause; /* optional using clause for more tables */ + Node *whereClause; /* qualifications */ + List *returningList; /* list of expressions to return */ + WithClause *withClause; /* WITH clause */ } DeleteStmt; /* ---------------------- @@ -1618,13 +1634,14 @@ typedef struct DeleteStmt */ typedef struct UpdateStmt { - NodeTag type; - RangeVar *relation; /* relation to update */ - List *targetList; /* the target list (of ResTarget) */ - Node *whereClause; /* qualifications */ - List *fromClause; /* optional from clause for more tables */ - List *returningList; /* list of expressions to return */ - WithClause *withClause; /* WITH clause */ + NodeTag type; + RangeVar *relation; /* relation to update */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ + List *targetList; /* the target list (of ResTarget) */ + Node *whereClause; /* qualifications */ + List *fromClause; /* optional from clause for more tables */ + List *returningList; /* list of expressions to return */ + WithClause *withClause; /* WITH clause */ } UpdateStmt; /* ---------------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 186e89905b..e9b7121bad 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -1893,6 +1893,7 @@ typedef struct ModifyTablePath List *returningLists; /* per-target-table RETURNING tlists */ List *rowMarks; /* PlanRowMarks (non-locking only) */ OnConflictExpr *onconflict; /* ON CONFLICT clause, or NULL */ + ForPortionOfExpr *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */ int epqParam; /* ID of Param for EvalPlanQual re-eval */ } ModifyTablePath; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 01a246d50e..50261b641a 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -231,6 +231,8 @@ typedef struct ModifyTable List *rowMarks; /* PlanRowMarks (non-locking only) */ int epqParam; /* ID of Param for EvalPlanQual re-eval */ OnConflictAction onConflictAction; /* ON CONFLICT action */ + // TODO: Instead of re-using Expr here, break it into pieces like onConflict{Action,Set,Where}? + Node *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */ List *arbiterIndexes; /* List of ON CONFLICT arbiter index OIDs */ List *onConflictSet; /* INSERT ON CONFLICT DO UPDATE targetlist */ List *onConflictCols; /* target column numbers for onConflictSet */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 433437643e..007e9ef458 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1592,4 +1592,31 @@ typedef struct OnConflictExpr List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ } OnConflictExpr; +/*---------- + * ForPortionOfExpr - represents a FOR PORTION OF ... expression + * + * TODO: more notes as needed + *---------- + */ +typedef struct ForPortionOfExpr +{ + NodeTag type; + int range_attno; /* Range column number */ + int start_attno; /* PERIOD start column number */ + int end_attno; /* PERIOD end column number */ + char *range_name; /* Range name */ + char *period_start_name; /* PERIOD start column name */ + char *period_end_name; /* PERIOD end column name */ + Expr *range; /* Range column or expression */ + // TODO: I do really use all these fields?: + Node *startCol; /* Start column if using a PERIOD */ + Node *endCol; /* End column if using a PERIOD */ + Node *targetStart; /* Same type as the range's elements */ + Node *targetEnd; /* Same type as the range's elements */ + Node *targetRange; /* A range from targetStart to targetEnd */ + Oid rangeType; /* type of targetRange */ + Node *overlapsExpr; /* range && targetRange */ + List *rangeSet; /* List of TargetEntrys to set the time column(s) */ +} ForPortionOfExpr; + #endif /* PRIMNODES_H */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index f704d39980..192e2a192b 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -275,7 +275,7 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, List *updateColnosLists, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, - int epqParam); + ForPortionOfExpr *forPortionOf, int epqParam); extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Node *limitOffset, Node *limitCount, diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 43fc561075..7545038a9a 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -316,6 +316,7 @@ PG_KEYWORD("period", PERIOD, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("portion", PORTION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, AS_LABEL) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index ee179082ce..8f1f629c86 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -55,6 +55,7 @@ typedef enum ParseExprKind EXPR_KIND_INSERT_TARGET, /* INSERT target list item */ EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */ + EXPR_KIND_UPDATE_PORTION, /* UPDATE FOR PORTION OF item */ EXPR_KIND_GROUP_BY, /* GROUP BY */ EXPR_KIND_ORDER_BY, /* ORDER BY */ EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */ diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h index 04c302c619..d8c1f1656a 100644 --- a/src/include/utils/rangetypes.h +++ b/src/include/utils/rangetypes.h @@ -147,5 +147,8 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache); extern bool range_split_internal(TypeCacheEntry *typcache, const RangeType *r1, const RangeType *r2, RangeType **output1, RangeType **output2); +extern void range_leftover_internal(TypeCacheEntry *typcache, const RangeType *r1, + const RangeType *r2, RangeType **output1, + RangeType **output2); #endif /* RANGETYPES_H */ diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out new file mode 100644 index 0000000000..c70baad685 --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,674 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF +-- Fails on tables without a temporal PK: +CREATE TABLE for_portion_of_test ( + id int4range PRIMARY KEY, + valid_at tsrange NOT NULL, + name text NOT NULL +); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE +SET name = 'foo'; +ERROR: relation "for_portion_of_test" does not have a temporal primary key +LINE 2: FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE + ^ +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; +ERROR: relation "for_portion_of_test" does not have a temporal primary key +LINE 2: FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; + ^ +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at tsrange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-01-05)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'), +('[4,5)', '(,2018-04-01)', 'four'), +('[5,6)', '(,)', 'five') +; +-- +-- UPDATE tests +-- +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'foo' +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE + ^ +-- Setting the range fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; +ERROR: multiple assignments to same column "valid_at" +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Setting with MAXVALUE lower bound fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MAXVALUE TO NULL +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: MAXVALUE can only be used for the upper bound +-- Setting with MINVALUE upper bound fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO MINVALUE +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: MINVALUE can only be used for the lower bound +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use subquery in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-... + ^ +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM lower(valid_at) TO MAXVALUE +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use column reference in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO MAXVALUE + ^ +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'three^1' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO MAXVALUE +SET name = 'four^2' +WHERE id = '[4,5)'; +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO MAXVALUE +SET name = 'two^2' +WHERE id = '[2,3)'; +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; +-- Updating with a shift/reduce conflict +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE + TO '2019-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; +ERROR: syntax error at or near "'2019-01-01'" +LINE 4: TO '2019-01-01' + ^ +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR) + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+---------------------------------------------------------+---------- + [1,2) | ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018") | one^2 + [1,2) | ["Sat Feb 03 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2 + [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2 + [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2 + [3,4) | ["Thu Mar 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | three + [3,4) | ["Fri Jun 01 00:00:00 2018","Tue Jan 01 00:00:00 2030") | three^1 + [3,4) | ["Tue Jan 01 00:00:00 2030",) | three^1* + [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1 + [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3 + [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2 + [5,6) | (,"Sun Jan 01 00:00:00 2017") | five + [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3 + [5,6) | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | five^3 + [5,6) | ["Tue Jan 01 00:00:00 2019","Wed Jan 01 00:00:00 2020") | five^3 + [5,6) | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | five + [5,6) | ["Tue Jan 01 00:00:00 2030",) | five* + [6,7) | ["Thu Feb 15 00:00:00 2018","Thu Mar 01 08:01:00 2018") | one^2 + [6,7) | ["Thu Mar 01 08:01:00 2018","Thu Mar 01 10:00:00 2018") | one^3 + [6,7) | ["Thu Mar 01 10:00:00 2018","Sat Mar 03 00:00:00 2018") | one^4 + [6,7) | ["Sat Mar 03 00:00:00 2018","Wed Apr 04 00:00:00 2018") | one^4 +(20 rows) + +-- +-- DELETE tests +-- +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+---------------------------------------------------------+--------- + [1,2) | ["Thu Feb 08 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2 + [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2 + [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2 + [3,4) | ["Thu Mar 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | three + [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1 + [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3 + [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2 + [5,6) | (,"Sun Jan 01 00:00:00 2017") | five + [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3 + [5,6) | ["Mon Jan 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | five^3 + [5,6) | ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025") | five +(11 rows) + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + id | valid_at | name +-------+---------------------------------------------------------+--------- + [3,4) | ["Thu Feb 01 00:00:00 2018","Thu Feb 15 00:00:00 2018") | three^3 +(1 row) + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows +CREATE FUNCTION for_portion_of_trigger() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % % of %', TG_WHEN, TG_OP, NEW.valid_at, OLD.valid_at; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; +CREATE TRIGGER trg_for_portion_of_before_insert + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_insert + AFTER INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_update + BEFORE UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_update + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_delete + BEFORE DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_delete + AFTER DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^4' +WHERE id = '[5,6)'; +NOTICE: BEFORE UPDATE ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") of ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025") +NOTICE: BEFORE INSERT ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") of +NOTICE: BEFORE INSERT ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") of +NOTICE: AFTER INSERT ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") of +NOTICE: AFTER INSERT ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") of +NOTICE: AFTER UPDATE ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") of ["Mon Jun 01 00:00:00 2020","Wed Jan 01 00:00:00 2025") +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; +NOTICE: BEFORE DELETE of ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") +NOTICE: BEFORE INSERT ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") of +NOTICE: BEFORE INSERT ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") of +NOTICE: AFTER INSERT ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") of +NOTICE: AFTER INSERT ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") of +NOTICE: AFTER DELETE of ["Sat Jan 01 00:00:00 2022","Wed Jan 01 00:00:00 2025") +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+---------------------------------------------------------+--------- + [1,2) | ["Thu Feb 08 00:00:00 2018","Thu Feb 15 00:00:00 2018") | one^2 + [2,3) | ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018") | two^2 + [3,4) | ["Mon Jan 01 00:00:00 2018","Thu Feb 01 00:00:00 2018") | three^2 + [3,4) | ["Thu Feb 01 00:00:00 2018","Thu Feb 15 00:00:00 2018") | three^3 + [3,4) | ["Thu Feb 15 00:00:00 2018","Thu Mar 01 00:00:00 2018") | three^2 + [3,4) | ["Thu Mar 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | three + [4,5) | (,"Sun Jan 01 00:00:00 2017") | four^1 + [4,5) | ["Sun Jan 01 00:00:00 2017","Thu Feb 01 00:00:00 2018") | four^3 + [4,5) | ["Thu Feb 01 00:00:00 2018","Sun Apr 01 00:00:00 2018") | four^2 + [5,6) | (,"Sun Jan 01 00:00:00 2017") | five + [5,6) | ["Sun Jan 01 00:00:00 2017","Mon Jan 01 00:00:00 2018") | five^3 + [5,6) | ["Mon Jan 01 00:00:00 2018","Fri Jun 01 00:00:00 2018") | five^3 + [5,6) | ["Mon Jun 01 00:00:00 2020","Fri Jan 01 00:00:00 2021") | five + [5,6) | ["Fri Jan 01 00:00:00 2021","Sat Jan 01 00:00:00 2022") | five^4 + [5,6) | ["Sat Jan 01 00:00:00 2022","Sun Jan 01 00:00:00 2023") | five + [5,6) | ["Mon Jan 01 00:00:00 2024","Wed Jan 01 00:00:00 2025") | five +(16 rows) + +-- +-- Now re-run the same tests but with a PERIOD instead of a range: +-- +DROP TABLE for_portion_of_test; +-- Fails on tables without a temporal PK: +CREATE TABLE for_portion_of_test ( + id int4range PRIMARY KEY, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + name text NOT NULL +); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE +SET name = 'foo'; +ERROR: relation "for_portion_of_test" does not have a temporal primary key +LINE 2: FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE + ^ +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; +ERROR: relation "for_portion_of_test" does not have a temporal primary key +LINE 2: FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; + ^ +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '2018-01-02', '2018-02-03', 'one'), +('[1,2)', '2018-02-03', '2018-03-03', 'one'), +('[1,2)', '2018-03-03', '2018-04-04', 'one'), +('[2,3)', '2018-01-01', '2018-01-05', 'two'), +('[3,4)', '2018-01-01', null, 'three'), +('[4,5)', null, '2018-04-01', 'four'), +('[5,6)', null, null, 'five') +; +-- +-- UPDATE tests +-- +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'foo' +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE + ^ +-- Setting the start column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_from = '1990-01-01' +WHERE id = '[5,6)'; +ERROR: multiple assignments to same column "valid_from" +-- Setting the end column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_til = '1999-01-01' +WHERE id = '[5,6)'; +ERROR: multiple assignments to same column "valid_til" +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use subquery in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-... + ^ +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM valid_from TO MAXVALUE +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use column reference in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM valid_from TO MAXVALUE + ^ +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'three^1' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO MAXVALUE +SET name = 'four^2' +WHERE id = '[4,5)'; +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO MAXVALUE +SET name = 'two^2' +WHERE id = '[2,3)'; +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; +-- Updating with a shift/reduce conflict +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE + TO '2019-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; +ERROR: syntax error at or near "'2019-01-01'" +LINE 4: TO '2019-01-01' + ^ +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR) + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; +-- TODO: UPDATE with generated columns too +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; + id | valid_from | valid_til | name +-------+--------------------------+--------------------------+---------- + [1,2) | Tue Jan 02 00:00:00 2018 | Sat Feb 03 00:00:00 2018 | one^2 + [1,2) | Sat Feb 03 00:00:00 2018 | Thu Feb 15 00:00:00 2018 | one^2 + [2,3) | Mon Jan 01 00:00:00 2018 | Fri Jan 05 00:00:00 2018 | two^2 + [3,4) | Mon Jan 01 00:00:00 2018 | Thu Mar 01 00:00:00 2018 | three^2 + [3,4) | Thu Mar 01 00:00:00 2018 | Fri Jun 01 00:00:00 2018 | three + [3,4) | Fri Jun 01 00:00:00 2018 | Tue Jan 01 00:00:00 2030 | three^1 + [3,4) | Tue Jan 01 00:00:00 2030 | | three^1* + [4,5) | | Sun Jan 01 00:00:00 2017 | four^1 + [4,5) | Sun Jan 01 00:00:00 2017 | Thu Feb 01 00:00:00 2018 | four^3 + [4,5) | Thu Feb 01 00:00:00 2018 | Sun Apr 01 00:00:00 2018 | four^2 + [5,6) | | Sun Jan 01 00:00:00 2017 | five + [5,6) | Sun Jan 01 00:00:00 2017 | Mon Jan 01 00:00:00 2018 | five^3 + [5,6) | Mon Jan 01 00:00:00 2018 | Tue Jan 01 00:00:00 2019 | five^3 + [5,6) | Tue Jan 01 00:00:00 2019 | Wed Jan 01 00:00:00 2020 | five^3 + [5,6) | Wed Jan 01 00:00:00 2020 | Tue Jan 01 00:00:00 2030 | five + [5,6) | Tue Jan 01 00:00:00 2030 | | five* + [6,7) | Thu Feb 15 00:00:00 2018 | Thu Mar 01 08:01:00 2018 | one^2 + [6,7) | Thu Mar 01 08:01:00 2018 | Thu Mar 01 10:00:00 2018 | one^3 + [6,7) | Thu Mar 01 10:00:00 2018 | Sat Mar 03 00:00:00 2018 | one^4 + [6,7) | Sat Mar 03 00:00:00 2018 | Wed Apr 04 00:00:00 2018 | one^4 +(20 rows) + +-- +-- DELETE tests +-- +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; + id | valid_from | valid_til | name +-------+--------------------------+--------------------------+--------- + [1,2) | Thu Feb 08 00:00:00 2018 | Thu Feb 15 00:00:00 2018 | one^2 + [2,3) | Mon Jan 01 00:00:00 2018 | Fri Jan 05 00:00:00 2018 | two^2 + [3,4) | Mon Jan 01 00:00:00 2018 | Thu Mar 01 00:00:00 2018 | three^2 + [3,4) | Thu Mar 01 00:00:00 2018 | Sun Apr 01 00:00:00 2018 | three + [4,5) | | Sun Jan 01 00:00:00 2017 | four^1 + [4,5) | Sun Jan 01 00:00:00 2017 | Thu Feb 01 00:00:00 2018 | four^3 + [4,5) | Thu Feb 01 00:00:00 2018 | Sun Apr 01 00:00:00 2018 | four^2 + [5,6) | | Sun Jan 01 00:00:00 2017 | five + [5,6) | Sun Jan 01 00:00:00 2017 | Mon Jan 01 00:00:00 2018 | five^3 + [5,6) | Mon Jan 01 00:00:00 2018 | Fri Jun 01 00:00:00 2018 | five^3 + [5,6) | Mon Jun 01 00:00:00 2020 | Wed Jan 01 00:00:00 2025 | five +(11 rows) + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + id | valid_from | valid_til | name +-------+--------------------------+--------------------------+--------- + [3,4) | Thu Feb 01 00:00:00 2018 | Thu Feb 15 00:00:00 2018 | three^3 +(1 row) + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows +CREATE FUNCTION for_portion_of_trigger2() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % [%,%) of [%,%)', TG_WHEN, TG_OP, NEW.valid_from, NEW.valid_til, OLD.valid_from, OLD.valid_til; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; +CREATE TRIGGER trg_for_portion_of_before_insert + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_insert + AFTER INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_before_update + BEFORE UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_update + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_before_delete + BEFORE DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_delete + AFTER DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^4' +WHERE id = '[5,6)'; +NOTICE: BEFORE UPDATE [Fri Jan 01 00:00:00 2021,Sat Jan 01 00:00:00 2022) of [Mon Jun 01 00:00:00 2020,Wed Jan 01 00:00:00 2025) +NOTICE: BEFORE INSERT [Mon Jun 01 00:00:00 2020,Fri Jan 01 00:00:00 2021) of [,) +NOTICE: BEFORE INSERT [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) of [,) +NOTICE: AFTER INSERT [Mon Jun 01 00:00:00 2020,Fri Jan 01 00:00:00 2021) of [,) +NOTICE: AFTER INSERT [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) of [,) +NOTICE: AFTER UPDATE [Fri Jan 01 00:00:00 2021,Sat Jan 01 00:00:00 2022) of [Mon Jun 01 00:00:00 2020,Wed Jan 01 00:00:00 2025) +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; +NOTICE: BEFORE DELETE [,) of [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) +NOTICE: BEFORE INSERT [Sat Jan 01 00:00:00 2022,Sun Jan 01 00:00:00 2023) of [,) +NOTICE: BEFORE INSERT [Mon Jan 01 00:00:00 2024,Wed Jan 01 00:00:00 2025) of [,) +NOTICE: AFTER INSERT [Sat Jan 01 00:00:00 2022,Sun Jan 01 00:00:00 2023) of [,) +NOTICE: AFTER INSERT [Mon Jan 01 00:00:00 2024,Wed Jan 01 00:00:00 2025) of [,) +NOTICE: AFTER DELETE [,) of [Sat Jan 01 00:00:00 2022,Wed Jan 01 00:00:00 2025) +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; + id | valid_from | valid_til | name +-------+--------------------------+--------------------------+--------- + [1,2) | Thu Feb 08 00:00:00 2018 | Thu Feb 15 00:00:00 2018 | one^2 + [2,3) | Mon Jan 01 00:00:00 2018 | Fri Jan 05 00:00:00 2018 | two^2 + [3,4) | Mon Jan 01 00:00:00 2018 | Thu Feb 01 00:00:00 2018 | three^2 + [3,4) | Thu Feb 01 00:00:00 2018 | Thu Feb 15 00:00:00 2018 | three^3 + [3,4) | Thu Feb 15 00:00:00 2018 | Thu Mar 01 00:00:00 2018 | three^2 + [3,4) | Thu Mar 01 00:00:00 2018 | Sun Apr 01 00:00:00 2018 | three + [4,5) | | Sun Jan 01 00:00:00 2017 | four^1 + [4,5) | Sun Jan 01 00:00:00 2017 | Thu Feb 01 00:00:00 2018 | four^3 + [4,5) | Thu Feb 01 00:00:00 2018 | Sun Apr 01 00:00:00 2018 | four^2 + [5,6) | | Sun Jan 01 00:00:00 2017 | five + [5,6) | Sun Jan 01 00:00:00 2017 | Mon Jan 01 00:00:00 2018 | five^3 + [5,6) | Mon Jan 01 00:00:00 2018 | Fri Jun 01 00:00:00 2018 | five^3 + [5,6) | Mon Jun 01 00:00:00 2020 | Fri Jan 01 00:00:00 2021 | five + [5,6) | Fri Jan 01 00:00:00 2021 | Sat Jan 01 00:00:00 2022 | five^4 + [5,6) | Sat Jan 01 00:00:00 2022 | Sun Jan 01 00:00:00 2023 | five + [5,6) | Mon Jan 01 00:00:00 2024 | Wed Jan 01 00:00:00 2025 | five +(16 rows) + diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 6836f41f09..539e9688eb 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -48,6 +48,7 @@ f_star|f fast_emp4000|t float4_tbl|f float8_tbl|f +for_portion_of_test|t func_index_heap|t hash_f8_heap|t hash_i4_heap|t diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 088387281a..5e78aaf854 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -58,7 +58,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table periods vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_3 create_cast constraints triggers select inherit typed_table periods vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse for_portion_of # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql new file mode 100644 index 0000000000..3b5755ba00 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,562 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF + +-- Fails on tables without a temporal PK: +CREATE TABLE for_portion_of_test ( + id int4range PRIMARY KEY, + valid_at tsrange NOT NULL, + name text NOT NULL +); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE +SET name = 'foo'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at tsrange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-01-05)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'), +('[4,5)', '(,2018-04-01)', 'four'), +('[5,6)', '(,)', 'five') +; + +-- +-- UPDATE tests +-- + +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'foo' +WHERE id = '[5,6)'; + +-- Setting the range fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; + +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Setting with MAXVALUE lower bound fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MAXVALUE TO NULL +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with MINVALUE upper bound fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO MINVALUE +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM lower(valid_at) TO MAXVALUE +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; + +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; + +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO MAXVALUE +SET name = 'four^2' +WHERE id = '[4,5)'; + +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; + +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO MAXVALUE +SET name = 'two^2' +WHERE id = '[2,3)'; + +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; + +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; + +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; + +-- Updating with a shift/reduce conflict +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE + TO '2019-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; + +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; + +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR) + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; + +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; + +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- +-- DELETE tests +-- + +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; + +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; + +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; + +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; + +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; + +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; + +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows + +CREATE FUNCTION for_portion_of_trigger() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % % of %', TG_WHEN, TG_OP, NEW.valid_at, OLD.valid_at; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; + +CREATE TRIGGER trg_for_portion_of_before_insert + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_insert + AFTER INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_update + BEFORE UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_update + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_delete + BEFORE DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_delete + AFTER DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^4' +WHERE id = '[5,6)'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- +-- Now re-run the same tests but with a PERIOD instead of a range: +-- + +DROP TABLE for_portion_of_test; + +-- Fails on tables without a temporal PK: +CREATE TABLE for_portion_of_test ( + id int4range PRIMARY KEY, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + name text NOT NULL +); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE +SET name = 'foo'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO MAXVALUE; + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_from timestamp, + valid_til timestamp, + PERIOD FOR valid_at (valid_from, valid_til), + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '2018-01-02', '2018-02-03', 'one'), +('[1,2)', '2018-02-03', '2018-03-03', 'one'), +('[1,2)', '2018-03-03', '2018-04-04', 'one'), +('[2,3)', '2018-01-01', '2018-01-05', 'two'), +('[3,4)', '2018-01-01', null, 'three'), +('[4,5)', null, '2018-04-01', 'four'), +('[5,6)', null, null, 'five') +; + +-- +-- UPDATE tests +-- + +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'foo' +WHERE id = '[5,6)'; + +-- Setting the start column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_from = '1990-01-01' +WHERE id = '[5,6)'; + +-- Setting the end column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET valid_til = '1999-01-01' +WHERE id = '[5,6)'; + +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM valid_from TO MAXVALUE +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO MAXVALUE +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; + +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; + +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO MAXVALUE +SET name = 'four^2' +WHERE id = '[4,5)'; + +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; + +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM MINVALUE TO MAXVALUE +SET name = 'two^2' +WHERE id = '[2,3)'; + +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; + +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; + +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; + +-- Updating with a shift/reduce conflict +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '1' HOUR TO MINUTE + TO '2019-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; + +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM '2018-03-01' AT TIME ZONE INTERVAL '2' HOUR + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; + +UPDATE for_portion_of_test +FOR PORTION OF valid_at + FROM ('2018-03-01' AT TIME ZONE INTERVAL '2' HOUR) + TO '2019-01-01' +SET name = 'one^4' +WHERE id = '[1,2)'; + +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; + +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; + +-- TODO: UPDATE with generated columns too +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; + +-- +-- DELETE tests +-- + +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; + +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; + +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; + +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; + +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; + +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; + +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows + +CREATE FUNCTION for_portion_of_trigger2() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % [%,%) of [%,%)', TG_WHEN, TG_OP, NEW.valid_from, NEW.valid_til, OLD.valid_from, OLD.valid_til; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; + +CREATE TRIGGER trg_for_portion_of_before_insert + BEFORE INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_insert + AFTER INSERT ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_before_update + BEFORE UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_update + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_before_delete + BEFORE DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); +CREATE TRIGGER trg_for_portion_of_after_delete + AFTER DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger2(); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^4' +WHERE id = '[5,6)'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_from NULLS FIRST, valid_til; -- 2.25.1