From 161d788aa456ee6645e8317a73c46e19b3fb2dc5 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 25 Jun 2021 18:54:35 -0700 Subject: [PATCH v40 2/4] Add UPDATE/DELETE FOR PORTION OF - Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. But most expressions work, e.g. NOW() + INTERVAL '1' HOUR. - Added logic to executor to insert new rows for the "leftover" part of a record touched by a FOR PORTION OF query. This uses SPI since each insert must be treated as a separate statement (according to the standard), firing BEFORE/AFTER STATEMENT triggers, getting its own transition table, etc. SPI also gives us easy tuple routing (in case the update changed the partition key). - Added tg_temporal descriptor to the TriggerData struct that we pass to trigger functions. Our triggers use this to learn what bounds were given in the FOR PORTION OF clause. - Documented FOR PORTION OF. - Documented tg_temporal struct. - Added tests. --- .../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 | 72 +- doc/src/sgml/ref/update.sgml | 90 +- doc/src/sgml/trigger.sgml | 64 +- src/backend/access/gist/gistvalidate.c | 34 +- src/backend/commands/explain.c | 2 +- src/backend/commands/tablecmds.c | 1 + src/backend/commands/trigger.c | 46 + src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 497 ++++++++++- src/backend/executor/spi.c | 2 +- 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 | 241 +++++- src/backend/parser/gram.y | 100 ++- 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/commands/trigger.h | 1 + src/include/nodes/execnodes.h | 25 + 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 | 792 ++++++++++++++++++ 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 | 619 ++++++++++++++ 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, 3168 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 f2bcd6aa98c..43be3ff9908 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -6373,6 +6373,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 372fe6dad15..786f57231a3 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -1579,6 +1579,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 aeb4d265a85..dbaf9a85e36 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..6330fdff4c0 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -22,7 +22,9 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] +DELETE FROM [ ONLY ] table_name [ * ] + [ FOR PORTION OF range_or_period_name FROM start_time TO end_time ] + [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ] @@ -54,6 +56,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 +151,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..c893cc795f2 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -22,7 +22,9 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] +UPDATE [ ONLY ] table_name [ * ] + [ FOR PORTION OF range_or_period_name for_portion_of_target ] + [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -51,6 +53,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 +151,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 a9abaab9056..e4f63075a36 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -366,6 +366,15 @@ responsibility to avoid that. + + If an UPDATE or DELETE uses + FOR PORTION OF, causing new rows to be inserted + to preserve the leftover untargeted part of modified records, then + INSERT triggers are fired for each inserted + row. Each row is inserted separately, so they fire their own + statement triggers, and they have their own transition tables. + + trigger @@ -547,17 +556,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; @@ -825,6 +835,38 @@ 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 named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range column */ + Datum fp_targetRange; /* the range from FOR PORTION OF */ +} ForPortionOfState; + + + where fp_rangeName is the range + column named in the FOR PORTION OF clause, + fp_rangeType is its range type, + fp_rangeAttno is its attribute number, + 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 e1ad65b9a4f..d0886d91f07 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 18a5af6b919..5439adc0d28 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4396,7 +4396,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 1ccc80087c3..a507e9cd44c 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12323,6 +12323,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 3671e82535e..1ce1a7fb88b 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); @@ -4120,6 +4129,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; } @@ -4488,6 +4498,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); @@ -6022,6 +6033,40 @@ 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_rangeAttno = src->fp_rangeAttno; + + 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() * @@ -6437,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 cc9a594cba5..561ac69fc74 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1252,6 +1252,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 1161520f76b..9f2234320f6 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -52,6 +52,7 @@ #include "postgres.h" +#include "access/heapam.h" #include "access/htup_details.h" #include "access/tableam.h" #include "access/xact.h" @@ -59,14 +60,18 @@ #include "executor/execPartition.h" #include "executor/executor.h" #include "executor/nodeModifyTable.h" +#include "executor/spi.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "optimizer/optimizer.h" +#include "parser/parse_relation.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" @@ -123,6 +128,19 @@ typedef struct UpdateContext LockTupleMode lockmode; } UpdateContext; +/* + * FPO_QueryHashEntry + */ +typedef struct FPO_QueryHashEntry { + Oid key; + SPIPlanPtr plan; +} FPO_QueryHashEntry; + +/* + * Plan cache for FOR PORTION OF inserts + */ +#define FPO_INIT_QUERYHASHSIZE 32 +static HTAB *fpo_query_cache = NULL; static void ExecBatchInsert(ModifyTableState *mtstate, ResultRelInfo *resultRelInfo, @@ -144,6 +162,13 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static void fpo_InitHashTable(void); +static SPIPlanPtr fpo_FetchPreparedPlan(Oid relid); +static void fpo_HashPreparedPlan(Oid relid, SPIPlanPtr plan); +static void ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -166,6 +191,7 @@ static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag); +static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate); /* @@ -1214,6 +1240,367 @@ ExecInsert(ModifyTableContext *context, return result; } +/* ---------------------------------------------------------------- + * fpo_InitHashTable + * + * Creates a hash table to hold SPI plans to insert leftovers + * from a PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +fpo_InitHashTable(void) +{ + HASHCTL ctl; + + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(FPO_QueryHashEntry); + fpo_query_cache = hash_create("FPO_query_cache", + FPO_INIT_QUERYHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); +} + +/* ---------------------------------------------------------------- + * fpo_FetchPreparedPlan + * + * Lookup for a query key in our private hash table of + * prepared and saved SPI execution plans. Returns the plan + * if found or NULL. + * ---------------------------------------------------------------- + */ +static SPIPlanPtr +fpo_FetchPreparedPlan(Oid relid) +{ + FPO_QueryHashEntry *entry; + SPIPlanPtr plan; + + if (!fpo_query_cache) + fpo_InitHashTable(); + + /* + * Lookup for the key + */ + entry = (FPO_QueryHashEntry *) hash_search(fpo_query_cache, + &relid, + HASH_FIND, NULL); + + if (!entry) + return NULL; + + /* + * Check whether the plan is still valid. If it isn't, we don't want to + * simply rely on plancache.c to regenerate it; rather we should start + * from scratch and rebuild the query text too. This is to cover cases + * such as table/column renames. We depend on the plancache machinery to + * detect possible invalidations, though. + * + * CAUTION: this check is only trustworthy if the caller has already + * locked both FK and PK rels. + */ + plan = entry->plan; + if (plan && SPI_plan_is_valid(plan)) + return plan; + + /* + * Otherwise we might as well flush the cached plan now, to free a little + * memory space before we make a new one. + */ + entry->plan = NULL; + if (plan) + SPI_freeplan(plan); + + return NULL; +} + +/* ---------------------------------------------------------------- + * fpo_HashPreparedPlan + * + * Add another plan to our private SPI query plan hashtable. + * ---------------------------------------------------------------- + */ +static void +fpo_HashPreparedPlan(Oid relid, SPIPlanPtr plan) +{ + FPO_QueryHashEntry *entry; + bool found; + + /* + * On the first call initialize the hashtable + */ + if (!fpo_query_cache) + fpo_InitHashTable(); + + /* + * Add the new plan. We might be overwriting an entry previously found + * invalid by fpo_FetchPreparedPlan. + */ + entry = (FPO_QueryHashEntry *) hash_search(fpo_query_cache, + &relid, + HASH_ENTER, &found); + Assert(!found || entry->plan == NULL); + entry->plan = plan; +} + +/* ---------------------------------------------------------------- + * 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; + Oid relid; + TupleDesc tupdesc; + int natts; + Datum oldRange; + TypeCacheEntry *typcache; + ForPortionOfState *fpoState = resultRelInfo->ri_forPortionOf; + TupleTableSlot *oldtupleSlot = fpoState->fp_Existing; + TupleTableSlot *leftoverSlot; + TupleConversionMap *map = NULL; + HeapTuple oldtuple = NULL; + FmgrInfo flinfo; + ReturnSetInfo rsi; + Relation rel; + 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/deleted. + * Must read with the attno of the leaf partition. + */ + + rangeAttno = forPortionOf->rangeVar->varattno; + if (resultRelInfo->ri_RootResultRelInfo) + map = ExecGetChildToRootMap(resultRelInfo); + if (map != NULL) + rangeAttno = map->attrMap->attnums[rangeAttno - 1]; + slot_getallattrs(oldtupleSlot); + + if (oldtupleSlot->tts_isnull[rangeAttno - 1]) + elog(ERROR, "found a NULL range in a temporal table"); + oldRange = oldtupleSlot->tts_values[rangeAttno - 1]; + + /* + * Get the range's type cache entry. This is worth caching for the whole + * UPDATE/DELETE 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; + + /* + * 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; + + rel = resultRelInfo->ri_RelationDesc; + relid = RelationGetRelid(rel); + + /* Insert a leftover for each value returned by the without_portion helper function */ + while (true) + { + Datum leftover = FunctionCallInvoke(fcinfo); + SPIPlanPtr qplan = NULL; + int spi_result; + + /* Are we done? */ + if (rsi.isDone == ExprEndResult) + break; + + if (fcinfo->isnull) + elog(ERROR, "Got a null from without_portion function"); + + if (!didInit) + { + /* + * 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, + fpoState->fp_Leftover); + } + else + leftoverSlot = oldtupleSlot; + + tupdesc = leftoverSlot->tts_tupleDescriptor; + natts = tupdesc->natts; + + /* + * If targeting a leaf partition, + * it may not have fp_values/fp_nulls yet. + */ + if (!fpoState->fp_values) + { + fpoState->fp_values = palloc(natts * sizeof(Datum)); + fpoState->fp_nulls = palloc(natts * sizeof(char)); + } + + /* + * Copy (potentially mapped) oldtuple values into SPI input arrays. + * We'll overwrite the range/start/end attributes below. + */ + memcpy(fpoState->fp_values, leftoverSlot->tts_values, natts * sizeof(Datum)); + + SPI_connect(); + + didInit = true; + } + + /* + * Build an SPI plan if we don't have one yet. + * We always insert into the root partition, + * so that we get tuple routing. + * Therefore the plan is the same no matter which leaf + * we are updating/deleting. + */ + if (!qplan && !(qplan = fpo_FetchPreparedPlan(relid))) + { + Oid *types = palloc0(natts * sizeof(Oid)); + int i; + bool started = false; + StringInfoData querybuf; + StringInfoData parambuf; + const char *tablename; + const char *schemaname; + const char *colname; + + initStringInfo(&querybuf); + initStringInfo(¶mbuf); + + schemaname = get_namespace_name(RelationGetNamespace(rel)); + tablename = RelationGetRelationName(rel); + appendStringInfo(&querybuf, "INSERT INTO %s (", + quote_qualified_identifier(schemaname, tablename)); + + for (i = 0; i < natts; i++) { + /* Don't try to insert into dropped or generated columns */ + if (tupdesc->attrs[i].attisdropped || tupdesc->attrs[i].attgenerated) + continue; + + types[i] = tupdesc->attrs[i].atttypid; + + colname = quote_identifier(NameStr(*attnumAttName(rel, i + 1))); + if (started) + { + appendStringInfo(&querybuf, ", %s", colname); + appendStringInfo(¶mbuf, ", $%d", i + 1); + } + else + { + appendStringInfo(&querybuf, "%s", colname); + appendStringInfo(¶mbuf, "$%d", i + 1); + } + started = true; + } + appendStringInfo(&querybuf, ") VALUES (%s)", parambuf.data); + + qplan = SPI_prepare(querybuf.data, natts, types); + if (!qplan) + elog(ERROR, "SPI_prepare returned %s for %s", + SPI_result_code_string(SPI_result), querybuf.data); + + SPI_keepplan(qplan); + fpo_HashPreparedPlan(relid, qplan); + } + + /* + * Set up the SPI params. + * Copy most attributes' old values, + * but for the range/start/end use the leftover. + */ + + /* Convert bool null array to SPI char array */ + for (int i = 0; i < natts; i++) + { + /* + * Don't try to insert into dropped or generated columns. + * Tell SPI these params are null just to be safe. + */ + if (tupdesc->attrs[i].attisdropped || tupdesc->attrs[i].attgenerated) + fpoState->fp_nulls[i] = 'n'; + else + fpoState->fp_nulls[i] = leftoverSlot->tts_isnull[i] ? 'n' : ' '; + } + + fpoState->fp_nulls[forPortionOf->rangeVar->varattno - 1] = ' '; + fpoState->fp_values[forPortionOf->rangeVar->varattno - 1] = leftover; + + spi_result = SPI_execute_snapshot(qplan, + fpoState->fp_values, + fpoState->fp_nulls, + GetLatestSnapshot(), + InvalidSnapshot, false, true, 0); + if (spi_result != SPI_OK_INSERT) + elog(ERROR, "SPI_execute_snapshot returned %s", SPI_result_code_string(spi_result)); + } + + if (didInit) + { + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + if (shouldFree) + heap_freetuple(oldtuple); + } +} + /* ---------------------------------------------------------------- * ExecBatchInsert * @@ -1366,7 +1753,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, @@ -1399,6 +1787,10 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ar_delete_trig_tcs = NULL; } + /* Compute leftovers in FOR PORTION OF */ + 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); @@ -1779,7 +2171,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; @@ -2145,6 +2541,10 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, NULL, NIL, (updateCxt->updateIndexes == TU_Summarizing)); + /* Compute leftovers in FOR PORTION OF */ + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid); + /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, NULL, NULL, @@ -4640,6 +5040,99 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } } + /* + * If needed, initialize the target range for FOR PORTION OF. + */ + if (node->forPortionOf) + { + ResultRelInfo *rootResultRelInfo; + TupleDesc tupDesc; + ForPortionOfExpr *forPortionOf; + Datum targetRange; + bool isNull; + ExprContext *econtext; + ExprState *exprState; + ForPortionOfState *fpoState; + + rootResultRelInfo = mtstate->resultRelInfo; + if (rootResultRelInfo->ri_RootResultRelInfo) + rootResultRelInfo = rootResultRelInfo->ri_RootResultRelInfo; + + tupDesc = rootResultRelInfo->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(rootResultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + /* Create the tuple slot for INSERTing the leftovers */ + + fpoState->fp_Leftover = + ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual); + + /* Allocate our SPI param arrays here so we can reuse them */ + fpoState->fp_values = palloc(tupDesc->natts * sizeof(Datum)); + fpoState->fp_nulls = palloc(tupDesc->natts * sizeof(char)); + + /* + * We must attach the ForPortionOfState to all result rels, + * in case of a cross-partition update or triggers firing + * on partitions. + * XXX: Can we defer this to only the leafs we touch? + */ + 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); + /* + * Leafs need them own SPI input arrays + * since they might have extra attributes, + * but we'll allocate those as needed. + */ + leafState->fp_values = NULL; + leafState->fp_nulls = NULL; + + 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; + + /* 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/executor/spi.c b/src/backend/executor/spi.c index 90d98345764..5db513b84cb 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -761,7 +761,7 @@ SPI_execute_plan_with_paramlist(SPIPlanPtr plan, ParamListInfo params, * end of the command. * * This is currently not documented in spi.sgml because it is only intended - * for use by RI triggers. + * for use by RI triggers and FOR PORTION OF. * * Passing snapshot == InvalidSnapshot will select the normal behavior of * fetching a new snapshot for each query. diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 0d00e029f32..5e7e1316e81 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)) @@ -3591,6 +3601,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; @@ -3772,6 +3795,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 c13586c537e..cc299cd35d6 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -316,7 +316,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); @@ -2837,6 +2837,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); @@ -7110,7 +7111,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; @@ -7176,6 +7177,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 0f423e96847..eda2b1cf45d 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2048,6 +2048,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 fc97bf6ee26..a4cc6e09620 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3827,7 +3827,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); @@ -3894,6 +3894,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 e901203424d..6554c1cb7bd 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,10 +50,12 @@ #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/guc.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -59,10 +64,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); @@ -482,6 +493,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 @@ -515,6 +540,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_DELETE; @@ -553,7 +579,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, @@ -1189,7 +1219,7 @@ transformOnConflictClause(ParseState *pstate, * Now transform the UPDATE subexpressions. */ onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, @@ -1219,6 +1249,186 @@ 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")); + + 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 @@ -2426,6 +2636,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_UPDATE; @@ -2443,6 +2654,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 */ @@ -2459,7 +2674,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, @@ -2469,7 +2685,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; @@ -2488,7 +2704,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; @@ -2541,6 +2757,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 4aa8646af7b..eced82efd23 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; @@ -553,6 +554,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type relation_expr %type extended_relation_expr %type relation_expr_opt_alias +%type opt_alias +%type for_portion_of_clause %type tablesample_clause opt_repeatable_clause %type target_el set_target insert_column_item @@ -762,7 +765,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION 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 @@ -881,12 +884,15 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * json_predicate_type_constraint and json_key_uniqueness_constraint_opt * productions (see comments there). * + * TO is assigned the same precedence as IDENT, to support the opt_interval + * production (see comment there). + * * Like the UNBOUNDED PRECEDING/FOLLOWING case, NESTED is assigned a lower * precedence than PATH to fix ambiguity in the json_table production. */ %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 + SET KEYS OBJECT_P SCALAR TO USING VALUE_P WITH WITHOUT PATH %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -12325,6 +12331,20 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->withClause = $1; $$ = (Node *) n; } + | opt_with_clause DELETE_P FROM relation_expr for_portion_of_clause opt_alias + using_clause where_or_current_clause returning_clause + { + DeleteStmt *n = makeNode(DeleteStmt); + + n->relation = $4; + n->forPortionOf = $5; + n->relation->alias = $6; + n->usingClause = $7; + n->whereClause = $8; + n->returningList = $9; + n->withClause = $1; + $$ = (Node *) n; + } ; using_clause: @@ -12399,6 +12419,25 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->withClause = $1; $$ = (Node *) n; } + | opt_with_clause UPDATE relation_expr + for_portion_of_clause opt_alias + SET set_clause_list + from_clause + where_or_current_clause + returning_clause + { + UpdateStmt *n = makeNode(UpdateStmt); + + n->relation = $3; + n->forPortionOf = $4; + n->relation->alias = $5; + n->targetList = $7; + n->fromClause = $8; + n->whereClause = $9; + n->returningList = $10; + n->withClause = $1; + $$ = (Node *) n; + } ; set_clause_list: @@ -13864,6 +13903,44 @@ relation_expr_opt_alias: relation_expr %prec UMINUS } ; +opt_alias: + AS ColId + { + Alias *alias = makeNode(Alias); + + alias->aliasname = $2; + $$ = alias; + } + | BareColLabel + { + Alias *alias = makeNode(Alias); + + alias->aliasname = $1; + $$ = alias; + } + | /* empty */ %prec UMINUS { $$ = NULL; } + ; + +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; + } + ; + /* * TABLESAMPLE decoration in a FROM item */ @@ -14697,17 +14774,26 @@ opt_timezone: | /*EMPTY*/ { $$ = false; } ; +/* + * We need to handle this shift/reduce conflict: + * FOR PORTION OF valid_at FROM t + INTERVAL '1' YEAR TO MONTH. + * We don't see far enough ahead to know if there is another TO coming. + * We prefer to interpret this as FROM (t + INTERVAL '1' YEAR TO MONTH), + * i.e. to shift. + * That gives the user the option of adding parentheses to get the other meaning. + * If we reduced, intervals could never have a TO. + */ opt_interval: - YEAR_P + YEAR_P %prec IS { $$ = list_make1(makeIntConst(INTERVAL_MASK(YEAR), @1)); } | MONTH_P { $$ = list_make1(makeIntConst(INTERVAL_MASK(MONTH), @1)); } - | DAY_P + | DAY_P %prec IS { $$ = list_make1(makeIntConst(INTERVAL_MASK(DAY), @1)); } - | HOUR_P + | HOUR_P %prec IS { $$ = list_make1(makeIntConst(INTERVAL_MASK(HOUR), @1)); } | MINUTE_P - { $$ = list_make1(makeIntConst(INTERVAL_MASK(MINUTE), @1)); } + { $$ = list_make1(makeIntConst(INTERVAL_MASK(MINUTE), @1)); } %prec IS | interval_second { $$ = $1; } | YEAR_P TO MONTH_P @@ -17757,6 +17843,7 @@ unreserved_keyword: | PLAN | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -18382,6 +18469,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 efa730c1676..53985f74df5 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -579,6 +579,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 @@ -970,6 +977,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 36c1b7a88f2..417d789fd9a 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 @@ -1860,6 +1863,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 @@ -3153,6 +3159,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 6d59a2bb8dc..e9f64e74bc2 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3684,6 +3684,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, @@ -4023,6 +4047,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 6896e1ae638..297fba71bb5 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); /* @@ -449,6 +455,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); @@ -613,6 +620,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); @@ -712,6 +720,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); @@ -801,9 +811,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); @@ -909,6 +926,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); @@ -1029,6 +1047,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); @@ -1260,6 +1279,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); @@ -2405,6 +2425,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, @@ -2460,6 +2481,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 @@ -3136,3 +3163,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/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 e4698a28c4f..8da5618ee19 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; @@ -431,6 +433,26 @@ 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 named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range column */ + Datum fp_targetRange; /* the range from FOR PORTION OF */ + TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */ + TupleTableSlot *fp_Existing; /* slot to store old tuple */ + TupleTableSlot *fp_Leftover; /* slot to store leftover */ + Datum *fp_values; /* SPI input for leftover values */ + char *fp_nulls; /* SPI input for nulls */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -558,6 +580,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 5b62df32733..08f12fc79ca 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 */ @@ -1581,6 +1584,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 @@ -2053,12 +2071,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; /* ---------------------- @@ -2067,13 +2086,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 07e2415398e..d538ae23808 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2392,6 +2392,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 62cd6a6666e..c2958907eb1 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -244,6 +244,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 1035e6560c1..b594cccab92 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -289,7 +289,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 899d64ad55f..425987afee7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -343,6 +343,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 543df568147..8c273da94e7 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..f493a67b0ad --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,792 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF +SET datestyle TO ISO, YMD; +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + 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 = 'one^1'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; +-- With a table alias with AS +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t +SET name = 'one^2'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; +-- With a table alias without AS +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t +SET name = 'one^3'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; +-- UPDATE with FROM +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' +SET name = 'one^4' +FROM (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; +-- DELETE with USING +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' +USING (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-01-15) | one + [1,2) | [2018-01-15,2019-01-01) | one^1 + [1,2) | [2019-01-01,2019-01-15) | one + [1,2) | [2019-01-20,2019-02-01) | one + [1,2) | [2019-02-01,2019-02-03) | one^2 + [1,2) | [2019-02-04,2019-02-05) | one^3 + [1,2) | [2019-02-06,2019-03-01) | one + [1,2) | [2019-03-01,2019-03-02) | one^4 + [1,2) | [2019-03-03,2020-01-01) | one +(9 rows) + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + 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 ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2025-01-01) | one + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2025-01-01) | foo +(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 ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-02-03) | [2018-01-15,2025-01-01) | bar +(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 ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2025-01-01) | bar +(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 ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2018-01-20) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2018-01-20) | 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 daterange, + 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, daterange('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 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------------- + [1,2) | empty | 1 empty + [1,2) | (,) | NULL to NULL + [1,2) | | 1 null + | [2018-01-01,2019-01-01) | NULL to NULL + | | +(5 rows) + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange 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.daterange(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^1' +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^2' +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)'; +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) +SET name = 'one^3' +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) | [2018-01-02,2018-02-03) | one^2 + [1,2) | [2018-02-03,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-06-01) | three + [3,4) | [2018-06-01,2030-01-01) | three^1 + [3,4) | [2030-01-01,) | three^1* + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2019-01-01) | five^2 + [5,6) | [2019-01-01,2020-01-01) | five^2 + [5,6) | [2020-01-01,2030-01-01) | five + [5,6) | [2030-01-01,) | five* + [6,7) | [2018-02-15,2018-03-03) | one^2 + [6,7) | [2018-03-03,2018-03-10) | one^2 + [6,7) | [2018-03-10,2018-03-17) | one^3 + [6,7) | [2018-03-17,2018-04-04) | one^2 +(20 rows) + +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) + VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' +SET name = 'one^1' +WHERE id = '[1,2)'; +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' +SET name = 'one^2' +WHERE id = '[1,2)'; +ERROR: syntax error at or near "'2014-01-01'" +LINE 4: TO '2014-01-01' + ^ +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; +-- FOR PORTION OF allows params in sql: +CREATE FUNCTION fpo_sql_update(target_from date, target_til date) +RETURNS VOID +AS $$ + UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM $1 TO $2 + SET name = concat(name, '*') + WHERE id = '[1,2)'; +$$ +LANGUAGE sql; +SELECT fpo_sql_update('2018-01-01', '2019-01-01'); + fpo_sql_update +---------------- + +(1 row) + +-- FOR PORTION OF allows params in plpgsql: +CREATE FUNCTION fpo_plpgsql_update(target_from date, target_til date) +RETURNS VOID +AS $$ +BEGIN + UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM $1 TO $2 + SET name = concat(name, '+') + WHERE id = '[1,2)'; +END; +$$ +LANGUAGE plpgsql; +SELECT fpo_plpgsql_update('2018-06-01', '2019-01-01'); + fpo_plpgsql_update +-------------------- + +(1 row) + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+-----------------------------------------------+------- + [1,2) | ["2000-01-01 00:00:00","2011-03-01 01:02:00") | one + [1,2) | ["2011-03-01 01:02:00","2012-01-01 00:00:00") | one^1 + [1,2) | ["2012-01-01 00:00:00","2015-03-01 01:00:00") | one + [1,2) | ["2015-03-01 01:00:00","2016-01-01 00:00:00") | one^3 + [1,2) | ["2016-01-01 00:00:00","2018-01-01 00:00:00") | one + [1,2) | ["2018-01-01 00:00:00","2018-06-01 00:00:00") | one* + [1,2) | ["2018-06-01 00:00:00","2019-01-01 00:00:00") | one*+ + [1,2) | ["2019-01-01 00:00:00","2020-01-01 00:00:00") | one +(8 rows) + +DROP TABLE for_portion_of_test2; +-- +-- 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) | [2018-02-08,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-04-01) | three + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2018-06-01) | five^2 + [5,6) | [2020-06-01,2025-01-01) | 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) | [2018-02-01,2018-02-15) | 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, TG_LEVEL, 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(); +CREATE TRIGGER trg_for_portion_of_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_stmt + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + 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^3' +WHERE id = '[5,6)'; +NOTICE: BEFORE UPDATE STATEMENT of +NOTICE: BEFORE UPDATE ROW [2021-01-01,2022-01-01) of [2020-06-01,2025-01-01) +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2020-06-01,2021-01-01) of +NOTICE: AFTER INSERT ROW [2020-06-01,2021-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2022-01-01,2025-01-01) of +NOTICE: AFTER INSERT ROW [2022-01-01,2025-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: AFTER UPDATE ROW [2021-01-01,2022-01-01) of [2020-06-01,2025-01-01) +NOTICE: AFTER UPDATE STATEMENT of +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 STATEMENT of +NOTICE: BEFORE DELETE ROW of [2022-01-01,2025-01-01) +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2022-01-01,2023-01-01) of +NOTICE: AFTER INSERT ROW [2022-01-01,2023-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2024-01-01,2025-01-01) of +NOTICE: AFTER INSERT ROW [2024-01-01,2025-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: AFTER DELETE ROW of [2022-01-01,2025-01-01) +NOTICE: AFTER DELETE STATEMENT of +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2018-02-08,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-02-01) | three^2 + [3,4) | [2018-02-01,2018-02-15) | three^3 + [3,4) | [2018-02-15,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-04-01) | three + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2018-06-01) | five^2 + [5,6) | [2020-06-01,2021-01-01) | five + [5,6) | [2021-01-01,2022-01-01) | five^3 + [5,6) | [2022-01-01,2023-01-01) | five + [5,6) | [2024-01-01,2025-01-01) | five +(16 rows) + +DROP FUNCTION for_portion_of_trigger CASCADE; +NOTICE: drop cascades to 4 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 +drop cascades to trigger trg_for_portion_of_before_stmt on table for_portion_of_test +drop cascades to trigger trg_for_portion_of_after_stmt 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 + IF TG_OP = 'INSERT' THEN + RAISE NOTICE '%: % %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE '%: % %, OLD table = %, NEW table = %', + TG_NAME, TG_OP, 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 '%: % %, OLD table = %', + TG_NAME, TG_OP, 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: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +NOTICE: for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",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: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: for_portion_of_test_delete_trig: DELETE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_delete_trig_stmt: DELETE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",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: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +NOTICE: for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",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) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one + [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1 + [1,2) | {[2018-03-03,2018-03-05)} | one + [1,2) | {[2018-03-05,2018-04-04)} | one^1 + [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-15,2018-05-01)} | two + [3,4) | {[2018-01-01,)} | 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) | [2018-01-02,2018-01-10) | one + [1,2) | [2018-01-10,2018-02-03) | one^1 + [1,2) | [2018-02-03,2018-02-10) | one^1 + [1,2) | [2018-02-10,2018-03-03) | one + [1,2) | [2018-03-03,2018-04-04) | one + [2,3) | [2018-01-01,2018-01-15) | two + [2,3) | [2018-02-15,2018-05-01) | two + [3,4) | [2018-01-01,) | 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) | [2000-01-01,2010-01-01) | one + [3,4) | [2000-01-01,2010-01-01) | three + [5,6) | [2000-01-01,2010-01-01) | 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) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 + [3,4) | [2000-01-01,2000-03-01) | three + [3,4) | [2000-03-01,2000-04-01) | three^1 + [3,4) | [2000-04-01,2000-06-01) | three + [3,4) | [2000-06-01,2000-07-01) | five^2 + [3,4) | [2000-07-01,2010-01-01) | three + [4,5) | [2000-06-01,2000-07-01) | one^2 + [5,6) | [2000-01-01,2000-03-01) | five + [5,6) | [2000-03-01,2000-04-01) | five^1 + [5,6) | [2000-04-01,2000-06-01) | five + [5,6) | [2000-07-01,2010-01-01) | five +(15 rows) + +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 +(5 rows) + +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; + name | id | valid_at +---------+-------+------------------------- + three | [3,4) | [2000-01-01,2000-03-01) + three^1 | [3,4) | [2000-03-01,2000-04-01) + three | [3,4) | [2000-04-01,2000-06-01) + five^2 | [3,4) | [2000-06-01,2000-07-01) + three | [3,4) | [2000-07-01,2010-01-01) + one^2 | [4,5) | [2000-06-01,2000-07-01) +(6 rows) + +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + name | valid_at | id +--------+-------------------------+------- + five | [2000-01-01,2000-03-01) | [5,6) + five^1 | [2000-03-01,2000-04-01) | [5,6) + five | [2000-04-01,2000-06-01) | [5,6) + five | [2000-07-01,2010-01-01) | [5,6) +(4 rows) + +DROP TABLE temporal_partitioned; +RESET datestyle; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 34a32bd11d2..7048ea0a59e 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1948,6 +1948,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 1d903babd33..ba8761cc9f8 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 8786058ed0c..53903705493 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 86171c994c9..eccaf5c0f09 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 81e4222d26a..a4e559fe419 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..f6fb0801c62 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,619 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF + +SET datestyle TO ISO, YMD; + +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + 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 = 'one^1'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; + +-- With a table alias with AS + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t +SET name = 'one^2'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; + +-- With a table alias without AS + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t +SET name = 'one^3'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; + +-- UPDATE with FROM + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' +SET name = 'one^4' +FROM (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; + +-- DELETE with USING + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' +USING (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + 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 ORDER BY id, valid1_at, valid2_at; + +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 ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +-- 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 daterange, + 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, daterange('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 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange 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^1' +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^2' +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)'; + +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) +SET name = 'one^3' +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; + +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) + VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' +SET name = 'one^1' +WHERE id = '[1,2)'; + +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' +SET name = 'one^2' +WHERE id = '[1,2)'; + +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; + +-- FOR PORTION OF allows params in sql: +CREATE FUNCTION fpo_sql_update(target_from date, target_til date) +RETURNS VOID +AS $$ + UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM $1 TO $2 + SET name = concat(name, '*') + WHERE id = '[1,2)'; +$$ +LANGUAGE sql; +SELECT fpo_sql_update('2018-01-01', '2019-01-01'); + +-- FOR PORTION OF allows params in plpgsql: +CREATE FUNCTION fpo_plpgsql_update(target_from date, target_til date) +RETURNS VOID +AS $$ +BEGIN + UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM $1 TO $2 + SET name = concat(name, '+') + WHERE id = '[1,2)'; +END; +$$ +LANGUAGE plpgsql; +SELECT fpo_plpgsql_update('2018-06-01', '2019-01-01'); + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; +DROP TABLE for_portion_of_test2; + +-- +-- 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, TG_LEVEL, 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(); +CREATE TRIGGER trg_for_portion_of_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_stmt + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + 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^3' +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 + IF TG_OP = 'INSERT' THEN + RAISE NOTICE '%: % %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE '%: % %, OLD table = %, NEW table = %', + TG_NAME, TG_OP, 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 '%: % %, OLD table = %', + TG_NAME, TG_OP, 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; + +RESET datestyle; 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 3f54b0f8f05..1d9f91567fb 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.45.0