From e41e42d444bfa89aec704c74bb03ca966f9f4442 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Fri, 25 Jun 2021 18:54:35 -0700 Subject: [PATCH v43 5/8] Add UPDATE/DELETE FOR PORTION OF - Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. But we permit the magic UNBOUNDED keyword as the FROM or the TO (or both) to perform an unbounded update/delete. We also accept functions like NOW(). - Added logic to executor to insert new rows for the "leftover" part of a record touched by a FOR PORTION OF query. - Added tg_temporal descriptor to the TriggerData struct that we pass to trigger functions. Our triggers use this to learn what bounds were given in the FOR PORTION OF clause. - Documented FOR PORTION OF. - Documented tg_temporal struct. - Added tests. --- .../postgres_fdw/expected/postgres_fdw.out | 45 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 34 + contrib/sepgsql/proc.c | 2 +- contrib/unaccent/unaccent.c | 2 +- doc/src/sgml/ref/create_publication.sgml | 6 + doc/src/sgml/ref/delete.sgml | 72 +- doc/src/sgml/ref/update.sgml | 90 ++- doc/src/sgml/trigger.sgml | 64 +- src/backend/commands/explain.c | 2 +- src/backend/commands/tablecmds.c | 1 + src/backend/commands/trigger.c | 46 ++ src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 497 +++++++++++- src/backend/executor/spi.c | 2 +- src/backend/nodes/nodeFuncs.c | 24 + src/backend/optimizer/plan/createplan.c | 6 +- src/backend/optimizer/plan/planner.c | 1 + src/backend/optimizer/util/pathnode.c | 3 +- src/backend/parser/analyze.c | 241 +++++- src/backend/parser/gram.y | 100 ++- src/backend/parser/parse_agg.c | 10 + src/backend/parser/parse_collate.c | 1 + src/backend/parser/parse_expr.c | 8 + src/backend/parser/parse_func.c | 3 + src/backend/parser/parse_merge.c | 2 +- src/backend/rewrite/rewriteHandler.c | 40 + src/backend/utils/adt/ri_triggers.c | 74 ++ src/backend/utils/cache/lsyscache.c | 61 +- src/include/commands/trigger.h | 1 + src/include/nodes/execnodes.h | 25 + src/include/nodes/parsenodes.h | 20 + src/include/nodes/pathnodes.h | 1 + src/include/nodes/plannodes.h | 1 + src/include/nodes/primnodes.h | 26 + src/include/optimizer/pathnode.h | 2 +- src/include/parser/analyze.h | 3 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/utils/lsyscache.h | 4 +- src/test/regress/expected/for_portion_of.out | 764 ++++++++++++++++++ src/test/regress/expected/opr_sanity.out | 1 + src/test/regress/expected/privileges.out | 18 + src/test/regress/expected/updatable_views.out | 32 + .../regress/expected/without_overlaps.out | 186 ++++- src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/for_portion_of.sql | 597 ++++++++++++++ src/test/regress/sql/opr_sanity.sql | 1 + src/test/regress/sql/privileges.sql | 18 + src/test/regress/sql/updatable_views.sql | 14 + src/test/regress/sql/without_overlaps.sql | 96 ++- src/test/subscription/t/034_temporal.pl | 110 ++- 51 files changed, 3271 insertions(+), 91 deletions(-) create mode 100644 src/test/regress/expected/for_portion_of.out create mode 100644 src/test/regress/sql/for_portion_of.sql diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index f2bcd6aa98c..d7b93891ddb 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -57,11 +57,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, @@ -88,10 +96,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 -- =================================================================== @@ -139,6 +154,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 -- =================================================================== @@ -210,7 +231,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 @@ -6125,6 +6147,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 372fe6dad15..61c075850dc 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -61,12 +61,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, @@ -94,11 +102,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 @@ -153,6 +168,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 -- =================================================================== @@ -1499,6 +1522,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/contrib/sepgsql/proc.c b/contrib/sepgsql/proc.c index 4899bc2a3e3..20120fd531f 100644 --- a/contrib/sepgsql/proc.c +++ b/contrib/sepgsql/proc.c @@ -161,7 +161,7 @@ sepgsql_proc_drop(Oid functionId) * check db_schema:{remove_name} permission */ object.classId = NamespaceRelationId; - object.objectId = get_func_namespace(functionId); + object.objectId = get_func_namespace(functionId, true); object.objectSubId = 0; audit_name = getObjectIdentity(&object, false); diff --git a/contrib/unaccent/unaccent.c b/contrib/unaccent/unaccent.c index 0217696aac1..e87a588a3ae 100644 --- a/contrib/unaccent/unaccent.c +++ b/contrib/unaccent/unaccent.c @@ -447,7 +447,7 @@ unaccent_dict(PG_FUNCTION_ARGS) * Use the "unaccent" dictionary that is in the same schema that this * function is in. */ - Oid procnspid = get_func_namespace(fcinfo->flinfo->fn_oid); + Oid procnspid = get_func_namespace(fcinfo->flinfo->fn_oid, true); const char *dictname = "unaccent"; dictOid = GetSysCacheOid2(TSDICTNAMENSP, Anum_pg_ts_dict_oid, diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index d2cac06fd76..1818f36b6d1 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -323,6 +323,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 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 7717855bc9e..6330fdff4c0 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -22,7 +22,9 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] +DELETE FROM [ ONLY ] table_name [ * ] + [ FOR PORTION OF range_or_period_name FROM start_time TO end_time ] + [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ] @@ -54,6 +56,39 @@ DELETE FROM [ ONLY ] table_name [ * circumstances. + + If the table has a range or multirange column, + you may supply a FOR PORTION OF clause, and your delete will + only affect rows that overlap the given interval. Furthermore, if a row's span + extends outside the FOR PORTION OF bounds, then your delete + will only change the span within those bounds. In effect you are deleting any + moment targeted by FOR PORTION OF and no moments outside. + + + + Specifically, after PostgreSQL deletes the existing row, + it will INSERT + new rows whose range or start/end column(s) receive the remaining span outside + the targeted bounds, containing the original values in other columns. + There will be zero to two inserted records, + depending on whether the original span extended before the targeted + FROM, after the targeted TO, both, or neither. + + + + These secondary inserts fire INSERT triggers. First + BEFORE DELETE triggers first, then + BEFORE INSERT, then AFTER INSERT, + then AFTER DELETE. + + + + These secondary inserts do not require INSERT privilege on the table. + This is because conceptually no new information has been added. The inserted rows only preserve + existing data about the untargeted time period. Note this may result in users firing INSERT + triggers who don't have insert privileges, so be careful about SECURITY DEFINER trigger functions! + + The optional RETURNING clause causes DELETE to compute and return value(s) based on each row actually deleted. @@ -116,6 +151,41 @@ DELETE FROM [ ONLY ] table_name [ * + + range_or_period_name + + + The range column or period to use when performing a temporal delete. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal delete. + This must be a value matching the base type of the range or period from + range_or_period_name. A + NULL here indicates a delete whose beginning is + unbounded (as with range types). + + + + + + end_time + + + The latest time (exclusive) to change in a temporal delete. + This must be a value matching the base type of the range or period from + range_or_period_name. A + NULL here indicates a delete whose end is unbounded + (as with range types). + + + + from_item diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 1c433bec2bb..c893cc795f2 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -22,7 +22,9 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] +UPDATE [ ONLY ] table_name [ * ] + [ FOR PORTION OF range_or_period_name for_portion_of_target ] + [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -51,6 +53,41 @@ UPDATE [ ONLY ] table_name [ * ] [ circumstances. + + If the table has a range or multirange column, + you may supply a FOR PORTION OF clause, and your update will + only affect rows that overlap the given interval. Furthermore, if a row's span + extends outside the FOR PORTION OF bounds, then your update + will only change the span within those bounds. In effect you are updating any + moment targeted by FOR PORTION OF and no moments outside. + + + + Specifically, when PostgreSQL updates the existing row, + it will also change the range or start/end column(s) so that their interval + no longer extends beyond the targeted FOR PORTION OF bounds. + Then PostgreSQL will INSERT + new rows whose range or start/end column(s) receive the remaining span outside + the targeted bounds, containing the un-updated values in other columns. + There will be zero to two inserted records, + depending on whether the original span extended before the targeted + FROM, after the targeted TO, both, or neither. + + + + These secondary inserts fire INSERT triggers. First + BEFORE UPDATE triggers first, then + BEFORE INSERT, then AFTER INSERT, + then AFTER UPDATE. + + + + These secondary inserts do not require INSERT privilege on the table. + This is because conceptually no new information has been added. The inserted rows only preserve + existing data about the untargeted time period. Note this may result in users firing INSERT + triggers who don't have insert privileges, so be careful about SECURITY DEFINER trigger functions! + + The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. @@ -114,6 +151,57 @@ UPDATE [ ONLY ] table_name [ * ] [ + + range_or_period_name + + + The range column or period to use when performing a temporal update. + + + + + + for_portion_of_target + + + The interval to update. If you are targeting a range column or PERIOD, + you may give this in the form FROM + start_time TO + end_time. + Otherwise you must use + (expression) + where the expression yields a value for the same type as + range_or_period_name. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal update. + This must be a value matching the base type of the range or period from + range_or_period_name. A + NULL here indicates an update whose beginning is + unbounded (as with range types). + + + + + + end_time + + + The latest time (exclusive) to change in a temporal update. + This must be a value matching the base type of the range or period from + range_or_period_name. A + NULL here indicates an update whose end is unbounded + (as with range types). + + + + column_name diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index a9abaab9056..624514ffbfd 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -366,6 +366,15 @@ responsibility to avoid that. + + If an UPDATE or DELETE uses + FOR PORTION OF, causing new rows to be inserted + to preserve the leftover untargeted part of modified records, then + INSERT triggers are fired for each inserted + row. Each row is inserted separately, so they fire their own + statement triggers, and they have their own transition tables. + + trigger @@ -547,17 +556,18 @@ CALLED_AS_TRIGGER(fcinfo) typedef struct TriggerData { - NodeTag type; - TriggerEvent tg_event; - Relation tg_relation; - HeapTuple tg_trigtuple; - HeapTuple tg_newtuple; - Trigger *tg_trigger; - TupleTableSlot *tg_trigslot; - TupleTableSlot *tg_newslot; - Tuplestorestate *tg_oldtable; - Tuplestorestate *tg_newtable; - const Bitmapset *tg_updatedcols; + NodeTag type; + TriggerEvent tg_event; + Relation tg_relation; + HeapTuple tg_trigtuple; + HeapTuple tg_newtuple; + Trigger *tg_trigger; + TupleTableSlot *tg_trigslot; + TupleTableSlot *tg_newslot; + Tuplestorestate *tg_oldtable; + Tuplestorestate *tg_newtable; + const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; @@ -825,6 +835,38 @@ typedef struct Trigger + + + tg_temporal + + + Set for UPDATE and DELETE queries + that use FOR PORTION OF, otherwise NULL. + Contains a pointer to a structure of type + ForPortionOfState, defined in + nodes/execnodes.h: + + +typedef struct ForPortionOfState +{ + NodeTag type; + + char *fp_rangeName; /* the column named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range column */ + Datum fp_targetRange; /* the range/multirange/etc from FOR PORTION OF */ +} ForPortionOfState; + + + where fp_rangeName is the range + column named in the FOR PORTION OF clause, + fp_rangeType is its range type, + fp_rangeAttno is its attribute number, + and fp_targetRange is a rangetype value created + by evaluating the FOR PORTION OF bounds. + + + diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 7c0fd63b2f0..c4f057982ee 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -4407,7 +4407,7 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es) objectname = get_func_name(funcid); if (es->verbose) - namespace = get_namespace_name_or_temp(get_func_namespace(funcid)); + namespace = get_namespace_name_or_temp(get_func_namespace(funcid, true)); } } objecttag = "Function Name"; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 51e280dda58..cf894b2298e 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12423,6 +12423,7 @@ validateForeignKeyConstraint(char *conname, trigdata.tg_trigtuple = ExecFetchSlotHeapTuple(slot, false, NULL); trigdata.tg_trigslot = slot; trigdata.tg_trigger = &trig; + trigdata.tg_temporal = NULL; fcinfo->context = (Node *) &trigdata; diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 09356e46d16..55a25a36c8b 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -47,6 +47,7 @@ #include "storage/lmgr.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/datum.h" #include "utils/fmgroids.h" #include "utils/guc_hooks.h" #include "utils/inval.h" @@ -2632,6 +2633,7 @@ ExecBSDeleteTriggers(EState *estate, ResultRelInfo *relinfo) LocTriggerData.tg_event = TRIGGER_EVENT_DELETE | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -2731,6 +2733,7 @@ ExecBRDeleteTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { HeapTuple newtuple; @@ -2822,6 +2825,7 @@ ExecIRDeleteTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, slot, false); @@ -2885,6 +2889,7 @@ ExecBSUpdateTriggers(EState *estate, ResultRelInfo *relinfo) TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; LocTriggerData.tg_updatedcols = updatedCols; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; for (i = 0; i < trigdesc->numtriggers; i++) { Trigger *trigger = &trigdesc->triggers[i]; @@ -3020,6 +3025,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate, TRIGGER_EVENT_ROW | TRIGGER_EVENT_BEFORE; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; updatedCols = ExecGetAllUpdatedCols(relinfo, estate); LocTriggerData.tg_updatedcols = updatedCols; for (i = 0; i < trigdesc->numtriggers; i++) @@ -3169,6 +3175,7 @@ ExecIRUpdateTriggers(EState *estate, ResultRelInfo *relinfo, TRIGGER_EVENT_ROW | TRIGGER_EVENT_INSTEAD; LocTriggerData.tg_relation = relinfo->ri_RelationDesc; + LocTriggerData.tg_temporal = relinfo->ri_forPortionOf; ExecForceStoreHeapTuple(trigtuple, oldslot, false); @@ -3635,6 +3642,7 @@ typedef struct AfterTriggerSharedData Oid ats_tgoid; /* the trigger's ID */ Oid ats_relid; /* the relation it's on */ CommandId ats_firing_id; /* ID for firing cycle */ + ForPortionOfState *for_portion_of; /* the FOR PORTION OF clause */ struct AfterTriggersTableData *ats_table; /* transition table access */ Bitmapset *ats_modifiedcols; /* modified columns */ } AfterTriggerSharedData; @@ -3908,6 +3916,7 @@ static SetConstraintState SetConstraintStateCreate(int numalloc); static SetConstraintState SetConstraintStateCopy(SetConstraintState origstate); static SetConstraintState SetConstraintStateAddItem(SetConstraintState state, Oid tgoid, bool tgisdeferred); +static ForPortionOfState *CopyForPortionOfState(ForPortionOfState *src); static void cancel_prior_stmt_triggers(Oid relid, CmdType cmdType, int tgevent); @@ -4120,6 +4129,7 @@ afterTriggerAddEvent(AfterTriggerEventList *events, newshared->ats_relid == evtshared->ats_relid && newshared->ats_event == evtshared->ats_event && newshared->ats_table == evtshared->ats_table && + newshared->for_portion_of == evtshared->for_portion_of && newshared->ats_firing_id == 0) break; } @@ -4488,6 +4498,7 @@ AfterTriggerExecute(EState *estate, LocTriggerData.tg_relation = rel; if (TRIGGER_FOR_UPDATE(LocTriggerData.tg_trigger->tgtype)) LocTriggerData.tg_updatedcols = evtshared->ats_modifiedcols; + LocTriggerData.tg_temporal = evtshared->for_portion_of; MemoryContextReset(per_tuple_context); @@ -6022,6 +6033,40 @@ AfterTriggerPendingOnRel(Oid relid) return false; } +/* ---------- + * ForPortionOfState() + * + * Copys a ForPortionOfState into the current memory context. + */ +static ForPortionOfState * +CopyForPortionOfState(ForPortionOfState *src) +{ + ForPortionOfState *dst = NULL; + if (src) { + MemoryContext oldctx; + RangeType *r; + TypeCacheEntry *typcache; + + /* + * Need to lift the FOR PORTION OF details into a higher memory context + * because cascading foreign key update/deletes can cause triggers to fire + * triggers, and the AfterTriggerEvents will outlive the FPO + * details of the original query. + */ + oldctx = MemoryContextSwitchTo(TopTransactionContext); + dst = makeNode(ForPortionOfState); + dst->fp_rangeName = pstrdup(src->fp_rangeName); + dst->fp_rangeType = src->fp_rangeType; + dst->fp_rangeAttno = src->fp_rangeAttno; + + r = DatumGetRangeTypeP(src->fp_targetRange); + typcache = lookup_type_cache(RangeTypeGetOid(r), TYPECACHE_RANGE_INFO); + dst->fp_targetRange = datumCopy(src->fp_targetRange, typcache->typbyval, typcache->typlen); + MemoryContextSwitchTo(oldctx); + } + return dst; +} + /* ---------- * AfterTriggerSaveEvent() * @@ -6437,6 +6482,7 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo, else new_shared.ats_table = NULL; new_shared.ats_modifiedcols = afterTriggerCopyBitmap(modifiedCols); + new_shared.for_portion_of = CopyForPortionOfState(relinfo->ri_forPortionOf); afterTriggerAddEvent(&afterTriggers.query_stack[afterTriggers.query_depth].events, &new_event, &new_shared); diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index cc9a594cba5..561ac69fc74 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1252,6 +1252,7 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo, resultRelInfo->ri_projectReturning = NULL; resultRelInfo->ri_onConflictArbiterIndexes = NIL; resultRelInfo->ri_onConflict = NULL; + resultRelInfo->ri_forPortionOf = NULL; resultRelInfo->ri_ReturningSlot = NULL; resultRelInfo->ri_TrigOldSlot = NULL; resultRelInfo->ri_TrigNewSlot = NULL; diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 1161520f76b..9f2234320f6 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -52,6 +52,7 @@ #include "postgres.h" +#include "access/heapam.h" #include "access/htup_details.h" #include "access/tableam.h" #include "access/xact.h" @@ -59,14 +60,18 @@ #include "executor/execPartition.h" #include "executor/executor.h" #include "executor/nodeModifyTable.h" +#include "executor/spi.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" #include "optimizer/optimizer.h" +#include "parser/parse_relation.h" #include "rewrite/rewriteHandler.h" #include "storage/lmgr.h" +#include "utils/array.h" #include "utils/builtins.h" #include "utils/datum.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -123,6 +128,19 @@ typedef struct UpdateContext LockTupleMode lockmode; } UpdateContext; +/* + * FPO_QueryHashEntry + */ +typedef struct FPO_QueryHashEntry { + Oid key; + SPIPlanPtr plan; +} FPO_QueryHashEntry; + +/* + * Plan cache for FOR PORTION OF inserts + */ +#define FPO_INIT_QUERYHASHSIZE 32 +static HTAB *fpo_query_cache = NULL; static void ExecBatchInsert(ModifyTableState *mtstate, ResultRelInfo *resultRelInfo, @@ -144,6 +162,13 @@ static bool ExecOnConflictUpdate(ModifyTableContext *context, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static void fpo_InitHashTable(void); +static SPIPlanPtr fpo_FetchPreparedPlan(Oid relid); +static void fpo_HashPreparedPlan(Oid relid, SPIPlanPtr plan); +static void ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -166,6 +191,7 @@ static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag); +static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate); /* @@ -1214,6 +1240,367 @@ ExecInsert(ModifyTableContext *context, return result; } +/* ---------------------------------------------------------------- + * fpo_InitHashTable + * + * Creates a hash table to hold SPI plans to insert leftovers + * from a PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +fpo_InitHashTable(void) +{ + HASHCTL ctl; + + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(FPO_QueryHashEntry); + fpo_query_cache = hash_create("FPO_query_cache", + FPO_INIT_QUERYHASHSIZE, + &ctl, HASH_ELEM | HASH_BLOBS); +} + +/* ---------------------------------------------------------------- + * fpo_FetchPreparedPlan + * + * Lookup for a query key in our private hash table of + * prepared and saved SPI execution plans. Returns the plan + * if found or NULL. + * ---------------------------------------------------------------- + */ +static SPIPlanPtr +fpo_FetchPreparedPlan(Oid relid) +{ + FPO_QueryHashEntry *entry; + SPIPlanPtr plan; + + if (!fpo_query_cache) + fpo_InitHashTable(); + + /* + * Lookup for the key + */ + entry = (FPO_QueryHashEntry *) hash_search(fpo_query_cache, + &relid, + HASH_FIND, NULL); + + if (!entry) + return NULL; + + /* + * Check whether the plan is still valid. If it isn't, we don't want to + * simply rely on plancache.c to regenerate it; rather we should start + * from scratch and rebuild the query text too. This is to cover cases + * such as table/column renames. We depend on the plancache machinery to + * detect possible invalidations, though. + * + * CAUTION: this check is only trustworthy if the caller has already + * locked both FK and PK rels. + */ + plan = entry->plan; + if (plan && SPI_plan_is_valid(plan)) + return plan; + + /* + * Otherwise we might as well flush the cached plan now, to free a little + * memory space before we make a new one. + */ + entry->plan = NULL; + if (plan) + SPI_freeplan(plan); + + return NULL; +} + +/* ---------------------------------------------------------------- + * fpo_HashPreparedPlan + * + * Add another plan to our private SPI query plan hashtable. + * ---------------------------------------------------------------- + */ +static void +fpo_HashPreparedPlan(Oid relid, SPIPlanPtr plan) +{ + FPO_QueryHashEntry *entry; + bool found; + + /* + * On the first call initialize the hashtable + */ + if (!fpo_query_cache) + fpo_InitHashTable(); + + /* + * Add the new plan. We might be overwriting an entry previously found + * invalid by fpo_FetchPreparedPlan. + */ + entry = (FPO_QueryHashEntry *) hash_search(fpo_query_cache, + &relid, + HASH_ENTER, &found); + Assert(!found || entry->plan == NULL); + entry->plan = plan; +} + +/* ---------------------------------------------------------------- + * ExecForPortionOfLeftovers + * + * Insert tuples for the untouched portion of a row in a FOR + * PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid) +{ + ModifyTableState *mtstate = context->mtstate; + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + AttrNumber rangeAttno; + Oid relid; + TupleDesc tupdesc; + int natts; + Datum oldRange; + TypeCacheEntry *typcache; + ForPortionOfState *fpoState = resultRelInfo->ri_forPortionOf; + TupleTableSlot *oldtupleSlot = fpoState->fp_Existing; + TupleTableSlot *leftoverSlot; + TupleConversionMap *map = NULL; + HeapTuple oldtuple = NULL; + FmgrInfo flinfo; + ReturnSetInfo rsi; + Relation rel; + bool didInit = false; + bool shouldFree = false; + LOCAL_FCINFO(fcinfo, 2); + + /* + * Get the range of the old pre-UPDATE/DELETE tuple, + * so we can intersect it with the FOR PORTION OF target + * and see if there are any "leftovers" to insert. + * + * We have already locked the tuple in ExecUpdate/ExecDelete + * and it has passed EvalPlanQual. + * Make sure we're looking at the most recent version. + * Otherwise concurrent updates of the same tuple in READ COMMITTED + * could insert conflicting "leftovers". + */ + if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot)) + elog(ERROR, "failed to fetch tuple for FOR PORTION OF"); + + /* + * Get the old range of the record being updated/deleted. + * Must read with the attno of the leaf partition. + */ + + rangeAttno = forPortionOf->rangeVar->varattno; + if (resultRelInfo->ri_RootResultRelInfo) + map = ExecGetChildToRootMap(resultRelInfo); + if (map != NULL) + rangeAttno = map->attrMap->attnums[rangeAttno - 1]; + slot_getallattrs(oldtupleSlot); + + if (oldtupleSlot->tts_isnull[rangeAttno - 1]) + elog(ERROR, "found a NULL range in a temporal table"); + oldRange = oldtupleSlot->tts_values[rangeAttno - 1]; + + /* + * Get the range's type cache entry. This is worth caching for the whole + * UPDATE/DELETE as range functions do. + */ + + typcache = fpoState->fp_leftoverstypcache; + if (typcache == NULL) + { + typcache = lookup_type_cache(forPortionOf->rangeType, 0); + fpoState->fp_leftoverstypcache = typcache; + } + + /* + * Get the ranges to the left/right of the targeted range. + * We call a SETOF support function and insert as many leftovers + * as it gives us. Although rangetypes have 0/1/2 leftovers, + * multiranges have 0/1, and other types may have more. + */ + + fmgr_info(forPortionOf->withoutPortionProc, &flinfo); + rsi.type = T_ReturnSetInfo; + rsi.econtext = mtstate->ps.ps_ExprContext; + rsi.expectedDesc = NULL; + rsi.allowedModes = (int) (SFRM_ValuePerCall); + rsi.returnMode = SFRM_ValuePerCall; + rsi.setResult = NULL; + rsi.setDesc = NULL; + + InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi); + fcinfo->args[0].value = oldRange; + fcinfo->args[0].isnull = false; + fcinfo->args[1].value = fpoState->fp_targetRange; + fcinfo->args[1].isnull = false; + + /* + * If there are partitions, we must insert into the root table, + * so we get tuple routing. We already set up leftoverSlot + * with the root tuple descriptor. + */ + if (resultRelInfo->ri_RootResultRelInfo) + resultRelInfo = resultRelInfo->ri_RootResultRelInfo; + + rel = resultRelInfo->ri_RelationDesc; + relid = RelationGetRelid(rel); + + /* Insert a leftover for each value returned by the without_portion helper function */ + while (true) + { + Datum leftover = FunctionCallInvoke(fcinfo); + SPIPlanPtr qplan = NULL; + int spi_result; + + /* Are we done? */ + if (rsi.isDone == ExprEndResult) + break; + + if (fcinfo->isnull) + elog(ERROR, "Got a null from without_portion function"); + + if (!didInit) + { + /* + * Convert oldtuple to the base table's format if necessary. + * We need to insert leftovers through the root partition + * so they get routed correctly. + */ + if (map != NULL) + { + leftoverSlot = execute_attr_map_slot(map->attrMap, + oldtupleSlot, + fpoState->fp_Leftover); + } + else + leftoverSlot = oldtupleSlot; + + tupdesc = leftoverSlot->tts_tupleDescriptor; + natts = tupdesc->natts; + + /* + * If targeting a leaf partition, + * it may not have fp_values/fp_nulls yet. + */ + if (!fpoState->fp_values) + { + fpoState->fp_values = palloc(natts * sizeof(Datum)); + fpoState->fp_nulls = palloc(natts * sizeof(char)); + } + + /* + * Copy (potentially mapped) oldtuple values into SPI input arrays. + * We'll overwrite the range/start/end attributes below. + */ + memcpy(fpoState->fp_values, leftoverSlot->tts_values, natts * sizeof(Datum)); + + SPI_connect(); + + didInit = true; + } + + /* + * Build an SPI plan if we don't have one yet. + * We always insert into the root partition, + * so that we get tuple routing. + * Therefore the plan is the same no matter which leaf + * we are updating/deleting. + */ + if (!qplan && !(qplan = fpo_FetchPreparedPlan(relid))) + { + Oid *types = palloc0(natts * sizeof(Oid)); + int i; + bool started = false; + StringInfoData querybuf; + StringInfoData parambuf; + const char *tablename; + const char *schemaname; + const char *colname; + + initStringInfo(&querybuf); + initStringInfo(¶mbuf); + + schemaname = get_namespace_name(RelationGetNamespace(rel)); + tablename = RelationGetRelationName(rel); + appendStringInfo(&querybuf, "INSERT INTO %s (", + quote_qualified_identifier(schemaname, tablename)); + + for (i = 0; i < natts; i++) { + /* Don't try to insert into dropped or generated columns */ + if (tupdesc->attrs[i].attisdropped || tupdesc->attrs[i].attgenerated) + continue; + + types[i] = tupdesc->attrs[i].atttypid; + + colname = quote_identifier(NameStr(*attnumAttName(rel, i + 1))); + if (started) + { + appendStringInfo(&querybuf, ", %s", colname); + appendStringInfo(¶mbuf, ", $%d", i + 1); + } + else + { + appendStringInfo(&querybuf, "%s", colname); + appendStringInfo(¶mbuf, "$%d", i + 1); + } + started = true; + } + appendStringInfo(&querybuf, ") VALUES (%s)", parambuf.data); + + qplan = SPI_prepare(querybuf.data, natts, types); + if (!qplan) + elog(ERROR, "SPI_prepare returned %s for %s", + SPI_result_code_string(SPI_result), querybuf.data); + + SPI_keepplan(qplan); + fpo_HashPreparedPlan(relid, qplan); + } + + /* + * Set up the SPI params. + * Copy most attributes' old values, + * but for the range/start/end use the leftover. + */ + + /* Convert bool null array to SPI char array */ + for (int i = 0; i < natts; i++) + { + /* + * Don't try to insert into dropped or generated columns. + * Tell SPI these params are null just to be safe. + */ + if (tupdesc->attrs[i].attisdropped || tupdesc->attrs[i].attgenerated) + fpoState->fp_nulls[i] = 'n'; + else + fpoState->fp_nulls[i] = leftoverSlot->tts_isnull[i] ? 'n' : ' '; + } + + fpoState->fp_nulls[forPortionOf->rangeVar->varattno - 1] = ' '; + fpoState->fp_values[forPortionOf->rangeVar->varattno - 1] = leftover; + + spi_result = SPI_execute_snapshot(qplan, + fpoState->fp_values, + fpoState->fp_nulls, + GetLatestSnapshot(), + InvalidSnapshot, false, true, 0); + if (spi_result != SPI_OK_INSERT) + elog(ERROR, "SPI_execute_snapshot returned %s", SPI_result_code_string(spi_result)); + } + + if (didInit) + { + if (SPI_finish() != SPI_OK_FINISH) + elog(ERROR, "SPI_finish failed"); + + if (shouldFree) + heap_freetuple(oldtuple); + } +} + /* ---------------------------------------------------------------- * ExecBatchInsert * @@ -1366,7 +1753,8 @@ ExecDeleteAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * * Closing steps of tuple deletion; this invokes AFTER FOR EACH ROW triggers, * including the UPDATE triggers if the deletion is being done as part of a - * cross-partition tuple move. + * cross-partition tuple move. It also inserts leftovers from a FOR PORTION OF + * delete. */ static void ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -1399,6 +1787,10 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ar_delete_trig_tcs = NULL; } + /* Compute leftovers in FOR PORTION OF */ + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid); + /* AFTER ROW DELETE Triggers */ ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple, ar_delete_trig_tcs, changingPart); @@ -1779,7 +2171,11 @@ ExecCrossPartitionUpdate(ModifyTableContext *context, if (resultRelInfo == mtstate->rootResultRelInfo) ExecPartitionCheckEmitError(resultRelInfo, slot, estate); - /* Initialize tuple routing info if not already done. */ + /* + * Initialize tuple routing info if not already done. + * Note whatever we do here must be done in ExecInitModifyTable + * for FOR PORTION OF as well. + */ if (mtstate->mt_partition_tuple_routing == NULL) { Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; @@ -2145,6 +2541,10 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, NULL, NIL, (updateCxt->updateIndexes == TU_Summarizing)); + /* Compute leftovers in FOR PORTION OF */ + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid); + /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, NULL, NULL, @@ -4640,6 +5040,99 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } } + /* + * If needed, initialize the target range for FOR PORTION OF. + */ + if (node->forPortionOf) + { + ResultRelInfo *rootResultRelInfo; + TupleDesc tupDesc; + ForPortionOfExpr *forPortionOf; + Datum targetRange; + bool isNull; + ExprContext *econtext; + ExprState *exprState; + ForPortionOfState *fpoState; + + rootResultRelInfo = mtstate->resultRelInfo; + if (rootResultRelInfo->ri_RootResultRelInfo) + rootResultRelInfo = rootResultRelInfo->ri_RootResultRelInfo; + + tupDesc = rootResultRelInfo->ri_RelationDesc->rd_att; + forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + + /* Eval the FOR PORTION OF target */ + if (mtstate->ps.ps_ExprContext == NULL) + ExecAssignExprContext(estate, &mtstate->ps); + econtext = mtstate->ps.ps_ExprContext; + + exprState = ExecPrepareExpr((Expr *) forPortionOf->targetRange, estate); + targetRange = ExecEvalExpr(exprState, econtext, &isNull); + if (isNull) + elog(ERROR, "Got a NULL FOR PORTION OF target range"); + + /* Create state for FOR PORTION OF operation */ + + fpoState = makeNode(ForPortionOfState); + fpoState->fp_rangeName = forPortionOf->range_name; + fpoState->fp_rangeType = forPortionOf->rangeType; + fpoState->fp_rangeAttno = forPortionOf->rangeVar->varattno; + fpoState->fp_targetRange = targetRange; + + /* Initialize slot for the existing tuple */ + + fpoState->fp_Existing = + table_slot_create(rootResultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + /* Create the tuple slot for INSERTing the leftovers */ + + fpoState->fp_Leftover = + ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual); + + /* Allocate our SPI param arrays here so we can reuse them */ + fpoState->fp_values = palloc(tupDesc->natts * sizeof(Datum)); + fpoState->fp_nulls = palloc(tupDesc->natts * sizeof(char)); + + /* + * We must attach the ForPortionOfState to all result rels, + * in case of a cross-partition update or triggers firing + * on partitions. + * XXX: Can we defer this to only the leafs we touch? + */ + for (i = 0; i < nrels; i++) + { + ForPortionOfState *leafState; + resultRelInfo = &mtstate->resultRelInfo[i]; + + leafState = makeNode(ForPortionOfState); + leafState->fp_rangeName = fpoState->fp_rangeName; + leafState->fp_rangeType = fpoState->fp_rangeType; + leafState->fp_rangeAttno = fpoState->fp_rangeAttno; + leafState->fp_targetRange = fpoState->fp_targetRange; + leafState->fp_Leftover = fpoState->fp_Leftover; + /* Each partition needs a slot matching its tuple descriptor */ + leafState->fp_Existing = + table_slot_create(resultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + /* + * Leafs need them own SPI input arrays + * since they might have extra attributes, + * but we'll allocate those as needed. + */ + leafState->fp_values = NULL; + leafState->fp_nulls = NULL; + + resultRelInfo->ri_forPortionOf = leafState; + } + + /* Make sure the root relation has the FOR PORTION OF clause too. */ + if (node->rootRelation > 0) + mtstate->rootResultRelInfo->ri_forPortionOf = fpoState; + + /* Don't free the ExprContext here because the result must last for the whole query */ + } + /* * If we have any secondary relations in an UPDATE or DELETE, they need to * be treated like non-locked relations in SELECT FOR UPDATE, i.e., the diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 2fb2e73604e..b07d88ce2fe 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -764,7 +764,7 @@ SPI_execute_plan_with_paramlist(SPIPlanPtr plan, ParamListInfo params, * end of the command. * * This is currently not documented in spi.sgml because it is only intended - * for use by RI triggers. + * for use by RI triggers and FOR PORTION OF. * * Passing snapshot == InvalidSnapshot will select the normal behavior of * fetching a new snapshot for each query. diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index f76072228c9..95011120495 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2561,6 +2561,14 @@ expression_tree_walker_impl(Node *node, return true; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node; + + if (WALK(forPortionOf->targetRange)) + return true; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -2707,6 +2715,8 @@ query_tree_walker_impl(Query *query, return true; if (WALK(query->mergeJoinCondition)) return true; + if (WALK(query->forPortionOf)) + return true; if (WALK(query->returningList)) return true; if (WALK(query->jointree)) @@ -3591,6 +3601,19 @@ expression_tree_mutator_impl(Node *node, return (Node *) newnode; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *fpo = (ForPortionOfExpr *) node; + ForPortionOfExpr *newnode; + + FLATCOPY(newnode, fpo, ForPortionOfExpr); + MUTATE(newnode->rangeVar, fpo->rangeVar, Var *); + MUTATE(newnode->targetRange, fpo->targetRange, Node *); + MUTATE(newnode->rangeTargetList, fpo->rangeTargetList, List *); + + return (Node *) newnode; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -3772,6 +3795,7 @@ query_tree_mutator_impl(Query *query, MUTATE(query->onConflict, query->onConflict, OnConflictExpr *); MUTATE(query->mergeActionList, query->mergeActionList, List *); MUTATE(query->mergeJoinCondition, query->mergeJoinCondition, Node *); + MUTATE(query->forPortionOf, query->forPortionOf, ForPortionOfExpr *); MUTATE(query->returningList, query->returningList, List *); MUTATE(query->jointree, query->jointree, FromExpr *); MUTATE(query->setOperations, query->setOperations, Node *); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index f2ed0d81f61..200d2989f1a 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); @@ -2838,6 +2838,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); @@ -7111,7 +7112,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTable *node = makeNode(ModifyTable); List *fdw_private_list; @@ -7177,6 +7178,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, node->exclRelTlist = onconflict->exclRelTlist; } node->updateColnosLists = updateColnosLists; + node->forPortionOf = (Node *) forPortionOf; node->withCheckOptionLists = withCheckOptionLists; node->returningLists = returningLists; node->rowMarks = rowMarks; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 0f423e96847..eda2b1cf45d 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2048,6 +2048,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction, parse->onConflict, mergeActionLists, mergeJoinConditions, + parse->forPortionOf, assign_special_exec_param(root)); } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index fc97bf6ee26..a4cc6e09620 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3827,7 +3827,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTablePath *pathnode = makeNode(ModifyTablePath); @@ -3894,6 +3894,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->returningLists = returningLists; pathnode->rowMarks = rowMarks; pathnode->onconflict = onconflict; + pathnode->forPortionOf = forPortionOf; pathnode->epqParam = epqParam; pathnode->mergeActionLists = mergeActionLists; pathnode->mergeJoinConditions = mergeJoinConditions; diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 506e0631615..8d6876a41f9 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -24,7 +24,10 @@ #include "postgres.h" +#include "access/gist.h" +#include "access/stratnum.h" #include "access/sysattr.h" +#include "catalog/pg_am.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -47,10 +50,12 @@ #include "parser/parse_relation.h" #include "parser/parse_target.h" #include "parser/parse_type.h" +#include "parser/parser.h" #include "parser/parsetree.h" #include "utils/backend_status.h" #include "utils/builtins.h" #include "utils/guc.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -59,10 +64,16 @@ post_parse_analyze_hook_type post_parse_analyze_hook = NULL; static Query *transformOptionalSelectInto(ParseState *pstate, Node *parseTree); +static Node *addForPortionOfWhereConditions(Query *qry, ForPortionOfClause *forPortionOf, + Node *whereClause); static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt); static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt); static OnConflictExpr *transformOnConflictClause(ParseState *pstate, OnConflictClause *onConflictClause); +static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOfClause, + bool isUpdate); static int count_rowexpr_columns(ParseState *pstate, Node *expr); static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt); static Query *transformValuesClause(ParseState *pstate, SelectStmt *stmt); @@ -567,6 +578,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 @@ -600,6 +625,7 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_DELETE; @@ -638,7 +664,11 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; - qual = transformWhereClause(pstate, stmt->whereClause, + if (stmt->forPortionOf) + qry->forPortionOf = transformForPortionOfClause(pstate, qry->resultRelation, stmt->forPortionOf, false); + + whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause); + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList, @@ -1274,7 +1304,7 @@ transformOnConflictClause(ParseState *pstate, * Now transform the UPDATE subexpressions. */ onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); onConflictWhere = transformWhereClause(pstate, onConflictClause->whereClause, @@ -1304,6 +1334,186 @@ transformOnConflictClause(ParseState *pstate, return result; } +/* + * transformForPortionOfClause + * + * Transforms a ForPortionOfClause in an UPDATE/DELETE statement. + * + * - Look up the range/period requested. + * - Build a compatible range value from the FROM and TO expressions. + * - Build an "overlaps" expression for filtering. + * - For UPDATEs, build an "intersects" expression the rewriter can add + * to the targetList to change the temporal bounds. + */ +static ForPortionOfExpr * +transformForPortionOfClause(ParseState *pstate, + int rtindex, + ForPortionOfClause *forPortionOf, + bool isUpdate) +{ + Relation targetrel = pstate->p_target_relation; + RTEPermissionInfo *target_perminfo = pstate->p_target_nsitem->p_perminfo; + char *range_name = forPortionOf->range_name; + char *range_type_namespace = NULL; + char *range_type_name = NULL; + int range_attno = InvalidAttrNumber; + Form_pg_attribute attr; + Oid opclass; + Oid opfamily; + Oid opcintype; + Oid funcid = InvalidOid; + StrategyNumber strat; + Oid opid; + ForPortionOfExpr *result; + Var *rangeVar; + Node *targetExpr; + + /* We don't support FOR PORTION OF FDW queries. */ + if (targetrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("foreign tables don't support FOR PORTION OF"))); + + result = makeNode(ForPortionOfExpr); + + /* Look up the FOR PORTION OF name requested. */ + range_attno = attnameAttNum(targetrel, range_name, false); + if (range_attno == InvalidAttrNumber) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column or period \"%s\" of relation \"%s\" does not exist", + range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + attr = TupleDescAttr(targetrel->rd_att, range_attno - 1); + + rangeVar = makeVar( + rtindex, + range_attno, + attr->atttypid, + attr->atttypmod, + attr->attcollation, + 0); + rangeVar->location = forPortionOf->location; + result->rangeVar = rangeVar; + result->rangeType = attr->atttypid; + if (!get_typname_and_namespace(attr->atttypid, &range_type_name, &range_type_namespace)) + elog(ERROR, "cache lookup failed for type %u", attr->atttypid); + + + if (forPortionOf->target) + /* + * We were already given an expression for the target, + * so we don't have to build anything. + */ + targetExpr = forPortionOf->target; + else + { + /* Make sure it's a range column */ + if (!type_is_range(attr->atttypid)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" of relation \"%s\" is not a range type", + range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + + /* + * Build a range from the FROM ... TO .... bounds. + * This should give a constant result, so we accept functions like NOW() + * but not column references, subqueries, etc. + * + * It also permits UNBOUNDED in either place. + */ + targetExpr = (Node *) makeFuncCall( + list_make2(makeString(range_type_namespace), makeString(range_type_name)), + list_make2(forPortionOf->target_start, forPortionOf->target_end), + COERCE_EXPLICIT_CALL, + forPortionOf->location); + } + result->targetRange = transformExpr(pstate, targetExpr, EXPR_KIND_UPDATE_PORTION); + + /* + * Build overlapsExpr to use in the whereClause. + * This means we only hit rows matching the FROM & TO bounds. + * We must look up the overlaps operator (usually "&&"). + */ + opclass = GetDefaultOpClass(attr->atttypid, GIST_AM_OID); + strat = RTOverlapStrategyNumber; + GetOperatorFromWellKnownStrategy(opclass, InvalidOid, &opid, &strat); + result->overlapsExpr = (Node *) makeSimpleA_Expr(AEXPR_OP, get_opname(opid), + (Node *) copyObject(rangeVar), targetExpr, + forPortionOf->location); + + /* + * Look up the withoutPortionOper so we can compute the leftovers. + * Leftovers will be old_range @- target_range + * (one per element of the result). + */ + funcid = InvalidOid; + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_WITHOUT_PORTION_PROC); + + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify a without_overlaps support function for type %s", format_type_be(opcintype)), + errhint("Define a without_overlaps support function for operator class \"%d\" for access method \"%s\".", + opclass, "gist")); + + result->withoutPortionProc = funcid; + + if (isUpdate) + { + /* + * Now make sure we update the start/end time of the record. + * For a range col (r) this is `r = r * targetRange`. + */ + Oid funcnamespace; + char *funcname; + char *funcnamespacename; + Expr *rangeTLEExpr; + TargetEntry *tle; + + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_INTERSECT_PROC); + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify an intersect support function for type %s", format_type_be(opcintype)), + errhint("Define an intersect support function for operator class \"%d\" for access method \"%s\".", + opclass, "gist")); + + funcname = get_func_name(funcid); + if (!funcname) + elog(ERROR, "cache lookup failed for function %u", funcid); + funcnamespace = get_func_namespace(funcid, false); + funcnamespacename = get_namespace_name(funcnamespace); + if (!funcnamespacename) + elog(ERROR, "cache lookup failed for namespace %u", funcnamespace); + + rangeTLEExpr = (Expr *) makeFuncCall( + list_make2(makeString(funcnamespacename), makeString(funcname)), + list_make2(copyObject(rangeVar), targetExpr), + COERCE_EXPLICIT_CALL, + forPortionOf->location); + rangeTLEExpr = (Expr *) transformExpr(pstate, (Node *) rangeTLEExpr, EXPR_KIND_UPDATE_PORTION); + + /* Make a TLE to set the range column */ + result->rangeTargetList = NIL; + tle = makeTargetEntry(rangeTLEExpr, range_attno, range_name, false); + result->rangeTargetList = lappend(result->rangeTargetList, tle); + + /* Mark the range column as requiring update permissions */ + target_perminfo->updatedCols = bms_add_member(target_perminfo->updatedCols, + range_attno - FirstLowInvalidHeapAttributeNumber); + } + else + result->rangeTargetList = NIL; + + result->range_name = range_name; + + return result; +} /* * BuildOnConflictExcludedTargetlist @@ -2512,6 +2722,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) { Query *qry = makeNode(Query); ParseNamespaceItem *nsitem; + Node *whereClause; Node *qual; qry->commandType = CMD_UPDATE; @@ -2529,6 +2740,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 */ @@ -2545,7 +2760,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; - qual = transformWhereClause(pstate, stmt->whereClause, + whereClause = addForPortionOfWhereConditions(qry, stmt->forPortionOf, stmt->whereClause); + qual = transformWhereClause(pstate, whereClause, EXPR_KIND_WHERE, "WHERE"); qry->returningList = transformReturningList(pstate, stmt->returningList, @@ -2555,7 +2771,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; @@ -2574,7 +2790,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; @@ -2627,6 +2843,21 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) errhint("SET target columns cannot be qualified with the relation name.") : 0, parser_errposition(pstate, origTarget->location))); + /* + * If this is a FOR PORTION OF update, + * forbid directly setting the range column, + * since that would conflict with the implicit updates. + */ + if (forPortionOf != NULL) + { + if (attrno == forPortionOf->rangeVar->varattno) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("can't directly assign to \"%s\" in a FOR PORTION OF update", + origTarget->name), + parser_errposition(pstate, origTarget->location))); + } + updateTargetListEntry(pstate, tle, origTarget->name, attrno, origTarget->indirection, diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index dd458182f02..7116dacce1d 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -240,6 +240,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; @@ -536,6 +537,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 @@ -745,7 +748,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 @@ -864,12 +867,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 '*' '/' '%' @@ -12305,6 +12311,20 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->stmt_location = @$; $$ = (Node *) n; } + | opt_with_clause DELETE_P FROM relation_expr for_portion_of_clause opt_alias + using_clause where_or_current_clause returning_clause + { + DeleteStmt *n = makeNode(DeleteStmt); + + n->relation = $4; + n->forPortionOf = $5; + n->relation->alias = $6; + n->usingClause = $7; + n->whereClause = $8; + n->returningList = $9; + n->withClause = $1; + $$ = (Node *) n; + } ; using_clause: @@ -12380,6 +12400,25 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->stmt_location = @$; $$ = (Node *) n; } + | opt_with_clause UPDATE relation_expr + for_portion_of_clause opt_alias + SET set_clause_list + from_clause + where_or_current_clause + returning_clause + { + UpdateStmt *n = makeNode(UpdateStmt); + + n->relation = $3; + n->forPortionOf = $4; + n->relation->alias = $5; + n->targetList = $7; + n->fromClause = $8; + n->whereClause = $9; + n->returningList = $10; + n->withClause = $1; + $$ = (Node *) n; + } ; set_clause_list: @@ -13863,6 +13902,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 */ @@ -14696,16 +14773,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; } @@ -17756,6 +17842,7 @@ unreserved_keyword: | PLAN | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -18381,6 +18468,7 @@ bare_label_keyword: | PLAN | PLANS | POLICY + | PORTION | POSITION | PRECEDING | PREPARE diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index efa730c1676..53985f74df5 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -579,6 +579,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + if (isAgg) + err = _("aggregate functions are not allowed in FOR PORTION OF expressions"); + else + err = _("grouping operations are not allowed in FOR PORTION OF expressions"); + + break; /* * There is intentionally no default: case here, so that the @@ -970,6 +977,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + err = _("window functions are not allowed in FOR PORTION OF expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_collate.c b/src/backend/parser/parse_collate.c index 44529bb49e6..14ffb7ba649 100644 --- a/src/backend/parser/parse_collate.c +++ b/src/backend/parser/parse_collate.c @@ -484,6 +484,7 @@ assign_collations_walker(Node *node, assign_collations_context *context) case T_JoinExpr: case T_FromExpr: case T_OnConflictExpr: + case T_ForPortionOfExpr: case T_SortGroupClause: case T_MergeAction: (void) expression_tree_walker(node, diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index c2806297aa4..8ae51017d43 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -584,6 +584,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 @@ -1858,6 +1861,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 @@ -3151,6 +3157,8 @@ ParseExprKindName(ParseExprKind exprKind) return "UPDATE"; case EXPR_KIND_MERGE_WHEN: return "MERGE WHEN"; + case EXPR_KIND_UPDATE_PORTION: + return "FOR PORTION OF"; case EXPR_KIND_GROUP_BY: return "GROUP BY"; case EXPR_KIND_ORDER_BY: diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 9b23344a3b1..99921d713be 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2658,6 +2658,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_UPDATE_PORTION: + err = _("set-returning functions are not allowed in FOR PORTION OF expressions"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_merge.c b/src/backend/parser/parse_merge.c index 87df79027d7..56f676a1f1f 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -385,7 +385,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) pstate->p_is_insert = false; action->targetList = transformUpdateTargetList(pstate, - mergeWhenClause->targetList); + mergeWhenClause->targetList, NULL); } break; case CMD_DELETE: diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 6d59a2bb8dc..e9f64e74bc2 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3684,6 +3684,30 @@ rewriteTargetView(Query *parsetree, Relation view) &parsetree->hasSubLinks); } + if (parsetree->forPortionOf && parsetree->commandType == CMD_UPDATE) + { + /* + * Like the INSERT/UPDATE code above, update the resnos in the + * auxiliary UPDATE targetlist to refer to columns of the base + * relation. + */ + foreach(lc, parsetree->forPortionOf->rangeTargetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + TargetEntry *view_tle; + + if (tle->resjunk) + continue; + + view_tle = get_tle_by_resno(view_targetlist, tle->resno); + if (view_tle != NULL && !view_tle->resjunk && IsA(view_tle->expr, Var)) + tle->resno = ((Var *) view_tle->expr)->varattno; + else + elog(ERROR, "attribute number %d not found in view targetlist", + tle->resno); + } + } + /* * For UPDATE/DELETE/MERGE, pull up any WHERE quals from the view. We * know that any Vars in the quals must reference the one base relation, @@ -4023,6 +4047,22 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length) else if (event == CMD_UPDATE) { Assert(parsetree->override == OVERRIDING_NOT_SET); + /* + * Update FOR PORTION OF column(s) automatically. Don't + * do this until we're done rewriting a view update, so + * that we don't add the same update on the recursion. + */ + if (parsetree->forPortionOf && + rt_entry_relation->rd_rel->relkind != RELKIND_VIEW) + { + ListCell *tl; + foreach(tl, parsetree->forPortionOf->rangeTargetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(tl); + parsetree->targetList = lappend(parsetree->targetList, tle); + } + } + parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c index 6896e1ae638..297fba71bb5 100644 --- a/src/backend/utils/adt/ri_triggers.c +++ b/src/backend/utils/adt/ri_triggers.c @@ -228,6 +228,7 @@ static bool ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int forPortionOfParam, Datum forPortionOf, bool detectNewRows, int expect_OK); static void ri_ExtractValues(Relation rel, TupleTableSlot *slot, const RI_ConstraintInfo *riinfo, bool rel_is_pk, @@ -236,6 +237,11 @@ static void ri_ReportViolation(const RI_ConstraintInfo *riinfo, Relation pk_rel, Relation fk_rel, TupleTableSlot *violatorslot, TupleDesc tupdesc, int queryno, bool partgone) pg_attribute_noreturn(); +static bool fpo_targets_pk_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo); +static Datum restrict_cascading_range(const ForPortionOfState *tg_temporal, + const RI_ConstraintInfo *riinfo, + TupleTableSlot *oldslot); /* @@ -449,6 +455,7 @@ RI_FKey_check(TriggerData *trigdata) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, NULL, newslot, + -1, (Datum) 0, pk_rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE, SPI_OK_SELECT); @@ -613,6 +620,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel, result = ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* treat like update */ SPI_OK_SELECT); @@ -712,6 +720,8 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) TupleTableSlot *oldslot; RI_QueryKey qkey; SPIPlanPtr qplan; + int targetRangeParam = -1; + Datum targetRange = (Datum) 0; riinfo = ri_FetchConstraintInfo(trigdata->tg_trigger, trigdata->tg_relation, true); @@ -801,9 +811,16 @@ ri_restrict(TriggerData *trigdata, bool is_no_action) /* * We have a plan now. Run it to check for existing references. */ + if (trigdata->tg_temporal) + { + targetRangeParam = riinfo->nkeys - 1; + targetRange = restrict_cascading_range(trigdata->tg_temporal, riinfo, oldslot); + } + ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + targetRangeParam, targetRange, true, /* must detect new rows */ SPI_OK_SELECT); @@ -909,6 +926,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_DELETE); @@ -1029,6 +1047,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, newslot, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -1260,6 +1279,7 @@ ri_set(TriggerData *trigdata, bool is_set_null, int tgkind) ri_PerformCheck(riinfo, &qkey, qplan, fk_rel, pk_rel, oldslot, NULL, + -1, (Datum) 0, true, /* must detect new rows */ SPI_OK_UPDATE); @@ -2405,6 +2425,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, RI_QueryKey *qkey, SPIPlanPtr qplan, Relation fk_rel, Relation pk_rel, TupleTableSlot *oldslot, TupleTableSlot *newslot, + int forPortionOfParam, Datum forPortionOf, bool detectNewRows, int expect_OK) { Relation query_rel, @@ -2460,6 +2481,12 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo, ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk, vals, nulls); } + /* Add one more query param if we are using FOR PORTION OF */ + if (forPortionOf) + { + vals[forPortionOfParam] = forPortionOf; + nulls[forPortionOfParam] = ' '; + } /* * In READ COMMITTED mode, we just need to use an up-to-date regular @@ -3136,3 +3163,50 @@ RI_FKey_trigger_type(Oid tgfoid) return RI_TRIGGER_NONE; } + +/* + * fpo_targets_pk_range + * + * Returns true iff the primary key referenced by riinfo includes the range + * column targeted by the FOR PORTION OF clause (according to tg_temporal). + */ +static bool +fpo_targets_pk_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo) +{ + if (tg_temporal == NULL) + return false; + + return riinfo->pk_attnums[riinfo->nkeys - 1] == tg_temporal->fp_rangeAttno; +} + +/* + * restrict_cascading_range - + * + * Returns a Datum of RangeTypeP holding the appropriate timespan + * to target child records when we CASCADE/SET NULL/SET DEFAULT. + * + * In a normal UPDATE/DELETE this should be the parent's own valid time, + * but if there was a FOR PORTION OF clause, then we should use that to + * trim down the parent's span further. + */ +static Datum +restrict_cascading_range(const ForPortionOfState *tg_temporal, const RI_ConstraintInfo *riinfo, TupleTableSlot *oldslot) +{ + Datum pkRecordRange; + bool isnull; + + pkRecordRange = slot_getattr(oldslot, riinfo->pk_attnums[riinfo->nkeys - 1], &isnull); + if (isnull) + elog(ERROR, "application time should not be null"); + + if (fpo_targets_pk_range(tg_temporal, riinfo)) + { + RangeType *r1 = DatumGetRangeTypeP(pkRecordRange); + RangeType *r2 = DatumGetRangeTypeP(tg_temporal->fp_targetRange); + Oid rngtypid = RangeTypeGetOid(r1); + TypeCacheEntry *typcache = lookup_type_cache(rngtypid, TYPECACHE_RANGE_INFO); + return RangeTypePGetDatum(range_intersect_internal(typcache, r1, r2)); + } + else + return pkRecordRange; +} diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index a85dc0d891f..254731830ba 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1629,7 +1629,7 @@ get_func_name(Oid funcid) * Returns the pg_namespace OID associated with a given function. */ Oid -get_func_namespace(Oid funcid) +get_func_namespace(Oid funcid, bool missing_ok) { HeapTuple tp; @@ -1643,8 +1643,10 @@ get_func_namespace(Oid funcid) ReleaseSysCache(tp); return result; } - else - return InvalidOid; + else if (!missing_ok) + elog(ERROR, "cache lookup failed for function %u", funcid); + + return InvalidOid; } /* @@ -2188,6 +2190,59 @@ get_typisdefined(Oid typid) return false; } +/* + * get_typname + * + * Returns the name of a given type + * + * Returns a palloc'd copy of the string, or NULL if no such type. + */ +char * +get_typname(Oid typid) +{ + HeapTuple tp; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + char *result; + + result = pstrdup(NameStr(typtup->typname)); + ReleaseSysCache(tp); + return result; + } + else + return NULL; +} + +/* + * get_typname_and_namespace + * + * Returns the name and namespace of a given type + * + * Returns true if one found, or false if not. + */ +bool +get_typname_and_namespace(Oid typid, char **typname, char **typnamespace) +{ + HeapTuple tp; + + tp = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp); + + *typname = pstrdup(NameStr(typtup->typname)); + *typnamespace = get_namespace_name(typtup->typnamespace); + ReleaseSysCache(tp); + /* *typnamespace is NULL if it wasn't found: */ + return *typnamespace; + } + else + return false; +} + /* * get_typlen * diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index 8a5a9fe6422..3d156483325 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -41,6 +41,7 @@ typedef struct TriggerData Tuplestorestate *tg_oldtable; Tuplestorestate *tg_newtable; const Bitmapset *tg_updatedcols; + ForPortionOfState *tg_temporal; } TriggerData; /* diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 182a6956bb0..41ef0605394 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -42,12 +42,14 @@ #include "storage/condition_variable.h" #include "utils/hsearch.h" #include "utils/queryenvironment.h" +#include "utils/rangetypes.h" #include "utils/reltrigger.h" #include "utils/sharedtuplestore.h" #include "utils/snapshot.h" #include "utils/sortsupport.h" #include "utils/tuplesort.h" #include "utils/tuplestore.h" +#include "utils/typcache.h" struct PlanState; /* forward references in this file */ struct ParallelHashJoinState; @@ -431,6 +433,26 @@ typedef struct MergeActionState ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */ } MergeActionState; +/* + * ForPortionOfState + * + * Executor state of a FOR PORTION OF operation. + */ +typedef struct ForPortionOfState +{ + NodeTag type; + + char *fp_rangeName; /* the column named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range column */ + Datum fp_targetRange; /* the range/multirange/etc from FOR PORTION OF */ + TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */ + TupleTableSlot *fp_Existing; /* slot to store old tuple */ + TupleTableSlot *fp_Leftover; /* slot to store leftover */ + Datum *fp_values; /* SPI input for leftover values */ + char *fp_nulls; /* SPI input for nulls */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -558,6 +580,9 @@ typedef struct ResultRelInfo /* for MERGE, expr state for checking the join condition */ ExprState *ri_MergeJoinCondition; + /* FOR PORTION OF evaluation state */ + ForPortionOfState *ri_forPortionOf; + /* partition check expression state (NULL if not set up yet) */ ExprState *ri_PartitionCheckExpr; diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b40b661ec8a..eabadd47304 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -142,6 +142,9 @@ typedef struct Query */ int resultRelation pg_node_attr(query_jumble_ignore); + /* FOR PORTION OF clause for UPDATE/DELETE */ + ForPortionOfExpr *forPortionOf; + /* has aggregates in tlist or havingQual */ bool hasAggs pg_node_attr(query_jumble_ignore); /* has window functions in tlist */ @@ -1582,6 +1585,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 @@ -2064,6 +2082,7 @@ typedef struct DeleteStmt WithClause *withClause; /* WITH clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } DeleteStmt; /* ---------------------- @@ -2081,6 +2100,7 @@ typedef struct UpdateStmt WithClause *withClause; /* WITH clause */ ParseLoc stmt_location; /* start location, or -1 if unknown */ ParseLoc stmt_len; /* length in bytes; 0 means "rest of string" */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } UpdateStmt; /* ---------------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index add0f9e45fc..24497ca1d8c 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2395,6 +2395,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 52f29bcdb69..73f3171cddb 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -244,6 +244,7 @@ typedef struct ModifyTable List *rowMarks; /* PlanRowMarks (non-locking only) */ int epqParam; /* ID of Param for EvalPlanQual re-eval */ OnConflictAction onConflictAction; /* ON CONFLICT action */ + Node *forPortionOf; /* FOR PORTION OF clause for UPDATE/DELETE */ List *arbiterIndexes; /* List of ON CONFLICT arbiter index OIDs */ List *onConflictSet; /* INSERT ON CONFLICT DO UPDATE targetlist */ List *onConflictCols; /* target column numbers for onConflictSet */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index b0ef1952e8f..8392018a821 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2336,4 +2336,30 @@ typedef struct OnConflictExpr List *exclRelTlist; /* tlist of the EXCLUDED pseudo relation */ } OnConflictExpr; +/*---------- + * ForPortionOfExpr - represents a FOR PORTION OF ... expression + * + * We set up an expression to make a range from the FROM/TO bounds, + * so that we can use range operators with it. + * + * Then we set up an overlaps expression between that and the range column, + * so that we can find the rows we need to update/delete. + * + * In the executor we'll also build an intersect expression between the + * targeted range and the range column, so that we can update the start/end + * bounds of the UPDATE'd record. + *---------- + */ +typedef struct ForPortionOfExpr +{ + NodeTag type; + Var *rangeVar; /* Range column */ + char *range_name; /* Range name */ + Node *targetRange; /* FOR PORTION OF bounds as a range */ + Oid rangeType; /* type of targetRange */ + Node *overlapsExpr; /* range && targetRange */ + List *rangeTargetList; /* List of TargetEntrys to set the time column(s) */ + Oid withoutPortionProc; /* proc for old_range @- target_range */ +} ForPortionOfExpr; + #endif /* PRIMNODES_H */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 1035e6560c1..b594cccab92 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -289,7 +289,7 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam); + ForPortionOfExpr *forPortionOf, int epqParam); extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel, Path *subpath, Node *limitOffset, Node *limitCount, diff --git a/src/include/parser/analyze.h b/src/include/parser/analyze.h index 28b66fccb43..6c70dae1452 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -43,7 +43,8 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist, List *stmtcols, List *icolumns, List *attrnos, bool strip_indirection); extern List *transformUpdateTargetList(ParseState *pstate, - List *origTlist); + List *origTlist, + ForPortionOfExpr *forPortionOf); extern List *transformReturningList(ParseState *pstate, List *returningList, ParseExprKind exprKind); extern Query *transformTopLevelStmt(ParseState *pstate, RawStmt *parseTree); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 899d64ad55f..425987afee7 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -343,6 +343,7 @@ PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plan", PLAN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("portion", PORTION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("position", POSITION, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("preceding", PRECEDING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("precision", PRECISION, COL_NAME_KEYWORD, AS_LABEL) diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index 2375e95c107..85a4a909e7f 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -56,6 +56,7 @@ typedef enum ParseExprKind EXPR_KIND_UPDATE_SOURCE, /* UPDATE assignment source item */ EXPR_KIND_UPDATE_TARGET, /* UPDATE assignment target item */ EXPR_KIND_MERGE_WHEN, /* MERGE WHEN [NOT] MATCHED condition */ + EXPR_KIND_UPDATE_PORTION, /* UPDATE FOR PORTION OF item */ EXPR_KIND_GROUP_BY, /* GROUP BY */ EXPR_KIND_ORDER_BY, /* ORDER BY */ EXPR_KIND_DISTINCT_ON, /* DISTINCT ON */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 20446f6f836..e7ad513146a 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -121,7 +121,7 @@ extern Oid get_negator(Oid opno); extern RegProcedure get_oprrest(Oid opno); extern RegProcedure get_oprjoin(Oid opno); extern char *get_func_name(Oid funcid); -extern Oid get_func_namespace(Oid funcid); +extern Oid get_func_namespace(Oid funcid, bool missing_ok); extern Oid get_func_rettype(Oid funcid); extern int get_func_nargs(Oid funcid); extern Oid get_func_signature(Oid funcid, Oid **argtypes, int *nargs); @@ -145,6 +145,8 @@ extern Oid get_rel_relam(Oid relid); extern Oid get_transform_fromsql(Oid typid, Oid langid, List *trftypes); extern Oid get_transform_tosql(Oid typid, Oid langid, List *trftypes); extern bool get_typisdefined(Oid typid); +extern char *get_typname(Oid typid); +extern bool get_typname_and_namespace(Oid typid, char **typname, char **typnamespace); extern int16 get_typlen(Oid typid); extern bool get_typbyval(Oid typid); extern void get_typlenbyval(Oid typid, int16 *typlen, bool *typbyval); diff --git a/src/test/regress/expected/for_portion_of.out b/src/test/regress/expected/for_portion_of.out new file mode 100644 index 00000000000..19a4e9ac523 --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,764 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF +SET datestyle TO ISO, YMD; +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2020-01-01)', 'one'); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' +SET name = 'one^1'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; +-- With a table alias with AS +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t +SET name = 'one^2'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; +-- With a table alias without AS +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t +SET name = 'one^3'; +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; +-- UPDATE with FROM +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' +SET name = 'one^4' +FROM (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; +-- DELETE with USING +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' +USING (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-01-15) | one + [1,2) | [2018-01-15,2019-01-01) | one^1 + [1,2) | [2019-01-01,2019-01-15) | one + [1,2) | [2019-01-20,2019-02-01) | one + [1,2) | [2019-02-01,2019-02-03) | one^2 + [1,2) | [2019-02-04,2019-02-05) | one^3 + [1,2) | [2019-02-06,2019-03-01) | one + [1,2) | [2019-03-01,2019-03-02) | one^4 + [1,2) | [2019-03-03,2020-01-01) | one +(9 rows) + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); +UPDATE for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL +SET name = 'foo'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2025-01-01) | one + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2025-01-01) | foo +(2 rows) + +UPDATE for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL +SET name = 'bar'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-02-03) | [2018-01-15,2025-01-01) | bar +(4 rows) + +DELETE FROM for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2025-01-01) | bar +(4 rows) + +DELETE FROM for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2018-01-20) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2018-01-20) | bar +(4 rows) + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, daterange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------------- + [1,2) | empty | 1 empty + [1,2) | (,) | NULL to NULL + [1,2) | | 1 null + | [2018-01-01,2019-01-01) | NULL to NULL + | | +(5 rows) + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-01-05)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'), +('[4,5)', '(,2018-04-01)', 'four'), +('[5,6)', '(,)', 'five') +; +-- +-- UPDATE tests +-- +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +SET name = 'foo' +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- Setting the range fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO NULL +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; +ERROR: can't directly assign to "valid_at" in a FOR PORTION OF update +LINE 3: SET valid_at = '[1990-01-01,1999-01-01)' + ^ +-- The wrong type fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM 1 TO 4 +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: function pg_catalog.daterange(integer, integer) does not exist +LINE 2: FOR PORTION OF valid_at FROM 1 TO 4 + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use subquery in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-... + ^ +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM lower(valid_at) TO NULL +SET name = 'nope' +WHERE id = '[3,4)'; +ERROR: cannot use column reference in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + ^ +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO NULL +SET name = 'three^1' +WHERE id = '[3,4)'; +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO NULL +SET name = 'four^2' +WHERE id = '[4,5)'; +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'two^2' +WHERE id = '[2,3)'; +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^1' +WHERE id = '[5,6)'; +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) +SET name = 'one^3' +WHERE id = '[1,2)'; +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+---------- + [1,2) | [2018-01-02,2018-02-03) | one^2 + [1,2) | [2018-02-03,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-06-01) | three + [3,4) | [2018-06-01,2030-01-01) | three^1 + [3,4) | [2030-01-01,) | three^1* + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2019-01-01) | five^2 + [5,6) | [2019-01-01,2020-01-01) | five^2 + [5,6) | [2020-01-01,2030-01-01) | five + [5,6) | [2030-01-01,) | five* + [6,7) | [2018-02-15,2018-03-03) | one^2 + [6,7) | [2018-03-03,2018-03-10) | one^2 + [6,7) | [2018-03-10,2018-03-17) | one^3 + [6,7) | [2018-03-17,2018-04-04) | one^2 +(20 rows) + +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) + VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' +SET name = 'one^1' +WHERE id = '[1,2)'; +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' +SET name = 'one^2' +WHERE id = '[1,2)'; +ERROR: syntax error at or near "'2014-01-01'" +LINE 4: TO '2014-01-01' + ^ +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; +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; +-- +-- DELETE tests +-- +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; +ERROR: column or period "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2018-02-08,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-04-01) | three + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2018-06-01) | five^2 + [5,6) | [2020-06-01,2025-01-01) | five +(11 rows) + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + id | valid_at | name +-------+-------------------------+--------- + [3,4) | [2018-02-01,2018-02-15) | three^3 +(1 row) + +-- 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) + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows +CREATE FUNCTION for_portion_of_trigger() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % % % of %', TG_WHEN, TG_OP, TG_LEVEL, NEW.valid_at, OLD.valid_at; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; +CREATE TRIGGER trg_for_portion_of_before + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_stmt + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; +NOTICE: BEFORE UPDATE STATEMENT of +NOTICE: BEFORE UPDATE ROW [2021-01-01,2022-01-01) of [2020-06-01,2025-01-01) +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2020-06-01,2021-01-01) of +NOTICE: AFTER INSERT ROW [2020-06-01,2021-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2022-01-01,2025-01-01) of +NOTICE: AFTER INSERT ROW [2022-01-01,2025-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: AFTER UPDATE ROW [2021-01-01,2022-01-01) of [2020-06-01,2025-01-01) +NOTICE: AFTER UPDATE STATEMENT of +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; +NOTICE: BEFORE DELETE STATEMENT of +NOTICE: BEFORE DELETE ROW of [2022-01-01,2025-01-01) +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2022-01-01,2023-01-01) of +NOTICE: AFTER INSERT ROW [2022-01-01,2023-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: BEFORE INSERT STATEMENT of +NOTICE: BEFORE INSERT ROW [2024-01-01,2025-01-01) of +NOTICE: AFTER INSERT ROW [2024-01-01,2025-01-01) of +NOTICE: AFTER INSERT STATEMENT of +NOTICE: AFTER DELETE ROW of [2022-01-01,2025-01-01) +NOTICE: AFTER DELETE STATEMENT of +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2018-02-08,2018-02-15) | one^2 + [2,3) | [2018-01-01,2018-01-05) | two^2 + [3,4) | [2018-01-01,2018-02-01) | three^2 + [3,4) | [2018-02-01,2018-02-02) | three^3 + [3,4) | [2018-02-03,2018-02-15) | three^3 + [3,4) | [2018-02-15,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-04-01) | three + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^3 + [4,5) | [2018-02-01,2018-04-01) | four^2 + [5,6) | (,2017-01-01) | five + [5,6) | [2017-01-01,2018-01-01) | five^2 + [5,6) | [2018-01-01,2018-06-01) | five^2 + [5,6) | [2020-06-01,2021-01-01) | five + [5,6) | [2021-01-01,2022-01-01) | five^3 + [5,6) | [2022-01-01,2023-01-01) | five + [5,6) | [2024-01-01,2025-01-01) | five +(17 rows) + +DROP FUNCTION for_portion_of_trigger CASCADE; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to trigger trg_for_portion_of_before on table for_portion_of_test +drop cascades to trigger trg_for_portion_of_after on table for_portion_of_test +drop cascades to trigger trg_for_portion_of_before_stmt on table for_portion_of_test +drop cascades to trigger trg_for_portion_of_after_stmt on table for_portion_of_test +-- Triggers with a custom transition table name: +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF TG_OP = 'INSERT' THEN + RAISE NOTICE '%: % %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE '%: % %, OLD table = %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, + (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table), + (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'DELETE' THEN + RAISE NOTICE '%: % %, OLD table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table); + END IF; + RETURN NULL; +END; +$$; +CREATE TRIGGER for_portion_of_test_insert_trig +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_insert_trig_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_update_trig +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_update_trig_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_delete_trig +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); +CREATE TRIGGER for_portion_of_test_delete_trig_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +NOTICE: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +NOTICE: for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +ROLLBACK; +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +NOTICE: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: for_portion_of_test_delete_trig: DELETE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: for_portion_of_test_delete_trig_stmt: DELETE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one) +ROLLBACK; +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +NOTICE: for_portion_of_test_insert_trig: INSERT ROW, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: for_portion_of_test_insert_trig_stmt: INSERT STATEMENT, NEW table = ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: for_portion_of_test_update_trig: UPDATE ROW, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +NOTICE: for_portion_of_test_update_trig_stmt: UPDATE STATEMENT, OLD table = ("[1,2)","[2018-01-01,2020-01-01)",one), NEW table = ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +ROLLBACK; +-- Test with multiranges +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), +('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), +('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), +('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); +; +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01'))) +SET name = 'one^1' +WHERE id = '[1,2)'; +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15'))) +WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+---------------------------------------------------------------------------+------- + [1,2) | {[2018-01-02,2018-01-10),[2018-02-10,2018-03-03)} | one + [1,2) | {[2018-01-10,2018-02-03),[2018-02-04,2018-02-10)} | one^1 + [1,2) | {[2018-03-03,2018-03-05)} | one + [1,2) | {[2018-03-05,2018-04-04)} | one^1 + [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-15,2018-05-01)} | two + [3,4) | {[2018-01-01,)} | three +(6 rows) + +DROP TABLE for_portion_of_test2; +-- Test with a custom range type +CREATE TYPE mydaterange AS range(subtype=date); +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-05-01)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'); +; +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' +SET name = 'one^1' +WHERE id = '[1,2)'; +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' +WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-01-10) | one + [1,2) | [2018-01-10,2018-02-03) | one^1 + [1,2) | [2018-02-03,2018-02-10) | one^1 + [1,2) | [2018-02-10,2018-03-03) | one + [1,2) | [2018-03-03,2018-04-04) | one + [2,3) | [2018-01-01,2018-01-15) | two + [2,3) | [2018-02-15,2018-05-01) | two + [3,4) | [2018-01-01,) | three +(8 rows) + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); +INSERT INTO temporal_partitioned VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); +SELECT * FROM temporal_partitioned; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2010-01-01) | one + [3,4) | [2000-01-01,2010-01-01) | three + [5,6) | [2000-01-01,2010-01-01) | five +(3 rows) + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; +-- Update all partitions at once (each with leftovers) +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 + [3,4) | [2000-01-01,2000-03-01) | three + [3,4) | [2000-03-01,2000-04-01) | three^1 + [3,4) | [2000-04-01,2000-06-01) | three + [3,4) | [2000-06-01,2000-07-01) | five^2 + [3,4) | [2000-07-01,2010-01-01) | three + [4,5) | [2000-06-01,2000-07-01) | one^2 + [5,6) | [2000-01-01,2000-03-01) | five + [5,6) | [2000-03-01,2000-04-01) | five^1 + [5,6) | [2000-04-01,2000-06-01) | five + [5,6) | [2000-07-01,2010-01-01) | five +(15 rows) + +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 +(5 rows) + +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; + name | id | valid_at +---------+-------+------------------------- + three | [3,4) | [2000-01-01,2000-03-01) + three^1 | [3,4) | [2000-03-01,2000-04-01) + three | [3,4) | [2000-04-01,2000-06-01) + five^2 | [3,4) | [2000-06-01,2000-07-01) + three | [3,4) | [2000-07-01,2010-01-01) + one^2 | [4,5) | [2000-06-01,2000-07-01) +(6 rows) + +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + name | valid_at | id +--------+-------------------------+------- + five | [2000-01-01,2000-03-01) | [5,6) + five^1 | [2000-03-01,2000-04-01) | [5,6) + five | [2000-04-01,2000-06-01) | [5,6) + five | [2000-07-01,2010-01-01) | [5,6) +(4 rows) + +DROP TABLE temporal_partitioned; +RESET datestyle; diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 34a32bd11d2..7048ea0a59e 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1948,6 +1948,7 @@ WHERE a1.amopfamily = 0 OR a1.amoplefttype = 0 OR a1.amoprighttype = 0 SELECT a1.amopfamily, a1.amopstrategy FROM pg_amop as a1 WHERE NOT ((a1.amoppurpose = 's' AND a1.amopsortfamily = 0) OR + (a1.amoppurpose = 'p' AND a1.amopsortfamily = 0) OR (a1.amoppurpose = 'o' AND a1.amopsortfamily <> 0)); amopfamily | amopstrategy ------------+-------------- diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 1d903babd33..30d5d30f52f 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1038,6 +1038,24 @@ ERROR: null value in column "b" of relation "errtst_part_2" violates not-null c DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; +-- test column-level privileges on the range used in FOR PORTION OF +SET SESSION AUTHORIZATION regress_priv_user1; +CREATE TABLE t1 ( + c1 int4range, + valid_at tsrange, + CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS) +); +GRANT SELECT ON t1 TO regress_priv_user2; +GRANT SELECT ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +ERROR: permission denied for table t1 +SET SESSION AUTHORIZATION regress_priv_user3; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user1; +DROP TABLE t1; -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 ADD COLUMN three integer; diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out index 8786058ed0c..53903705493 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3666,6 +3666,38 @@ select * from uv_iocu_tab; drop view uv_iocu_view; drop table uv_iocu_tab; +-- Check UPDATE FOR PORTION OF works correctly +create table uv_fpo_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_view as + select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab; +insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1); +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 +(2 rows) + +update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]'; +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0 +(3 rows) + +delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]'; +select * from uv_fpo_view; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Thu Jan 01 00:00:00 2015") | [1,2) | 2.0 + 0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [1,2) | 2.0 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Sun Jan 01 00:00:00 2017") | [1,2) | 2.0 +(3 rows) + -- Test whole-row references to the view create table uv_iocu_tab (a int unique, b text); create view uv_iocu_view as diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out index a05b64e2f78..2f857d4961f 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -889,6 +889,36 @@ INSERT INTO temporal3 (id, valid_at, id2, name) ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01' + SET name = name || '1'; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01' + SET name = name || '2' + WHERE id = '[2,3)'; +SELECT * FROM temporal3 ORDER BY id, valid_at; + id | valid_at | id2 | name +-------+-------------------------+--------+------- + [1,2) | [2000-01-01,2000-05-01) | [7,8) | foo + [1,2) | [2000-05-01,2000-07-01) | [7,8) | foo1 + [1,2) | [2000-07-01,2010-01-01) | [7,8) | foo + [2,3) | [2000-01-01,2000-04-01) | [9,10) | bar + [2,3) | [2000-04-01,2000-05-01) | [9,10) | bar2 + [2,3) | [2000-05-01,2000-06-01) | [9,10) | bar12 + [2,3) | [2000-06-01,2000-07-01) | [9,10) | bar1 + [2,3) | [2000-07-01,2010-01-01) | [9,10) | bar +(8 rows) + +-- conflicting id only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3'); +ERROR: conflicting key value violates exclusion constraint "temporal3_pk" +DETAIL: Key (id, valid_at)=([1,2), [2005-01-01,2006-01-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-07-01,2010-01-01)). +-- conflicting id2 only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3'); +ERROR: conflicting key value violates exclusion constraint "temporal3_uniq" +DETAIL: Key (id2, valid_at)=([9,10), [2005-01-01,2010-01-01)) conflicts with existing key (id2, valid_at)=([9,10), [2000-07-01,2010-01-01)). DROP TABLE temporal3; -- -- test changing the PK's dependencies @@ -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 @@ -1714,6 +1778,18 @@ UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1767,6 +1843,18 @@ UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1803,9 +1891,22 @@ BEGIN; COMMIT; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test FK referenced deletes RESTRICT -- @@ -1834,9 +1935,22 @@ BEGIN; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". ROLLBACK; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test ON UPDATE/DELETE options -- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 81e4222d26a..a4e559fe419 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -48,7 +48,7 @@ test: create_index create_index_spgist create_view index_including index_includi # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse +test: create_aggregate create_function_sql create_cast constraints triggers select inherit typed_table vacuum drop_if_exists updatable_views roleattributes create_am hash_func errors infinite_recurse for_portion_of # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/sql/for_portion_of.sql b/src/test/regress/sql/for_portion_of.sql new file mode 100644 index 00000000000..e66a55fe014 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,597 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF + +SET datestyle TO ISO, YMD; + +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2020-01-01)', 'one'); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' +SET name = 'one^1'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; + +-- With a table alias with AS + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t +SET name = 'one^2'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; + +-- With a table alias without AS + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t +SET name = 'one^3'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; + +-- UPDATE with FROM + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' +SET name = 'one^4' +FROM (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; + +-- DELETE with USING + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' +USING (SELECT '[1,2)'::int4range) AS t2(id) +WHERE for_portion_of_test.id = t2.id; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test VALUES +('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); + +UPDATE for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL +SET name = 'foo'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +UPDATE for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL +SET name = 'bar'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, daterange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-01-05)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'), +('[4,5)', '(,2018-04-01)', 'four'), +('[5,6)', '(,)', 'five') +; + +-- +-- UPDATE tests +-- + +-- Setting with a missing column fails +UPDATE for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +SET name = 'foo' +WHERE id = '[5,6)'; + +-- Setting the range fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO NULL +SET valid_at = '[1990-01-01,1999-01-01)' +WHERE id = '[5,6)'; + +-- The wrong type fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM 1 TO 4 +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with timestamps reversed fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Setting with a subquery fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with a column fails +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM lower(valid_at) TO NULL +SET name = 'nope' +WHERE id = '[3,4)'; + +-- Setting with timestamps equal does nothing +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +SET name = 'three^0' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO NULL +SET name = 'three^1' +WHERE id = '[3,4)'; + +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-03-01' +SET name = 'three^2' +WHERE id = '[3,4)'; + +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-01' +SET name = 'four^1' +WHERE id = '[4,5)'; + +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO NULL +SET name = 'four^2' +WHERE id = '[4,5)'; + +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' +SET name = 'four^3' +WHERE id = '[4,5)'; + +-- Updating an enclosed span +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'two^2' +WHERE id = '[2,3)'; + +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' +SET name = 'five^1' +WHERE id = '[5,6)'; + +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' +SET name = 'five^2' +WHERE id = '[5,6)'; + +-- Updating multiple enclosed spans +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +SET name = 'one^2' +WHERE id = '[1,2)'; + +-- With a direct target +UPDATE for_portion_of_test +FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-17')) +SET name = 'one^3' +WHERE id = '[1,2)'; + +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-15' TO NULL +SET id = '[6,7)' +WHERE id = '[1,2)'; + +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2030-01-01' TO NULL +SET name = name || '*'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) + VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' +SET name = 'one^1' +WHERE id = '[1,2)'; + +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' +SET name = 'one^2' +WHERE id = '[1,2)'; + +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' +SET name = 'one^3' +WHERE id = '[1,2)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; +DROP TABLE for_portion_of_test2; + +-- +-- DELETE tests +-- + +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test +FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL +WHERE id = '[5,6)'; + +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' +WHERE id = '[3,4)'; + +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' +WHERE id = '[3,4)'; + +-- Deleting with a closed/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-06-01' TO '2020-06-01' +WHERE id = '[5,6)'; + +-- Deleting with a closed/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2018-04-01' TO NULL +WHERE id = '[3,4)'; + +-- Deleting with an open/closed target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO '2018-02-08' +WHERE id = '[1,2)'; + +-- Deleting with an open/open target +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM NULL TO NULL +WHERE id = '[6,7)'; + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2025-01-01' TO NULL; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- UPDATE ... RETURNING returns only the updated values (not the inserted side values) +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-02-15' +SET name = 'three^3' +WHERE id = '[3,4)' +RETURNING *; + +-- 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 *; + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows + +CREATE FUNCTION for_portion_of_trigger() +RETURNS trigger +AS +$$ +BEGIN + RAISE NOTICE '% % % % of %', TG_WHEN, TG_OP, TG_LEVEL, NEW.valid_at, OLD.valid_at; + IF TG_OP = 'DELETE' THEN + RETURN OLD; + ELSE + RETURN NEW; + END IF; +END; +$$ +LANGUAGE plpgsql; + +CREATE TRIGGER trg_for_portion_of_before + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); +CREATE TRIGGER trg_for_portion_of_after_stmt + AFTER INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT + EXECUTE FUNCTION for_portion_of_trigger(); + +UPDATE for_portion_of_test +FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' +SET name = 'five^3' +WHERE id = '[5,6)'; + +DELETE FROM for_portion_of_test +FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' +WHERE id = '[5,6)'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; +DROP FUNCTION for_portion_of_trigger CASCADE; + +-- Triggers with a custom transition table name: + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); + +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF TG_OP = 'INSERT' THEN + RAISE NOTICE '%: % %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'UPDATE' THEN + RAISE NOTICE '%: % %, OLD table = %, NEW table = %', + TG_NAME, TG_OP, TG_LEVEL, + (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table), + (SELECT string_agg(new_table::text, ', ' ORDER BY id) FROM new_table); + ELSIF TG_OP = 'DELETE' THEN + RAISE NOTICE '%: % %, OLD table = %', + TG_NAME, TG_OP, TG_LEVEL, (SELECT string_agg(old_table::text, ', ' ORDER BY id) FROM old_table); + END IF; + RETURN NULL; +END; +$$; + +CREATE TRIGGER for_portion_of_test_insert_trig +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_insert_trig_stmt +AFTER INSERT ON for_portion_of_test +REFERENCING NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_update_trig +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_update_trig_stmt +AFTER UPDATE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_delete_trig +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH ROW EXECUTE PROCEDURE dump_trigger(); + +CREATE TRIGGER for_portion_of_test_delete_trig_stmt +AFTER DELETE ON for_portion_of_test +REFERENCING OLD TABLE AS old_table +FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(); + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +ROLLBACK; + +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +ROLLBACK; + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +ROLLBACK; + +-- Test with multiranges + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), +('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), +('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), +('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); +; + +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-10', '2018-02-10'), daterange('2018-03-05', '2018-05-01'))) +SET name = 'one^1' +WHERE id = '[1,2)'; + +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-15', '2018-02-15'), daterange('2018-03-01', '2018-03-15'))) +WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; + +-- Test with a custom range type + +CREATE TYPE mydaterange AS range(subtype=date); + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 +VALUES +('[1,2)', '[2018-01-02,2018-02-03)', 'one'), +('[1,2)', '[2018-02-03,2018-03-03)', 'one'), +('[1,2)', '[2018-03-03,2018-04-04)', 'one'), +('[2,3)', '[2018-01-01,2018-05-01)', 'two'), +('[3,4)', '[2018-01-01,)', 'three'); +; + +UPDATE for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' +SET name = 'one^1' +WHERE id = '[1,2)'; + +DELETE FROM for_portion_of_test2 +FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' +WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; + +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed + +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); + +INSERT INTO temporal_partitioned VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); + +SELECT * FROM temporal_partitioned; + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; + +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; + +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; + +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; + +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; + +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; + +-- Update all partitions at once (each with leftovers) + +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + +DROP TABLE temporal_partitioned; + +RESET datestyle; diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 2fe7b6dcc49..2dffe7c5b6a 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1241,6 +1241,7 @@ WHERE a1.amopfamily = 0 OR a1.amoplefttype = 0 OR a1.amoprighttype = 0 SELECT a1.amopfamily, a1.amopstrategy FROM pg_amop as a1 WHERE NOT ((a1.amoppurpose = 's' AND a1.amopsortfamily = 0) OR + (a1.amoppurpose = 'p' AND a1.amopsortfamily = 0) OR (a1.amoppurpose = 'o' AND a1.amopsortfamily <> 0)); -- amopmethod must match owning opfamily's opfmethod diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 3f54b0f8f05..0688b3662e1 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -723,6 +723,24 @@ UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; +-- test column-level privileges on the range used in FOR PORTION OF +SET SESSION AUTHORIZATION regress_priv_user1; +CREATE TABLE t1 ( + c1 int4range, + valid_at tsrange, + CONSTRAINT t1pk PRIMARY KEY (c1, valid_at WITHOUT OVERLAPS) +); +GRANT SELECT ON t1 TO regress_priv_user2; +GRANT SELECT ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1, valid_at) ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user3; +UPDATE t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01' SET c1 = '[2,3)'; +SET SESSION AUTHORIZATION regress_priv_user1; +DROP TABLE t1; + -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 ADD COLUMN three integer; diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql index 93b693ae837..9bf237c71d4 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1865,6 +1865,20 @@ select * from uv_iocu_tab; drop view uv_iocu_view; drop table uv_iocu_tab; +-- Check UPDATE FOR PORTION OF works correctly +create table uv_fpo_tab (id int4range, valid_at tsrange, b float, + constraint pk_uv_fpo_tab primary key (id, valid_at without overlaps)); +insert into uv_fpo_tab values ('[1,1]', '[2020-01-01, 2030-01-01)', 0); +create view uv_fpo_view as + select b, b+1 as c, valid_at, id, '2.0'::text as two from uv_fpo_tab; + +insert into uv_fpo_view (id, valid_at, b) values ('[1,1]', '[2010-01-01, 2020-01-01)', 1); +select * from uv_fpo_view; +update uv_fpo_view for portion of valid_at from '2015-01-01' to '2020-01-01' set b = 2 where id = '[1,1]'; +select * from uv_fpo_view; +delete from uv_fpo_view for portion of valid_at from '2017-01-01' to '2022-01-01' where id = '[1,1]'; +select * from uv_fpo_view; + -- Test whole-row references to the view create table uv_iocu_tab (a int unique, b text); create view uv_iocu_view as diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql index ebee2f2932e..f68f78ffa35 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -632,6 +632,20 @@ INSERT INTO temporal3 (id, valid_at, id2, name) ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01' + SET name = name || '1'; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01' + SET name = name || '2' + WHERE id = '[2,3)'; +SELECT * FROM temporal3 ORDER BY id, valid_at; +-- conflicting id only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3'); +-- conflicting id2 only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3'); DROP TABLE temporal3; -- @@ -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 @@ -1262,6 +1304,16 @@ COMMIT; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1311,6 +1363,16 @@ ROLLBACK; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing an unreferenced part is okay: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +SET id = '[7,8)' +WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +SET id = '[7,8)' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1346,9 +1408,20 @@ BEGIN; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); COMMIT; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test FK referenced deletes RESTRICT @@ -1377,9 +1450,20 @@ BEGIN; DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ROLLBACK; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- clean up: +DELETE FROM temporal_fk_rng2rng WHERE parent_id = '[5,6)'; +DELETE FROM temporal_rng WHERE id IN ('[5,6)'); -- -- test ON UPDATE/DELETE options diff --git a/src/test/subscription/t/034_temporal.pl b/src/test/subscription/t/034_temporal.pl index 3ef4249a9fe..7a6d84edf45 100644 --- a/src/test/subscription/t/034_temporal.pl +++ b/src/test/subscription/t/034_temporal.pl @@ -108,6 +108,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)'"); @@ -115,6 +121,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'); @@ -136,16 +148,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: @@ -163,6 +181,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)'"); @@ -170,6 +194,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'); @@ -285,16 +315,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: @@ -308,16 +344,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: @@ -331,17 +373,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 @@ -430,16 +478,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: @@ -453,16 +507,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 @@ -573,6 +633,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)'"); @@ -580,6 +646,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'); @@ -605,6 +677,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)'"); @@ -612,6 +690,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'); @@ -637,6 +721,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)'"); @@ -644,6 +734,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'); -- 2.42.0