From 22e8849536d92312430d72ecfdc2ef1c5d408a9a Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 25 Jun 2021 18:54:35 -0700 Subject: [PATCH v59 05/11] 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. We do accept functions like NOW(). - Added logic to executor to insert new rows for the "temporal leftover" part of a record touched by a FOR PORTION OF query. - Documented FOR PORTION OF. - Added tests. Author: Paul A. Jungwirth --- .../postgres_fdw/expected/postgres_fdw.out | 45 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 34 + doc/src/sgml/ref/create_publication.sgml | 6 + doc/src/sgml/ref/delete.sgml | 96 +- doc/src/sgml/ref/update.sgml | 98 +- doc/src/sgml/trigger.sgml | 9 + src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 330 ++++- 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 | 248 +++- 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 | 43 + src/backend/utils/adt/ri_triggers.c | 2 +- src/backend/utils/cache/lsyscache.c | 27 + src/include/nodes/execnodes.h | 22 + src/include/nodes/parsenodes.h | 20 + src/include/nodes/pathnodes.h | 1 + src/include/nodes/plannodes.h | 2 + src/include/nodes/primnodes.h | 27 + src/include/optimizer/pathnode.h | 2 +- src/include/parser/analyze.h | 3 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/utils/lsyscache.h | 1 + src/test/regress/expected/for_portion_of.out | 1248 +++++++++++++++++ src/test/regress/expected/privileges.out | 18 + src/test/regress/expected/updatable_views.out | 32 + .../regress/expected/without_overlaps.out | 245 +++- src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/for_portion_of.sql | 905 ++++++++++++ src/test/regress/sql/privileges.sql | 18 + src/test/regress/sql/updatable_views.sql | 14 + src/test/regress/sql/without_overlaps.sql | 120 +- src/test/subscription/t/034_temporal.pl | 110 +- src/tools/pgindent/typedefs.list | 4 + 43 files changed, 3804 insertions(+), 89 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 cd28126049d..8c0f1e8b771 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -50,11 +50,19 @@ CREATE TABLE "S 1"."T 4" ( c3 text, CONSTRAINT t4_pkey PRIMARY KEY (c1) ); +CREATE TABLE "S 1"."T 5" ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL, + CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS) +); -- Disable autovacuum for these tables to avoid unexpected effects of that ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); +ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false'); INSERT INTO "S 1"."T 1" SELECT id, id % 10, @@ -81,10 +89,17 @@ INSERT INTO "S 1"."T 4" 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests +INSERT INTO "S 1"."T 5" + SELECT int4range(id, id + 1), + id + 1, + 'AAA' || to_char(id, 'FM000'), + '[2000-01-01,2020-01-01)' + FROM generate_series(1, 100) id; ANALYZE "S 1"."T 1"; ANALYZE "S 1"."T 2"; ANALYZE "S 1"."T 3"; ANALYZE "S 1"."T 4"; +ANALYZE "S 1"."T 5"; -- =================================================================== -- create foreign tables -- =================================================================== @@ -132,6 +147,12 @@ CREATE FOREIGN TABLE ft7 ( c2 int NOT NULL, c3 text ) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); +CREATE FOREIGN TABLE ft8 ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL +) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5'); -- =================================================================== -- tests for validator -- =================================================================== @@ -214,7 +235,8 @@ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') | public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') | public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') | -(6 rows) + public | ft8 | loopback | (schema_name 'S 1', table_name 'T 5') | +(7 rows) -- Test that alteration of server options causes reconnection -- Remote's errors might be non-English, so hide them to ensure stable results @@ -6303,6 +6325,27 @@ DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; ft2 (1 row) +-- Test UPDATE FOR PORTION OF +UPDATE ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01' +SET c2 = c2 + 1 +WHERE c1 = '[1,2)'; +ERROR: foreign tables don't support FOR PORTION OF +SELECT * FROM ft8 WHERE c1 = '[1,2)' ORDER BY c1, c4; + c1 | c2 | c3 | c4 +-------+----+--------+------------------------- + [1,2) | 2 | AAA001 | [01-01-2000,01-01-2020) +(1 row) + +-- Test DELETE FOR PORTION OF +DELETE FROM ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01' +WHERE c1 = '[2,3)'; +ERROR: foreign tables don't support FOR PORTION OF +SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4; + c1 | c2 | c3 | c4 +-------+----+--------+------------------------- + [2,3) | 3 | AAA002 | [01-01-2000,01-01-2020) +(1 row) + -- Test UPDATE/DELETE with RETURNING on a three-table join INSERT INTO ft2 (c1,c2,c3) SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 9a8f9e28135..738fc24fb07 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -54,12 +54,20 @@ CREATE TABLE "S 1"."T 4" ( c3 text, CONSTRAINT t4_pkey PRIMARY KEY (c1) ); +CREATE TABLE "S 1"."T 5" ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL, + CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS) +); -- Disable autovacuum for these tables to avoid unexpected effects of that ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); +ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false'); INSERT INTO "S 1"."T 1" SELECT id, @@ -87,11 +95,18 @@ INSERT INTO "S 1"."T 4" 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests +INSERT INTO "S 1"."T 5" + SELECT int4range(id, id + 1), + id + 1, + 'AAA' || to_char(id, 'FM000'), + '[2000-01-01,2020-01-01)' + FROM generate_series(1, 100) id; ANALYZE "S 1"."T 1"; ANALYZE "S 1"."T 2"; ANALYZE "S 1"."T 3"; ANALYZE "S 1"."T 4"; +ANALYZE "S 1"."T 5"; -- =================================================================== -- create foreign tables @@ -146,6 +161,14 @@ CREATE FOREIGN TABLE ft7 ( c3 text ) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); +CREATE FOREIGN TABLE ft8 ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL +) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5'); + + -- =================================================================== -- tests for validator -- =================================================================== @@ -1538,6 +1561,17 @@ EXPLAIN (verbose, costs off) DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; +-- Test UPDATE FOR PORTION OF +UPDATE ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01' +SET c2 = c2 + 1 +WHERE c1 = '[1,2)'; +SELECT * FROM ft8 WHERE c1 = '[1,2)' ORDER BY c1, c4; + +-- Test DELETE FOR PORTION OF +DELETE FROM ft8 FOR PORTION OF c4 FROM '2005-01-01' TO '2006-01-01' +WHERE c1 = '[2,3)'; +SELECT * FROM ft8 WHERE c1 = '[2,3)' ORDER BY c1, c4; + -- Test UPDATE/DELETE with RETURNING on a three-table join INSERT INTO ft2 (c1,c2,c3) SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id; diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index 66a70e5c5b5..6ac8f935a78 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -390,6 +390,12 @@ CREATE PUBLICATION name for each row inserted, updated, or deleted. + + For a FOR PORTION OF command, the publication will publish an + UPDATE or DELETE, followed by one + INSERT for each temporal leftover row inserted. + + ATTACHing a table into a partition tree whose root is published using a publication with publish_via_partition_root diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 29649f6afd6..f425309fd5d 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_name for_portion_of_target ] + [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ] @@ -55,6 +57,43 @@ 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 history + extends outside the FOR PORTION OF bounds, then your delete + will only change the history 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 temporal leftovers: + rows whose range or multirange receive the remaining history outside + the targeted bounds, with un-updated values in their other columns. + There will be zero to two inserted records, + depending on whether the original history extended before the targeted + FROM, after the targeted TO, both, or neither. + Multiranges never require two temporal leftovers, because one value can always contain + whatever history remains. + + + + These secondary inserts fire INSERT triggers. + Both STATEMENT and ROW triggers are fired. + The BEFORE DELETE triggers are fired 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. @@ -117,6 +156,57 @@ DELETE FROM [ ONLY ] table_name [ * + + range_name + + + The range or multirange column to use when performing a temporal delete. + + + + + + for_portion_of_target + + + The interval to delete. If you are targeting a range column, + you may give this in the form FROM + start_time TO + end_time. + Otherwise you must use + (expression) + where the expression yields a value of the same type as + range_name. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal delete. + This must be a value matching the base type of the range from + range_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 from + range_name. A + NULL here indicates a delete whose end is unbounded + (as with range types). + + + + from_item @@ -238,6 +328,10 @@ DELETE count suppressed by a BEFORE DELETE trigger. If count is 0, no rows were deleted by the query (this is not considered an error). + If FOR PORTION OF was used, the + count also includes + temporal leftovers + that were inserted. diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 40cca063946..ad3224c2df2 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_name for_portion_of_target ] + [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -52,6 +54,45 @@ 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 history + extends outside the FOR PORTION OF bounds, then your update + will only change the history 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 multirange so that their interval + no longer extends beyond the targeted FOR PORTION OF bounds. + Then PostgreSQL will INSERT + new temporal leftovers: + rows whose range or multirange receive the remaining history outside + the targeted bounds, with un-updated values in their other columns. + There will be zero to two inserted records, + depending on whether the original history extended before the targeted + FROM, after the targeted TO, both, or neither. + Multiranges never require two temporal leftovers, because one value can always contain + whatever history remains. + + + + These secondary inserts fire INSERT triggers. + Both STATEMENT and ROW triggers are fired. + The BEFORE UPDATE triggers are fired 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. @@ -116,6 +157,57 @@ UPDATE [ ONLY ] table_name [ * ] [ + + range_name + + + The range or multirange column to use when performing a temporal update. + + + + + + for_portion_of_target + + + The interval to update. If you are targeting a range column, + you may give this in the form FROM + start_time TO + end_time. + Otherwise you must use + (expression) + where the expression yields a value of the same type as + range_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 from + range_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 from + range_name. A + NULL here indicates an update whose end is unbounded + (as with range types). + + + + column_name @@ -283,6 +375,10 @@ UPDATE count updates were suppressed by a BEFORE UPDATE trigger. If count is 0, no rows were updated by the query (this is not considered an error). + If FOR PORTION OF was used, the + count also includes + temporal leftovers + that were inserted. diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index bb1b5faf34e..e3ad9806528 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -373,6 +373,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 diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 27c9eec697b..269c877dbcf 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1295,6 +1295,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 4c5647ac38a..665dbc18239 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -68,6 +68,7 @@ #include "storage/lmgr.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -131,7 +132,6 @@ typedef struct UpdateContext LockTupleMode lockmode; } UpdateContext; - static void ExecBatchInsert(ModifyTableState *mtstate, ResultRelInfo *resultRelInfo, TupleTableSlot **slots, @@ -152,6 +152,10 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static void ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -174,6 +178,9 @@ static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag); +static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate); +static void fireBSTriggers(ModifyTableState *node); +static void fireASTriggers(ModifyTableState *node); /* @@ -1355,6 +1362,192 @@ ExecInsert(ModifyTableContext *context, return result; } +/* ---------------------------------------------------------------- + * ExecForPortionOfLeftovers + * + * Insert tuples for the untouched portion of a row in a FOR + * PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid) +{ + ModifyTableState *mtstate = context->mtstate; + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + AttrNumber rangeAttno; + Datum oldRange; + TypeCacheEntry *typcache; + ForPortionOfState *fpoState = resultRelInfo->ri_forPortionOf; + TupleTableSlot *oldtupleSlot = fpoState->fp_Existing; + TupleTableSlot *leftoverSlot = fpoState->fp_Leftover; + TupleConversionMap *map = NULL; + HeapTuple oldtuple = NULL; + CmdType oldOperation; + TransitionCaptureState *oldTcs; + FmgrInfo flinfo; + ReturnSetInfo rsi; + bool didInit = false; + bool shouldFree = false; + + LOCAL_FCINFO(fcinfo, 2); + + /* + * Get the old pre-UPDATE/DELETE tuple. We will use its range to compute + * untouched parts of history, and if necessary we will insert copies + * with truncated start/end times. + * + * We have already locked the tuple in ExecUpdate/ExecDelete, and it has + * passed EvalPlanQual. This ensures that concurrent updates in READ + * COMMITTED can't insert conflicting temporal 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 being updated. + */ + + 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 temporal 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; + + /* + * Insert a leftover for each value returned by the without_portion helper + * function + */ + while (true) + { + Datum leftover = FunctionCallInvoke(fcinfo); + + /* Are we done? */ + if (rsi.isDone == ExprEndResult) + break; + + if (fcinfo->isnull) + elog(ERROR, "Got a null from without_portion function"); + + if (!didInit) + { + /* + * Make a copy of the pre-UPDATE row. Then we'll overwrite the + * range column below. Convert oldtuple to the base table's format + * if necessary. We need to insert temporal leftovers through the + * root partition so they get routed correctly. + */ + if (map != NULL) + { + leftoverSlot = execute_attr_map_slot(map->attrMap, + oldtupleSlot, + leftoverSlot); + } + else + { + oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, &shouldFree); + ExecForceStoreHeapTuple(oldtuple, leftoverSlot, false); + } + + /* + * Save some mtstate things so we can restore them below. XXX: + * Should we create our own ModifyTableState instead? + */ + oldOperation = mtstate->operation; + mtstate->operation = CMD_INSERT; + oldTcs = mtstate->mt_transition_capture; + + didInit = true; + } + + leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover; + leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false; + ExecMaterializeSlot(leftoverSlot); + + /* + * If there are partitions, we must insert into the root table, so we + * get tuple routing. We already set up leftoverSlot with the root + * tuple descriptor. + */ + if (resultRelInfo->ri_RootResultRelInfo) + resultRelInfo = resultRelInfo->ri_RootResultRelInfo; + + /* + * The standard says that each temporal leftover should execute its + * own INSERT statement, firing all statement and row triggers, but + * skipping insert permission checks. Therefore we give each insert + * its own transition table. If we just push & pop a new trigger level + * for each insert, we get exactly what we need. + */ + AfterTriggerBeginQuery(); + ExecSetupTransitionCaptureState(mtstate, estate); + fireBSTriggers(mtstate); + ExecInsert(context, resultRelInfo, leftoverSlot, node->canSetTag, NULL, NULL); + fireASTriggers(mtstate); + AfterTriggerEndQuery(estate); + } + + if (didInit) + { + mtstate->operation = oldOperation; + mtstate->mt_transition_capture = oldTcs; + + if (shouldFree) + heap_freetuple(oldtuple); + } +} + /* ---------------------------------------------------------------- * ExecBatchInsert * @@ -1508,7 +1701,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 temporal leftovers from a + * DELETE FOR PORTION OF. */ static void ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -1541,6 +1735,10 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ar_delete_trig_tcs = NULL; } + /* Compute temporal 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); @@ -1966,7 +2164,10 @@ 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; @@ -2315,7 +2516,8 @@ lreplace: * ExecUpdateEpilogue -- subroutine for ExecUpdate * * Closing steps of updating a tuple. Must be called if ExecUpdateAct - * returns indicating that the tuple was updated. + * returns indicating that the tuple was updated. It also inserts temporal + * leftovers from an UPDATE FOR PORTION OF. */ static void ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, @@ -2333,6 +2535,10 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, NULL, NIL, (updateCxt->updateIndexes == TU_Summarizing)); + /* Compute temporal 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, @@ -5062,6 +5268,122 @@ 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 temporal leftovers */ + + fpoState->fp_Leftover = + ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual); + + /* + * We must attach the ForPortionOfState to all result rels, in case of + * a cross-partition update or triggers firing on partitions. 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); + + resultRelInfo->ri_forPortionOf = leafState; + } + + /* Make sure the root relation has the FOR PORTION OF clause too. */ + if (node->rootRelation > 0) + mtstate->rootResultRelInfo->ri_forPortionOf = fpoState; + + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && + mtstate->mt_partition_tuple_routing == NULL) + { + /* + * We will need tuple routing to insert temporal leftovers. Since + * we are initializing things before ExecCrossPartitionUpdate + * runs, we must do everything it needs as well. + */ + if (mtstate->mt_partition_tuple_routing == NULL) + { + Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; + MemoryContext oldcxt; + + /* Things built here have to last for the query duration. */ + oldcxt = MemoryContextSwitchTo(estate->es_query_cxt); + + mtstate->mt_partition_tuple_routing = + ExecSetupPartitionTupleRouting(estate, rootRel); + + /* + * Before a partition's tuple can be re-routed, it must first + * be converted to the root's format, so we'll need a slot for + * storing such tuples. + */ + Assert(mtstate->mt_root_tuple_slot == NULL); + mtstate->mt_root_tuple_slot = table_slot_create(rootRel, NULL); + + MemoryContextSwitchTo(oldcxt); + } + } + + /* + * Don't free the ExprContext here because the result must last for + * the whole query + */ + } + /* * If we have any secondary relations in an UPDATE or DELETE, they need to * be treated like non-locked relations in SELECT FOR UPDATE, i.e., the diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index ede838cd40c..e40e8eecf73 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2567,6 +2567,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; @@ -2715,6 +2723,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)) @@ -3609,6 +3619,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; @@ -3790,6 +3813,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 8af091ba647..4a43f579f84 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); @@ -2675,6 +2675,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); @@ -7001,7 +7002,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); bool returning_old_or_new = false; @@ -7070,6 +7071,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, node->exclRelTlist = onconflict->exclRelTlist; } node->updateColnosLists = updateColnosLists; + node->forPortionOf = (Node *) forPortionOf; node->withCheckOptionLists = withCheckOptionLists; node->returningOldAlias = root->parse->returningOldAlias; node->returningNewAlias = root->parse->returningNewAlias; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index c4fd646b999..7e2f19fb5e2 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2151,6 +2151,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 e4fd6950fad..32b1930b945 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3635,7 +3635,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); @@ -3701,6 +3701,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 3b392b084ad..5cf73278e16 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -24,7 +24,10 @@ #include "postgres.h" +#include "access/stratnum.h" #include "access/sysattr.h" +#include "catalog/pg_am.h" +#include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -50,7 +53,9 @@ #include "parser/parsetree.h" #include "utils/backend_status.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/guc.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -67,10 +72,16 @@ typedef struct SelectStmtPassthrough 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, SelectStmtPassthrough *passthru); @@ -493,6 +504,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 @@ -565,6 +590,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_DELETE; @@ -603,7 +629,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"); transformReturningClause(pstate, qry, stmt->returningClause, @@ -1238,7 +1268,7 @@ transformOnConflictClause(ParseState *pstate, * Now transform the UPDATE subexpressions. */ onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, @@ -1268,6 +1298,194 @@ 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. + */ + 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; + GetOperatorFromCompareType(opclass, InvalidOid, COMPARE_OVERLAP, &opid, &strat); + result->overlapsExpr = (Node *) makeSimpleA_Expr(AEXPR_OP, get_opname(opid), + (Node *) copyObject(rangeVar), targetExpr, + forPortionOf->location); + + /* + * Look up the without_portion func. This computes the bounds of temporal + * leftovers. + * + * XXX: Find a more extensible way to look up the function, permitting + * user-defined types. An opclass support function doesn't make sense, + * since there is no index involved. Perhaps a type support function. + */ + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + switch (opcintype) + { + case ANYRANGEOID: + result->withoutPortionProc = F_RANGE_MINUS_MULTI; + break; + case ANYMULTIRANGEOID: + result->withoutPortionProc = F_MULTIRANGE_MINUS_MULTI; + break; + default: + elog(ERROR, "unexpected opcintype: %u", opcintype); + } + else + elog(ERROR, "unexpected opclass: %u", opclass); + + 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 intersectoperoid; + List *funcArgs = NIL; + FuncExpr *rangeTLEExpr; + TargetEntry *tle; + + /* + * Whatever operator is used for intersect by temporal foreign keys, + * we can use its backing procedure for intersects in FOR PORTION OF. + * XXX: Share code with FindFKPeriodOpers? + */ + switch (opcintype) + { + case ANYRANGEOID: + intersectoperoid = OID_RANGE_INTERSECT_RANGE_OP; + break; + case ANYMULTIRANGEOID: + intersectoperoid = OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP; + break; + default: + elog(ERROR, "Unexpected opcintype: %u", opcintype); + } + funcid = get_opcode(intersectoperoid); + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify an intersect function for type %s", format_type_be(opcintype))); + + targetExpr = transformExpr(pstate, targetExpr, EXPR_KIND_UPDATE_PORTION); + funcArgs = lappend(funcArgs, copyObject(rangeVar)); + funcArgs = lappend(funcArgs, targetExpr); + rangeTLEExpr = makeFuncExpr(funcid, attr->atttypid, funcArgs, + InvalidOid, InvalidOid, COERCE_EXPLICIT_CALL); + + /* Make a TLE to set the range column */ + result->rangeTargetList = NIL; + tle = makeTargetEntry((Expr *) 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 @@ -2491,6 +2709,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_UPDATE; @@ -2508,6 +2727,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 */ @@ -2524,7 +2747,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"); transformReturningClause(pstate, qry, stmt->returningClause, @@ -2534,7 +2758,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; @@ -2553,7 +2777,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; @@ -2606,6 +2830,20 @@ 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 a65097f19cf..fe987842b06 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -250,6 +250,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RangeVar *range; IntoClause *into; WithClause *with; + ForPortionOfClause *forportionof; InferClause *infer; OnConflictClause *onconflict; A_Indices *aind; @@ -554,6 +555,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 @@ -763,7 +766,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 @@ -882,12 +885,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 '*' '/' '%' @@ -12530,6 +12536,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->returningClause = $9; + n->withClause = $1; + $$ = (Node *) n; + } ; using_clause: @@ -12604,6 +12624,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->returningClause = $10; + n->withClause = $1; + $$ = (Node *) n; + } ; set_clause_list: @@ -14101,6 +14140,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 */ @@ -14941,16 +15018,25 @@ 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 + | MINUTE_P %prec IS { $$ = list_make1(makeIntConst(INTERVAL_MASK(MINUTE), @1)); } | interval_second { $$ = $1; } @@ -18004,6 +18090,7 @@ unreserved_keyword: | PLAN | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -18633,6 +18720,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 3254c83cc6c..8b8c2b9299c 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 @@ -996,6 +1003,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 d2e218353f3..522345b1668 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 12119f147fc..d8a96fbf534 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -585,6 +585,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 @@ -1861,6 +1864,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 @@ -3174,6 +3180,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 778d69c6f3c..4764dd21c90 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2783,6 +2783,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 51d7703eff7..ed276c41460 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 adc9e7600e1..e883b7f2a60 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3729,6 +3729,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, @@ -4068,6 +4092,25 @@ 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 059fc5ebf60..d6b1eb57a48 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -128,7 +128,7 @@ typedef struct RI_ConstraintInfo Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */ Oid period_contained_by_oper; /* anyrange <@ anyrange */ Oid agged_period_contained_by_oper; /* fkattr <@ range_agg(pkattr) */ - Oid period_intersect_oper; /* anyrange * anyrange */ + Oid period_intersect_oper; /* anyrange * anyrange (or multirange) */ dlist_node valid_link; /* Link in list of valid entries */ } RI_ConstraintInfo; diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index fa7cd7e06a7..43158afac15 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -2355,6 +2355,33 @@ get_typisdefined(Oid typid) return false; } +/* + * 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/nodes/execnodes.h b/src/include/nodes/execnodes.h index 18ae8f0d4bb..acb3d5458a2 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -48,6 +48,7 @@ #include "utils/sortsupport.h" #include "utils/tuplesort.h" #include "utils/tuplestore.h" +#include "utils/typcache.h" /* * forward references in this file @@ -452,6 +453,24 @@ 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/multirange 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 */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -588,6 +607,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 ecbddd12e1b..a09e9d9ba6a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -147,6 +147,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 */ @@ -1613,6 +1616,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 @@ -2126,6 +2144,7 @@ typedef struct DeleteStmt Node *whereClause; /* qualifications */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } DeleteStmt; /* ---------------------- @@ -2141,6 +2160,7 @@ typedef struct UpdateStmt List *fromClause; /* optional from clause for more tables */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } UpdateStmt; /* ---------------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 30d889b54c5..eba697257f2 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2612,6 +2612,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 c4393a94321..92b87c14859 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -368,6 +368,8 @@ typedef struct ModifyTable List *onConflictCols; /* WHERE for ON CONFLICT UPDATE */ Node *onConflictWhere; + /* FOR PORTION OF clause for UPDATE/DELETE */ + Node *forPortionOf; /* RTI of the EXCLUDED pseudo relation */ Index exclRelRTI; /* tlist of the EXCLUDED pseudo relation */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 1b4436f2ff6..fbbcd77dd84 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2385,4 +2385,31 @@ 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; /* SRF 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 955e9056858..ac0f691743f 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -286,7 +286,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 f29ed03b476..4614be052dc 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 void transformReturningClause(ParseState *pstate, Query *qry, ReturningClause *returningClause, ParseExprKind exprKind); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 84182eaaae2..a8def3a386c 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -346,6 +346,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 f7d07c84542..3e457d961fe 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 50fb149e9ac..5b50ef230ab 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -150,6 +150,7 @@ 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 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..9288b4224f7 --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,1248 @@ +-- 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 (id, valid_at, name) 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 (id, valid1_at, valid2_at, name) 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 (id, valid_at, name) 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; +-- +-- UPDATE tests +-- +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 (id, valid_at, name) 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') + ; +\set QUIET false +-- Updating 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 + ^ +-- Updating 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 + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. +-- Updating 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 +-- Updating 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 '201... + ^ +-- Updating 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 + ^ +-- Updating 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)'; +UPDATE 0 +-- 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)'; +UPDATE 2 +-- 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)'; +UPDATE 2 +-- 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)'; +UPDATE 2 +-- 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)'; +UPDATE 3 +-- 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)'; +UPDATE 1 +-- 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)'; +UPDATE 1 +-- 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)'; +UPDATE 3 +-- 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)'; +UPDATE 5 +-- 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)'; +UPDATE 3 +-- Updating 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)'; +UPDATE 3 +-- 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 5 +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL + SET name = name || '*'; +UPDATE 4 +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) + +\set QUIET true +-- 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)'; +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","2020-01-01 00:00:00") | one +(5 rows) + +DROP TABLE for_portion_of_test2; +-- UPDATE FOR PORTION OF in a CTE: +-- Visible to SELECT: +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[10,11)' + RETURNING id, valid_at, name +) +SELECT * + FROM for_portion_of_test AS t, update_apr + WHERE t.id = update_apr.id; + id | valid_at | name | id | valid_at | name +---------+-------------------------+------+---------+-------------------------+---------- + [10,11) | [2018-01-01,2020-01-01) | ten | [10,11) | [2018-04-01,2018-05-01) | Apr 2018 +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + id | valid_at | name +---------+-------------------------+---------- + [10,11) | [2018-04-01,2018-05-01) | Apr 2018 + [10,11) | [2018-01-01,2018-04-01) | ten + [10,11) | [2018-05-01,2020-01-01) | ten +(3 rows) + +-- Not visible to UPDATE: +-- Tuples updated/inserted within the CTE are not visible to the main query yet, +-- but neither are old tuples the CTE changed: +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[11,12)' + RETURNING id, valid_at, name +) +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + AS t + SET name = 'May 2018' + FROM update_apr AS j + WHERE t.id = j.id; +SELECT * FROM for_portion_of_test WHERE id = '[11,12)'; + id | valid_at | name +---------+-------------------------+---------- + [11,12) | [2018-04-01,2018-05-01) | Apr 2018 + [11,12) | [2018-01-01,2018-04-01) | eleven + [11,12) | [2018-05-01,2020-01-01) | eleven +(3 rows) + +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)'); +-- UPDATE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + SET name = concat(_target_from::text, ' to ', _target_til::text) + WHERE id = $1; +END; +$$; +SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01'); + fpo_update +------------ + +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + id | valid_at | name +---------+-------------------------+-------------------------- + [10,11) | [2018-01-01,2019-01-01) | 2015-01-01 to 2019-01-01 + [10,11) | [2019-01-01,2020-01-01) | ten +(2 rows) + +DROP TABLE for_portion_of_test; +-- +-- DELETE tests +-- +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 (id, valid_at, name) 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'), + ('[6,7)', '[2018-01-01,)', 'six'), + ('[7,8)', '(,2018-04-01)', 'seven'), + ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'), + ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'), + ('[8,9)', '[2018-03-03,2018-04-04)', 'eight') + ; +\set QUIET false +-- 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 + ^ +-- The wrong type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO 4 + 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 + ^ +DETAIL: No function of that name accepts the given argument types. +HINT: You might need to add explicit type casts. +-- 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 a subquery fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + 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 '201... + ^ +-- Deleting with a column fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + 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 + ^ +-- 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)'; +DELETE 0 +-- Deleting a finite/open portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + WHERE id = '[3,4)'; +DELETE 2 +-- Deleting a finite/open portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-03-01' + WHERE id = '[6,7)'; +DELETE 2 +-- Deleting an open/finite portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-02-01' + WHERE id = '[4,5)'; +DELETE 2 +-- Deleting an open/finite portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO NULL + WHERE id = '[7,8)'; +DELETE 2 +-- Deleting a finite/finite portion with an exact fit +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01' + WHERE id = '[4,5)'; +DELETE 1 +-- Deleting an enclosed span +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[2,3)'; +DELETE 1 +-- Deleting an open/open portion with a finite/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' + WHERE id = '[5,6)'; +DELETE 3 +-- Deleting an enclosed span with separate protruding spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03' + WHERE id = '[1,2)'; +DELETE 1 +-- Deleting multiple enclosed spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[8,9)'; +DELETE 3 +-- Deleting with a direct target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) + WHERE id = '[1,2)'; +DELETE 3 +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL; +DELETE 4 +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one + [3,4) | [2018-01-01,2018-06-01) | three + [5,6) | (,2018-01-01) | five + [5,6) | [2019-01-01,2030-01-01) | five + [6,7) | [2018-03-01,2030-01-01) | six + [7,8) | (,2017-01-01) | seven +(8 rows) + +\set QUIET true +-- 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) + +-- DELETE ... RETURNING returns the deleted values (regardless of bounds) +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-02' TO '2018-02-03' + WHERE id = '[3,4)' + RETURNING *; + id | valid_at | name +-------+-------------------------+--------- + [3,4) | [2018-02-01,2018-02-15) | three^3 +(1 row) + +-- DELETE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + WHERE id = $1; +END; +$$; +SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01'); + fpo_delete +------------ + +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + id | valid_at | name +---------+-------------------------+------ + [10,11) | [2019-01-01,2020-01-01) | ten +(1 row) + +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)'); +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + + IF TG_ARGV[0] THEN + RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); + ELSE + RAISE NOTICE ' old: %', OLD.valid_at; + END IF; + IF TG_ARGV[1] THEN + RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table); + ELSE + RAISE NOTICE ' new: %', NEW.valid_at; + END IF; + + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + RETURN OLD; + END IF; +END; +$$; +-- statement triggers: +CREATE TRIGGER fpo_before_stmt +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_stmt +AFTER INSERT ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_update_stmt +AFTER UPDATE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_delete_stmt +AFTER DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +-- row triggers: +CREATE TRIGGER fpo_before_row +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +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: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2019-01-01,2030-01-01) +NOTICE: new: [2021-01-01,2022-01-01) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2021-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2021-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2030-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2030-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: [2019-01-01,2030-01-01) +NOTICE: new: [2021-01-01,2022-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' + WHERE id = '[5,6)'; +NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: old: [2022-01-01,2030-01-01) +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2023-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2023-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2024-01-01,2030-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2024-01-01,2030-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2022-01-01,2030-01-01) +NOTICE: new: +NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one + [3,4) | [2018-01-01,2018-02-01) | three + [3,4) | [2018-02-01,2018-02-02) | three^3 + [3,4) | [2018-02-03,2018-02-15) | three^3 + [3,4) | [2018-02-15,2018-06-01) | three + [5,6) | (,2018-01-01) | five + [5,6) | [2019-01-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,2030-01-01) | five + [6,7) | [2018-03-01,2030-01-01) | six + [7,8) | (,2017-01-01) | seven +(14 rows) + +-- 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'); +-- statement triggers: +CREATE TRIGGER fpo_before_stmt +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true); +CREATE TRIGGER fpo_after_update_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true); +CREATE TRIGGER fpo_after_delete_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false); +-- row triggers: +CREATE TRIGGER fpo_before_row +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true); +CREATE TRIGGER fpo_after_update_row +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(true, true); +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false); +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: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-15,2019-01-01) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-01,2018-01-15) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[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: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-21,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: +NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: +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: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-01,2018-01-02) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-02,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +ROLLBACK; +-- Deferred triggers +-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables) +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 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); +CREATE CONSTRAINT TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE CONSTRAINT TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE CONSTRAINT TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +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'; +COMMIT; +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-01,2018-01-15) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2020-01-01) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-15,2019-01-01) +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +COMMIT; +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-21,2019-01-01) +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2018-01-15,2019-01-01) +NOTICE: new: +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2018-01-01,2018-01-15) +NOTICE: new: +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +COMMIT; +SELECT * FROM for_portion_of_test; + id | valid_at | name +-------+-------------------------+-------------------------- + [1,2) | [2019-01-01,2020-01-01) | one + [1,2) | [2018-01-21,2019-01-01) | 2018-01-15_to_2019-01-01 +(2 rows) + +-- test FOR PORTION OF from triggers during FOR PORTION OF: +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 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'), + ('[2,3)', '[2018-01-01,2020-01-01)', 'two'), + ('[3,4)', '[2018-01-01,2020-01-01)', 'three'), + ('[4,5)', '[2018-01-01,2020-01-01)', 'four'); +CREATE FUNCTION trg_fpo_update() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01' + SET name = CONCAT(name, '^') + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; +CREATE FUNCTION trg_fpo_delete() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01' + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; +-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-01,2018-02-01) | one + [1,2) | [2018-02-01,2018-03-01) | one^ + [1,2) | [2018-03-01,2018-05-01) | one + [1,2) | [2018-05-01,2018-06-01) | one* + [1,2) | [2018-06-01,2020-01-01) | one +(5 rows) + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; +-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [2,3) | [2018-01-01,2018-02-01) | two + [2,3) | [2018-02-01,2018-03-01) | two^ + [2,3) | [2018-03-01,2018-05-01) | two + [2,3) | [2018-06-01,2020-01-01) | two +(4 rows) + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[3,4)'; +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [3,4) | [2018-01-01,2018-03-01) | three + [3,4) | [2018-04-01,2018-05-01) | three + [3,4) | [2018-05-01,2018-06-01) | three* + [3,4) | [2018-06-01,2020-01-01) | three +(4 rows) + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; +-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[4,5)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [4,5) | [2018-01-01,2018-03-01) | four + [4,5) | [2018-04-01,2018-05-01) | four + [4,5) | [2018-06-01,2020-01-01) | four +(3 rows) + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- 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 (id, valid_at, name) 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 (id, valid_at, name) 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 (id, valid_at, name) 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/privileges.out b/src/test/regress/expected/privileges.out index daafaa94fde..9ff8e7fb363 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1119,6 +1119,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 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 03df7e75b7b..4fb928d561d 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3700,6 +3700,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 f3144bdc39c..401550b5482 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -2,7 +2,7 @@ -- -- We leave behind several tables to test pg_dump etc: -- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. +-- temporal_fk_rng2rng, temporal_fk2_rng2rng. SET datestyle TO ISO, YMD; -- -- test input parser @@ -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 @@ -920,26 +950,43 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; + tableoid | id | valid_at | name +----------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-02-01) | one + tp1 | [1,2) | [2000-02-01,2000-03-01) | one + tp2 | [3,4) | [2000-01-01,2010-01-01) | three (3 rows) -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; + tableoid | id | valid_at | name +----------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-01-15) | one + tp1 | [1,2) | [2000-01-15,2000-02-01) | one2 + tp1 | [1,2) | [2000-02-01,2000-02-15) | one2 + tp1 | [1,2) | [2000-02-15,2000-02-20) | one + tp1 | [1,2) | [2000-02-25,2000-03-01) | one + tp1 | [2,3) | [2002-01-01,2003-01-01) | three + tp2 | [3,4) | [2000-01-01,2000-01-15) | three + tp2 | [3,4) | [2000-02-15,2002-01-01) | three + tp2 | [3,4) | [2003-01-01,2010-01-01) | three + tp2 | [4,5) | [2000-02-20,2000-02-25) | one +(10 rows) DROP TABLE temporal_partitioned; -- temporal UNIQUE: @@ -955,26 +1002,43 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one - [3,4) | [2000-01-01,2010-01-01) | three +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; + tableoid | id | valid_at | name +----------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-02-01) | one + tp1 | [1,2) | [2000-02-01,2000-03-01) | one + tp2 | [3,4) | [2000-01-01,2010-01-01) | three (3 rows) -SELECT * FROM tp1 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------ - [1,2) | [2000-01-01,2000-02-01) | one - [1,2) | [2000-02-01,2000-03-01) | one -(2 rows) - -SELECT * FROM tp2 ORDER BY id, valid_at; - id | valid_at | name --------+-------------------------+------- - [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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; + tableoid | id | valid_at | name +----------+-------+-------------------------+------- + tp1 | [1,2) | [2000-01-01,2000-01-15) | one + tp1 | [1,2) | [2000-01-15,2000-02-01) | one2 + tp1 | [1,2) | [2000-02-01,2000-02-15) | one2 + tp1 | [1,2) | [2000-02-15,2000-02-20) | one + tp1 | [1,2) | [2000-02-25,2000-03-01) | one + tp1 | [2,3) | [2002-01-01,2003-01-01) | three + tp2 | [3,4) | [2000-01-01,2000-01-15) | three + tp2 | [3,4) | [2000-02-15,2002-01-01) | three + tp2 | [3,4) | [2003-01-01,2010-01-01) | three + tp2 | [4,5) | [2000-02-20,2000-02-25) | one +(10 rows) DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY @@ -1755,6 +1819,33 @@ 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". +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2016-02-01,2016-03-01) + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) + [7,8) | [2018-01-02,2018-01-03) +(4 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + -- 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') @@ -1802,6 +1893,42 @@ 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)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + +-- 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". +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + -- 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'); @@ -1818,11 +1945,12 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE RESTRICT; ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD -- --- test ON UPDATE/DELETE options +-- rng2rng test ON UPDATE/DELETE options -- -- test FK referenced updates CASCADE +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1830,8 +1958,9 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE CASCADE ON UPDATE CASCADE; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1839,9 +1968,10 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE SET NULL ON UPDATE SET NULL; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET DEFAULT +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', ADD CONSTRAINT temporal_fk_rng2rng_fk @@ -2211,6 +2341,22 @@ UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing an unreferenced part is okay: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +UPDATE temporal_mltrng SET id = '[7,8)' + WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- test FK referenced updates RESTRICT -- @@ -2253,6 +2399,19 @@ BEGIN; COMMIT; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- deleting an unreferenced part is okay: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- FK between partitioned tables: ranges -- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index a0f5fab0f5d..84435026217 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..0e6c2db5a75 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,905 @@ +-- 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 (id, valid_at, name) 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 (id, valid1_at, valid2_at, name) 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 (id, valid_at, name) 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; + +-- +-- UPDATE tests +-- + +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 (id, valid_at, name) 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') + ; +\set QUIET false + +-- Updating 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)'; + +-- Updating 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)'; + +-- Updating 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)'; + +-- Updating 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)'; + +-- Updating 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)'; + +-- Updating 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)'; + +-- Updating 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; +\set QUIET true + +-- 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)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; +DROP TABLE for_portion_of_test2; + +-- UPDATE FOR PORTION OF in a CTE: + +-- Visible to SELECT: +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[10,11)' + RETURNING id, valid_at, name +) +SELECT * + FROM for_portion_of_test AS t, update_apr + WHERE t.id = update_apr.id; +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + +-- Not visible to UPDATE: +-- Tuples updated/inserted within the CTE are not visible to the main query yet, +-- but neither are old tuples the CTE changed: +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[11,12)' + RETURNING id, valid_at, name +) +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + AS t + SET name = 'May 2018' + FROM update_apr AS j + WHERE t.id = j.id; +SELECT * FROM for_portion_of_test WHERE id = '[11,12)'; +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)'); + +-- UPDATE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + SET name = concat(_target_from::text, ' to ', _target_til::text) + WHERE id = $1; +END; +$$; +SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01'); +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + +DROP TABLE for_portion_of_test; + +-- +-- DELETE tests +-- + +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 (id, valid_at, name) 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'), + ('[6,7)', '[2018-01-01,)', 'six'), + ('[7,8)', '(,2018-04-01)', 'seven'), + ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'), + ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'), + ('[8,9)', '[2018-03-03,2018-04-04)', 'eight') + ; +\set QUIET false + +-- 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)'; + +-- The wrong type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO 4 + WHERE id = '[3,4)'; + +-- 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 a subquery fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + WHERE id = '[3,4)'; + +-- Deleting with a column fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + 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 a finite/open portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + WHERE id = '[3,4)'; + +-- Deleting a finite/open portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-03-01' + WHERE id = '[6,7)'; + +-- Deleting an open/finite portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-02-01' + WHERE id = '[4,5)'; + +-- Deleting an open/finite portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO NULL + WHERE id = '[7,8)'; + +-- Deleting a finite/finite portion with an exact fit +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01' + WHERE id = '[4,5)'; + +-- Deleting an enclosed span +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[2,3)'; + +-- Deleting an open/open portion with a finite/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' + WHERE id = '[5,6)'; + +-- Deleting an enclosed span with separate protruding spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03' + WHERE id = '[1,2)'; + +-- Deleting multiple enclosed spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[8,9)'; + +-- Deleting with a direct target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) + WHERE id = '[1,2)'; + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; +\set QUIET true + +-- 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 *; + +-- DELETE ... RETURNING returns the deleted values (regardless of bounds) +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-02' TO '2018-02-03' + WHERE id = '[3,4)' + RETURNING *; + +-- DELETE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + WHERE id = $1; +END; +$$; +SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01'); +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)'); + + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows + +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + + IF TG_ARGV[0] THEN + RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); + ELSE + RAISE NOTICE ' old: %', OLD.valid_at; + END IF; + IF TG_ARGV[1] THEN + RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table); + ELSE + RAISE NOTICE ' new: %', NEW.valid_at; + END IF; + + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + RETURN OLD; + END IF; +END; +$$; + +-- statement triggers: + +CREATE TRIGGER fpo_before_stmt +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_stmt +AFTER INSERT ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_update_stmt +AFTER UPDATE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_delete_stmt +AFTER DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +-- row triggers: + +CREATE TRIGGER fpo_before_row +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + + +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; + +-- 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'); + +-- statement triggers: + +CREATE TRIGGER fpo_before_stmt +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true); + +CREATE TRIGGER fpo_after_update_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true); + +CREATE TRIGGER fpo_after_delete_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false); + +-- row triggers: + +CREATE TRIGGER fpo_before_row +BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true); + +CREATE TRIGGER fpo_after_update_row +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(true, true); + +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false); + +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; + +-- Deferred triggers +-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables) + +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 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); + +CREATE CONSTRAINT TRIGGER fpo_after_insert_row +AFTER INSERT ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE CONSTRAINT TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE CONSTRAINT TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +DEFERRABLE INITIALLY DEFERRED +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +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'; +COMMIT; + +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +COMMIT; + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +COMMIT; + +SELECT * FROM for_portion_of_test; + +-- test FOR PORTION OF from triggers during FOR PORTION OF: + +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 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'), + ('[2,3)', '[2018-01-01,2020-01-01)', 'two'), + ('[3,4)', '[2018-01-01,2020-01-01)', 'three'), + ('[4,5)', '[2018-01-01,2020-01-01)', 'four'); + +CREATE FUNCTION trg_fpo_update() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01' + SET name = CONCAT(name, '^') + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; + +CREATE FUNCTION trg_fpo_delete() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01' + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; + +-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF + +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[1,2)'; + +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; + +-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF + +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; + +-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF + +CREATE TRIGGER fpo_after_update_row +AFTER UPDATE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[3,4)'; + +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; + +-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF + +CREATE TRIGGER fpo_after_delete_row +AFTER DELETE ON for_portion_of_test +FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[4,5)'; + +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; + +-- 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 (id, valid_at, name) 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 (id, valid_at, name) 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 (id, valid_at, name) 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/privileges.sql b/src/test/regress/sql/privileges.sql index 96eff1104d2..ae57f233314 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -765,6 +765,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 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 c071fffc116..e8c04e3ad91 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1881,6 +1881,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 4aaca242bbe..c5c89fe40ab 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -2,7 +2,7 @@ -- -- We leave behind several tables to test pg_dump etc: -- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. +-- temporal_fk_rng2rng, temporal_fk2_rng2rng. SET datestyle TO ISO, YMD; @@ -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; -- @@ -667,9 +681,23 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -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; +SELECT tableoid::regclass, * FROM temporal_partitioned 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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- temporal UNIQUE: @@ -685,9 +713,23 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -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; +SELECT tableoid::regclass, * FROM temporal_partitioned 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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY @@ -1291,6 +1333,18 @@ 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)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; -- 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') @@ -1338,6 +1392,18 @@ 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)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; +-- 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)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; -- 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'); @@ -1356,12 +1422,13 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE RESTRICT; -- --- test ON UPDATE/DELETE options +-- rng2rng test ON UPDATE/DELETE options -- -- test FK referenced updates CASCADE +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1369,8 +1436,9 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE CASCADE ON UPDATE CASCADE; -- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1378,9 +1446,10 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE SET NULL ON UPDATE SET NULL; -- test FK referenced updates SET DEFAULT +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', ADD CONSTRAINT temporal_fk_rng2rng_fk @@ -1716,6 +1785,20 @@ BEGIN; WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; -- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' + WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing an unreferenced part is okay: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '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_mltrng2mltrng WHERE id = '[3,4)'; UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); @@ -1760,6 +1843,17 @@ BEGIN; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +WHERE id = '[5,6)'; +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- FK between partitioned tables: ranges diff --git a/src/test/subscription/t/034_temporal.pl b/src/test/subscription/t/034_temporal.pl index 6bbf6567279..b74693cb89c 100644 --- a/src/test/subscription/t/034_temporal.pl +++ b/src/test/subscription/t/034_temporal.pl @@ -137,6 +137,12 @@ is( $stderr, qq(psql::1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), "can't UPDATE temporal_no_key DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "UPDATE temporal_no_key FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't UPDATE FOR PORTION OF temporal_no_key DEFAULT"); ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); @@ -144,6 +150,12 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_no_key DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_no_key DEFAULT"); $node_publisher->wait_for_catchup('sub1'); @@ -165,16 +177,22 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk DEFAULT'); # replicate with a unique key: @@ -192,6 +210,12 @@ is( $stderr, qq(psql::1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), "can't UPDATE temporal_unique DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't UPDATE FOR PORTION OF temporal_unique DEFAULT"); ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); @@ -199,6 +223,12 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_unique DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_unique DEFAULT"); $node_publisher->wait_for_catchup('sub1'); @@ -287,16 +317,22 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_no_key FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_no_key ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key FULL'); # replicate with a primary key: @@ -310,16 +346,22 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk FULL'); # replicate with a unique key: @@ -333,17 +375,23 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_unique ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b -[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique FULL'); +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b +[4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique DEFAULT'); # cleanup @@ -425,16 +473,22 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk USING INDEX'); # replicate with a unique key: @@ -448,16 +502,22 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_unique ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique USING INDEX'); # cleanup @@ -543,6 +603,12 @@ is( $stderr, qq(psql::1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), "can't UPDATE temporal_no_key NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "UPDATE temporal_no_key FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot update table "temporal_no_key" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't UPDATE temporal_no_key NOTHING"); ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); @@ -550,6 +616,12 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_no_key NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE temporal_no_key NOTHING"); $node_publisher->wait_for_catchup('sub1'); @@ -575,6 +647,12 @@ is( $stderr, qq(psql::1: ERROR: cannot update table "temporal_pk" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), "can't UPDATE temporal_pk NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot update table "temporal_pk" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't UPDATE temporal_pk NOTHING"); ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); @@ -582,6 +660,12 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_pk NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE temporal_pk NOTHING"); $node_publisher->wait_for_catchup('sub1'); @@ -607,6 +691,12 @@ is( $stderr, qq(psql::1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), "can't UPDATE temporal_unique NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot update table "temporal_unique" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't UPDATE FOR PORTION OF temporal_unique NOTHING"); ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); @@ -614,6 +704,12 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_unique NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'"); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_unique NOTHING"); $node_publisher->wait_for_catchup('sub1'); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 018b5919cf6..c0a8253418c 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -837,6 +837,9 @@ ForBothState ForEachState ForFiveState ForFourState +ForPortionOfClause +ForPortionOfExpr +ForPortionOfState ForThreeState ForeignAsyncConfigureWait_function ForeignAsyncNotify_function @@ -970,6 +973,7 @@ Form_pg_ts_template Form_pg_type Form_pg_user_mapping FormatNode +FPO_QueryHashEntry FreeBlockNumberArray FreeListData FreePageBtree -- 2.39.5