From a909ac8e2e7b56897eb8f1506641f381b316b54c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 25 Jun 2021 18:54:35 -0700 Subject: [PATCH v38 6/8] 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 UNBOUNDED keyword as the FROM or the TO (or both) to perform an unbounded update/delete. We also accept functions like NOW(). - Added logic to executor to insert new rows for the "leftover" part of a record touched by a FOR PORTION OF query. - 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. --- .../postgres_fdw/expected/postgres_fdw.out | 5 + contrib/postgres_fdw/sql/postgres_fdw.sql | 4 + contrib/sepgsql/proc.c | 2 +- contrib/unaccent/unaccent.c | 2 +- doc/src/sgml/catalogs.sgml | 4 +- doc/src/sgml/ref/delete.sgml | 69 ++ doc/src/sgml/ref/update.sgml | 87 +++ doc/src/sgml/trigger.sgml | 60 +- src/backend/access/gist/gistvalidate.c | 34 +- src/backend/commands/explain.c | 2 +- src/backend/commands/tablecmds.c | 1 + src/backend/commands/trigger.c | 49 ++ src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 300 +++++++- src/backend/nodes/nodeFuncs.c | 24 + src/backend/optimizer/plan/createplan.c | 6 +- src/backend/optimizer/plan/planner.c | 1 + src/backend/optimizer/util/pathnode.c | 3 +- src/backend/parser/analyze.c | 242 ++++++- src/backend/parser/gram.y | 56 +- 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/parser/parse_merge.c | 2 +- src/backend/rewrite/rewriteHandler.c | 40 + src/backend/utils/adt/ri_triggers.c | 74 ++ src/backend/utils/cache/lsyscache.c | 57 +- src/include/access/stratnum.h | 2 +- src/include/commands/trigger.h | 1 + src/include/nodes/execnodes.h | 26 + src/include/nodes/parsenodes.h | 46 +- src/include/nodes/pathnodes.h | 1 + src/include/nodes/plannodes.h | 1 + src/include/nodes/primnodes.h | 26 + src/include/optimizer/pathnode.h | 2 +- src/include/parser/analyze.h | 3 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/utils/lsyscache.h | 4 +- src/test/regress/expected/for_portion_of.out | 685 ++++++++++++++++++ src/test/regress/expected/opr_sanity.out | 1 + src/test/regress/expected/privileges.out | 18 + src/test/regress/expected/updatable_views.out | 32 + .../regress/expected/without_overlaps.out | 140 ++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/for_portion_of.sql | 531 ++++++++++++++ src/test/regress/sql/opr_sanity.sql | 1 + src/test/regress/sql/privileges.sql | 18 + src/test/regress/sql/updatable_views.sql | 14 + src/test/regress/sql/without_overlaps.sql | 88 +++ 51 files changed, 2727 insertions(+), 64 deletions(-) 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/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 212434711e3..2c1c58961e1 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6362,6 +6362,11 @@ DELETE FROM ft2 DELETE FROM ft2 WHERE ft2.c1 > 2000; ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); +-- Test UPDATE/DELETE with FOR PORTION OF +UPDATE ft2 FOR PORTION OF c3 FROM 'a' TO 'b' SET c1 = 1; +ERROR: foreign tables don't support FOR PORTION OF +DELETE FROM ft2 FOR PORTION OF c3 FROM 'a' TO 'b'; +ERROR: foreign tables don't support FOR PORTION OF -- Test that trigger on remote table works as expected CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ BEGIN diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 371e1319338..684c004adb9 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1571,6 +1571,10 @@ DELETE FROM ft2 DELETE FROM ft2 WHERE ft2.c1 > 2000; ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw'); +-- Test UPDATE/DELETE with FOR PORTION OF +UPDATE ft2 FOR PORTION OF c3 FROM 'a' TO 'b' SET c1 = 1; +DELETE FROM ft2 FOR PORTION OF c3 FROM 'a' TO 'b'; + -- Test that trigger on remote table works as expected CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$ BEGIN diff --git a/contrib/sepgsql/proc.c b/contrib/sepgsql/proc.c index 4899bc2a3e3..20120fd531f 100644 --- a/contrib/sepgsql/proc.c +++ b/contrib/sepgsql/proc.c @@ -161,7 +161,7 @@ sepgsql_proc_drop(Oid functionId) * check db_schema:{remove_name} permission */ object.classId = NamespaceRelationId; - object.objectId = get_func_namespace(functionId); + object.objectId = get_func_namespace(functionId, true); object.objectSubId = 0; audit_name = getObjectIdentity(&object, false); diff --git a/contrib/unaccent/unaccent.c b/contrib/unaccent/unaccent.c index 707962305f8..3b7646ec72a 100644 --- a/contrib/unaccent/unaccent.c +++ b/contrib/unaccent/unaccent.c @@ -449,7 +449,7 @@ unaccent_dict(PG_FUNCTION_ARGS) * Use the "unaccent" dictionary that is in the same schema that this * function is in. */ - Oid procnspid = get_func_namespace(fcinfo->flinfo->fn_oid); + Oid procnspid = get_func_namespace(fcinfo->flinfo->fn_oid, true); const char *dictname = "unaccent"; dictOid = GetSysCacheOid2(TSDICTNAMENSP, Anum_pg_ts_dict_oid, diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index a5ff5c8da55..84a2f7e4106 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -833,7 +833,7 @@ amoppurpose char - Operator purpose, either s for search or + Operator purpose, either s for search, or o for ordering @@ -865,7 +865,7 @@ The B-tree operator family this entry sorts according to, if an - ordering operator; zero if a search operator or portion operator + ordering operator; zero if a search operator diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 7717855bc9e..eade59b93ab 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,39 @@ DELETE FROM [ ONLY ] table_name [ * circumstances. + + If the table has a range or multirange column, + 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 your delete + will only change the span within those bounds. In effect you are deleting any + moment targeted by FOR PORTION OF and no moments outside. + + + + Specifically, after PostgreSQL deletes the existing row, + it will INSERT + new rows whose range or start/end column(s) receive the remaining span outside + the targeted bounds, containing the original values in other columns. + There will be zero to two inserted records, + depending on whether the original span extended before the targeted + FROM, after the targeted TO, both, or neither. + + + + These secondary inserts fire INSERT triggers. First + BEFORE DELETE triggers first, then + BEFORE INSERT, then AFTER INSERT, + then AFTER DELETE. + + + + These secondary inserts do not require INSERT privilege on the table. + This is because conceptually no new information has been added. The inserted rows only preserve + existing data about the untargeted time period. Note this may result in users firing INSERT + triggers who don't have insert privileges, so be careful about SECURITY DEFINER trigger functions! + + The optional RETURNING clause causes DELETE to compute and return value(s) based on each row actually deleted. @@ -116,6 +150,41 @@ DELETE FROM [ ONLY ] table_name [ * + + range_or_period_name + + + The range column or period to use when performing a temporal delete. + + + + + + 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. A + NULL here indicates a delete whose beginning is + unbounded (as with range types). + + + + + + 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. A + NULL here indicates a delete whose end is unbounded + (as with range types). + + + + from_item diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 1c433bec2bb..cfa158ed95b 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 for_portion_of_target ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -51,6 +52,41 @@ UPDATE [ ONLY ] table_name [ * ] [ circumstances. + + If the table has a range or multirange column, + 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 your update + will only change the span within those bounds. In effect you are updating any + moment targeted by FOR PORTION OF and no moments outside. + + + + Specifically, when PostgreSQL updates the existing row, + it will also change the range or start/end column(s) so that their interval + no longer extends beyond the targeted FOR PORTION OF bounds. + Then PostgreSQL will INSERT + new rows whose range or start/end column(s) receive the remaining span outside + the targeted bounds, containing the un-updated values in other columns. + There will be zero to two inserted records, + depending on whether the original span extended before the targeted + FROM, after the targeted TO, both, or neither. + + + + These secondary inserts fire INSERT triggers. First + BEFORE UPDATE triggers first, then + BEFORE INSERT, then AFTER INSERT, + then AFTER UPDATE. + + + + These secondary inserts do not require INSERT privilege on the table. + This is because conceptually no new information has been added. The inserted rows only preserve + existing data about the untargeted time period. Note this may result in users firing INSERT + triggers who don't have insert privileges, so be careful about SECURITY DEFINER trigger functions! + + The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. @@ -114,6 +150,57 @@ UPDATE [ ONLY ] table_name [ * ] [ + + range_or_period_name + + + The range column or period to use when performing a temporal update. + + + + + + for_portion_of_target + + + The interval to update. If you are targeting a range column or PERIOD, + you may give this in the form FROM + start_time TO + end_time. + Otherwise you must use + (expression) + where the expression yields a value for the same type as + range_or_period_name. + + + + + + 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. A + NULL here indicates an update whose beginning is + unbounded (as with range types). + + + + + + 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. A + NULL here indicates an update whose end is unbounded + (as with range types). + + + + column_name diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 31626536a2e..f3e66da014a 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -546,17 +546,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; @@ -824,6 +825,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/gist/gistvalidate.c b/src/backend/access/gist/gistvalidate.c index 0a2395fbaf6..4eb6ff42836 100644 --- a/src/backend/access/gist/gistvalidate.c +++ b/src/backend/access/gist/gistvalidate.c @@ -201,7 +201,7 @@ gistvalidate(Oid opclassoid) } /* GiST supports ORDER BY operators */ - if (oprform->amoppurpose != AMOP_SEARCH) + if (oprform->amoppurpose == AMOP_ORDER) { /* ... but must have matching distance proc */ if (!OidIsValid(get_opfamily_proc(opfamilyoid, @@ -228,24 +228,38 @@ gistvalidate(Oid opclassoid) result = false; } } - else + else if (oprform->amoppurpose == AMOP_SEARCH) { /* Search operators must always return bool */ op_rettype = BOOLOID; } - - /* Check operator signature */ - if (!check_amop_signature(oprform->amopopr, op_rettype, - oprform->amoplefttype, - oprform->amoprighttype)) + else { + /* Nothing else it could be */ ereport(INFO, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), - errmsg("operator family \"%s\" of access method %s contains operator %s with wrong signature", - opfamilyname, "gist", - format_operator(oprform->amopopr)))); + errmsg("operator family \"%s\" of access method %s contains unknown purpose %c for operator %s", + opfamilyname, "gist", oprform->amoppurpose, + format_operator(oprform->amopopr)))); + op_rettype = InvalidOid; result = false; } + + /* Check operator signature */ + if (OidIsValid(op_rettype)) + { + if (!check_amop_signature(oprform->amopopr, op_rettype, + oprform->amoplefttype, + oprform->amoprighttype)) + { + ereport(INFO, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("operator family \"%s\" of access method %s contains operator %s with wrong signature", + opfamilyname, "gist", + format_operator(oprform->amopopr)))); + result = false; + } + } } /* Now check for inconsistent groups of operators/functions */ diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 5771aabf40a..0487b07746d 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4168,7 +4168,7 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) objectname = get_func_name(funcid); if (es->verbose) - namespace = get_namespace_name_or_temp(get_func_namespace(funcid)); + namespace = get_namespace_name_or_temp(get_func_namespace(funcid, true)); } } objecttag = "Function Name"; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 82404ab7aa3..b91a7455b11 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12331,6 +12331,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 29d30bfb6f7..3578fb0a4d4 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -47,6 +47,7 @@ #include "storage/lmgr.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc_hooks.h" #include "utils/inval.h" @@ -2632,6 +2633,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]; @@ -2731,6 +2733,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; @@ -2822,6 +2825,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); @@ -2885,6 +2889,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]; @@ -3020,6 +3025,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++) @@ -3169,6 +3175,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); @@ -3635,6 +3642,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; @@ -3908,6 +3916,7 @@ static SetConstraintState SetConstraintStateCreate(int numalloc); static SetConstraintState SetConstraintStateCopy(SetConstraintState origstate); 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); @@ -4117,6 +4126,7 @@ afterTriggerAddEvent(AfterTriggerEventList *events, newshared->ats_relid == evtshared->ats_relid && newshared->ats_event == evtshared->ats_event && newshared->ats_table == evtshared->ats_table && + newshared->for_portion_of == evtshared->for_portion_of && newshared->ats_firing_id == 0) break; } @@ -4485,6 +4495,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); @@ -6019,6 +6030,43 @@ AfterTriggerPendingOnRel(Oid relid) return false; } +/* ---------- + * ForPortionOfState() + * + * Copys 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, 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; + dst->fp_rangeAttno = src->fp_rangeAttno; + dst->fp_periodStartAttno = src->fp_periodStartAttno; + dst->fp_periodEndAttno = src->fp_periodEndAttno; + + 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() * @@ -6434,6 +6482,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, else new_shared.ats_table = NULL; new_shared.ats_modifiedcols = afterTriggerCopyBitmap(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 4d7c92d63c1..cf239156da9 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1248,6 +1248,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 4913e493199..a0f29285f45 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -65,8 +65,10 @@ #include "optimizer/optimizer.h" #include "rewrite/rewriteHandler.h" #include "storage/lmgr.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -144,6 +146,10 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static void ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -166,6 +172,7 @@ static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag); +static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate); /* @@ -1211,6 +1218,169 @@ ExecInsert(ModifyTableContext *context, return result; } +/* ---------------------------------------------------------------- + * ExecForPortionOfLeftovers + * + * Insert tuples for the untouched portion of a row in a FOR + * PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid) +{ + ModifyTableState *mtstate = context->mtstate; + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + AttrNumber rangeAttno; + Datum oldRange; + bool isNull = false; + TypeCacheEntry *typcache; + ForPortionOfState *fpoState = resultRelInfo->ri_forPortionOf; + TupleTableSlot *oldtupleSlot = fpoState->fp_Existing; + TupleTableSlot *leftoverSlot = fpoState->fp_Leftover; + TupleConversionMap *map = NULL; + HeapTuple oldtuple = NULL; + CmdType oldOperation; + TransitionCaptureState *oldTcs; + FmgrInfo flinfo; + ReturnSetInfo rsi; + bool didInit = false; + bool shouldFree = false; + LOCAL_FCINFO(fcinfo, 2); + + /* + * Get the range of the old pre-UPDATE/DELETE tuple, + * so we can intersect it with the FOR PORTION OF target + * and see if there are any "leftovers" to insert. + * + * We have already locked the tuple in ExecUpdate/ExecDelete + * and it has passed EvalPlanQual. + * Make sure we're looking at the most recent version. + * Otherwise concurrent updates of the same tuple in READ COMMITTED + * could insert conflicting "leftovers". + */ + if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot)) + elog(ERROR, "failed to fetch tuple for FOR PORTION OF"); + + /* + * Get the old range of the record being updated. + * Must read with the attno of the leaf partition being updated + */ + + rangeAttno = forPortionOf->rangeVar->varattno; + if (resultRelInfo->ri_RootResultRelInfo) + map = ExecGetChildToRootMap(resultRelInfo); + if (map != NULL) + rangeAttno = map->attrMap->attnums[rangeAttno - 1]; + oldRange = slot_getattr(oldtupleSlot, rangeAttno, &isNull); + + if (isNull) + elog(ERROR, "found a NULL range in a temporal table"); + + /* + * Get the range's type cache entry. This is worth caching for the whole UPDATE + * as range functions do. + */ + + typcache = fpoState->fp_leftoverstypcache; + if (typcache == NULL) + { + typcache = lookup_type_cache(forPortionOf->rangeType, 0); + fpoState->fp_leftoverstypcache = typcache; + } + + /* + * Get the ranges to the left/right of the targeted range. + * We call a SETOF support function and insert as many leftovers + * as it gives us. Although rangetypes have 0/1/2 leftovers, + * multiranges have 0/1, and other types may have more. + */ + + fmgr_info(forPortionOf->withoutPortionProc, &flinfo); + rsi.type = T_ReturnSetInfo; + rsi.econtext = mtstate->ps.ps_ExprContext; + rsi.expectedDesc = NULL; + rsi.allowedModes = (int) (SFRM_ValuePerCall); + rsi.returnMode = SFRM_ValuePerCall; + rsi.setResult = NULL; + rsi.setDesc = NULL; + + InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi); + fcinfo->args[0].value = oldRange; + fcinfo->args[0].isnull = false; + fcinfo->args[1].value = fpoState->fp_targetRange; + fcinfo->args[1].isnull = false; + + while (true) + { + Datum leftover = FunctionCallInvoke(fcinfo); + + /* Are we done? */ + if (rsi.isDone == ExprEndResult) + break; + + if (fcinfo->isnull) + elog(ERROR, "Got a null from without_portion function"); + + if (!didInit) + { + /* + * Make a copy of the pre-UPDATE row. + * Then we'll overwrite the range column below. + * Convert oldtuple to the base table's format if necessary. + * We need to insert leftovers through the root partition + * so they get routed correctly. + */ + if (map != NULL) + leftoverSlot = execute_attr_map_slot(map->attrMap, + oldtupleSlot, + leftoverSlot); + else + { + oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, &shouldFree); + ExecForceStoreHeapTuple(oldtuple, leftoverSlot, false); + } + + /* Save some mtstate things so we can restore them below. */ + // TODO: Do we need a more systematic way of doing this, + // e.g. a new mtstate or even a separate ForPortionOfLeftovers node? + oldOperation = mtstate->operation; + mtstate->operation = CMD_INSERT; + oldTcs = mtstate->mt_transition_capture; + + didInit = true; + } + + /* store the new range */ + leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover; + leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false; + ExecMaterializeSlot(leftoverSlot); + + /* + * If there are partitions, we must insert into the root table, + * so we get tuple routing. We already set up leftoverSlot + * with the root tuple descriptor. + */ + if (resultRelInfo->ri_RootResultRelInfo) + resultRelInfo = resultRelInfo->ri_RootResultRelInfo; + + ExecSetupTransitionCaptureState(mtstate, estate); + ExecInsert(context, resultRelInfo, leftoverSlot, node->canSetTag, NULL, NULL); + } + + if (didInit) + { + mtstate->operation = oldOperation; + mtstate->mt_transition_capture = oldTcs; + + if (shouldFree) + heap_freetuple(oldtuple); + } +} + /* ---------------------------------------------------------------- * ExecBatchInsert * @@ -1363,7 +1533,8 @@ ExecDeleteAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * * Closing steps of tuple deletion; this invokes AFTER FOR EACH ROW triggers, * including the UPDATE triggers if the deletion is being done as part of a - * cross-partition tuple move. + * cross-partition tuple move. It also inserts leftovers from a FOR PORTION OF + * delete. */ static void ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -1396,6 +1567,11 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ar_delete_trig_tcs = NULL; } + /* Compute leftovers in FOR PORTION OF */ + // TODO: Skip this for FDW deletes? + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid); + /* AFTER ROW DELETE Triggers */ ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple, ar_delete_trig_tcs, changingPart); @@ -1776,7 +1952,11 @@ ExecCrossPartitionUpdate(ModifyTableContext *context, if (resultRelInfo == mtstate->rootResultRelInfo) ExecPartitionCheckEmitError(resultRelInfo, slot, estate); - /* Initialize tuple routing info if not already done. */ + /* + * Initialize tuple routing info if not already done. + * Note whatever we do here must be done in ExecInitModifyTable + * for FOR PORTION OF as well. + */ if (mtstate->mt_partition_tuple_routing == NULL) { Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; @@ -2142,6 +2322,11 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, NULL, NIL, (updateCxt->updateIndexes == TU_Summarizing)); + /* Compute leftovers in FOR PORTION OF */ + // TODO: Skip this for FDW updates? + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid); + /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, NULL, NULL, @@ -4573,6 +4758,117 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } } + /* + * If needed, initialize the target range for FOR PORTION OF. + */ + if (node->forPortionOf) + { + TupleDesc tupDesc; + ForPortionOfExpr *forPortionOf; + Datum targetRange; + bool isNull; + ExprContext *econtext; + ExprState *exprState; + ForPortionOfState *fpoState; + + resultRelInfo = mtstate->resultRelInfo; + if (resultRelInfo->ri_RootResultRelInfo) + resultRelInfo = resultRelInfo->ri_RootResultRelInfo; + + tupDesc = resultRelInfo->ri_RelationDesc->rd_att; + forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + + /* 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"); + + /* Create state for FOR PORTION OF operation */ + + fpoState = makeNode(ForPortionOfState); + fpoState->fp_rangeName = forPortionOf->range_name; + fpoState->fp_rangeType = forPortionOf->rangeType; + fpoState->fp_rangeAttno = forPortionOf->rangeVar->varattno; + fpoState->fp_targetRange = targetRange; + + /* Initialize slot for the existing tuple */ + + fpoState->fp_Existing = + table_slot_create(resultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + /* Create the tuple slot for INSERTing the leftovers */ + + fpoState->fp_Leftover = + ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual); + + /* + * We must attach the ForPortionOfState to all result rels, + * in case of a cross-partition update or triggers firing + * on partitions. + */ + for (i = 0; i < nrels; i++) + { + ForPortionOfState *leafState; + resultRelInfo = &mtstate->resultRelInfo[i]; + + leafState = makeNode(ForPortionOfState); + leafState->fp_rangeName = fpoState->fp_rangeName; + leafState->fp_rangeType = fpoState->fp_rangeType; + leafState->fp_rangeAttno = fpoState->fp_rangeAttno; + leafState->fp_targetRange = fpoState->fp_targetRange; + leafState->fp_Leftover = fpoState->fp_Leftover; + /* Each partition needs a slot matching its tuple descriptor */ + leafState->fp_Existing = + table_slot_create(resultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + resultRelInfo->ri_forPortionOf = leafState; + } + + /* Make sure the root relation has the FOR PORTION OF clause too. */ + if (node->rootRelation > 0) + mtstate->rootResultRelInfo->ri_forPortionOf = fpoState; + + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && + mtstate->mt_partition_tuple_routing == NULL) + { + /* + * We will need tuple routing to insert leftovers. + * Since we are initializing things before ExecCrossPartitionUpdate runs, + * we must do everything it needs as well. + */ + if (mtstate->mt_partition_tuple_routing == NULL) + { + Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; + MemoryContext oldcxt; + + /* Things built here have to last for the query duration. */ + oldcxt = MemoryContextSwitchTo(estate->es_query_cxt); + + mtstate->mt_partition_tuple_routing = + ExecSetupPartitionTupleRouting(estate, rootRel); + + /* + * Before a partition's tuple can be re-routed, it must first be + * converted to the root's format, so we'll need a slot for storing + * such tuples. + */ + Assert(mtstate->mt_root_tuple_slot == NULL); + mtstate->mt_root_tuple_slot = table_slot_create(rootRel, NULL); + + MemoryContextSwitchTo(oldcxt); + } + } + + /* 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, i.e., the diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d2e2af4f811..8c56dc67c30 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2561,6 +2561,14 @@ expression_tree_walker_impl(Node *node, return true; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node; + + if (WALK(forPortionOf->targetRange)) + return true; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -2707,6 +2715,8 @@ query_tree_walker_impl(Query *query, return true; if (WALK(query->mergeJoinCondition)) return true; + if (WALK(query->forPortionOf)) + return true; if (WALK(query->returningList)) return true; if (WALK(query->jointree)) @@ -3586,6 +3596,19 @@ expression_tree_mutator_impl(Node *node, return (Node *) newnode; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *fpo = (ForPortionOfExpr *) node; + ForPortionOfExpr *newnode; + + FLATCOPY(newnode, fpo, ForPortionOfExpr); + MUTATE(newnode->rangeVar, fpo->rangeVar, Var *); + MUTATE(newnode->targetRange, fpo->targetRange, Node *); + MUTATE(newnode->rangeTargetList, fpo->rangeTargetList, List *); + + return (Node *) newnode; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -3767,6 +3790,7 @@ query_tree_mutator_impl(Query *query, MUTATE(query->onConflict, query->onConflict, OnConflictExpr *); MUTATE(query->mergeActionList, query->mergeActionList, List *); MUTATE(query->mergeJoinCondition, query->mergeJoinCondition, Node *); + MUTATE(query->forPortionOf, query->forPortionOf, ForPortionOfExpr *); MUTATE(query->returningList, query->returningList, List *); MUTATE(query->jointree, query->jointree, FromExpr *); MUTATE(query->setOperations, query->setOperations, Node *); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index fe5a323cfd7..0519106da1f 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -313,7 +313,7 @@ static ModifyTable *make_modifytable(PlannerInfo *root, Plan *subplan, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam); + ForPortionOfExpr *forPortionOf, int epqParam); static GatherMerge *create_gather_merge_plan(PlannerInfo *root, GatherMergePath *best_path); @@ -2833,6 +2833,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->onconflict, best_path->mergeActionLists, best_path->mergeJoinConditions, + best_path->forPortionOf, best_path->epqParam); copy_generic_path_info(&plan->plan, &best_path->path); @@ -7029,7 +7030,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTable *node = makeNode(ModifyTable); List *fdw_private_list; @@ -7095,6 +7096,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 948afd90948..0a85a89356b 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1988,6 +1988,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, parse->onConflict, mergeActionLists, mergeJoinConditions, + parse->forPortionOf, assign_special_exec_param(root)); } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 54e042a8a59..f9a12716769 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3725,7 +3725,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTablePath *pathnode = makeNode(ModifyTablePath); @@ -3791,6 +3791,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->returningLists = returningLists; pathnode->rowMarks = rowMarks; pathnode->onconflict = onconflict; + pathnode->forPortionOf = forPortionOf; pathnode->epqParam = epqParam; pathnode->mergeActionLists = mergeActionLists; pathnode->mergeJoinConditions = mergeJoinConditions; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 28fed9d87f6..4ca77b9d3e9 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -24,7 +24,10 @@ #include "postgres.h" +#include "access/gist.h" +#include "access/stratnum.h" #include "access/sysattr.h" +#include "catalog/pg_am.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -47,9 +50,11 @@ #include "parser/parse_relation.h" #include "parser/parse_target.h" #include "parser/parse_type.h" +#include "parser/parser.h" #include "parser/parsetree.h" #include "utils/backend_status.h" #include "utils/builtins.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -58,10 +63,16 @@ post_parse_analyze_hook_type post_parse_analyze_hook = NULL; static Query *transformOptionalSelectInto(ParseState *pstate, Node *parseTree); +static Node *addForPortionOfWhereConditions(Query *qry, ForPortionOfClause *forPortionOf, + Node *whereClause); static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt); static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt); static OnConflictExpr *transformOnConflictClause(ParseState *pstate, OnConflictClause *onConflictClause); +static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOfClause, + bool isUpdate); static int count_rowexpr_columns(ParseState *pstate, Node *expr); static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt); static Query *transformValuesClause(ParseState *pstate, SelectStmt *stmt); @@ -476,6 +487,20 @@ stmt_requires_parse_analysis(RawStmt *parseTree) return result; } +static Node * +addForPortionOfWhereConditions(Query *qry, ForPortionOfClause *forPortionOf, Node *whereClause) +{ + if (forPortionOf) + { + if (whereClause) + return (Node *) makeBoolExpr(AND_EXPR, list_make2(qry->forPortionOf->overlapsExpr, whereClause), -1); + else + return qry->forPortionOf->overlapsExpr; + } + else + return whereClause; +} + /* * analyze_requires_snapshot * Returns true if a snapshot must be set before doing parse analysis @@ -509,6 +534,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_DELETE; @@ -547,7 +573,11 @@ 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, false); + + whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause); + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList, @@ -1183,7 +1213,7 @@ transformOnConflictClause(ParseState *pstate, * Now transform the UPDATE subexpressions. */ onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, @@ -1213,6 +1243,187 @@ transformOnConflictClause(ParseState *pstate, return result; } +/* + * transformForPortionOfClause + * + * Transforms a ForPortionOfClause in an UPDATE/DELETE statement. + * + * - Look up the range/period requested. + * - Build a compatible range value from the FROM and TO expressions. + * - Build an "overlaps" expression for filtering. + * - For UPDATEs, build an "intersects" expression the rewriter can add + * to the targetList to change the temporal bounds. + */ +static ForPortionOfExpr * +transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOf, + bool isUpdate) +{ + Relation targetrel = pstate->p_target_relation; + RTEPermissionInfo *target_perminfo = pstate->p_target_nsitem->p_perminfo; + char *range_name = forPortionOf->range_name; + char *range_type_namespace = NULL; + char *range_type_name = NULL; + int range_attno = InvalidAttrNumber; + Form_pg_attribute attr; + Oid opclass; + Oid opfamily; + Oid opcintype; + Oid funcid = InvalidOid; + StrategyNumber strat; + Oid opid; + ForPortionOfExpr *result; + Var *rangeVar; + Node *targetExpr; + + /* We don't support FOR PORTION OF FDW queries. */ + if (targetrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("foreign tables don't support FOR PORTION OF"))); + + result = makeNode(ForPortionOfExpr); + + /* Look up the FOR PORTION OF name requested. */ + range_attno = attnameAttNum(targetrel, range_name, false); + if (range_attno == InvalidAttrNumber) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column or period \"%s\" of relation \"%s\" does not exist", + range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + attr = TupleDescAttr(targetrel->rd_att, range_attno - 1); + + rangeVar = makeVar( + rtindex, + range_attno, + attr->atttypid, + attr->atttypmod, + attr->attcollation, + 0); + rangeVar->location = forPortionOf->location; + result->rangeVar = rangeVar; + result->rangeType = attr->atttypid; + if (!get_typname_and_namespace(attr->atttypid, &range_type_name, &range_type_namespace)) + elog(ERROR, "cache lookup failed for type %u", attr->atttypid); + + + if (forPortionOf->target) + /* + * We were already given an expression for the target, + * so we don't have to build anything. + */ + targetExpr = forPortionOf->target; + else + { + /* 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(targetrel)), + parser_errposition(pstate, forPortionOf->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 UNBOUNDED in either place. + */ + targetExpr = (Node *) makeFuncCall( + list_make2(makeString(range_type_namespace), makeString(range_type_name)), + list_make2(forPortionOf->target_start, forPortionOf->target_end), + COERCE_EXPLICIT_CALL, + forPortionOf->location); + } + result->targetRange = transformExpr(pstate, targetExpr, EXPR_KIND_UPDATE_PORTION); + + /* + * Build overlapsExpr to use in the whereClause. + * This means we only hit rows matching the FROM & TO bounds. + * We must look up the overlaps operator (usually "&&"). + */ + opclass = GetDefaultOpClass(attr->atttypid, GIST_AM_OID); + strat = RTOverlapStrategyNumber; + GetOperatorFromWellKnownStrategy(opclass, InvalidOid, &opid, &strat); + result->overlapsExpr = (Node *) makeSimpleA_Expr(AEXPR_OP, get_opname(opid), + (Node *) copyObject(rangeVar), targetExpr, + forPortionOf->location); + + /* + * Look up the withoutPortionOper so we can compute the leftovers. + * Leftovers will be old_range @- target_range + * (one per element of the result). + */ + funcid = InvalidOid; + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_WITHOUT_PORTION_PROC); + + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify a without_overlaps support function for type %s", format_type_be(opcintype)), + errhint("Define a without_overlaps support function for operator class \"%d\" for access method \"%s\".", + opclass, "gist")); + + result->withoutPortionProc = funcid; + + if (isUpdate) + { + /* + * Now make sure we update the start/end time of the record. + * For a range col (r) this is `r = r * targetRange`. + */ + Oid funcnamespace; + char *funcname; + char *funcnamespacename; + Expr *rangeTLEExpr; + TargetEntry *tle; + + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_INTERSECT_PROC); + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify an intersect support function for type %s", format_type_be(opcintype)), + errhint("Define an intersect support function for operator class \"%d\" for access method \"%s\".", + opclass, "gist")); + + // TODO: do we really need to go through the name here? + funcname = get_func_name(funcid); + if (!funcname) + elog(ERROR, "cache lookup failed for function %u", funcid); + funcnamespace = get_func_namespace(funcid, false); + funcnamespacename = get_namespace_name(funcnamespace); + if (!funcnamespacename) + elog(ERROR, "cache lookup failed for namespace %u", funcnamespace); + + rangeTLEExpr = (Expr *) makeFuncCall( + list_make2(makeString(funcnamespacename), makeString(funcname)), + list_make2(copyObject(rangeVar), targetExpr), + COERCE_EXPLICIT_CALL, + forPortionOf->location); + rangeTLEExpr = (Expr *) transformExpr(pstate, (Node *) rangeTLEExpr, EXPR_KIND_UPDATE_PORTION); + + /* Make a TLE to set the range column */ + result->rangeTargetList = NIL; + tle = makeTargetEntry(rangeTLEExpr, range_attno, range_name, false); + result->rangeTargetList = lappend(result->rangeTargetList, tle); + + /* Mark the range column as requiring update permissions */ + target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols, + range_attno - FirstLowInvalidHeapAttributeNumber); + } + else + result->rangeTargetList = NIL; + + result->range_name = range_name; + + return result; +} /* * BuildOnConflictExcludedTargetlist @@ -2420,6 +2631,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_UPDATE; @@ -2437,6 +2649,10 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) stmt->relation->inh, true, ACL_UPDATE); + + if (stmt->forPortionOf) + qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf, true); + nsitem = pstate->p_target_nsitem; /* subqueries in FROM cannot access the result relation */ @@ -2453,7 +2669,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; - qual = transformWhereClause(pstate, stmt->whereClause, + whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause); + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList, @@ -2463,7 +2680,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->rteperminfos = pstate->p_rteperminfos; @@ -2482,7 +2699,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) * handle SET clause in UPDATE/MERGE/INSERT ... ON CONFLICT UPDATE */ List * -transformUpdateTargetList(ParseState *pstate, List *origTlist) +transformUpdateTargetList(ParseState *pstate, List *origTlist, ForPortionOfExpr *forPortionOf) { List *tlist = NIL; RTEPermissionInfo *target_perminfo; @@ -2535,6 +2752,21 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) errhint("SET target columns cannot be qualified with the relation name.") : 0, parser_errposition(pstate, origTarget->location))); + /* + * If this is a FOR PORTION OF update, + * forbid directly setting the range column, + * since that would conflict with the implicit updates. + */ + if (forPortionOf != NULL) + { + if (attrno == forPortionOf->rangeVar->varattno) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("can't directly assign to \"%s\" in a FOR PORTION OF update", + origTarget->name), + parser_errposition(pstate, origTarget->location))); + } + updateTargetListEntry(pstate, tle, origTarget->name, attrno, origTarget->indirection, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4addf4d533e..3bcd0c54216 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -257,6 +257,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; @@ -554,6 +555,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 @@ -765,7 +767,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH - PERIOD PLACING PLAN PLANS POLICY + PERIOD PLACING PLAN PLANS POLICY PORTION POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION @@ -887,6 +889,17 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * Like the UNBOUNDED PRECEDING/FOLLOWING case, NESTED is assigned a lower * precedence than PATH to fix ambiguity in the json_table production. */ +/* + * 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. + * TODO: I need to figure out a %prec solution before this gets committed! + */ +%nonassoc YEAR_P MONTH_P DAY_P HOUR_P MINUTE_P +%nonassoc TO %nonassoc UNBOUNDED NESTED /* ideally would have same precedence as IDENT */ %nonassoc IDENT PARTITION RANGE ROWS GROUPS PRECEDING FOLLOWING CUBE ROLLUP SET KEYS OBJECT_P SCALAR VALUE_P WITH WITHOUT PATH @@ -12353,14 +12366,16 @@ 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; } @@ -12423,6 +12438,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 @@ -12431,10 +12447,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; } @@ -13903,6 +13920,27 @@ relation_expr_opt_alias: relation_expr %prec UMINUS } ; +for_portion_of_clause: + FOR PORTION OF ColId '(' a_expr ')' + { + ForPortionOfClause *n = makeNode(ForPortionOfClause); + n->range_name = $4; + n->location = @4; + n->target = $6; + $$ = n; + } + | FOR PORTION OF ColId FROM a_expr TO a_expr + { + ForPortionOfClause *n = makeNode(ForPortionOfClause); + n->range_name = $4; + n->location = @4; + n->target_start = $6; + n->target_end = $8; + $$ = n; + } + | /*EMPTY*/ { $$ = NULL; } + ; + /* * TABLESAMPLE decoration in a FROM item */ @@ -17797,6 +17835,7 @@ unreserved_keyword: | PLAN | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -18425,6 +18464,7 @@ bare_label_keyword: | PLAN | PLANS | POLICY + | PORTION | POSITION | PRECEDING | PREPARE diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index bee7d8346a3..00edaf83bab 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -576,6 +576,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 @@ -967,6 +974,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 44529bb49e6..14ffb7ba649 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: case T_MergeAction: (void) expression_tree_walker(node, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index d2db69a3f90..5c99bb8848f 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -586,6 +586,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 @@ -1859,6 +1862,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 @@ -3150,6 +3156,8 @@ ParseExprKindName(ParseExprKind exprKind) return "UPDATE"; case EXPR_KIND_MERGE_WHEN: return "MERGE WHEN"; + 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 9b23344a3b1..99921d713be 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2658,6 +2658,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/parser/parse_merge.c b/src/backend/parser/parse_merge.c index 87df79027d7..56f676a1f1f 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -385,7 +385,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) pstate->p_is_insert = false; action->targetList = transformUpdateTargetList(pstate, - mergeWhenClause->targetList); + mergeWhenClause->targetList, NULL); } break; case CMD_DELETE: diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index e1d805d113e..b76253b4198 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3667,6 +3667,30 @@ rewriteTargetView(Query *parsetree, Relation view) &parsetree->hasSubLinks); } + if (parsetree->forPortionOf && parsetree->commandType == CMD_UPDATE) + { + /* + * Like the INSERT/UPDATE code above, update the resnos in the + * auxiliary UPDATE targetlist to refer to columns of the base + * relation. + */ + foreach(lc, parsetree->forPortionOf->rangeTargetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + TargetEntry *view_tle; + + if (tle->resjunk) + continue; + + view_tle = get_tle_by_resno(view_targetlist, tle->resno); + if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var)) + tle->resno = ((Var *) view_tle->expr)->varattno; + else + elog(ERROR, "attribute number %d not found in view targetlist", + tle->resno); + } + } + /* * For UPDATE/DELETE/MERGE, pull up any WHERE quals from the view. We * know that any Vars in the quals must reference the one base relation, @@ -4006,6 +4030,22 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length) else if (event == CMD_UPDATE) { Assert(parsetree->override == OVERRIDING_NOT_SET); + /* + * Update FOR PORTION OF column(s) automatically. Don't + * do this until we're done rewriting a view update, so + * that we don't add the same update on the recursion. + */ + if (parsetree->forPortionOf && + rt_entry_relation->rd_rel->relkind != RELKIND_VIEW) + { + ListCell *tl; + foreach(tl, parsetree->forPortionOf->rangeTargetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(tl); + parsetree->targetList = lappend(parsetree->targetList, tle); + } + } + parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index db30a6cc1e4..cbfb50f1287 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -228,6 +228,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int forPortionOfParam, Datum forPortionOf, bool detectNewRows, int expect_OK); static void ri_ExtractValues(Relation rel, TupleTableSlot *slot, const RI_ConstraintInfo *riinfo, bool rel_is_pk, @@ -236,6 +237,11 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool partgone) pg_attribute_noreturn(); +static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo); +static Datum restrict_cascading_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo, + TupleTableSlot *oldslot); /* @@ -450,6 +456,7 @@ RI_FKey_check(TriggerData *trigdata) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, NULL, newslot, + -1, (Datum) 0, pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE, SPI_OK_SELECT); @@ -615,6 +622,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, result = ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* treat like update */ SPI_OK_SELECT); @@ -714,6 +722,8 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) TupleTableSlot *oldslot; RI_QueryKey qkey; SPIPlanPtr qplan; + int targetRangeParam = -1; + Datum targetRange = (Datum) 0; riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, trigdata->tg_relation, true); @@ -804,9 +814,16 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) /* * We have a plan now. Run it to check for existing references. */ + if (trigdata->tg_temporal) + { + targetRangeParam = riinfo->nkeys - 1; + targetRange = restrict_cascading_range(trigdata->tg_temporal, riinfo, oldslot); + } + ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + targetRangeParam, targetRange, true, /* must detect new rows */ SPI_OK_SELECT); @@ -913,6 +930,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_DELETE); @@ -1034,6 +1052,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, newslot, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1266,6 +1285,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -2413,6 +2433,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int forPortionOfParam, Datum forPortionOf, bool detectNewRows, int expect_OK) { Relation query_rel, @@ -2468,6 +2489,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk, vals, nulls); } + /* Add one more query param if we are using FOR PORTION OF */ + if (forPortionOf) + { + vals[forPortionOfParam] = forPortionOf; + nulls[forPortionOfParam] = ' '; + } /* * In READ COMMITTED mode, we just need to use an up-to-date regular @@ -3144,3 +3171,50 @@ RI_FKey_trigger_type(Oid tgfoid) return RI_TRIGGER_NONE; } + +/* + * fpo_targets_pk_range + * + * Returns true iff the primary key referenced by riinfo includes the range + * column targeted by the FOR PORTION OF clause (according to tg_temporal). + */ +static bool +fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo) +{ + if (tg_temporal == NULL) + return false; + + return riinfo->pk_attnums[riinfo->nkeys - 1] == tg_temporal->fp_rangeAttno; +} + +/* + * restrict_cascading_range - + * + * Returns a Datum of RangeTypeP holding the appropriate timespan + * to target child records when we CASCADE/SET NULL/SET DEFAULT. + * + * In a normal UPDATE/DELETE this should be the parent's own valid time, + * but if there was a FOR PORTION OF clause, then we should use that to + * trim down the parent's span further. + */ +static Datum +restrict_cascading_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot) +{ + Datum pkRecordRange; + bool isnull; + + pkRecordRange = slot_getattr(oldslot, riinfo->pk_attnums[riinfo->nkeys - 1], &isnull); + if (isnull) + elog(ERROR, "application time should not be null"); + + if (fpo_targets_pk_range(tg_temporal, riinfo)) + { + RangeType *r1 = DatumGetRangeTypeP(pkRecordRange); + RangeType *r2 = DatumGetRangeTypeP(tg_temporal->fp_targetRange); + Oid rngtypid = RangeTypeGetOid(r1); + TypeCacheEntry *typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO); + return RangeTypePGetDatum(range_intersect_internal(typcache, r1, r2)); + } + else + return pkRecordRange; +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 48a280d089b..b67c0c03b97 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1629,7 +1629,7 @@ get_func_name(Oid funcid) * Returns the pg_namespace OID associated with a given function. */ Oid -get_func_namespace(Oid funcid) +get_func_namespace(Oid funcid, bool missing_ok) { HeapTuple tp; @@ -1643,6 +1643,8 @@ get_func_namespace(Oid funcid) ReleaseSysCache(tp); return result; } + else if (!missing_ok) + elog(ERROR, "cache lookup failed for function %u", funcid); else return InvalidOid; } @@ -2188,6 +2190,59 @@ get_typisdefined(Oid typid) return false; } +/* + * get_typname + * + * Returns the name of a given type + * + * Returns a palloc'd copy of the string, or NULL if no such type. + */ +char * +get_typname(Oid typid) +{ + HeapTuple tp; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + char *result; + + result = pstrdup(NameStr(typtup->typname)); + ReleaseSysCache(tp); + return result; + } + else + return NULL; +} + +/* + * get_typname_and_namespace + * + * Returns the name and namespace of a given type + * + * Returns true if one found, or false if not. + */ +bool +get_typname_and_namespace(Oid typid, char **typname, char **typnamespace) +{ + HeapTuple tp; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + + *typname = pstrdup(NameStr(typtup->typname)); + *typnamespace = get_namespace_name(typtup->typnamespace); + ReleaseSysCache(tp); + /* *typnamespace is NULL if it wasn't found: */ + return *typnamespace; + } + else + return false; +} + /* * get_typlen * diff --git a/src/include/access/stratnum.h b/src/include/access/stratnum.h index 8a47d3c9ec8..b801ef1e3c0 100644 --- a/src/include/access/stratnum.h +++ b/src/include/access/stratnum.h @@ -79,7 +79,7 @@ typedef uint16 StrategyNumber; #define RTOldBelowStrategyNumber 29 /* for old spelling of <<| */ #define RTOldAboveStrategyNumber 30 /* for old spelling of |>> */ -#define RTMaxStrategyNumber 30 +#define RTMaxStrategyNumber 32 #endif /* STRATNUM_H */ diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 8a5a9fe6422..3d156483325 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -41,6 +41,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 15a42d47dfe..06df800f331 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -42,12 +42,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; @@ -429,6 +431,27 @@ typedef struct MergeActionState ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */ } MergeActionState; +/* + * 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 */ + int fp_rangeAttno; /* the attno of the range column (or 0 for a PERIOD) */ + int fp_periodStartAttno; /* the attno of the PERIOD start column (or 0 for a range) */ + int fp_periodEndAttno; /* the attno of the PERIOD end column (or 0 for a range) */ + Datum fp_targetRange; /* the range from FOR PORTION OF */ + TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */ + TupleTableSlot *fp_Existing; /* slot to store existing target tuple in */ + TupleTableSlot *fp_Leftover; /* slot to store leftover */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -551,6 +574,9 @@ typedef struct ResultRelInfo /* for MERGE, expr state for checking the join condition */ ExprState *ri_MergeJoinCondition; + /* 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/parsenodes.h b/src/include/nodes/parsenodes.h index 1cb7b02c5c2..cc7bfb0f294 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -142,6 +142,9 @@ typedef struct Query */ int resultRelation pg_node_attr(query_jumble_ignore); + /* FOR PORTION OF clause for UPDATE/DELETE */ + ForPortionOfExpr *forPortionOf; + /* has aggregates in tlist or havingQual */ bool hasAggs pg_node_attr(query_jumble_ignore); /* has window functions in tlist */ @@ -1585,6 +1588,21 @@ typedef struct RowMarkClause bool pushedDown; /* pushed down from higher query level? */ } RowMarkClause; +/* + * ForPortionOfClause + * representation of FOR PORTION OF FROM TO + * or FOR PORTION OF () + */ +typedef struct ForPortionOfClause +{ + NodeTag type; + char *range_name; + int location; + Node *target; + Node *target_start; + Node *target_end; +} ForPortionOfClause; + /* * WithClause - * representation of WITH clause @@ -2057,12 +2075,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; /* ---------------------- @@ -2071,13 +2090,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 14ccfc1ac1c..26d06fcad2d 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2385,6 +2385,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 */ List *mergeActionLists; /* per-target-table lists of actions for * MERGE */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 1aeeaec95e1..d972298cde7 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -243,6 +243,7 @@ typedef struct ModifyTable List *rowMarks; /* PlanRowMarks (non-locking only) */ int epqParam; /* ID of Param for EvalPlanQual re-eval */ OnConflictAction onConflictAction; /* ON CONFLICT action */ + 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 ea47652adb8..01a101d3776 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2332,4 +2332,30 @@ typedef struct OnConflictExpr List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ } OnConflictExpr; +/*---------- + * ForPortionOfExpr - represents a FOR PORTION OF ... expression + * + * We set up an expression to make a range from the FROM/TO bounds, + * so that we can use range operators with it. + * + * Then we set up an overlaps expression between that and the range column, + * so that we can find the rows we need to update/delete. + * + * In the executor we'll also build an intersect expression between the + * targeted range and the range column, so that we can update the start/end + * bounds of the UPDATE'd record. + *---------- + */ +typedef struct ForPortionOfExpr +{ + NodeTag type; + Var *rangeVar; /* Range column */ + char *range_name; /* Range name */ + Node *targetRange; /* FOR PORTION OF bounds as a range */ + Oid rangeType; /* type of targetRange */ + Node *overlapsExpr; /* range && targetRange */ + List *rangeTargetList; /* List of TargetEntrys to set the time column(s) */ + Oid withoutPortionProc; /* proc for old_range @- target_range */ +} ForPortionOfExpr; + #endif /* PRIMNODES_H */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index f00bd55f393..0002dcf782a 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -285,7 +285,7 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - 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/analyze.h b/src/include/parser/analyze.h index 28b66fccb43..6c70dae1452 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -43,7 +43,8 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist, List *stmtcols, List *icolumns, List *attrnos, bool strip_indirection); extern List *transformUpdateTargetList(ParseState *pstate, - List *origTlist); + List *origTlist, + ForPortionOfExpr *forPortionOf); extern List *transformReturningList(ParseState *pstate, List *returningList, ParseExprKind exprKind); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f9a4afd4723..339f094a80d 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -344,6 +344,7 @@ PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plan", PLAN, UNRESERVED_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 5b781d87a9d..927f91b3191 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -56,6 +56,7 @@ typedef enum ParseExprKind EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */ EXPR_KIND_MERGE_WHEN, /* MERGE WHEN [NOT] MATCHED condition */ + 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/lsyscache.h b/src/include/utils/lsyscache.h index 20446f6f836..e7ad513146a 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -121,7 +121,7 @@ extern Oid get_negator(Oid opno); extern RegProcedure get_oprrest(Oid opno); extern RegProcedure get_oprjoin(Oid opno); extern char *get_func_name(Oid funcid); -extern Oid get_func_namespace(Oid funcid); +extern Oid get_func_namespace(Oid funcid, bool missing_ok); extern Oid get_func_rettype(Oid funcid); extern int get_func_nargs(Oid funcid); extern Oid get_func_signature(Oid funcid, Oid **argtypes, int *nargs); @@ -145,6 +145,8 @@ extern Oid get_rel_relam(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes); extern bool get_typisdefined(Oid typid); +extern char *get_typname(Oid typid); +extern bool get_typname_and_namespace(Oid typid, char **typname, char **typnamespace); extern int16 get_typlen(Oid typid); extern bool get_typbyval(Oid typid); extern void get_typlenbyval(Oid typid, int16 *typlen, bool *typbyval); 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 00000000000..11ab59aeb40 --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,685 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at tsrange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2020-01-01)', 'one'); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' +SET name = 'foo'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO NULL; +SELECT * FROM for_portion_of_test; + id | valid_at | name +-------+---------------------------------------------------------+------ + [1,2) | ["Mon Jan 15 00:00:00 2018","Tue Jan 01 00:00:00 2019") | foo + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | one + [1,2) | ["Tue Jan 01 00:00:00 2019","Tue Jan 15 00:00:00 2019") | one +(3 rows) + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at tsrange, + valid2_at tsrange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); +UPDATE for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL +SET name = 'foo'; +SELECT * FROM for_portion_of_test; + id | valid1_at | valid2_at | name +-------+---------------------------------------------------------+---------------------------------------------------------+------ + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2025") | foo + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2025") | one +(2 rows) + +UPDATE for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL +SET name = 'bar'; +SELECT * FROM for_portion_of_test; + id | valid1_at | valid2_at | name +-------+---------------------------------------------------------+---------------------------------------------------------+------ + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Feb 03 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one +(4 rows) + +DELETE FROM for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test; + id | valid1_at | valid2_at | name +-------+---------------------------------------------------------+---------------------------------------------------------+------ + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Wed Jan 01 00:00:00 2025") | bar + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo +(4 rows) + +DELETE FROM for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test; + id | valid1_at | valid2_at | name +-------+---------------------------------------------------------+---------------------------------------------------------+------ + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | one + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Thu Jan 01 00:00:00 2015","Mon Jan 15 00:00:00 2018") | foo + [1,2) | ["Tue Jan 02 00:00:00 2018","Mon Jan 15 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | bar + [1,2) | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | ["Mon Jan 15 00:00:00 2018","Sat Jan 20 00:00:00 2018") | bar +(4 rows) + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, tsrange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test; + id | valid_at | name +-------+---------------------------------------------------------+-------------- + [1,2) | | 1 null + [1,2) | empty | 1 empty + | | + [1,2) | (,) | NULL to NULL + | ["Mon Jan 01 00:00:00 2018","Tue Jan 01 00:00:00 2019") | NULL to NULL +(5 rows) + +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 NULL +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 NULL + ^ +-- Setting the range fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO NULL +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; +ERROR: can't directly assign to "valid_at" in a FOR PORTION OF update +LINE 3: SET valid_at = '[1990-01-01,1999-01-01)' + ^ +-- The wrong type fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM 1 TO 4 +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: function pg_catalog.tsrange(integer, integer) does not exist +LINE 2: FOR PORTION OF valid_at FROM 1 TO 4 + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- 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 lower(valid_at) TO NULL +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 NULL + ^ +-- 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 NULL +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 NULL 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 NULL 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 NULL +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 NULL TO NULL +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)'; +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (tsrange('2018-03-10', '2018-03-17')) +SET name = 'one^5' +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","Sat Mar 10 00:00:00 2018") | one^4 + [6,7) | ["Sat Mar 10 00:00:00 2018","Sat Mar 17 00:00:00 2018") | one^5 + [6,7) | ["Sat Mar 17 00:00:00 2018","Wed Apr 04 00:00:00 2018") | one^4 +(22 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 + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after + AFTER INSERT OR UPDATE OR 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) + +DROP FUNCTION for_portion_of_trigger CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to trigger trg_for_portion_of_before on table for_portion_of_test +drop cascades to trigger trg_for_portion_of_after on table for_portion_of_test +-- Triggers with a custom transition table name: +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'TG_OP = %', TG_OP; + IF TG_OP = 'INSERT' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, NEW table = %', + TG_NAME, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, OLD table = %, NEW table = %', + TG_NAME, TG_LEVEL, + (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table), + (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'DELETE' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, OLD table = %', + TG_NAME, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table); + END IF; + RETURN NULL; +END; +$$; +CREATE TRIGGER for_portion_of_test_insert_trig +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_insert_trig_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_update_trig +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_update_trig_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_delete_trig +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_delete_trig_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +NOTICE: TG_OP = INSERT +NOTICE: trigger = for_portion_of_test_insert_trig, TG_LEVEL = ROW, NEW table = ("[1,2)","[01-01-2018,01-15-2018)",one), ("[1,2)","[01-01-2019,01-01-2020)",one) +NOTICE: TG_OP = INSERT +NOTICE: trigger = for_portion_of_test_insert_trig, TG_LEVEL = ROW, NEW table = ("[1,2)","[01-01-2018,01-15-2018)",one), ("[1,2)","[01-01-2019,01-01-2020)",one) +NOTICE: TG_OP = UPDATE +NOTICE: trigger = for_portion_of_test_update_trig, TG_LEVEL = ROW, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one), NEW table = ("[1,2)","[01-15-2018,01-01-2019)",2018-01-15_to_2019-01-01) +NOTICE: TG_OP = UPDATE +NOTICE: trigger = for_portion_of_test_update_trig_stmt, TG_LEVEL = STATEMENT, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one), NEW table = ("[1,2)","[01-15-2018,01-01-2019)",2018-01-15_to_2019-01-01) +ROLLBACK; +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +NOTICE: TG_OP = INSERT +NOTICE: trigger = for_portion_of_test_insert_trig, TG_LEVEL = ROW, NEW table = ("[1,2)","[01-21-2018,01-01-2020)",one) +NOTICE: TG_OP = DELETE +NOTICE: trigger = for_portion_of_test_delete_trig, TG_LEVEL = ROW, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one) +NOTICE: TG_OP = DELETE +NOTICE: trigger = for_portion_of_test_delete_trig_stmt, TG_LEVEL = STATEMENT, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one) +ROLLBACK; +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +NOTICE: TG_OP = INSERT +NOTICE: trigger = for_portion_of_test_insert_trig, TG_LEVEL = ROW, NEW table = ("[1,2)","[01-02-2018,01-01-2020)",one) +NOTICE: TG_OP = UPDATE +NOTICE: trigger = for_portion_of_test_update_trig, TG_LEVEL = ROW, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one), NEW table = ("[1,2)","[01-01-2018,01-02-2018)",NULL_to_2018-01-01) +NOTICE: TG_OP = UPDATE +NOTICE: trigger = for_portion_of_test_update_trig_stmt, TG_LEVEL = STATEMENT, OLD table = ("[1,2)","[01-01-2018,01-01-2020)",one), NEW table = ("[1,2)","[01-01-2018,01-02-2018)",NULL_to_2018-01-01) +ROLLBACK; +-- Test with multiranges +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), +('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), +('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), +('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); +; +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01'))) +SET name = 'one^1' +WHERE id = '[1,2)'; +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15'))) +WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+---------------------------------------------------------------------------+------- + [1,2) | {[01-02-2018,01-10-2018),[02-10-2018,03-03-2018)} | one + [1,2) | {[01-10-2018,02-03-2018),[02-04-2018,02-10-2018)} | one^1 + [1,2) | {[03-03-2018,03-05-2018)} | one + [1,2) | {[03-05-2018,04-04-2018)} | one^1 + [2,3) | {[01-01-2018,01-15-2018),[02-15-2018,03-01-2018),[03-15-2018,05-01-2018)} | two + [3,4) | {[01-01-2018,)} | three +(6 rows) + +DROP TABLE for_portion_of_test2; +-- Test with a custom range type +CREATE TYPE mydaterange AS range(subtype=date); +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +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-05-01)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'); +; +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' +SET name = 'one^1' +WHERE id = '[1,2)'; +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' +WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [01-02-2018,01-10-2018) | one + [1,2) | [01-10-2018,02-03-2018) | one^1 + [1,2) | [02-03-2018,02-10-2018) | one^1 + [1,2) | [02-10-2018,03-03-2018) | one + [1,2) | [03-03-2018,04-04-2018) | one + [2,3) | [01-01-2018,01-15-2018) | two + [2,3) | [02-15-2018,05-01-2018) | two + [3,4) | [01-01-2018,) | three +(8 rows) + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); +INSERT INTO temporal_partitioned VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); +SELECT * FROM temporal_partitioned; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [01-01-2000,01-01-2010) | one + [3,4) | [01-01-2000,01-01-2010) | three + [5,6) | [01-01-2000,01-01-2010) | five +(3 rows) + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; +-- Update all partitions at once (each with leftovers) +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [01-01-2000,03-01-2000) | one + [1,2) | [03-01-2000,04-01-2000) | one^1 + [1,2) | [04-01-2000,06-01-2000) | one + [1,2) | [07-01-2000,01-01-2010) | one + [2,3) | [06-01-2000,07-01-2000) | three^2 + [3,4) | [01-01-2000,03-01-2000) | three + [3,4) | [03-01-2000,04-01-2000) | three^1 + [3,4) | [04-01-2000,06-01-2000) | three + [3,4) | [06-01-2000,07-01-2000) | five^2 + [3,4) | [07-01-2000,01-01-2010) | three + [4,5) | [06-01-2000,07-01-2000) | one^2 + [5,6) | [01-01-2000,03-01-2000) | five + [5,6) | [03-01-2000,04-01-2000) | five^1 + [5,6) | [04-01-2000,06-01-2000) | five + [5,6) | [07-01-2000,01-01-2010) | five +(15 rows) + +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [01-01-2000,03-01-2000) | one + [1,2) | [03-01-2000,04-01-2000) | one^1 + [1,2) | [04-01-2000,06-01-2000) | one + [1,2) | [07-01-2000,01-01-2010) | one + [2,3) | [06-01-2000,07-01-2000) | three^2 +(5 rows) + +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; + name | id | valid_at +---------+-------+------------------------- + three | [3,4) | [01-01-2000,03-01-2000) + three^1 | [3,4) | [03-01-2000,04-01-2000) + three | [3,4) | [04-01-2000,06-01-2000) + five^2 | [3,4) | [06-01-2000,07-01-2000) + three | [3,4) | [07-01-2000,01-01-2010) + one^2 | [4,5) | [06-01-2000,07-01-2000) +(6 rows) + +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + name | valid_at | id +--------+-------------------------+------- + five | [01-01-2000,03-01-2000) | [5,6) + five^1 | [03-01-2000,04-01-2000) | [5,6) + five | [04-01-2000,06-01-2000) | [5,6) + five | [07-01-2000,01-01-2010) | [5,6) +(4 rows) + +DROP TABLE temporal_partitioned; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 9d047b21b88..9b6722c4d58 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1944,6 +1944,7 @@ WHERE a1.amopfamily = 0 OR a1.amoplefttype = 0 OR a1.amoprighttype = 0 SELECT a1.amopfamily, a1.amopstrategy FROM pg_amop as a1 WHERE NOT ((a1.amoppurpose = 's' AND a1.amopsortfamily = 0) OR + (a1.amoppurpose = 'p' AND a1.amopsortfamily = 0) OR (a1.amoppurpose = 'o' AND a1.amopsortfamily <> 0)); amopfamily | amopstrategy ------------+-------------- diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index fab0cc800fc..255c581ed7d 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1038,6 +1038,24 @@ ERROR: null value in column "b" of relation "errtst_part_2" violates not-null c DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; +-- test column-level privileges on the range/PERIOD used in FOR PORTION OF +SET SESSION AUTHORIZATION regress_priv_user1; +CREATE TABLE t1 ( + c1 int4range, + valid_at tsrange, + CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS) +); +GRANT SELECT ON t1 TO regress_priv_user2; +GRANT SELECT ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +ERROR: permission denied for table t1 +SET SESSION AUTHORIZATION regress_priv_user3; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user1; +DROP TABLE t1; -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 ADD COLUMN three integer; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 420769a40c9..f973e1554c2 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3666,6 +3666,38 @@ select * from uv_iocu_tab; drop view uv_iocu_view; drop table uv_iocu_tab; +-- Check UPDATE FOR PORTION OF works correctly +create table uv_fpo_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_view as + select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab; +insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1); +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 +(2 rows) + +update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]'; +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0 +(3 rows) + +delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]'; +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0 + 0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Sun Jan 01 00:00:00 2017") | [1,2) | 2.0 +(3 rows) + -- Test whole-row references to the view create table uv_iocu_tab (a int unique, b text); create view uv_iocu_view as diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index 0608d250784..ab96ea9bf10 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -889,6 +889,36 @@ INSERT INTO temporal3 (id, valid_at, id2, name) ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01' + SET name = name || '1'; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01' + SET name = name || '2' + WHERE id = '[2,3)'; +SELECT * FROM temporal3 ORDER BY id, valid_at; + id | valid_at | id2 | name +-------+-------------------------+--------+------- + [1,2) | [2000-01-01,2000-05-01) | [7,8) | foo + [1,2) | [2000-05-01,2000-07-01) | [7,8) | foo1 + [1,2) | [2000-07-01,2010-01-01) | [7,8) | foo + [2,3) | [2000-01-01,2000-04-01) | [9,10) | bar + [2,3) | [2000-04-01,2000-05-01) | [9,10) | bar2 + [2,3) | [2000-05-01,2000-06-01) | [9,10) | bar12 + [2,3) | [2000-06-01,2000-07-01) | [9,10) | bar1 + [2,3) | [2000-07-01,2010-01-01) | [9,10) | bar +(8 rows) + +-- conflicting id only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3'); +ERROR: conflicting key value violates exclusion constraint "temporal3_pk" +DETAIL: Key (id, valid_at)=([1,2), [2005-01-01,2006-01-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-07-01,2010-01-01)). +-- conflicting id2 only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3'); +ERROR: conflicting key value violates exclusion constraint "temporal3_uniq" +DETAIL: Key (id2, valid_at)=([9,10), [2005-01-01,2010-01-01)) conflicts with existing key (id2, valid_at)=([9,10), [2000-07-01,2010-01-01)). DROP TABLE temporal3; -- -- test changing the PK's dependencies @@ -941,6 +971,36 @@ SELECT * FROM tp2 ORDER BY id, valid_at; [3,4) | [2000-01-01,2010-01-01) | three (1 row) +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2000-01-15) | one + [1,2) | [2000-01-15,2000-02-01) | one2 + [1,2) | [2000-02-01,2000-02-15) | one2 + [1,2) | [2000-02-15,2000-02-20) | one + [1,2) | [2000-02-25,2000-03-01) | one + [2,3) | [2002-01-01,2003-01-01) | three + [3,4) | [2000-01-01,2000-01-15) | three + [3,4) | [2000-02-15,2002-01-01) | three + [3,4) | [2003-01-01,2010-01-01) | three + [4,5) | [2000-02-20,2000-02-25) | one +(10 rows) + DROP TABLE temporal_partitioned; -- temporal UNIQUE: CREATE TABLE temporal_partitioned ( @@ -976,6 +1036,36 @@ SELECT * FROM tp2 ORDER BY id, valid_at; [3,4) | [2000-01-01,2010-01-01) | three (1 row) +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2000-01-15) | one + [1,2) | [2000-01-15,2000-02-01) | one2 + [1,2) | [2000-02-01,2000-02-15) | one2 + [1,2) | [2000-02-15,2000-02-20) | one + [1,2) | [2000-02-25,2000-03-01) | one + [2,3) | [2002-01-01,2003-01-01) | three + [3,4) | [2000-01-01,2000-01-15) | three + [3,4) | [2000-02-15,2002-01-01) | three + [3,4) | [2003-01-01,2010-01-01) | three + [4,5) | [2000-02-20,2000-02-25) | one +(10 rows) + DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY -- (should fail) @@ -1707,6 +1797,18 @@ UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1760,6 +1862,18 @@ UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1796,9 +1910,22 @@ BEGIN; COMMIT; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test FK referenced deletes RESTRICT -- @@ -1827,9 +1954,22 @@ BEGIN; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". ROLLBACK; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test ON UPDATE/DELETE options -- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index ddc155c195f..5e9693a1d24 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -48,7 +48,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table 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 00000000000..96ad87c9cf1 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,531 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF + +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at tsrange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2020-01-01)', 'one'); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' +SET name = 'foo'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO NULL; + +SELECT * FROM for_portion_of_test; + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at tsrange, + valid2_at tsrange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); + +UPDATE for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL +SET name = 'foo'; +SELECT * FROM for_portion_of_test; + +UPDATE for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL +SET name = 'bar'; +SELECT * FROM for_portion_of_test; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test; + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, tsrange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test; + +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 NULL +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 NULL +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; + +-- The wrong type fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM 1 TO 4 +SET name = 'nope' +WHERE id = '[3,4)'; + +-- 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 lower(valid_at) TO NULL +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 NULL +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 NULL 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 NULL 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 NULL +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 NULL TO NULL +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)'; + +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (tsrange('2018-03-10', '2018-03-17')) +SET name = 'one^5' +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 + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after + AFTER INSERT OR UPDATE OR 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; +DROP FUNCTION for_portion_of_trigger CASCADE; + +-- Triggers with a custom transition table name: + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); + +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE 'TG_OP = %', TG_OP; + IF TG_OP = 'INSERT' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, NEW table = %', + TG_NAME, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, OLD table = %, NEW table = %', + TG_NAME, TG_LEVEL, + (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table), + (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'DELETE' THEN + RAISE NOTICE 'trigger = %, TG_LEVEL = %, OLD table = %', + TG_NAME, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table); + END IF; + RETURN NULL; +END; +$$; + +CREATE TRIGGER for_portion_of_test_insert_trig +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_insert_trig_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_update_trig +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_update_trig_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_delete_trig +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_delete_trig_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +ROLLBACK; + +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +ROLLBACK; + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +ROLLBACK; + +-- Test with multiranges + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), +('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), +('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), +('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); +; + +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01'))) +SET name = 'one^1' +WHERE id = '[1,2)'; + +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15'))) +WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; + +-- Test with a custom range type + +CREATE TYPE mydaterange AS range(subtype=date); + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +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-05-01)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'); +; + +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' +SET name = 'one^1' +WHERE id = '[1,2)'; + +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' +WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; + +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed + +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); + +INSERT INTO temporal_partitioned VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); + +SELECT * FROM temporal_partitioned; + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; + +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; + +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; + +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; + +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; + +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; + +-- Update all partitions at once (each with leftovers) + +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + +DROP TABLE temporal_partitioned; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 2fe7b6dcc49..2dffe7c5b6a 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1241,6 +1241,7 @@ WHERE a1.amopfamily = 0 OR a1.amoplefttype = 0 OR a1.amoprighttype = 0 SELECT a1.amopfamily, a1.amopstrategy FROM pg_amop as a1 WHERE NOT ((a1.amoppurpose = 's' AND a1.amopsortfamily = 0) OR + (a1.amoppurpose = 'p' AND a1.amopsortfamily = 0) OR (a1.amoppurpose = 'o' AND a1.amopsortfamily <> 0)); -- amopmethod must match owning opfamily's opfmethod diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index ae338e8cc8e..7ad687316a4 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -723,6 +723,24 @@ UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; +-- test column-level privileges on the range/PERIOD used in FOR PORTION OF +SET SESSION AUTHORIZATION regress_priv_user1; +CREATE TABLE t1 ( + c1 int4range, + valid_at tsrange, + CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS) +); +GRANT SELECT ON t1 TO regress_priv_user2; +GRANT SELECT ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user3; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user1; +DROP TABLE t1; + -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 ADD COLUMN three integer; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 93b693ae837..9bf237c71d4 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1865,6 +1865,20 @@ select * from uv_iocu_tab; drop view uv_iocu_view; drop table uv_iocu_tab; +-- Check UPDATE FOR PORTION OF works correctly +create table uv_fpo_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_view as + select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab; + +insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1); +select * from uv_fpo_view; +update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]'; +select * from uv_fpo_view; +delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]'; +select * from uv_fpo_view; + -- Test whole-row references to the view create table uv_iocu_tab (a int unique, b text); create view uv_iocu_view as diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index 943edf3da63..bab34659793 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -632,6 +632,20 @@ INSERT INTO temporal3 (id, valid_at, id2, name) ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01' + SET name = name || '1'; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01' + SET name = name || '2' + WHERE id = '[2,3)'; +SELECT * FROM temporal3 ORDER BY id, valid_at; +-- conflicting id only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3'); +-- conflicting id2 only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3'); DROP TABLE temporal3; -- @@ -670,6 +684,22 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES SELECT * FROM temporal_partitioned ORDER BY id, valid_at; SELECT * FROM tp1 ORDER BY id, valid_at; SELECT * FROM tp2 ORDER BY id, valid_at; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- temporal UNIQUE: @@ -688,6 +718,22 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES SELECT * FROM temporal_partitioned ORDER BY id, valid_at; SELECT * FROM tp1 ORDER BY id, valid_at; SELECT * FROM tp2 ORDER BY id, valid_at; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY @@ -1262,6 +1308,16 @@ COMMIT; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1311,6 +1367,16 @@ ROLLBACK; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1346,9 +1412,20 @@ BEGIN; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); COMMIT; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test FK referenced deletes RESTRICT @@ -1377,9 +1454,20 @@ BEGIN; DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ROLLBACK; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test ON UPDATE/DELETE options -- 2.42.0