From 8e72d914c52876525a90b28444453de8085c866f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 1 Apr 2026 19:06:03 +0200 Subject: [PATCH] Add UPDATE/DELETE FOR PORTION OF This is an extension of the UPDATE and DELETE commands to do a "temporal update/delete" based on a range or multirange column. The user can say UPDATE t FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET ... (or likewise with DELETE) where valid_at is a range or multirange column. The command is automatically limited to rows overlapping the targeted portion, and only history within those bounds is changed. If a row represents history partly inside and partly outside the bounds, then the command truncates the row's application time to fit within the targeted portion, then it inserts one or more "temporal leftovers": new rows containing all the original values, except with the application-time column changed to only represent the untouched part of history. To compute the temporal leftovers that are required, we use the *_minus_multi set-returning functions defined in 5eed8ce50c. - Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. We do accept functions like NOW(). - Added logic to executor to insert new rows for the "temporal leftover" part of a record touched by a FOR PORTION OF query. - Documented FOR PORTION OF. - Added tests. Author: Paul A. Jungwirth Reviewed-by: Peter Eisentraut Discussion: https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com --- .../postgres_fdw/expected/postgres_fdw.out | 45 +- contrib/postgres_fdw/sql/postgres_fdw.sql | 34 + contrib/test_decoding/expected/ddl.out | 52 + contrib/test_decoding/sql/ddl.sql | 30 + doc/src/sgml/dml.sgml | 135 ++ doc/src/sgml/glossary.sgml | 15 + doc/src/sgml/images/Makefile | 4 +- doc/src/sgml/images/meson.build | 2 + doc/src/sgml/images/temporal-delete.svg | 41 + doc/src/sgml/images/temporal-delete.txt | 10 + doc/src/sgml/images/temporal-update.svg | 45 + doc/src/sgml/images/temporal-update.txt | 10 + doc/src/sgml/ref/create_publication.sgml | 6 + doc/src/sgml/ref/delete.sgml | 108 +- doc/src/sgml/ref/update.sgml | 112 +- doc/src/sgml/trigger.sgml | 12 + src/backend/executor/execMain.c | 1 + src/backend/executor/nodeModifyTable.c | 350 ++- src/backend/nodes/nodeFuncs.c | 33 + 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 | 357 ++- src/backend/parser/gram.y | 111 +- 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 | 75 +- src/backend/utils/adt/ruleutils.c | 41 + src/backend/utils/cache/lsyscache.c | 25 + src/include/nodes/execnodes.h | 22 + src/include/nodes/parsenodes.h | 21 + src/include/nodes/pathnodes.h | 1 + src/include/nodes/plannodes.h | 2 + src/include/nodes/primnodes.h | 35 + src/include/optimizer/pathnode.h | 2 +- src/include/parser/analyze.h | 3 +- src/include/parser/kwlist.h | 1 + src/include/parser/parse_node.h | 1 + src/include/utils/lsyscache.h | 1 + src/test/regress/expected/for_portion_of.out | 2100 +++++++++++++++++ src/test/regress/expected/privileges.out | 28 + src/test/regress/expected/updatable_views.out | 32 + .../regress/expected/without_overlaps.out | 245 +- src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/for_portion_of.sql | 1368 +++++++++++ src/test/regress/sql/privileges.sql | 27 + src/test/regress/sql/updatable_views.sql | 14 + src/test/regress/sql/without_overlaps.sql | 120 +- src/test/subscription/t/034_temporal.pl | 100 +- src/tools/pgindent/typedefs.list | 3 + 53 files changed, 5728 insertions(+), 88 deletions(-) create mode 100644 doc/src/sgml/images/temporal-delete.svg create mode 100644 doc/src/sgml/images/temporal-delete.txt create mode 100644 doc/src/sgml/images/temporal-update.svg create mode 100644 doc/src/sgml/images/temporal-update.txt 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 0f5271d476e..ac34a1acacb 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -50,11 +50,19 @@ CREATE TABLE "S 1"."T 4" ( c3 text, CONSTRAINT t4_pkey PRIMARY KEY (c1) ); +CREATE TABLE "S 1"."T 5" ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL, + CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS) +); -- Disable autovacuum for these tables to avoid unexpected effects of that ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); +ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false'); INSERT INTO "S 1"."T 1" SELECT id, id % 10, @@ -81,10 +89,17 @@ INSERT INTO "S 1"."T 4" 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests +INSERT INTO "S 1"."T 5" + SELECT int4range(id, id + 1), + id + 1, + 'AAA' || to_char(id, 'FM000'), + '[2000-01-01,2020-01-01)' + FROM generate_series(1, 100) id; ANALYZE "S 1"."T 1"; ANALYZE "S 1"."T 2"; ANALYZE "S 1"."T 3"; ANALYZE "S 1"."T 4"; +ANALYZE "S 1"."T 5"; -- =================================================================== -- create foreign tables -- =================================================================== @@ -132,6 +147,12 @@ CREATE FOREIGN TABLE ft7 ( c2 int NOT NULL, c3 text ) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); +CREATE FOREIGN TABLE ft8 ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL +) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5'); -- =================================================================== -- tests for validator -- =================================================================== @@ -214,7 +235,8 @@ ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1'); public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') | public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') | public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') | -(6 rows) + public | ft8 | loopback | (schema_name 'S 1', table_name 'T 5') | +(7 rows) -- Test that alteration of server options causes reconnection -- Remote's errors might be non-English, so hide them to ensure stable results @@ -6311,6 +6333,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 49ed797e8ef..0e218b29a29 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -54,12 +54,20 @@ CREATE TABLE "S 1"."T 4" ( c3 text, CONSTRAINT t4_pkey PRIMARY KEY (c1) ); +CREATE TABLE "S 1"."T 5" ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL, + CONSTRAINT t5_pkey PRIMARY KEY (c1, c4 WITHOUT OVERLAPS) +); -- Disable autovacuum for these tables to avoid unexpected effects of that ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 3" SET (autovacuum_enabled = 'false'); ALTER TABLE "S 1"."T 4" SET (autovacuum_enabled = 'false'); +ALTER TABLE "S 1"."T 5" SET (autovacuum_enabled = 'false'); INSERT INTO "S 1"."T 1" SELECT id, @@ -87,11 +95,18 @@ INSERT INTO "S 1"."T 4" 'AAA' || to_char(id, 'FM000') FROM generate_series(1, 100) id; DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests +INSERT INTO "S 1"."T 5" + SELECT int4range(id, id + 1), + id + 1, + 'AAA' || to_char(id, 'FM000'), + '[2000-01-01,2020-01-01)' + FROM generate_series(1, 100) id; ANALYZE "S 1"."T 1"; ANALYZE "S 1"."T 2"; ANALYZE "S 1"."T 3"; ANALYZE "S 1"."T 4"; +ANALYZE "S 1"."T 5"; -- =================================================================== -- create foreign tables @@ -146,6 +161,14 @@ CREATE FOREIGN TABLE ft7 ( c3 text ) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4'); +CREATE FOREIGN TABLE ft8 ( + c1 int4range NOT NULL, + c2 int NOT NULL, + c3 text, + c4 daterange NOT NULL +) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 5'); + + -- =================================================================== -- tests for validator -- =================================================================== @@ -1553,6 +1576,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/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out index bcd1f74b2bc..6819812e806 100644 --- a/contrib/test_decoding/expected/ddl.out +++ b/contrib/test_decoding/expected/ddl.out @@ -192,6 +192,58 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc COMMIT (33 rows) +-- FOR PORTION OF setup +CREATE TABLE replication_example_temporal(id int4range, valid_at daterange, somedata int, text varchar(120), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)); +INSERT INTO replication_example_temporal VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 1, 'aaa'); +INSERT INTO replication_example_temporal VALUES ('[2,3)', '[2000-01-01,2020-01-01)', 1, 'aaa'); +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + data +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + BEGIN + table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa' + COMMIT + BEGIN + table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa' + COMMIT +(6 rows) + +-- UPDATE FOR PORTION OF support +BEGIN; + UPDATE replication_example_temporal + FOR PORTION OF valid_at FROM '2010-01-01' TO '2011-01-01' + SET somedata = 2, + text = 'bbb' + WHERE id = '[1,2)'; +COMMIT; +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + data +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ + BEGIN + table public.replication_example_temporal: UPDATE: old-key: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2020)' new-tuple: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2010,01-01-2011)' somedata[integer]:2 text[character varying]:'bbb' + table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2000,01-01-2010)' somedata[integer]:1 text[character varying]:'aaa' + table public.replication_example_temporal: INSERT: id[int4range]:'[1,2)' valid_at[daterange]:'[01-01-2011,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa' + COMMIT +(5 rows) + +-- DELETE FOR PORTION OF support +BEGIN; + DELETE FROM replication_example_temporal + FOR PORTION OF valid_at FROM '2012-01-01' TO '2013-01-01' + WHERE id = '[2,3)'; +COMMIT; +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + data +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + BEGIN + table public.replication_example_temporal: DELETE: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2020)' + table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2000,01-01-2012)' somedata[integer]:1 text[character varying]:'aaa' + table public.replication_example_temporal: INSERT: id[int4range]:'[2,3)' valid_at[daterange]:'[01-01-2013,01-01-2020)' somedata[integer]:1 text[character varying]:'aaa' + COMMIT +(5 rows) + -- MERGE support BEGIN; MERGE INTO replication_example t diff --git a/contrib/test_decoding/sql/ddl.sql b/contrib/test_decoding/sql/ddl.sql index 2f8e4e7f2cc..6d0b7d77778 100644 --- a/contrib/test_decoding/sql/ddl.sql +++ b/contrib/test_decoding/sql/ddl.sql @@ -93,6 +93,36 @@ COMMIT; /* display results */ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); +-- FOR PORTION OF setup +CREATE TABLE replication_example_temporal(id int4range, valid_at daterange, somedata int, text varchar(120), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)); +INSERT INTO replication_example_temporal VALUES ('[1,2)', '[2000-01-01,2020-01-01)', 1, 'aaa'); +INSERT INTO replication_example_temporal VALUES ('[2,3)', '[2000-01-01,2020-01-01)', 1, 'aaa'); + +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + +-- UPDATE FOR PORTION OF support +BEGIN; + UPDATE replication_example_temporal + FOR PORTION OF valid_at FROM '2010-01-01' TO '2011-01-01' + SET somedata = 2, + text = 'bbb' + WHERE id = '[1,2)'; +COMMIT; + +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + +-- DELETE FOR PORTION OF support +BEGIN; + DELETE FROM replication_example_temporal + FOR PORTION OF valid_at FROM '2012-01-01' TO '2013-01-01' + WHERE id = '[2,3)'; +COMMIT; + +/* display results */ +SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'include-xids', '0', 'skip-empty-xacts', '1'); + -- MERGE support BEGIN; MERGE INTO replication_example t diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml index cd348d5773a..86d3865ae90 100644 --- a/doc/src/sgml/dml.sgml +++ b/doc/src/sgml/dml.sgml @@ -261,6 +261,141 @@ DELETE FROM products; + + Updating and Deleting Temporal Data + + + Special syntax is available to update and delete from application-time + temporal tables (see ). (No extra + syntax is required to insert into them: the user just provides the + application time values like any other column.) When updating or deleting, + the user can target a specific portion of history. Only rows overlapping + that history are affected, and within those rows only the targeted history + is changed. If a row contains more history beyond what is targeted, its + application time is reduced to fit within the targeted portion, and new + rows are inserted to preserve the history that was not targeted. + + + + Recall the example table from , + containing this data: + + + product_no | price | valid_at +------------+-------+------------------------- + 5 | 5.00 | [2020-01-01,2022-01-01) + 5 | 8.00 | [2022-01-01,) + 6 | 9.00 | [2021-01-01,2024-01-01) + + + A temporal update might look like this: + + +UPDATE products + FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01' + SET price = 12.00 + WHERE product_no = 5; + + + That command will update the second record for product 5. It will set the + price to 12.00 and the application time to + [2023-09-01,2025-03-01). Then, since the row's + application time was originally [2022-01-01,), the + command must insert two temporal leftovers: one + for history before September 1, 2023, and another for history since March + 1, 2025. After the update, the table has four rows for product 5: + + + product_no | price | valid_at +------------+-------+------------------------- + 5 | 5.00 | [2020-01-01,2022-01-01) + 5 | 8.00 | [2022-01-01,2023-09-01) + 5 | 12.00 | [2023-09-01,2025-03-01) + 5 | 8.00 | [2025-03-01,) + 6 | 9.00 | [2021-01-01,2024-01-01) + + + The new history could be plotted as in . + + +
+ Temporal Update Example + + + + + +
+ + + Similarly, a specific portion of history may be targeted when deleting rows + from a table. In that case, the original rows are removed, but new + temporal + leftovers are inserted to preserve the untouched history. The + syntax for a temporal delete is: + + +DELETE FROM products + FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01' + WHERE product_no = 5; + + + Continuing the example, this command would delete two records. The + first record would yield a single temporal leftover, and the second + would be deleted entirely. The rows in the table would now be: + + + product_no | price | valid_at +------------+-------+------------------------- + 5 | 5.00 | [2020-01-01,2021-08-01) + 5 | 12.00 | [2023-09-01,2025-03-01) + 5 | 8.00 | [2025-03-01,) + 6 | 9.00 | [2021-01-01,2024-01-01) + + + The new history could be plotted as in . + + +
+ Temporal Delete Example + + + + + +
+ + + Instead of using the FROM ... TO ... syntax, temporal + update/delete commands can also give the targeted range/multirange + directly, inside parentheses. For example: DELETE FROM products + FOR PORTION OF valid_at ('[2028-01-01,)') .... This syntax is + required when application time is stored in a multirange column. + + + + When application time is stored in a range type column, zero, one or two + temporal leftovers are produced by each row that is updated/deleted. With + a multirange column, only zero or one temporal leftover is produced. The + leftover bounds are computed using range_minus_multi and + multirange_minus_multi (see ). + + + + The bounds given to FOR PORTION OF must be constant. + Functions like now() are allowed, but column references + are not. + + + + When temporal leftovers are inserted, all INSERT + triggers are fired, but permission checks for inserting rows are + skipped. + + + Returning Data from Modified Rows diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index e2db5bcc78c..113d7640626 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1933,6 +1933,21 @@ + + Temporal leftovers + + + After a temporal update or delete, the portion of history that was not + updated/deleted. When using ranges to track application time, there may be + zero, one, or two stretches of history that were not updated/deleted + (before and/or after the portion that was updated/deleted). New rows are + automatically inserted into the table to preserve that history. A single + multirange can accommodate the untouched history before and after the + update/delete, so there will be only zero or one leftover. + + + + Temporal table diff --git a/doc/src/sgml/images/Makefile b/doc/src/sgml/images/Makefile index fd55b9ad23f..38f8869d78d 100644 --- a/doc/src/sgml/images/Makefile +++ b/doc/src/sgml/images/Makefile @@ -7,7 +7,9 @@ ALL_IMAGES = \ gin.svg \ pagelayout.svg \ temporal-entities.svg \ - temporal-references.svg + temporal-references.svg \ + temporal-update.svg \ + temporal-delete.svg DITAA = ditaa DOT = dot diff --git a/doc/src/sgml/images/meson.build b/doc/src/sgml/images/meson.build index 8e601e877a5..ea1fdee3901 100644 --- a/doc/src/sgml/images/meson.build +++ b/doc/src/sgml/images/meson.build @@ -16,6 +16,8 @@ all_files = [ 'pagelayout.txt', 'temporal-entities.txt', 'temporal-references.txt', + 'temporal-update.txt', + 'temporal-delete.txt', ] foreach file : all_files diff --git a/doc/src/sgml/images/temporal-delete.svg b/doc/src/sgml/images/temporal-delete.svg new file mode 100644 index 00000000000..2d8b1d6ec7b --- /dev/null +++ b/doc/src/sgml/images/temporal-delete.svg @@ -0,0 +1,41 @@ + + + + + + + + + + + + + + + + + + + + + + + products + (5, 5.00, + [1 Jan 2020,1 Aug 2021)) + 2020 + 2021 + 2022 + 2023 + products + (5, 12.00, + [1 Sep 2023,1 Mar 2025)) + 2024 + 2025 + products + (5, 8.00, + [1 Mar 2025,)) + 2026 + ... + + diff --git a/doc/src/sgml/images/temporal-delete.txt b/doc/src/sgml/images/temporal-delete.txt new file mode 100644 index 00000000000..bf79b2207c3 --- /dev/null +++ b/doc/src/sgml/images/temporal-delete.txt @@ -0,0 +1,10 @@ ++----------------------------+ +-------------------------------+--------------------------+ +| cGRE | | cGRE | cGRE | +| products | | products | products | +| (5, 5.00, | | (5, 12.00, | (5, 8.00, | +| [1 Jan 2020,1 Aug 2021)) | | [1 Sep 2023,1 Mar 2025)) | [1 Mar 2025,)) | +| | | | | ++----------------------------+ +-------------------------------+--------------------------+ + +| | | | | | | | +2020 2021 2022 2023 2024 2025 2026 ... diff --git a/doc/src/sgml/images/temporal-update.svg b/doc/src/sgml/images/temporal-update.svg new file mode 100644 index 00000000000..6c7c43c8d22 --- /dev/null +++ b/doc/src/sgml/images/temporal-update.svg @@ -0,0 +1,45 @@ + + + + + + + + + + + + + + + + + + + + + + + + products + (5, 5.00, + [1 Jan 2020,1 Jan 2022)) + 2020 + 2021 + products + (5, 8.00, + [1 Jan 2022,1 Sep 2023)) + 2022 + 2023 + products + (5, 12.00, + [1 Sep 2023,1 Mar 2025)) + 2024 + 2025 + products + (5, 8.00, + [1 Mar 2025,)) + 2026 + ... + + diff --git a/doc/src/sgml/images/temporal-update.txt b/doc/src/sgml/images/temporal-update.txt new file mode 100644 index 00000000000..87a16382810 --- /dev/null +++ b/doc/src/sgml/images/temporal-update.txt @@ -0,0 +1,10 @@ ++-----------------------------------+-----------------------------+----------------------------+------------------------------+ +| cGRE | cGRE | cGRE | cGRE | +| products | products | products | products | +| (5, 5.00, | (5, 8.00, | (5, 12.00, | (5, 8.00, | +| [1 Jan 2020,1 Jan 2022)) | [1 Jan 2022,1 Sep 2023)) | [1 Sep 2023,1 Mar 2025)) | [1 Mar 2025,)) | +| | | | | ++-----------------------------------+-----------------------------+----------------------------+------------------------------+ + +| | | | | | | | +2020 2021 2022 2023 2024 2025 2026 ... diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index 16536e37eec..0ac576d3f10 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -436,6 +436,12 @@ CREATE PUBLICATION name for each row inserted, updated, or deleted. + + For an UPDATE/DELETE ... FOR PORTION OF command, the + publication will publish an UPDATE or DELETE, + followed by one INSERT for each temporal leftover row inserted. + + ATTACHing a table into a partition tree whose root is published using a publication with publish_via_partition_root diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index b9367f2b23c..9066d7ea83d 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -22,11 +22,18 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] +DELETE FROM [ ONLY ] table_name [ * ] + [ FOR PORTION OF range_column_name for_portion_of_target ] + [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ] { * | output_expression [ [ AS ] output_name ] } [, ...] ] + +where for_portion_of_target is: + +{ FROM start_time TO end_time | + ( portion ) } @@ -64,12 +71,33 @@ DELETE FROM [ ONLY ] table_name [ * output list of SELECT. + + If the FOR PORTION OF clause is used, the delete will + only affect rows that overlap the given portion. Furthermore, if a row's + application time extends outside the FOR PORTION OF + bounds, then the delete will only change the application time within those + bounds. In effect, only the history targeted by FOR PORTION + OF is deleted, and no moments outside. Furthermore, after a row + is deleted, new temporal + leftovers might be inserted: rows whose range or multirange + receives the remaining application time outside the targeted bounds, with + the original values in their other columns. For range columns, there will + be zero to two inserted records, depending on whether the original + application time was completely deleted, extended before/after the change, + or both. Multiranges never require two temporal leftovers, because one + value can always contain whatever application time remains. + + You must have the DELETE privilege on the table to delete from it, as well as the SELECT privilege for any table in the USING clause or whose values are read in the condition. + When FOR PORTION OF is used, the secondary inserts do + not require INSERT privilege on the table. (This is + because conceptually no new information is being added; the inserted rows + only preserve existing data about the untargeted time period.) @@ -117,6 +145,56 @@ DELETE FROM [ ONLY ] table_name [ * + + range_column_name + + + The range or multirange column to use when performing a temporal delete. + + + + + + for_portion_of_target + + + The portion to delete. If targeting a range column, this can be in the + form FROM start_time TO + end_time. Otherwise, it + must be in the form (portion), where + portion is an expression + that yields a value of the same type as range_column_name. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal delete. This must + be a value matching the base type of the range from range_column_name. A null value here + indicates a delete whose beginning is unbounded (as with range types). + + + + + + end_time + + + The latest time (exclusive) to change in a temporal delete. This must + be a value matching the base type of the range from range_column_name. A null value here + indicates a delete whose end is unbounded (as with range types). + + + + from_item @@ -238,6 +316,10 @@ DELETE count suppressed by a BEFORE DELETE trigger. If count is 0, no rows were deleted by the query (this is not considered an error). + If FOR PORTION OF was used, the + count does not include + temporal leftovers + that were inserted. @@ -245,7 +327,13 @@ DELETE count clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) deleted by the - command. + command. If FOR PORTION OF was used, the + RETURNING clause gives one result for each deleted row, + but does not include inserted + temporal leftovers. + The value of the application-time column matches the old value of the deleted + row(s). Note this will represent more application time than was actually erased, + if temporal leftovers were inserted. @@ -272,6 +360,13 @@ DELETE FROM films In some cases the join style is easier to write or faster to execute than the sub-select style. + + + When FOR PORTION OF is used, this can result in users + who don't have INSERT privileges firing + INSERT triggers. This should be considered when + using SECURITY DEFINER trigger functions. + @@ -306,6 +401,15 @@ DELETE FROM tasks WHERE CURRENT OF c_tasks; + + An example of a temporal delete: + +DELETE FROM products + FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01' + WHERE product_no = 5; + + + While there is no LIMIT clause for DELETE, it is possible to get a similar effect diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index b523766abe3..92c12c36915 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_column_name for_portion_of_target ] + [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) @@ -31,6 +33,11 @@ UPDATE [ ONLY ] table_name [ * ] [ [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ] { * | output_expression [ [ AS ] output_name ] } [, ...] ] + +where for_portion_of_target is: + +{ FROM start_time TO end_time | + ( portion ) } @@ -63,6 +70,27 @@ UPDATE [ ONLY ] table_name [ * ] [ output list of SELECT. + + If the FOR PORTION OF clause is used, the update will + only affect rows that overlap the given portion. Furthermore, if a row's + application time extends outside the FOR PORTION OF + bounds, then the update will only change the application time within those + bounds. In effect, only the history targeted by FOR PORTION + OF is updated, and no moments outside. Furthermore, after a row + is updated, the range or multirange is first shrunk so that its application + time no longer extends beyond the targeted FOR PORTION + OF bounds. Then, new temporal leftovers might + be inserted: rows whose range or multirange receives the remaining + application time outside the targeted + FROM/TO bounds, with the original + values in their other columns. For range columns, there will be zero to + two inserted records, depending on whether the original application time + was completely updated, extended before/after the change, or both. + Multiranges never require two temporal leftovers, because one value can + always contain whatever application time remains. + + You must have the UPDATE privilege on the table, or at least on the column(s) that are listed to be updated. @@ -70,6 +98,10 @@ UPDATE [ ONLY ] table_name [ * ] [ privilege on any column whose values are read in the expressions or condition. + When FOR PORTION OF is used, the secondary inserts do + not require INSERT privilege on the table. (This is + because conceptually no new information is being added; the inserted rows + only preserve existing data about the untargeted time period.) @@ -116,6 +148,56 @@ UPDATE [ ONLY ] table_name [ * ] [ + + range_column_name + + + The range or multirange column to use when performing a temporal update. + + + + + + for_portion_of_target + + + The portion to update. If targeting a range column, this can be in the + form FROM start_time TO + end_time. Otherwise, it + must be in the form (portion) where + portion is an expression + that yields a value of the same type as range_column_name. + + + + + + start_time + + + The earliest time (inclusive) to change in a temporal update. This must + be a value matching the base type of the range from range_column_name. A null value here + indicates an update whose beginning is unbounded (as with range types). + + + + + + end_time + + + The latest time (exclusive) to change in a temporal update. This must + be a value matching the base type of the range from range_column_name. A null value here + indicates an update whose end is unbounded (as with range types). + + + + column_name @@ -283,6 +365,10 @@ UPDATE count updates were suppressed by a BEFORE UPDATE trigger. If count is 0, no rows were updated by the query (this is not considered an error). + If FOR PORTION OF was used, the + count does not include + temporal leftovers + that were inserted. @@ -290,7 +376,12 @@ UPDATE count clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) updated by the - command. + command. If FOR PORTION OF was used, the + RETURNING clause gives one result for each updated row, + but does not include inserted + temporal leftovers. + The value of the application-time column matches the new value of the updated + row(s). @@ -355,6 +446,13 @@ UPDATE count partition that is not the same as the ancestor that's mentioned in the UPDATE query. + + + When FOR PORTION OF is used, this can result in users + who don't have INSERT privileges firing + INSERT triggers. This should be considered when + using SECURITY DEFINER trigger functions. + @@ -477,6 +575,16 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; + + An example of a temporal update: + +UPDATE products + FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01' + SET price = 12.00 + WHERE product_no = 5; + + + Updates affecting many rows can have negative effects on system performance, such as table bloat, increased replica lag, and increased diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 0062f1a3fd1..8a5e7278212 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -215,6 +215,18 @@ are fired for the same kind of action. + + 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. + (The BEFORE DELETE/UPDATE triggers are fired first, + then BEFORE INSERT, then AFTER + INSERT, then AFTER DELETE/UPDATE.) + + Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 58b84955c2b..45e00c6af85 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1314,6 +1314,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 582bcc367c0..331ac4649bc 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -71,6 +71,7 @@ #include "utils/builtins.h" #include "utils/datum.h" #include "utils/injection_point.h" +#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -167,6 +168,10 @@ static bool ExecOnConflictSelect(ModifyTableContext *context, TupleTableSlot *excludedSlot, bool canSetTag, TupleTableSlot **returning); +static void ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid); static TupleTableSlot *ExecPrepareTupleRouting(ModifyTableState *mtstate, EState *estate, PartitionTupleRouting *proute, @@ -189,6 +194,9 @@ static TupleTableSlot *ExecMergeMatched(ModifyTableContext *context, static TupleTableSlot *ExecMergeNotMatched(ModifyTableContext *context, ResultRelInfo *resultRelInfo, bool canSetTag); +static void ExecSetupTransitionCaptureState(ModifyTableState *mtstate, EState *estate); +static void fireBSTriggers(ModifyTableState *node); +static void fireASTriggers(ModifyTableState *node); /* @@ -1384,6 +1392,227 @@ ExecInsert(ModifyTableContext *context, return result; } +/* ---------------------------------------------------------------- + * ExecForPortionOfLeftovers + * + * Insert tuples for the untouched portion of a row in a FOR + * PORTION OF UPDATE/DELETE + * ---------------------------------------------------------------- + */ +static void +ExecForPortionOfLeftovers(ModifyTableContext *context, + EState *estate, + ResultRelInfo *resultRelInfo, + ItemPointer tupleid) +{ + ModifyTableState *mtstate = context->mtstate; + ModifyTable *node = (ModifyTable *) mtstate->ps.plan; + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node->forPortionOf; + AttrNumber rangeAttno; + Datum oldRange; + TypeCacheEntry *typcache; + ForPortionOfState *fpoState; + TupleTableSlot *oldtupleSlot; + TupleTableSlot *leftoverSlot; + TupleConversionMap *map = NULL; + HeapTuple oldtuple = NULL; + CmdType oldOperation; + TransitionCaptureState *oldTcs; + FmgrInfo flinfo; + ReturnSetInfo rsi; + bool didInit = false; + bool shouldFree = false; + + LOCAL_FCINFO(fcinfo, 2); + + if (!resultRelInfo->ri_forPortionOf) + { + /* + * If we don't have a ForPortionOfState yet, we must be a partition + * child being hit for the first time. Make a copy from the root, with + * our own tupleTableSlot. We do this lazily so that we don't pay the + * price of unused partitions. + */ + ForPortionOfState *leafState = makeNode(ForPortionOfState); + + if (!mtstate->rootResultRelInfo) + elog(ERROR, "no root relation but ri_forPortionOf is uninitialized"); + + fpoState = mtstate->rootResultRelInfo->ri_forPortionOf; + Assert(fpoState); + + leafState->fp_rangeName = fpoState->fp_rangeName; + leafState->fp_rangeType = fpoState->fp_rangeType; + leafState->fp_rangeAttno = fpoState->fp_rangeAttno; + leafState->fp_targetRange = fpoState->fp_targetRange; + leafState->fp_Leftover = fpoState->fp_Leftover; + /* Each partition needs a slot matching its tuple descriptor */ + leafState->fp_Existing = + table_slot_create(resultRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + resultRelInfo->ri_forPortionOf = leafState; + } + fpoState = resultRelInfo->ri_forPortionOf; + oldtupleSlot = fpoState->fp_Existing; + leftoverSlot = fpoState->fp_Leftover; + + /* + * Get the old pre-UPDATE/DELETE tuple. We will use its range to compute + * untouched parts of history, and if necessary we will insert copies with + * truncated start/end times. + * + * We have already locked the tuple in ExecUpdate/ExecDelete, and it has + * passed EvalPlanQual. This ensures that concurrent updates in READ + * COMMITTED can't insert conflicting temporal leftovers. + */ + if (!table_tuple_fetch_row_version(resultRelInfo->ri_RelationDesc, tupleid, SnapshotAny, oldtupleSlot)) + elog(ERROR, "failed to fetch tuple for FOR PORTION OF"); + + /* + * Get the old range of the record being updated/deleted. Must read with + * the attno of the leaf partition being updated. + */ + + rangeAttno = forPortionOf->rangeVar->varattno; + if (resultRelInfo->ri_RootResultRelInfo) + map = ExecGetChildToRootMap(resultRelInfo); + if (map != NULL) + rangeAttno = map->attrMap->attnums[rangeAttno - 1]; + slot_getallattrs(oldtupleSlot); + + if (oldtupleSlot->tts_isnull[rangeAttno - 1]) + elog(ERROR, "found a NULL range in a temporal table"); + oldRange = oldtupleSlot->tts_values[rangeAttno - 1]; + + /* + * Get the range's type cache entry. This is worth caching for the whole + * UPDATE/DELETE as range functions do. + */ + + typcache = fpoState->fp_leftoverstypcache; + if (typcache == NULL) + { + typcache = lookup_type_cache(forPortionOf->rangeType, 0); + fpoState->fp_leftoverstypcache = typcache; + } + + /* + * Get the ranges to the left/right of the targeted range. We call a SETOF + * support function and insert as many temporal leftovers as it gives us. + * Although rangetypes have 0/1/2 leftovers, multiranges have 0/1, and + * other types may have more. + */ + + fmgr_info(forPortionOf->withoutPortionProc, &flinfo); + rsi.type = T_ReturnSetInfo; + rsi.econtext = mtstate->ps.ps_ExprContext; + rsi.expectedDesc = NULL; + rsi.allowedModes = (int) (SFRM_ValuePerCall); + rsi.returnMode = SFRM_ValuePerCall; + rsi.setResult = NULL; + rsi.setDesc = NULL; + + InitFunctionCallInfoData(*fcinfo, &flinfo, 2, InvalidOid, NULL, (Node *) &rsi); + fcinfo->args[0].value = oldRange; + fcinfo->args[0].isnull = false; + fcinfo->args[1].value = fpoState->fp_targetRange; + fcinfo->args[1].isnull = false; + + /* + * If there are partitions, we must insert into the root table, so we get + * tuple routing. We already set up leftoverSlot with the root tuple + * descriptor. + */ + if (resultRelInfo->ri_RootResultRelInfo) + resultRelInfo = resultRelInfo->ri_RootResultRelInfo; + + /* + * Insert a leftover for each value returned by the without_portion helper + * function + */ + while (true) + { + Datum leftover = FunctionCallInvoke(fcinfo); + + /* Are we done? */ + if (rsi.isDone == ExprEndResult) + break; + + if (fcinfo->isnull) + elog(ERROR, "Got a null from without_portion function"); + + /* + * Does the new Datum violate domain checks? Row-level CHECK + * constraints are validated by ExecInsert, so we don't need to do + * anything here for those. + */ + if (forPortionOf->isDomain) + domain_check(leftover, false, forPortionOf->rangeVar->vartype, NULL, NULL); + + if (!didInit) + { + /* + * Make a copy of the pre-UPDATE row. Then we'll overwrite the + * range column below. Convert oldtuple to the base table's format + * if necessary. We need to insert temporal leftovers through the + * root partition so they get routed correctly. + */ + if (map != NULL) + { + leftoverSlot = execute_attr_map_slot(map->attrMap, + oldtupleSlot, + leftoverSlot); + } + else + { + oldtuple = ExecFetchSlotHeapTuple(oldtupleSlot, false, &shouldFree); + ExecForceStoreHeapTuple(oldtuple, leftoverSlot, false); + } + + /* + * Save some mtstate things so we can restore them below. XXX: + * Should we create our own ModifyTableState instead? + */ + oldOperation = mtstate->operation; + mtstate->operation = CMD_INSERT; + oldTcs = mtstate->mt_transition_capture; + + didInit = true; + } + + leftoverSlot->tts_values[forPortionOf->rangeVar->varattno - 1] = leftover; + leftoverSlot->tts_isnull[forPortionOf->rangeVar->varattno - 1] = false; + ExecMaterializeSlot(leftoverSlot); + + /* + * The standard says that each temporal leftover should execute its + * own INSERT statement, firing all statement and row triggers, but + * skipping insert permission checks. Therefore we give each insert + * its own transition table. If we just push & pop a new trigger level + * for each insert, we get exactly what we need. + * + * We have to make sure that the inserts don't add to the ROW_COUNT + * diagnostic or the command tag, so we pass false for canSetTag. + */ + AfterTriggerBeginQuery(); + ExecSetupTransitionCaptureState(mtstate, estate); + fireBSTriggers(mtstate); + ExecInsert(context, resultRelInfo, leftoverSlot, false, NULL, NULL); + fireASTriggers(mtstate); + AfterTriggerEndQuery(estate); + } + + if (didInit) + { + mtstate->operation = oldOperation; + mtstate->mt_transition_capture = oldTcs; + + if (shouldFree) + heap_freetuple(oldtuple); + } +} + /* ---------------------------------------------------------------- * ExecBatchInsert * @@ -1537,7 +1766,8 @@ ExecDeleteAct(ModifyTableContext *context, ResultRelInfo *resultRelInfo, * * Closing steps of tuple deletion; this invokes AFTER FOR EACH ROW triggers, * including the UPDATE triggers if the deletion is being done as part of a - * cross-partition tuple move. + * cross-partition tuple move. It also inserts temporal leftovers from a + * DELETE FOR PORTION OF. */ static void ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, @@ -1570,6 +1800,10 @@ ExecDeleteEpilogue(ModifyTableContext *context, ResultRelInfo *resultRelInfo, ar_delete_trig_tcs = NULL; } + /* Compute temporal leftovers in FOR PORTION OF */ + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, estate, resultRelInfo, tupleid); + /* AFTER ROW DELETE Triggers */ ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple, ar_delete_trig_tcs, changingPart); @@ -1995,7 +2229,10 @@ ExecCrossPartitionUpdate(ModifyTableContext *context, if (resultRelInfo == mtstate->rootResultRelInfo) ExecPartitionCheckEmitError(resultRelInfo, slot, estate); - /* Initialize tuple routing info if not already done. */ + /* + * Initialize tuple routing info if not already done. Note whatever we do + * here must be done in ExecInitModifyTable for FOR PORTION OF as well. + */ if (mtstate->mt_partition_tuple_routing == NULL) { Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; @@ -2344,7 +2581,8 @@ lreplace: * ExecUpdateEpilogue -- subroutine for ExecUpdate * * Closing steps of updating a tuple. Must be called if ExecUpdateAct - * returns indicating that the tuple was updated. + * returns indicating that the tuple was updated. It also inserts temporal + * leftovers from an UPDATE FOR PORTION OF. */ static void ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, @@ -2366,6 +2604,10 @@ ExecUpdateEpilogue(ModifyTableContext *context, UpdateContext *updateCxt, NULL); } + /* Compute temporal leftovers in FOR PORTION OF */ + if (((ModifyTable *) context->mtstate->ps.plan)->forPortionOf) + ExecForPortionOfLeftovers(context, context->estate, resultRelInfo, tupleid); + /* AFTER ROW UPDATE Triggers */ ExecARUpdateTriggers(context->estate, resultRelInfo, NULL, NULL, @@ -5298,6 +5540,108 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } } + /* + * If needed, initialize the target range for FOR PORTION OF. + */ + if (node->forPortionOf) + { + ResultRelInfo *rootRelInfo; + TupleDesc tupDesc; + ForPortionOfExpr *forPortionOf; + Datum targetRange; + bool isNull; + ExprContext *econtext; + ExprState *exprState; + ForPortionOfState *fpoState; + + rootRelInfo = mtstate->resultRelInfo; + if (rootRelInfo->ri_RootResultRelInfo) + rootRelInfo = rootRelInfo->ri_RootResultRelInfo; + + tupDesc = rootRelInfo->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); + + /* + * FOR PORTION OF ... TO ... FROM should never give us a NULL target, + * but FOR PORTION OF (...) could. + */ + if (isNull) + ereport(ERROR, + (errmsg("FOR PORTION OF target was null")), + executor_errposition(estate, forPortionOf->targetLocation)); + + /* 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(rootRelInfo->ri_RelationDesc, + &mtstate->ps.state->es_tupleTable); + + /* Create the tuple slot for INSERTing the temporal leftovers */ + + fpoState->fp_Leftover = + ExecInitExtraTupleSlot(mtstate->ps.state, tupDesc, &TTSOpsVirtual); + + rootRelInfo->ri_forPortionOf = fpoState; + + /* + * Make sure the root relation has the FOR PORTION OF clause too. Each + * partition needs its own TupleTableSlot, since they can have + * different descriptors, so they'll use the root fpoState to + * initialize one if necessary. + */ + if (node->rootRelation > 0) + mtstate->rootResultRelInfo->ri_forPortionOf = fpoState; + + if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE && + mtstate->mt_partition_tuple_routing == NULL) + { + /* + * We will need tuple routing to insert temporal leftovers. Since + * we are initializing things before ExecCrossPartitionUpdate + * runs, we must do everything it needs as well. + */ + Relation rootRel = mtstate->rootResultRelInfo->ri_RelationDesc; + MemoryContext oldcxt; + + /* Things built here have to last for the query duration. */ + oldcxt = MemoryContextSwitchTo(estate->es_query_cxt); + + mtstate->mt_partition_tuple_routing = + ExecSetupPartitionTupleRouting(estate, rootRel); + + /* + * Before a partition's tuple can be re-routed, it must first be + * converted to the root's format, so we'll need a slot for + * storing such tuples. + */ + Assert(mtstate->mt_root_tuple_slot == NULL); + mtstate->mt_root_tuple_slot = table_slot_create(rootRel, NULL); + + MemoryContextSwitchTo(oldcxt); + } + + /* + * Don't free the ExprContext here because the result must last for + * the whole query. + */ + } + /* * If we have any secondary relations in an UPDATE or DELETE, they need to * be treated like non-locked relations in SELECT FOR UPDATE, i.e., the diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 6a850349cf7..c0b880ec233 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -2579,6 +2579,20 @@ expression_tree_walker_impl(Node *node, return true; } break; + case T_ForPortionOfExpr: + { + ForPortionOfExpr *forPortionOf = (ForPortionOfExpr *) node; + + if (WALK(forPortionOf->targetFrom)) + return true; + if (WALK(forPortionOf->targetTo)) + return true; + if (WALK(forPortionOf->targetRange)) + return true; + if (WALK(forPortionOf->overlapsExpr)) + return true; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -2747,6 +2761,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)) @@ -3647,6 +3663,22 @@ 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->targetFrom, fpo->targetFrom, Node *); + MUTATE(newnode->targetTo, fpo->targetTo, Node *); + MUTATE(newnode->targetRange, fpo->targetRange, Node *); + MUTATE(newnode->overlapsExpr, fpo->overlapsExpr, Node *); + MUTATE(newnode->rangeTargetList, fpo->rangeTargetList, List *); + + return (Node *) newnode; + } + break; case T_PartitionPruneStepOp: { PartitionPruneStepOp *opstep = (PartitionPruneStepOp *) node; @@ -3828,6 +3860,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 50b0e10308b..c7bc41c30d7 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -315,7 +315,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); @@ -2676,6 +2676,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); @@ -7009,7 +7010,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, List *mergeActionLists, List *mergeJoinConditions, - int epqParam) + ForPortionOfExpr *forPortionOf, int epqParam) { ModifyTable *node = makeNode(ModifyTable); bool returning_old_or_new = false; @@ -7082,6 +7083,7 @@ make_modifytable(PlannerInfo *root, Plan *subplan, node->exclRelTlist = onconflict->exclRelTlist; } node->updateColnosLists = updateColnosLists; + node->forPortionOf = (Node *) forPortionOf; node->withCheckOptionLists = withCheckOptionLists; node->returningOldAlias = root->parse->returningOldAlias; node->returningNewAlias = root->parse->returningNewAlias; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 07944612668..4ec76ce31a9 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -2221,6 +2221,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 96cc72a776b..73518c8f870 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -3698,7 +3698,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); @@ -3764,6 +3764,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 ad31dee2686..84deed9aaa6 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -24,8 +24,11 @@ #include "postgres.h" +#include "access/stratnum.h" #include "access/sysattr.h" #include "catalog/dependency.h" +#include "catalog/pg_am.h" +#include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/defrem.h" @@ -51,7 +54,10 @@ #include "parser/parsetree.h" #include "utils/backend_status.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/guc.h" +#include "utils/lsyscache.h" +#include "utils/rangetypes.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -72,6 +78,10 @@ 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, + const ForPortionOfClause *forPortionOfClause, + bool isUpdate); static int count_rowexpr_columns(ParseState *pstate, Node *expr); static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt, SelectStmtPassthrough *passthru); @@ -604,6 +614,12 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) nsitem->p_lateral_only = false; nsitem->p_lateral_ok = true; + if (stmt->forPortionOf) + qry->forPortionOf = transformForPortionOfClause(pstate, + qry->resultRelation, + stmt->forPortionOf, + false); + qual = transformWhereClause(pstate, stmt->whereClause, EXPR_KIND_WHERE, "WHERE"); @@ -1247,7 +1263,7 @@ transformOnConflictClause(ParseState *pstate, /* Process the UPDATE SET clause */ if (onConflictClause->action == ONCONFLICT_UPDATE) onConflictSet = - transformUpdateTargetList(pstate, onConflictClause->targetList); + transformUpdateTargetList(pstate, onConflictClause->targetList, NULL); /* Process the SELECT/UPDATE WHERE clause */ onConflictWhere = transformWhereClause(pstate, @@ -1279,6 +1295,319 @@ 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, used later by the + * rewriter. + * - 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, + const ForPortionOfClause *forPortionOf, + bool isUpdate) +{ + Relation targetrel = pstate->p_target_relation; + int range_attno = InvalidAttrNumber; + Form_pg_attribute attr; + Oid attbasetype; + Oid opclass; + Oid opfamily; + Oid opcintype; + Oid funcid = InvalidOid; + StrategyNumber strat; + Oid opid; + OpExpr *op; + ForPortionOfExpr *result; + Var *rangeVar; + + /* 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, forPortionOf->range_name, false); + if (range_attno == InvalidAttrNumber) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + forPortionOf->range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + attr = TupleDescAttr(targetrel->rd_att, range_attno - 1); + + attbasetype = getBaseType(attr->atttypid); + + rangeVar = makeVar(rtindex, + range_attno, + attr->atttypid, + attr->atttypmod, + attr->attcollation, + 0); + rangeVar->location = forPortionOf->location; + result->rangeVar = rangeVar; + + /* Require SELECT privilege on the application-time column. */ + markVarForSelectPriv(pstate, rangeVar); + + /* + * Use the basetype for the target, which shouldn't be required to follow + * domain rules. The table's column type is in the Var if we need it. + */ + result->rangeType = attbasetype; + result->isDomain = attbasetype != attr->atttypid; + + if (forPortionOf->target) + { + Oid declared_target_type = attbasetype; + Oid actual_target_type; + + /* + * We were already given an expression for the target, so we don't + * have to build anything. We still have to make sure we got the right + * type. NULL will be caught be the executor. + */ + + result->targetRange = transformExpr(pstate, + forPortionOf->target, + EXPR_KIND_FOR_PORTION); + + actual_target_type = exprType(result->targetRange); + + if (!can_coerce_type(1, &actual_target_type, &declared_target_type, COERCION_IMPLICIT)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("could not coerce FOR PORTION OF target from %s to %s", + format_type_be(actual_target_type), + format_type_be(declared_target_type)), + parser_errposition(pstate, exprLocation(forPortionOf->target)))); + + result->targetRange = coerce_type(pstate, + result->targetRange, + actual_target_type, + declared_target_type, + -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + exprLocation(forPortionOf->target)); + + /* + * XXX: For now we only support ranges and multiranges, so we fail on + * anything else. + */ + if (!type_is_range(attbasetype) && !type_is_multirange(attbasetype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" of relation \"%s\" is not a range or multirange type", + forPortionOf->range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + + } + else + { + Oid rngsubtype; + Oid declared_arg_types[2]; + Oid actual_arg_types[2]; + List *args; + + /* + * Make sure it's a range column. XXX: We could support this syntax on + * multirange columns too, if we just built a one-range multirange + * from the FROM/TO phrases. + */ + if (!type_is_range(attbasetype)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_COLUMN_REFERENCE), + errmsg("column \"%s\" of relation \"%s\" is not a range type", + forPortionOf->range_name, + RelationGetRelationName(targetrel)), + parser_errposition(pstate, forPortionOf->location))); + + rngsubtype = get_range_subtype(attbasetype); + declared_arg_types[0] = rngsubtype; + declared_arg_types[1] = rngsubtype; + + /* + * 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. + */ + result->targetFrom = transformExpr(pstate, + forPortionOf->target_start, + EXPR_KIND_FOR_PORTION); + result->targetTo = transformExpr(pstate, + forPortionOf->target_end, + EXPR_KIND_FOR_PORTION); + actual_arg_types[0] = exprType(result->targetFrom); + actual_arg_types[1] = exprType(result->targetTo); + args = list_make2(copyObject(result->targetFrom), + copyObject(result->targetTo)); + + /* + * Check the bound types separately, for better error message and + * location + */ + if (!can_coerce_type(1, actual_arg_types, declared_arg_types, COERCION_IMPLICIT)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("could not coerce FOR PORTION OF %s bound from %s to %s", + "FROM", + format_type_be(actual_arg_types[0]), + format_type_be(declared_arg_types[0])), + parser_errposition(pstate, exprLocation(forPortionOf->target_start)))); + if (!can_coerce_type(1, &actual_arg_types[1], &declared_arg_types[1], COERCION_IMPLICIT)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("could not coerce FOR PORTION OF %s bound from %s to %s", + "TO", + format_type_be(actual_arg_types[1]), + format_type_be(declared_arg_types[1])), + parser_errposition(pstate, exprLocation(forPortionOf->target_end)))); + + make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types); + result->targetRange = (Node *) makeFuncExpr(get_range_constructor2(attbasetype), + attbasetype, + args, + InvalidOid, InvalidOid, COERCE_EXPLICIT_CALL); + } + if (contain_volatile_functions_after_planning((Expr *) result->targetRange)) + ereport(ERROR, + (errmsg("FOR PORTION OF bounds cannot contain volatile functions"))); + + /* + * Build overlapsExpr to use as an extra qual. 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); + if (!OidIsValid(opclass)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("data type %s has no default operator class for access method \"%s\"", + format_type_be(attr->atttypid), "gist"), + errhint("You must define a default operator class for the data type."))); + + /* Look up the operators and functions we need. */ + GetOperatorFromCompareType(opclass, InvalidOid, COMPARE_OVERLAP, &opid, &strat); + op = makeNode(OpExpr); + op->opno = opid; + op->opfuncid = get_opcode(opid); + op->opresulttype = BOOLOID; + op->args = list_make2(copyObject(rangeVar), copyObject(result->targetRange)); + result->overlapsExpr = (Node *) op; + + /* + * Look up the without_portion func. This computes the bounds of temporal + * leftovers. + * + * XXX: Find a more extensible way to look up the function, permitting + * user-defined types. An opclass support function doesn't make sense, + * since there is no index involved. Perhaps a type support function. + */ + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + switch (opcintype) + { + case ANYRANGEOID: + result->withoutPortionProc = F_RANGE_MINUS_MULTI; + break; + case ANYMULTIRANGEOID: + result->withoutPortionProc = F_MULTIRANGE_MINUS_MULTI; + break; + default: + elog(ERROR, "unexpected opcintype: %u", opcintype); + } + else + elog(ERROR, "unexpected opclass: %u", opclass); + + if (isUpdate) + { + /* + * Now make sure we update the start/end time of the record. For a + * range col (r) this is `r = r * targetRange` (where * is the + * intersect operator). + */ + Oid intersectoperoid; + List *funcArgs; + Node *rangeTLEExpr; + TargetEntry *tle; + + /* + * Whatever operator is used for intersect by temporal foreign keys, + * we can use its backing procedure for intersects in FOR PORTION OF. + * XXX: Share code with FindFKPeriodOpers? + */ + switch (opcintype) + { + case ANYRANGEOID: + intersectoperoid = OID_RANGE_INTERSECT_RANGE_OP; + break; + case ANYMULTIRANGEOID: + intersectoperoid = OID_MULTIRANGE_INTERSECT_MULTIRANGE_OP; + break; + default: + elog(ERROR, "unexpected opcintype: %u", opcintype); + } + funcid = get_opcode(intersectoperoid); + if (!OidIsValid(funcid)) + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not identify an intersect function for type %s", + format_type_be(opcintype))); + + funcArgs = list_make2(copyObject(rangeVar), + copyObject(result->targetRange)); + rangeTLEExpr = (Node *) makeFuncExpr(funcid, attbasetype, funcArgs, + InvalidOid, InvalidOid, + COERCE_EXPLICIT_CALL); + + /* + * Coerce to domain if necessary. If we skip this, we will allow + * updating to forbidden values. + */ + rangeTLEExpr = coerce_type(pstate, + rangeTLEExpr, + attbasetype, + attr->atttypid, + -1, + COERCION_IMPLICIT, + COERCE_IMPLICIT_CAST, + exprLocation(forPortionOf->target)); + + /* Make a TLE to set the range column */ + result->rangeTargetList = NIL; + tle = makeTargetEntry((Expr *) rangeTLEExpr, range_attno, + forPortionOf->range_name, false); + result->rangeTargetList = lappend(result->rangeTargetList, tle); + + /* + * The range column will change, but you don't need UPDATE permission + * on it, so we don't add to updatedCols here. XXX: If + * https://www.postgresql.org/message-id/CACJufxEtY1hdLcx%3DFhnqp-ERcV1PhbvELG5COy_CZjoEW76ZPQ%40mail.gmail.com + * is merged (only validate CHECK constraints if they depend on one of + * the columns being UPDATEd), we need to make sure that code knows + * that we are updating the application-time column. + */ + } + else + result->rangeTargetList = NIL; + + result->range_name = forPortionOf->range_name; + result->location = forPortionOf->location; + result->targetLocation = forPortionOf->target_location; + + return result; +} /* * BuildOnConflictExcludedTargetlist @@ -2538,6 +2867,13 @@ 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 */ @@ -2564,7 +2900,8 @@ 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; @@ -2583,7 +2920,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; @@ -2636,6 +2973,20 @@ transformUpdateTargetList(ParseState *pstate, List *origTlist) errhint("SET target columns cannot be qualified with the relation name.") : 0, parser_errposition(pstate, origTarget->location))); + /* + * If this is a FOR PORTION OF update, forbid directly setting the + * range column, since that would conflict with the implicit updates. + */ + if (forPortionOf != NULL) + { + if (attrno == forPortionOf->rangeVar->varattno) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("cannot update column \"%s\" because it is used in FOR PORTION OF", + 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 9e86596dc05..f4a08baa95a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -559,6 +559,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type relation_expr %type extended_relation_expr %type relation_expr_opt_alias +%type for_portion_of_opt_alias +%type for_portion_of_clause %type tablesample_clause opt_repeatable_clause %type target_el set_target insert_column_item @@ -800,7 +802,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARAMETER PARSER PARTIAL PARTITION PARTITIONS PASSING PASSWORD PATH - PERIOD PLACING PLAN PLANS POLICY + PERIOD PLACING PLAN PLANS POLICY PORTION POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PROPERTIES PROPERTY PUBLICATION @@ -919,12 +921,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 RIGHT_ARROW '|' /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -13200,6 +13205,21 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias n->withClause = $1; $$ = (Node *) n; } + | opt_with_clause DELETE_P FROM relation_expr + for_portion_of_clause for_portion_of_opt_alias + using_clause where_or_current_clause returning_clause + { + DeleteStmt *n = makeNode(DeleteStmt); + + n->relation = $4; + n->forPortionOf = (ForPortionOfClause *) $5; + n->relation->alias = $6; + n->usingClause = $7; + n->whereClause = $8; + n->returningClause = $9; + n->withClause = $1; + $$ = (Node *) n; + } ; using_clause: @@ -13274,6 +13294,25 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias n->withClause = $1; $$ = (Node *) n; } + | opt_with_clause UPDATE relation_expr + for_portion_of_clause for_portion_of_opt_alias + SET set_clause_list + from_clause + where_or_current_clause + returning_clause + { + UpdateStmt *n = makeNode(UpdateStmt); + + n->relation = $3; + n->forPortionOf = (ForPortionOfClause *) $4; + n->relation->alias = $5; + n->targetList = $7; + n->fromClause = $8; + n->whereClause = $9; + n->returningClause = $10; + n->withClause = $1; + $$ = (Node *) n; + } ; set_clause_list: @@ -14787,6 +14826,55 @@ relation_expr_opt_alias: relation_expr %prec UMINUS } ; +/* + * If an UPDATE/DELETE has FOR PORTION OF, then the relation_expr is separated + * from its potential alias by the for_portion_of_clause. So this production + * handles the potential alias in those cases. We need to solve the same + * problems as relation_expr_opt_alias, in particular resolving a shift/reduce + * conflict where "set set" could be an alias plus the SET keyword, or the SET + * keyword then a column name. As above, we force the latter interpretation by + * giving the non-alias choice a higher precedence. + */ +for_portion_of_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->target_location = @6; + $$ = (Node *) 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->target_location = @5; + $$ = (Node *) n; + } + ; + /* * TABLESAMPLE decoration in a FROM item */ @@ -15627,16 +15715,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; } @@ -18934,6 +19031,7 @@ unreserved_keyword: | PLAN | PLANS | POLICY + | PORTION | PRECEDING | PREPARE | PREPARED @@ -19578,6 +19676,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 6076e9373c1..acb933392de 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -584,6 +584,13 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_FOR_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; case EXPR_KIND_PROPGRAPH_PROPERTY: if (isAgg) @@ -1035,6 +1042,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_PROPGRAPH_PROPERTY: err = _("window functions are not allowed in property definition expressions"); break; + case EXPR_KIND_FOR_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 de4b20cd6af..022b8cac122 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 d5fc9e7d6bc..f535f3b9351 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -588,6 +588,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_PARTITION_BOUND: err = _("cannot use column reference in partition bound expression"); break; + case EXPR_KIND_FOR_PORTION: + err = _("cannot use column reference in FOR PORTION OF expression"); + break; /* * There is intentionally no default: case here, so that the @@ -1886,6 +1889,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_PROPGRAPH_PROPERTY: err = _("cannot use subquery in property definition expression"); break; + case EXPR_KIND_FOR_PORTION: + err = _("cannot use subquery in FOR PORTION OF expression"); + break; /* * There is intentionally no default: case here, so that the @@ -3247,6 +3253,8 @@ ParseExprKindName(ParseExprKind exprKind) return "CYCLE"; case EXPR_KIND_PROPGRAPH_PROPERTY: return "property definition expression"; + case EXPR_KIND_FOR_PORTION: + return "FOR PORTION OF"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 8dbd41a3548..35ff6427147 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2786,6 +2786,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_PROPGRAPH_PROPERTY: err = _("set-returning functions are not allowed in property definition expressions"); break; + case EXPR_KIND_FOR_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 0a70d48fd4c..2e6dd166c98 100644 --- a/src/backend/parser/parse_merge.c +++ b/src/backend/parser/parse_merge.c @@ -381,7 +381,7 @@ transformMergeStmt(ParseState *pstate, MergeStmt *stmt) case CMD_UPDATE: action->targetList = transformUpdateTargetList(pstate, - mergeWhenClause->targetList); + mergeWhenClause->targetList, NULL); break; case CMD_DELETE: break; diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index e33fd81d735..021c73f1b67 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -3757,6 +3757,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, @@ -4113,6 +4137,37 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length, else if (event == CMD_UPDATE) { Assert(parsetree->override == OVERRIDING_NOT_SET); + + if (parsetree->forPortionOf) + { + /* + * Don't add FOR PORTION OF details until we're done rewriting + * a view update, so that we don't add the same qual and TLE + * on the recursion. + * + * Views don't need to do anything special here to remap Vars; + * that is handled by the tree walker. + */ + if (rt_entry_relation->rd_rel->relkind != RELKIND_VIEW) + { + ListCell *tl; + + /* + * Add qual: UPDATE FOR PORTION OF should be limited to + * rows that overlap the target range. + */ + AddQual(parsetree, parsetree->forPortionOf->overlapsExpr); + + /* Update FOR PORTION OF column(s) automatically. */ + foreach(tl, parsetree->forPortionOf->rangeTargetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(tl); + + parsetree->targetList = lappend(parsetree->targetList, tle); + } + } + } + parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, @@ -4158,7 +4213,25 @@ RewriteQuery(Query *parsetree, List *rewrite_events, int orig_rt_length, } else if (event == CMD_DELETE) { - /* Nothing to do here */ + if (parsetree->forPortionOf) + { + /* + * Don't add FOR PORTION OF details until we're done rewriting + * a view delete, so that we don't add the same qual on the + * recursion. + * + * Views don't need to do anything special here to remap Vars; + * that is handled by the tree walker. + */ + if (rt_entry_relation->rd_rel->relkind != RELKIND_VIEW) + { + /* + * Add qual: DELETE FOR PORTION OF should be limited to + * rows that overlap the target range. + */ + AddQual(parsetree, parsetree->forPortionOf->overlapsExpr); + } + } } else elog(ERROR, "unrecognized commandType: %d", (int) event); diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index e5f2b6082ce..aec5556b008 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -524,6 +524,8 @@ static void get_rte_alias(RangeTblEntry *rte, int varno, bool use_as, deparse_context *context); static void get_column_alias_list(deparse_columns *colinfo, deparse_context *context); +static void get_for_portion_of(ForPortionOfExpr *forPortionOf, + deparse_context *context); static void get_from_clause_coldeflist(RangeTblFunction *rtfunc, deparse_columns *colinfo, deparse_context *context); @@ -7553,6 +7555,9 @@ get_update_query_def(Query *query, deparse_context *context) only_marker(rte), generate_relation_name(rte->relid, NIL)); + /* Print the FOR PORTION OF, if needed */ + get_for_portion_of(query->forPortionOf, context); + /* Print the relation alias, if needed */ get_rte_alias(rte, query->resultRelation, false, context); @@ -7757,6 +7762,9 @@ get_delete_query_def(Query *query, deparse_context *context) only_marker(rte), generate_relation_name(rte->relid, NIL)); + /* Print the FOR PORTION OF, if needed */ + get_for_portion_of(query->forPortionOf, context); + /* Print the relation alias, if needed */ get_rte_alias(rte, query->resultRelation, false, context); @@ -13330,6 +13338,39 @@ get_rte_alias(RangeTblEntry *rte, int varno, bool use_as, quote_identifier(refname)); } +/* + * get_for_portion_of - print FOR PORTION OF if needed + * XXX: Newlines would help here, at least when pretty-printing. But then the + * alias and SET will be on their own line with a leading space. + */ +static void +get_for_portion_of(ForPortionOfExpr *forPortionOf, deparse_context *context) +{ + if (forPortionOf) + { + appendStringInfo(context->buf, " FOR PORTION OF %s", + quote_identifier(forPortionOf->range_name)); + + /* + * Try to write it as FROM ... TO ... if we received it that way, + * otherwise (targetExpr). + */ + if (forPortionOf->targetFrom && forPortionOf->targetTo) + { + appendStringInfoString(context->buf, " FROM "); + get_rule_expr(forPortionOf->targetFrom, context, false); + appendStringInfoString(context->buf, " TO "); + get_rule_expr(forPortionOf->targetTo, context, false); + } + else + { + appendStringInfoString(context->buf, " ("); + get_rule_expr(forPortionOf->targetRange, context, false); + appendStringInfoString(context->buf, ")"); + } + } +} + /* * get_column_alias_list - print column alias list for an RTE * diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 768b11e3b82..9b2b97f5cba 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -3670,6 +3670,31 @@ get_range_collation(Oid rangeOid) return InvalidOid; } +/* + * get_range_constructor2 + * Gets the 2-arg constructor for the given rangetype. + * + * Raises an error if not found. + */ +RegProcedure +get_range_constructor2(Oid rangeOid) +{ + HeapTuple tp; + + tp = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(rangeOid)); + if (HeapTupleIsValid(tp)) + { + Form_pg_range rngtup = (Form_pg_range) GETSTRUCT(tp); + RegProcedure result; + + result = rngtup->rngconstruct2; + ReleaseSysCache(tp); + return result; + } + else + elog(ERROR, "cache lookup failed for range type %u", rangeOid); +} + /* * get_range_multirange * Returns the multirange type of a given range type diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 684e398f824..090cfccf65f 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -39,6 +39,7 @@ #include "partitioning/partdefs.h" #include "storage/buf.h" #include "utils/reltrigger.h" +#include "utils/typcache.h" /* @@ -464,6 +465,24 @@ typedef struct MergeActionState ExprState *mas_whenqual; /* WHEN [NOT] MATCHED AND conditions */ } MergeActionState; +/* + * ForPortionOfState + * + * Executor state of a FOR PORTION OF operation. + */ +typedef struct ForPortionOfState +{ + NodeTag type; + + char *fp_rangeName; /* the column named in FOR PORTION OF */ + Oid fp_rangeType; /* the type of the FOR PORTION OF expression */ + int fp_rangeAttno; /* the attno of the range column */ + Datum fp_targetRange; /* the range/multirange from FOR PORTION OF */ + TypeCacheEntry *fp_leftoverstypcache; /* type cache entry of the range */ + TupleTableSlot *fp_Existing; /* slot to store old tuple */ + TupleTableSlot *fp_Leftover; /* slot to store leftover */ +} ForPortionOfState; + /* * ResultRelInfo * @@ -600,6 +619,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 a501cdfb249..91377a6cde3 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -147,6 +147,9 @@ typedef struct Query */ int resultRelation pg_node_attr(query_jumble_ignore); + /* FOR PORTION OF clause for UPDATE/DELETE */ + ForPortionOfExpr *forPortionOf; + /* has aggregates in tlist or havingQual */ bool hasAggs pg_node_attr(query_jumble_ignore); /* has window functions in tlist */ @@ -1697,6 +1700,22 @@ 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; /* column name of the range/multirange */ + ParseLoc location; /* token location, or -1 if unknown */ + ParseLoc target_location; /* token location, or -1 if unknown */ + Node *target; /* Expr from FOR PORTION OF col (...) syntax */ + Node *target_start; /* Expr from FROM ... TO ... syntax */ + Node *target_end; /* Expr from FROM ... TO ... syntax */ +} ForPortionOfClause; + /* * WithClause - * representation of WITH clause @@ -2211,6 +2230,7 @@ typedef struct DeleteStmt Node *whereClause; /* qualifications */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } DeleteStmt; /* ---------------------- @@ -2226,6 +2246,7 @@ typedef struct UpdateStmt List *fromClause; /* optional from clause for more tables */ ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ + ForPortionOfClause *forPortionOf; /* FOR PORTION OF clause */ } UpdateStmt; /* ---------------------- diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 7947d83d584..693b879f76d 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -2721,6 +2721,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 e2c00576d41..14a1dfed2b9 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -376,6 +376,8 @@ typedef struct ModifyTable List *onConflictCols; /* WHERE for ON CONFLICT DO SELECT/UPDATE */ Node *onConflictWhere; + /* FOR PORTION OF clause for UPDATE/DELETE */ + Node *forPortionOf; /* RTI of the EXCLUDED pseudo relation */ Index exclRelRTI; /* tlist of the EXCLUDED pseudo relation */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index f5b6b45664a..6dfc946c20b 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -2417,4 +2417,39 @@ 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. + * + * If the user used the FROM ... TO ... syntax, we save the individual + * expressions so that we can deparse them. + * + * 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 *targetFrom; /* FOR PORTION OF FROM bound, if given */ + Node *targetTo; /* FOR PORTION OF TO bound, if given */ + Node *targetRange; /* FOR PORTION OF bounds as a range/multirange */ + Oid rangeType; /* (base)type of targetRange */ + bool isDomain; /* Is rangeVar a domain? */ + Node *overlapsExpr; /* range && targetRange */ + List *rangeTargetList; /* List of TargetEntrys to set the time + * column(s) */ + Oid withoutPortionProc; /* SRF proc for old_range - target_range */ + ParseLoc location; /* token location, or -1 if unknown */ + ParseLoc targetLocation; /* token location, or -1 if unknown */ +} ForPortionOfExpr; + #endif /* PRIMNODES_H */ diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index da2d9b384b5..e8db321f92b 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -319,7 +319,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 e10270ff0ff..92c1c502945 100644 --- a/src/include/parser/analyze.h +++ b/src/include/parser/analyze.h @@ -43,7 +43,8 @@ extern List *transformInsertRow(ParseState *pstate, List *exprlist, List *stmtcols, List *icolumns, List *attrnos, bool strip_indirection); extern List *transformUpdateTargetList(ParseState *pstate, - List *origTlist); + List *origTlist, + ForPortionOfExpr *forPortionOf); extern void transformReturningClause(ParseState *pstate, Query *qry, ReturningClause *returningClause, ParseExprKind exprKind); diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index b7ded6e6088..51ead54f015 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -353,6 +353,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 1a7da399f1b..f7f4ba6c2a8 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_FOR_PORTION, /* UPDATE/DELETE 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 71b1a8f277d..e57795fa01f 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -202,6 +202,7 @@ extern char *get_namespace_name(Oid nspid); extern char *get_namespace_name_or_temp(Oid nspid); extern Oid get_range_subtype(Oid rangeOid); extern Oid get_range_collation(Oid rangeOid); +extern Oid get_range_constructor2(Oid rangeOid); extern Oid get_range_multirange(Oid rangeOid); extern Oid get_multirange_range(Oid multirangeOid); extern Oid get_index_column_opclass(Oid index_oid, int attno); 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..31f772c723d --- /dev/null +++ b/src/test/regress/expected/for_portion_of.out @@ -0,0 +1,2100 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF +SET datestyle TO ISO, YMD; +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2020-01-01)', 'one'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = 'one^1'; +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,2020-01-01) | one +(3 rows) + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; +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,2020-01-01) | one +(4 rows) + +-- With a table alias with AS +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t + SET name = 'one^2'; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; +-- With a table alias without AS +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t + SET name = 'one^3'; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; +-- UPDATE with FROM +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' + SET name = 'one^4' + FROM (SELECT '[1,2)'::int4range) AS t2(id) + WHERE for_portion_of_test.id = t2.id; +-- DELETE with USING +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' + USING (SELECT '[1,2)'::int4range) AS t2(id) + WHERE for_portion_of_test.id = t2.id; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-01-15) | one + [1,2) | [2018-01-15,2019-01-01) | one^1 + [1,2) | [2019-01-01,2019-01-15) | one + [1,2) | [2019-01-20,2019-02-01) | one + [1,2) | [2019-02-01,2019-02-03) | one^2 + [1,2) | [2019-02-04,2019-02-05) | one^3 + [1,2) | [2019-02-06,2019-03-01) | one + [1,2) | [2019-03-01,2019-03-02) | one^4 + [1,2) | [2019-03-03,2020-01-01) | one +(9 rows) + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test (id, valid1_at, valid2_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); +UPDATE for_portion_of_test + FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL + SET name = 'foo'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2025-01-01) | one + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2025-01-01) | foo +(2 rows) + +UPDATE for_portion_of_test + FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL + SET name = 'bar'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-02-03) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-02-03) | [2018-01-15,2025-01-01) | bar +(4 rows) + +DELETE FROM for_portion_of_test + FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2025-01-01) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2025-01-01) | bar +(4 rows) + +DELETE FROM for_portion_of_test + FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + id | valid1_at | valid2_at | name +-------+-------------------------+-------------------------+------ + [1,2) | [2018-01-02,2018-01-15) | [2015-01-01,2018-01-15) | one + [1,2) | [2018-01-02,2018-01-15) | [2018-01-15,2018-01-20) | bar + [1,2) | [2018-01-15,2018-01-20) | [2015-01-01,2018-01-15) | foo + [1,2) | [2018-01-15,2018-01-20) | [2018-01-15,2018-01-20) | bar +(4 rows) + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, daterange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------------- + [1,2) | empty | 1 empty + [1,2) | (,) | NULL to NULL + [1,2) | | 1 null + | [2018-01-01,2019-01-01) | NULL to NULL + | | +(5 rows) + +DROP TABLE for_portion_of_test; +-- +-- UPDATE tests +-- +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-01-05)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'), + ('[4,5)', '(,2018-04-01)', 'four'), + ('[5,6)', '(,)', 'five') + ; +\set QUIET false +-- Updating with a missing column fails +UPDATE for_portion_of_test + FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + SET name = 'foo' + WHERE id = '[5,6)'; +ERROR: column "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- Updating the range fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + SET valid_at = '[1990-01-01,1999-01-01)' + WHERE id = '[5,6)'; +ERROR: cannot update column "valid_at" because it is used in FOR PORTION OF +LINE 3: SET valid_at = '[1990-01-01,1999-01-01)' + ^ +-- The wrong start type fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + SET name = 'nope' + WHERE id = '[3,4)'; +ERROR: could not coerce FOR PORTION OF FROM bound from integer to date +LINE 2: FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + ^ +-- The wrong end type fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + SET name = 'nope' + WHERE id = '[3,4)'; +ERROR: could not coerce FOR PORTION OF TO bound from integer to date +LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + ^ +-- Updating with timestamps reversed fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' + SET name = 'three^1' + WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Updating with a subquery fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + SET name = 'nope' + WHERE id = '[3,4)'; +ERROR: cannot use subquery in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '201... + ^ +-- Updating with a column fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + SET name = 'nope' + WHERE id = '[3,4)'; +ERROR: cannot use column reference in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + ^ +-- Updating with timestamps equal does nothing +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' + SET name = 'three^0' + WHERE id = '[3,4)'; +UPDATE 0 +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + SET name = 'three^1' + WHERE id = '[3,4)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [3,4) | [2018-01-01,2018-06-01) | three + [3,4) | [2018-06-01,) | three^1 +(2 rows) + +-- Updating a finite/open portion with an open/finite target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-03-01' + SET name = 'three^2' + WHERE id = '[3,4)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [3,4) | [2018-01-01,2018-03-01) | three^2 + [3,4) | [2018-03-01,2018-06-01) | three + [3,4) | [2018-06-01,) | three^1 +(3 rows) + +-- Updating an open/finite portion with an open/finite target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-02-01' + SET name = 'four^1' + WHERE id = '[4,5)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [4,5) | (,2018-02-01) | four^1 + [4,5) | [2018-02-01,2018-04-01) | four +(2 rows) + +-- Updating an open/finite portion with a finite/open target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO NULL + SET name = 'four^2' + WHERE id = '[4,5)'; +UPDATE 2 +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [4,5) | (,2017-01-01) | four^1 + [4,5) | [2017-01-01,2018-02-01) | four^2 + [4,5) | [2018-02-01,2018-04-01) | four^2 +(3 rows) + +-- Updating a finite/finite portion with an exact fit +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO '2018-02-01' + SET name = 'four^3' + WHERE id = '[4,5)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [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 +(3 rows) + +-- Updating an enclosed span +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'two^2' + WHERE id = '[2,3)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [2,3) | [2018-01-01,2018-01-05) | two^2 +(1 row) + +-- Updating an open/open portion with a finite/finite target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' + SET name = 'five^1' + WHERE id = '[5,6)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [5,6) | (,2018-01-01) | five + [5,6) | [2018-01-01,2019-01-01) | five^1 + [5,6) | [2019-01-01,) | five +(3 rows) + +-- Updating an enclosed span with separate protruding spans +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO '2020-01-01' + SET name = 'five^2' + WHERE id = '[5,6)'; +UPDATE 3 +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [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,) | five +(5 rows) + +-- Updating multiple enclosed spans +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'one^2' + WHERE id = '[1,2)'; +UPDATE 3 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' 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-03-03) | one^2 + [1,2) | [2018-03-03,2018-04-04) | one^2 +(3 rows) + +-- Updating with a direct target +UPDATE for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15')) + SET name = 'one^3' + WHERE id = '[1,2)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' 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-03-03) | one^2 + [1,2) | [2018-03-03,2018-03-10) | one^2 + [1,2) | [2018-03-10,2018-03-15) | one^3 + [1,2) | [2018-03-15,2018-04-04) | one^2 +(5 rows) + +-- Updating with a direct target, coerced from a string +UPDATE for_portion_of_test + FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)') + SET name = 'one^3' + WHERE id = '[1,2)'; +UPDATE 1 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' 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-03-03) | one^2 + [1,2) | [2018-03-03,2018-03-10) | one^2 + [1,2) | [2018-03-10,2018-03-15) | one^3 + [1,2) | [2018-03-15,2018-03-17) | one^3 + [1,2) | [2018-03-17,2018-04-04) | one^2 +(6 rows) + +-- Updating with a direct target of the wrong range subtype fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (int4range(1, 4)) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from int4range to daterange +LINE 2: FOR PORTION OF valid_at (int4range(1, 4)) + ^ +-- Updating with a direct target of a non-rangetype fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (4) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from integer to daterange +LINE 2: FOR PORTION OF valid_at (4) + ^ +-- Updating with a direct target of NULL fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (NULL) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: FOR PORTION OF target was null +LINE 2: FOR PORTION OF valid_at (NULL) + ^ +-- Updating with a direct target of empty does nothing +UPDATE for_portion_of_test + FOR PORTION OF valid_at ('empty') + SET name = 'one^3' + WHERE id = '[1,2)'; +UPDATE 0 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' 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-03-03) | one^2 + [1,2) | [2018-03-03,2018-03-10) | one^2 + [1,2) | [2018-03-10,2018-03-15) | one^3 + [1,2) | [2018-03-15,2018-03-17) | one^3 + [1,2) | [2018-03-17,2018-04-04) | one^2 +(6 rows) + +-- Updating the non-range part of the PK: +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-15' TO NULL + SET id = '[6,7)' + WHERE id = '[1,2)'; +UPDATE 5 +SELECT * FROM for_portion_of_test WHERE id IN ('[1,2)', '[6,7)') 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 + [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-15) | one^3 + [6,7) | [2018-03-15,2018-03-17) | one^3 + [6,7) | [2018-03-17,2018-04-04) | one^2 +(7 rows) + +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL + SET name = name || '*'; +UPDATE 2 +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-15) | one^3 + [6,7) | [2018-03-15,2018-03-17) | one^3 + [6,7) | [2018-03-17,2018-04-04) | one^2 +(21 rows) + +\set QUIET true +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' + SET name = 'one^2' + WHERE id = '[1,2)'; +ERROR: syntax error at or near "'2014-01-01'" +LINE 4: TO '2014-01-01' + ^ +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+-----------------------------------------------+------- + [1,2) | ["2000-01-01 00:00:00","2011-03-01 01:02:00") | one + [1,2) | ["2011-03-01 01:02:00","2012-01-01 00:00:00") | one^1 + [1,2) | ["2012-01-01 00:00:00","2015-03-01 01:00:00") | one + [1,2) | ["2015-03-01 01:00:00","2016-01-01 00:00:00") | one^3 + [1,2) | ["2016-01-01 00:00:00","2020-01-01 00:00:00") | one +(5 rows) + +DROP TABLE for_portion_of_test2; +-- UPDATE FOR PORTION OF in a CTE: +-- The outer query sees the table how it was before the updates, +-- and with no leftovers yet, +-- but it also sees the new values via the RETURNING clause. +-- (We test RETURNING more directly, without a CTE, below.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[10,11)' + RETURNING id, valid_at, name +) +SELECT * + FROM for_portion_of_test AS t, update_apr + WHERE t.id = update_apr.id; + id | valid_at | name | id | valid_at | name +---------+-------------------------+------+---------+-------------------------+---------- + [10,11) | [2018-01-01,2020-01-01) | ten | [10,11) | [2018-04-01,2018-05-01) | Apr 2018 +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)' ORDER BY id, valid_at; + id | valid_at | name +---------+-------------------------+---------- + [10,11) | [2018-01-01,2018-04-01) | ten + [10,11) | [2018-04-01,2018-05-01) | Apr 2018 + [10,11) | [2018-05-01,2020-01-01) | ten +(3 rows) + +-- UPDATE FOR PORTION OF with current_date +-- (We take care not to make the expectation depend on the timestamp.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[99,100)', '[2000-01-01,)', 'foo'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM current_date TO null + SET name = 'bar' + WHERE id = '[99,100)'; +SELECT name, lower(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date - 1; + name | lower +------+------------ + foo | 2000-01-01 +(1 row) + +SELECT name, upper(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date + 1; + name | upper +------+------- + bar | +(1 row) + +-- UPDATE FOR PORTION OF with clock_timestamp() +-- fails because the function is volatile: +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM clock_timestamp()::date TO null + SET name = 'baz' + WHERE id = '[99,100)'; +ERROR: FOR PORTION OF bounds cannot contain volatile functions +-- clean up: +DELETE FROM for_portion_of_test WHERE id = '[99,100)'; +-- Not visible to UPDATE: +-- Tuples updated/inserted within the CTE are not visible to the main query yet, +-- but neither are old tuples the CTE changed: +-- (This is the same behavior as without FOR PORTION OF.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[11,12)' + RETURNING id, valid_at, name +) +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + AS t + SET name = 'May 2018' + FROM update_apr AS j + WHERE t.id = j.id; +SELECT * FROM for_portion_of_test WHERE id = '[11,12)' ORDER BY id, valid_at; + id | valid_at | name +---------+-------------------------+---------- + [11,12) | [2018-01-01,2018-04-01) | eleven + [11,12) | [2018-04-01,2018-05-01) | Apr 2018 + [11,12) | [2018-05-01,2020-01-01) | eleven +(3 rows) + +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)'); +-- UPDATE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + SET name = concat(_target_from::text, ' to ', _target_til::text) + WHERE id = $1; +END; +$$; +SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01'); + fpo_update +------------ + +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + id | valid_at | name +---------+-------------------------+-------------------------- + [10,11) | [2018-01-01,2019-01-01) | 2015-01-01 to 2019-01-01 + [10,11) | [2019-01-01,2020-01-01) | ten +(2 rows) + +-- UPDATE FOR PORTION OF in a compiled SQL function +CREATE FUNCTION fpo_update() +RETURNS text +BEGIN ATOMIC + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = 'one^1' + RETURNING name; +END; +\sf+ fpo_update() + CREATE OR REPLACE FUNCTION public.fpo_update() + RETURNS text + LANGUAGE sql +1 BEGIN ATOMIC +2 UPDATE for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' SET name = 'one^1'::text +3 RETURNING for_portion_of_test.name; +4 END +CREATE OR REPLACE function fpo_update() +RETURNS text +BEGIN ATOMIC + UPDATE for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01')) + SET name = 'one^1' + RETURNING name; +END; +\sf+ fpo_update() + CREATE OR REPLACE FUNCTION public.fpo_update() + RETURNS text + LANGUAGE sql +1 BEGIN ATOMIC +2 UPDATE for_portion_of_test FOR PORTION OF valid_at ((daterange('2018-01-15'::date, '2020-01-01'::date) * daterange('2019-01-01'::date, '2022-01-01'::date))) SET name = 'one^1'::text +3 RETURNING for_portion_of_test.name; +4 END +DROP FUNCTION fpo_update(); +DROP TABLE for_portion_of_test; +-- +-- DELETE tests +-- +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-01-05)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'), + ('[4,5)', '(,2018-04-01)', 'four'), + ('[5,6)', '(,)', 'five'), + ('[6,7)', '[2018-01-01,)', 'six'), + ('[7,8)', '(,2018-04-01)', 'seven'), + ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'), + ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'), + ('[8,9)', '[2018-03-03,2018-04-04)', 'eight') + ; +\set QUIET false +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test + FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + WHERE id = '[5,6)'; +ERROR: column "invalid_at" of relation "for_portion_of_test" does not exist +LINE 2: FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + ^ +-- The wrong start type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + WHERE id = '[3,4)'; +ERROR: could not coerce FOR PORTION OF FROM bound from integer to date +LINE 2: FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + ^ +-- The wrong end type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + WHERE id = '[3,4)'; +ERROR: could not coerce FOR PORTION OF TO bound from integer to date +LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + ^ +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' + WHERE id = '[3,4)'; +ERROR: range lower bound must be less than or equal to range upper bound +-- Deleting with a subquery fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + WHERE id = '[3,4)'; +ERROR: cannot use subquery in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '201... + ^ +-- Deleting with a column fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + WHERE id = '[3,4)'; +ERROR: cannot use column reference in FOR PORTION OF expression +LINE 2: FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + ^ +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' + WHERE id = '[3,4)'; +DELETE 0 +-- Deleting a finite/open portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + WHERE id = '[3,4)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [3,4) | [2018-01-01,2018-06-01) | three +(1 row) + +-- Deleting a finite/open portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-03-01' + WHERE id = '[6,7)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[6,7)' ORDER BY id, valid_at; + id | valid_at | name +-------+---------------+------ + [6,7) | [2018-03-01,) | six +(1 row) + +-- Deleting an open/finite portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-02-01' + WHERE id = '[4,5)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [4,5) | [2018-02-01,2018-04-01) | four +(1 row) + +-- Deleting an open/finite portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO NULL + WHERE id = '[7,8)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[7,8)' ORDER BY id, valid_at; + id | valid_at | name +-------+---------------+------- + [7,8) | (,2017-01-01) | seven +(1 row) + +-- Deleting a finite/finite portion with an exact fit +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01' + WHERE id = '[4,5)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +----+----------+------ +(0 rows) + +-- Deleting an enclosed span +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[2,3)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + id | valid_at | name +----+----------+------ +(0 rows) + +-- Deleting an open/open portion with a finite/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' + WHERE id = '[5,6)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + id | valid_at | name +-------+---------------+------ + [5,6) | (,2018-01-01) | five + [5,6) | [2019-01-01,) | five +(2 rows) + +-- Deleting an enclosed span with separate protruding spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03' + WHERE id = '[1,2)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-04-04) | one +(2 rows) + +-- Deleting multiple enclosed spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[8,9)'; +DELETE 3 +SELECT * FROM for_portion_of_test WHERE id = '[8,9)' ORDER BY id, valid_at; + id | valid_at | name +----+----------+------ +(0 rows) + +-- Deleting with a direct target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15')) + WHERE id = '[1,2)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-15,2018-04-04) | one +(3 rows) + +-- Deleting with a direct target, coerced from a string +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)') + WHERE id = '[1,2)'; +DELETE 1 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one +(3 rows) + +-- Deleting with a direct target of the wrong range subtype fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (int4range(1, 4)) + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from int4range to daterange +LINE 2: FOR PORTION OF valid_at (int4range(1, 4)) + ^ +-- Deleting with a direct target of a non-rangetype fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (4) + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from integer to daterange +LINE 2: FOR PORTION OF valid_at (4) + ^ +-- Deleting with a direct target of NULL fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (NULL) + WHERE id = '[1,2)'; +ERROR: FOR PORTION OF target was null +LINE 2: FOR PORTION OF valid_at (NULL) + ^ +-- Deleting with a direct target of empty does nothing +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at ('empty') + WHERE id = '[1,2)'; +DELETE 0 +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one +(3 rows) + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL; +DELETE 2 +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one + [3,4) | [2018-01-01,2018-06-01) | three + [5,6) | (,2018-01-01) | five + [5,6) | [2019-01-01,2030-01-01) | five + [6,7) | [2018-03-01,2030-01-01) | six + [7,8) | (,2017-01-01) | seven +(8 rows) + +\set QUIET true +-- UPDATE ... RETURNING returns only the updated values +-- (not the inserted side values, which are added by a separate "statement"): +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) + +-- UPDATE ... RETURNING supports NEW and OLD valid_at +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-10' TO '2018-02-20' + SET name = 'three^4' + WHERE id = '[3,4)' + RETURNING OLD.name, NEW.name, OLD.valid_at, NEW.valid_at; + name | name | valid_at | valid_at +---------+---------+-------------------------+------------------------- + three^3 | three^4 | [2018-02-01,2018-02-15) | [2018-02-10,2018-02-15) + three | three^4 | [2018-02-15,2018-06-01) | [2018-02-15,2018-02-20) +(2 rows) + +-- DELETE FOR PORTION OF with current_date +-- (We take care not to make the expectation depend on the timestamp.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[99,100)', '[2000-01-01,)', 'foo'); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM current_date TO null + WHERE id = '[99,100)'; +SELECT name, lower(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date - 1; + name | lower +------+------------ + foo | 2000-01-01 +(1 row) + +SELECT name, upper(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date + 1; + name | upper +------+------- +(0 rows) + +-- DELETE FOR PORTION OF with clock_timestamp() +-- fails because the function is volatile: +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM clock_timestamp()::date TO null + WHERE id = '[99,100)'; +ERROR: FOR PORTION OF bounds cannot contain volatile functions +-- clean up: +DELETE FROM for_portion_of_test WHERE id = '[99,100)'; +-- DELETE ... RETURNING returns the deleted values, regardless of bounds +-- (not the inserted side values, which are added by a separate "statement"): +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-10) | three^3 +(1 row) + +-- DELETE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + WHERE id = $1; +END; +$$; +SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01'); + fpo_delete +------------ + +(1 row) + +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + id | valid_at | name +---------+-------------------------+------ + [10,11) | [2019-01-01,2020-01-01) | ten +(1 row) + +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)'); +-- DELETE FOR PORTION OF in a compiled SQL function +CREATE FUNCTION fpo_delete() +RETURNS text +BEGIN ATOMIC + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + RETURNING name; +END; +\sf+ fpo_delete() + CREATE OR REPLACE FUNCTION public.fpo_delete() + RETURNS text + LANGUAGE sql +1 BEGIN ATOMIC +2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' +3 RETURNING for_portion_of_test.name; +4 END +CREATE OR REPLACE function fpo_delete() +RETURNS text +BEGIN ATOMIC + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01')) + RETURNING name; +END; +\sf+ fpo_delete() + CREATE OR REPLACE FUNCTION public.fpo_delete() + RETURNS text + LANGUAGE sql +1 BEGIN ATOMIC +2 DELETE FROM for_portion_of_test FOR PORTION OF valid_at ((daterange('2018-01-15'::date, '2020-01-01'::date) * daterange('2019-01-01'::date, '2022-01-01'::date))) +3 RETURNING for_portion_of_test.name; +4 END +DROP FUNCTION fpo_delete(); +-- test domains and CHECK constraints +-- With a domain on a rangetype +CREATE DOMAIN daterange_d AS daterange CHECK (upper(VALUE) <> '2005-05-05'::date); +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at daterange_d, + name text +); +INSERT INTO for_portion_of_test2 VALUES + (1, '[2000-01-01,2020-01-01)', 'one'), + (2, '[2000-01-01,2020-01-01)', 'two'); +INSERT INTO for_portion_of_test2 VALUES + (1, '[2000-01-01,2005-05-05)', 'nope'); +ERROR: value for domain daterange_d violates check constraint "daterange_d_check" +-- UPDATE works: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05' + SET name = 'one^1' + WHERE id = 1; +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)') + SET name = 'one^2' + WHERE id = 1; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; + id | valid_at | name +----+-------------------------+------- + 1 | [2000-01-01,2010-01-01) | one + 1 | [2010-01-01,2010-01-05) | one^1 + 1 | [2010-01-05,2010-01-07) | one + 1 | [2010-01-07,2010-01-09) | one^2 + 1 | [2010-01-09,2020-01-01) | one +(5 rows) + +-- The target is allowed to violate the domain: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + SET name = 'miss' + WHERE id = -1; +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)') + SET name = 'miss' + WHERE id = -1; +-- test the updated row violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + SET name = 'one^3' + WHERE id = 1; +ERROR: value for domain daterange_d violates check constraint "daterange_d_check" +-- test inserts violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01' + SET name = 'one^3' + WHERE id = 1; +ERROR: value for domain daterange_d violates check constraint "daterange_d_check" +-- test updated row violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-11' + SET name = 'one^3' + WHERE id = 1; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (1, [2000-01-01,2001-01-11), one^3). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02' + SET name = 'one^3' + WHERE id = 1; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (1, [2002-02-02,2010-01-01), one). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; + id | valid_at | name +----+-------------------------+------- + 1 | [2000-01-01,2010-01-01) | one + 1 | [2010-01-01,2010-01-05) | one^1 + 1 | [2010-01-05,2010-01-07) | one + 1 | [2010-01-07,2010-01-09) | one^2 + 1 | [2010-01-09,2020-01-01) | one +(5 rows) + +-- DELETE works: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05' + WHERE id = 2; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)') + WHERE id = 2; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; + id | valid_at | name +----+-------------------------+------ + 2 | [2000-01-01,2010-01-01) | two + 2 | [2010-01-05,2010-01-07) | two + 2 | [2010-01-09,2020-01-01) | two +(3 rows) + +-- The target is allowed to violate the domain: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + WHERE id = -1; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)') + WHERE id = -1; +-- test inserts violating the domain +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01' + WHERE id = 2; +ERROR: value for domain daterange_d violates check constraint "daterange_d_check" +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02'); +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02' + WHERE id = 2; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (2, [2002-02-02,2010-01-01), two). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; + id | valid_at | name +----+-------------------------+------ + 2 | [2000-01-01,2010-01-01) | two + 2 | [2010-01-05,2010-01-07) | two + 2 | [2010-01-09,2020-01-01) | two +(3 rows) + +DROP TABLE for_portion_of_test2; +-- With a domain on a multirangetype +CREATE FUNCTION multirange_lowers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$ + SELECT array_agg(lower(r)) FROM UNNEST(mr) u(r); +$$; +CREATE FUNCTION multirange_uppers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$ + SELECT array_agg(upper(r)) FROM UNNEST(mr) u(r); +$$; +CREATE DOMAIN datemultirange_d AS datemultirange CHECK (NOT '2005-05-05'::date = ANY (multirange_uppers(VALUE))); +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at datemultirange_d, + name text +); +INSERT INTO for_portion_of_test2 VALUES + (1, '{[2000-01-01,2020-01-01)}', 'one'), + (2, '{[2000-01-01,2020-01-01)}', 'two'); +INSERT INTO for_portion_of_test2 VALUES + (1, '{[2000-01-01,2005-05-05)}', 'nope'); +ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check" +-- UPDATE works: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}') + SET name = 'one^2' + WHERE id = 1; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; + id | valid_at | name +----+---------------------------------------------------+------- + 1 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | one + 1 | {[2010-01-07,2010-01-09)} | one^2 +(2 rows) + +-- The target is allowed to violate the domain: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + SET name = 'miss' + WHERE id = -1; +-- test the updated row violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + SET name = 'one^3' + WHERE id = 1; +ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check" +-- test inserts violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}') + SET name = 'one^3' + WHERE id = 1; +ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check" +-- test updated row violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2000-01-01,2001-01-11)}') + SET name = 'one^3' + WHERE id = 1; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (1, {[2000-01-01,2001-01-11)}, one^3). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at))); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}') + SET name = 'one^3' + WHERE id = 1; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (1, {[2000-01-01,2001-01-01),[2002-02-02,2010-01-07),[2010-01-09,202..., one). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; + id | valid_at | name +----+---------------------------------------------------+------- + 1 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | one + 1 | {[2010-01-07,2010-01-09)} | one^2 +(2 rows) + +-- DELETE works: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}') + WHERE id = 2; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; + id | valid_at | name +----+---------------------------------------------------+------ + 2 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | two +(1 row) + +-- The target is allowed to violate the domain: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + WHERE id = -1; +-- test inserts violating the domain +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}') + WHERE id = 2; +ERROR: value for domain datemultirange_d violates check constraint "datemultirange_d_check" +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at))); +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}') + WHERE id = 2; +ERROR: new row for relation "for_portion_of_test2" violates check constraint "fpo2_check" +DETAIL: Failing row contains (2, {[2000-01-01,2001-01-01),[2002-02-02,2010-01-07),[2010-01-09,202..., two). +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; + id | valid_at | name +----+---------------------------------------------------+------ + 2 | {[2000-01-01,2010-01-07),[2010-01-09,2020-01-01)} | two +(1 row) + +DROP TABLE for_portion_of_test2; +-- test on non-range/multirange columns +-- With a direct target and a scalar column +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at date, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '2020-01-01', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('2010-01-01') + SET name = 'one^1'; +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('2010-01-01') + ^ +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('2010-01-01'); +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('2010-01-01'); + ^ +DROP TABLE for_portion_of_test2; +-- With a direct target and a non-{,multi}range gistable column without overlaps +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at point, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '0,0', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('1,1') + SET name = 'one^1'; +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('1,1') + ^ +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('1,1'); +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('1,1'); + ^ +DROP TABLE for_portion_of_test2; +-- With a direct target and a non-{,multi}range column with overlaps +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at box, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '0,0,4,4', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('1,1,2,2') + SET name = 'one^1'; +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('1,1,2,2') + ^ +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('1,1,2,2'); +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range or multirange type +LINE 2: FOR PORTION OF valid_at ('1,1,2,2'); + ^ +DROP TABLE for_portion_of_test2; +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + + IF TG_ARGV[0] THEN + RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); + ELSE + RAISE NOTICE ' old: %', OLD.valid_at; + END IF; + IF TG_ARGV[1] THEN + RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table); + ELSE + RAISE NOTICE ' new: %', NEW.valid_at; + END IF; + + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + RETURN OLD; + END IF; +END; +$$; +-- statement triggers: +CREATE TRIGGER fpo_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_stmt + AFTER INSERT ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_update_stmt + AFTER UPDATE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_delete_stmt + AFTER DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +-- row triggers: +CREATE TRIGGER fpo_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' + SET name = 'five^3' + WHERE id = '[5,6)'; +NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2019-01-01,2030-01-01) +NOTICE: new: [2021-01-01,2022-01-01) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2021-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2021-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2030-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2030-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: [2019-01-01,2030-01-01) +NOTICE: new: [2021-01-01,2022-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' + WHERE id = '[5,6)'; +NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: old: [2022-01-01,2030-01-01) +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2023-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2022-01-01,2023-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2024-01-01,2030-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2024-01-01,2030-01-01) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2022-01-01,2030-01-01) +NOTICE: new: +NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2018-01-02,2018-02-03) | one + [1,2) | [2018-03-03,2018-03-10) | one + [1,2) | [2018-03-17,2018-04-04) | one + [3,4) | [2018-01-01,2018-02-01) | three + [3,4) | [2018-02-01,2018-02-02) | three^3 + [3,4) | [2018-02-03,2018-02-10) | three^3 + [3,4) | [2018-02-10,2018-02-15) | three^4 + [3,4) | [2018-02-15,2018-02-20) | three^4 + [3,4) | [2018-02-20,2018-06-01) | three + [5,6) | (,2018-01-01) | five + [5,6) | [2019-01-01,2021-01-01) | five + [5,6) | [2021-01-01,2022-01-01) | five^3 + [5,6) | [2022-01-01,2023-01-01) | five + [5,6) | [2024-01-01,2030-01-01) | five + [6,7) | [2018-03-01,2030-01-01) | six + [7,8) | (,2017-01-01) | seven +(16 rows) + +-- Triggers with a custom transition table name: +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); +-- statement triggers: +CREATE TRIGGER fpo_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_stmt + AFTER INSERT ON for_portion_of_test + REFERENCING NEW TABLE AS new_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true); +CREATE TRIGGER fpo_after_update_stmt + AFTER UPDATE ON for_portion_of_test + REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true); +CREATE TRIGGER fpo_after_delete_stmt + AFTER DELETE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false); +-- row triggers: +CREATE TRIGGER fpo_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + REFERENCING NEW TABLE AS new_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true); +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, true); +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false); +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-15,2019-01-01) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-01,2018-01-15) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-15)",one) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2019-01-01,2020-01-01)",one) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-15,2019-01-01)",2018-01-15_to_2019-01-01) +ROLLBACK; +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +NOTICE: fpo_before_stmt: BEFORE DELETE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE DELETE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-21,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-21,2020-01-01)",one) +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: +NOTICE: fpo_after_delete_stmt: AFTER DELETE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: +ROLLBACK; +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +NOTICE: fpo_before_stmt: BEFORE UPDATE STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-01,2018-01-02) +NOTICE: fpo_before_stmt: BEFORE INSERT STATEMENT: +NOTICE: old: +NOTICE: new: +NOTICE: fpo_before_row: BEFORE INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-02,2020-01-01) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: fpo_after_insert_stmt: AFTER INSERT STATEMENT: +NOTICE: old: +NOTICE: new: ("[1,2)","[2018-01-02,2020-01-01)",one) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +NOTICE: fpo_after_update_stmt: AFTER UPDATE STATEMENT: +NOTICE: old: ("[1,2)","[2018-01-01,2020-01-01)",one) +NOTICE: new: ("[1,2)","[2018-01-01,2018-01-02)",NULL_to_2018-01-01) +ROLLBACK; +-- Deferred triggers +-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables) +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); +CREATE CONSTRAINT TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE CONSTRAINT TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +CREATE CONSTRAINT TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +COMMIT; +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-01,2018-01-15) +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2019-01-01,2020-01-01) +NOTICE: fpo_after_update_row: AFTER UPDATE ROW: +NOTICE: old: [2018-01-01,2020-01-01) +NOTICE: new: [2018-01-15,2019-01-01) +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +COMMIT; +NOTICE: fpo_after_insert_row: AFTER INSERT ROW: +NOTICE: old: +NOTICE: new: [2018-01-21,2019-01-01) +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2018-01-15,2019-01-01) +NOTICE: new: +NOTICE: fpo_after_delete_row: AFTER DELETE ROW: +NOTICE: old: [2018-01-01,2018-01-15) +NOTICE: new: +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +COMMIT; +SELECT * FROM for_portion_of_test; + id | valid_at | name +-------+-------------------------+-------------------------- + [1,2) | [2019-01-01,2020-01-01) | one + [1,2) | [2018-01-21,2019-01-01) | 2018-01-15_to_2019-01-01 +(2 rows) + +-- test FOR PORTION OF from triggers during FOR PORTION OF: +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'), + ('[2,3)', '[2018-01-01,2020-01-01)', 'two'), + ('[3,4)', '[2018-01-01,2020-01-01)', 'three'), + ('[4,5)', '[2018-01-01,2020-01-01)', 'four'); +CREATE FUNCTION trg_fpo_update() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01' + SET name = CONCAT(name, '^') + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; +CREATE FUNCTION trg_fpo_delete() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01' + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; +-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2018-01-01,2018-02-01) | one + [1,2) | [2018-02-01,2018-03-01) | one^ + [1,2) | [2018-03-01,2018-05-01) | one + [1,2) | [2018-05-01,2018-06-01) | one* + [1,2) | [2018-06-01,2020-01-01) | one +(5 rows) + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; +-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [2,3) | [2018-01-01,2018-02-01) | two + [2,3) | [2018-02-01,2018-03-01) | two^ + [2,3) | [2018-03-01,2018-05-01) | two + [2,3) | [2018-06-01,2020-01-01) | two +(4 rows) + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[3,4)'; +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+-------- + [3,4) | [2018-01-01,2018-03-01) | three + [3,4) | [2018-04-01,2018-05-01) | three + [3,4) | [2018-05-01,2018-06-01) | three* + [3,4) | [2018-06-01,2020-01-01) | three +(4 rows) + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; +-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[4,5)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [4,5) | [2018-01-01,2018-03-01) | four + [4,5) | [2018-04-01,2018-05-01) | four + [4,5) | [2018-06-01,2020-01-01) | four +(3 rows) + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; +-- Test with multiranges +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), + ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), + ('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), + ('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); + ; +-- Updating with FROM/TO +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range type +LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + ^ +-- Updating with multirange +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)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY 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 +(4 rows) + +-- Updating with string coercion +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2018-03-05,2018-03-10)}') + SET name = 'one^2' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY 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-03-10)} | one^2 + [1,2) | {[2018-03-10,2018-04-04)} | one^1 +(5 rows) + +-- Updating with the wrong range subtype fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from int4multirange to datemultirange +LINE 2: FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + ^ +-- Updating with a non-multirangetype fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at (4) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: could not coerce FOR PORTION OF target from integer to datemultirange +LINE 2: FOR PORTION OF valid_at (4) + ^ +-- Updating with NULL fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at (NULL) + SET name = 'one^3' + WHERE id = '[1,2)'; +ERROR: FOR PORTION OF target was null +LINE 2: FOR PORTION OF valid_at (NULL) + ^ +-- Updating with empty does nothing +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{}') + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY 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-03-10)} | one^2 + [1,2) | {[2018-03-10,2018-04-04)} | one^1 +(5 rows) + +-- Deleting with FROM/TO +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +ERROR: column "valid_at" of relation "for_portion_of_test2" is not a range type +LINE 2: FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + ^ +-- Deleting with multirange +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 WHERE id = '[2,3)' ORDER BY valid_at; + id | valid_at | name +-------+---------------------------------------------------------------------------+------ + [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-15,2018-05-01)} | two +(1 row) + +-- Deleting with string coercion +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2018-03-05,2018-03-20)}') + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at; + id | valid_at | name +-------+---------------------------------------------------------------------------+------ + [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-20,2018-05-01)} | two +(1 row) + +-- Deleting with the wrong range subtype fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + WHERE id = '[2,3)'; +ERROR: could not coerce FOR PORTION OF target from int4multirange to datemultirange +LINE 2: FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + ^ +-- Deleting with a non-multirangetype fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at (4) + WHERE id = '[2,3)'; +ERROR: could not coerce FOR PORTION OF target from integer to datemultirange +LINE 2: FOR PORTION OF valid_at (4) + ^ +-- Deleting with NULL fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at (NULL) + WHERE id = '[2,3)'; +ERROR: FOR PORTION OF target was null +LINE 2: FOR PORTION OF valid_at (NULL) + ^ +-- Deleting with empty does nothing +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{}') + 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-03-10)} | one^2 + [1,2) | {[2018-03-10,2018-04-04)} | one^1 + [2,3) | {[2018-01-01,2018-01-15),[2018-02-15,2018-03-01),[2018-03-20,2018-05-01)} | two + [3,4) | {[2018-01-01,)} | three +(7 rows) + +DROP TABLE for_portion_of_test2; +-- Test with a custom range type +CREATE TYPE mydaterange AS range(subtype=date); +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-05-01)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'); + ; +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' + SET name = 'one^1' + WHERE id = '[1,2)'; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2018-01-02,2018-01-10) | one + [1,2) | [2018-01-10,2018-02-03) | one^1 + [1,2) | [2018-02-03,2018-02-10) | one^1 + [1,2) | [2018-02-10,2018-03-03) | one + [1,2) | [2018-03-03,2018-04-04) | one + [2,3) | [2018-01-01,2018-01-15) | two + [2,3) | [2018-02-15,2018-05-01) | two + [3,4) | [2018-01-01,) | three +(8 rows) + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); +INSERT INTO temporal_partitioned (id, valid_at, name) VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); +SELECT * FROM temporal_partitioned; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2010-01-01) | one + [3,4) | [2000-01-01,2010-01-01) | three + [5,6) | [2000-01-01,2010-01-01) | five +(3 rows) + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; +-- Update all partitions at once (each with leftovers) +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 + [3,4) | [2000-01-01,2000-03-01) | three + [3,4) | [2000-03-01,2000-04-01) | three^1 + [3,4) | [2000-04-01,2000-06-01) | three + [3,4) | [2000-06-01,2000-07-01) | five^2 + [3,4) | [2000-07-01,2010-01-01) | three + [4,5) | [2000-06-01,2000-07-01) | one^2 + [5,6) | [2000-01-01,2000-03-01) | five + [5,6) | [2000-03-01,2000-04-01) | five^1 + [5,6) | [2000-04-01,2000-06-01) | five + [5,6) | [2000-07-01,2010-01-01) | five +(15 rows) + +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+--------- + [1,2) | [2000-01-01,2000-03-01) | one + [1,2) | [2000-03-01,2000-04-01) | one^1 + [1,2) | [2000-04-01,2000-06-01) | one + [1,2) | [2000-07-01,2010-01-01) | one + [2,3) | [2000-06-01,2000-07-01) | three^2 +(5 rows) + +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; + name | id | valid_at +---------+-------+------------------------- + three | [3,4) | [2000-01-01,2000-03-01) + three^1 | [3,4) | [2000-03-01,2000-04-01) + three | [3,4) | [2000-04-01,2000-06-01) + five^2 | [3,4) | [2000-06-01,2000-07-01) + three | [3,4) | [2000-07-01,2010-01-01) + one^2 | [4,5) | [2000-06-01,2000-07-01) +(6 rows) + +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + name | valid_at | id +--------+-------------------------+------- + five | [2000-01-01,2000-03-01) | [5,6) + five^1 | [2000-03-01,2000-04-01) | [5,6) + five | [2000-04-01,2000-06-01) | [5,6) + five | [2000-07-01,2010-01-01) | [5,6) +(4 rows) + +DROP TABLE temporal_partitioned; +RESET datestyle; diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 7069e9febb8..0de13612818 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -1145,6 +1145,34 @@ 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) +); +-- UPDATE requires select permission on the valid_at column (but not update): +GRANT SELECT (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) 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; +-- DELETE requires select permission on the valid_at column: +GRANT DELETE ON t1 TO regress_priv_user2; +GRANT DELETE ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01'; +ERROR: permission denied for table t1 +SET SESSION AUTHORIZATION regress_priv_user3; +DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01'; +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 9cea538b8e8..8852160718f 100644 --- a/src/test/regress/expected/updatable_views.out +++ b/src/test/regress/expected/updatable_views.out @@ -3722,6 +3722,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 order by id, valid_at; + b | c | valid_at | id | two +---+---+---------------------------------------------------------+-------+----- + 1 | 2 | ["Fri Jan 01 00:00:00 2010","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [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 order by id, valid_at; + 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 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Wed Jan 01 00:00:00 2020") | [1,2) | 2.0 + 0 | 1 | ["Wed Jan 01 00:00:00 2020","Tue Jan 01 00:00:00 2030") | [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 order by id, valid_at; + 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 + 2 | 3 | ["Thu Jan 01 00:00:00 2015","Sun Jan 01 00:00:00 2017") | [1,2) | 2.0 + 0 | 1 | ["Sat Jan 01 00:00:00 2022","Tue Jan 01 00:00:00 2030") | [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 36d2c18bdbe..6f145b27c4d 100644 --- a/src/test/regress/expected/without_overlaps.out +++ b/src/test/regress/expected/without_overlaps.out @@ -2,7 +2,7 @@ -- -- We leave behind several tables to test pg_dump etc: -- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. +-- temporal_fk_rng2rng, temporal_fk2_rng2rng. SET datestyle TO ISO, YMD; -- -- test input parser @@ -892,6 +892,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 @@ -923,26 +953,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: @@ -958,26 +1005,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 @@ -1758,6 +1822,33 @@ UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- changing an unreferenced part is okay: +UPDATE temporal_rng + FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng + FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' + SET id = '[7,8)' + WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2016-02-01,2016-03-01) + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) + [7,8) | [2018-01-02,2018-01-03) +(4 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1805,6 +1896,42 @@ BEGIN; COMMIT; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng" +DETAIL: Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng". +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; + id | valid_at +-------+------------------------- + [5,6) | [2018-01-01,2018-01-02) + [5,6) | [2018-01-03,2018-02-01) +(2 rows) + +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; + id | valid_at | parent_id +-------+-------------------------+----------- + [3,4) | [2018-01-05,2018-01-10) | [5,6) +(1 row) + -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); @@ -1821,11 +1948,12 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE RESTRICT; ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD -- --- test ON UPDATE/DELETE options +-- rng2rng test ON UPDATE/DELETE options -- -- test FK referenced updates CASCADE +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1833,8 +1961,9 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE CASCADE ON UPDATE CASCADE; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1842,9 +1971,10 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE SET NULL ON UPDATE SET NULL; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET DEFAULT +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', ADD CONSTRAINT temporal_fk_rng2rng_fk @@ -2214,6 +2344,22 @@ UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- changing an unreferenced part is okay: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +UPDATE temporal_mltrng SET id = '[7,8)' + WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- test FK referenced updates RESTRICT -- @@ -2256,6 +2402,19 @@ BEGIN; COMMIT; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- deleting an unreferenced part is okay: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +WHERE id = '[5,6)'; +ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng" +DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng". +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- FK between partitioned tables: ranges -- diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 734da057c34..3a044ffd8bf 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 create_property_graph +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 create_property_graph 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..d4062acf1d1 --- /dev/null +++ b/src/test/regress/sql/for_portion_of.sql @@ -0,0 +1,1368 @@ +-- Tests for UPDATE/DELETE FOR PORTION OF + +SET datestyle TO ISO, YMD; + +-- Works on non-PK columns +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2020-01-01)', 'one'); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = 'one^1'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-01-15' TO '2019-01-20'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- With a table alias with AS + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-01' TO '2019-02-03' AS t + SET name = 'one^2'; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-03' TO '2019-02-04' AS t; + +-- With a table alias without AS + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-04' TO '2019-02-05' t + SET name = 'one^3'; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-02-05' TO '2019-02-06' t; + +-- UPDATE with FROM + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2019-03-01' to '2019-03-02' + SET name = 'one^4' + FROM (SELECT '[1,2)'::int4range) AS t2(id) + WHERE for_portion_of_test.id = t2.id; + +-- DELETE with USING + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2019-03-02' TO '2019-03-03' + USING (SELECT '[1,2)'::int4range) AS t2(id) + WHERE for_portion_of_test.id = t2.id; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- Works on more than one range +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid1_at daterange, + valid2_at daterange, + name text NOT NULL +); +INSERT INTO for_portion_of_test (id, valid1_at, valid2_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', '[2015-01-01,2025-01-01)', 'one'); + +UPDATE for_portion_of_test + FOR PORTION OF valid1_at FROM '2018-01-15' TO NULL + SET name = 'foo'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +UPDATE for_portion_of_test + FOR PORTION OF valid2_at FROM '2018-01-15' TO NULL + SET name = 'bar'; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid1_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid2_at FROM '2018-01-20' TO NULL; +SELECT * FROM for_portion_of_test ORDER BY id, valid1_at, valid2_at; + +-- Test with NULLs in the scalar/range key columns. +-- This won't happen if there is a PRIMARY KEY or UNIQUE constraint +-- but FOR PORTION OF shouldn't require that. +DROP TABLE for_portion_of_test; +CREATE UNLOGGED TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', NULL, '1 null'), + ('[1,2)', '(,)', '1 unbounded'), + ('[1,2)', 'empty', '1 empty'), + (NULL, NULL, NULL), + (NULL, daterange('2018-01-01', '2019-01-01'), 'null key'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + SET name = 'NULL to NULL'; +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test; + +-- +-- UPDATE tests +-- + +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-01-05)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'), + ('[4,5)', '(,2018-04-01)', 'four'), + ('[5,6)', '(,)', 'five') + ; +\set QUIET false + +-- Updating with a missing column fails +UPDATE for_portion_of_test + FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + SET name = 'foo' + WHERE id = '[5,6)'; + +-- Updating the range fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + SET valid_at = '[1990-01-01,1999-01-01)' + WHERE id = '[5,6)'; + +-- The wrong start type fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + SET name = 'nope' + WHERE id = '[3,4)'; + +-- The wrong end type fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + SET name = 'nope' + WHERE id = '[3,4)'; + +-- Updating with timestamps reversed fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' + SET name = 'three^1' + WHERE id = '[3,4)'; + +-- Updating with a subquery fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + SET name = 'nope' + WHERE id = '[3,4)'; + +-- Updating with a column fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + SET name = 'nope' + WHERE id = '[3,4)'; + +-- Updating with timestamps equal does nothing +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' + SET name = 'three^0' + WHERE id = '[3,4)'; + +-- Updating a finite/open portion with a finite/open target +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + SET name = 'three^1' + WHERE id = '[3,4)'; +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Updating with a direct target +UPDATE for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15')) + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Updating with a direct target, coerced from a string +UPDATE for_portion_of_test + FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)') + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Updating with a direct target of the wrong range subtype fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (int4range(1, 4)) + SET name = 'one^3' + WHERE id = '[1,2)'; + +-- Updating with a direct target of a non-rangetype fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (4) + SET name = 'one^3' + WHERE id = '[1,2)'; + +-- Updating with a direct target of NULL fails +UPDATE for_portion_of_test + FOR PORTION OF valid_at (NULL) + SET name = 'one^3' + WHERE id = '[1,2)'; + +-- Updating with a direct target of empty does nothing +UPDATE for_portion_of_test + FOR PORTION OF valid_at ('empty') + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- 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)'; +SELECT * FROM for_portion_of_test WHERE id IN ('[1,2)', '[6,7)') ORDER BY id, valid_at; + +-- UPDATE with no WHERE clause +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL + SET name = name || '*'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; +\set QUIET true + +-- Updating with a shift/reduce conflict +-- (requires a tsrange column) +CREATE UNLOGGED TABLE for_portion_of_test2 ( + id int4range, + valid_at tsrange, + name text +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', '[2000-01-01,2020-01-01)', 'one'); +-- updates [2011-03-01 01:02:00, 2012-01-01) (note 2 minutes) +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM '2011-03-01'::timestamp + INTERVAL '1:02:03' HOUR TO MINUTE + TO '2012-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; + +-- TO is used for the bound but not the INTERVAL: +-- syntax error +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM '2013-03-01'::timestamp + INTERVAL '1:02:03' HOUR + TO '2014-01-01' + SET name = 'one^2' + WHERE id = '[1,2)'; + +-- adding parens fixes it +-- updates [2015-03-01 01:00:00, 2016-01-01) (no minutes) +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at + FROM ('2015-03-01'::timestamp + INTERVAL '1:02:03' HOUR) + TO '2016-01-01' + SET name = 'one^3' + WHERE id = '[1,2)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; +DROP TABLE for_portion_of_test2; + +-- UPDATE FOR PORTION OF in a CTE: +-- The outer query sees the table how it was before the updates, +-- and with no leftovers yet, +-- but it also sees the new values via the RETURNING clause. +-- (We test RETURNING more directly, without a CTE, below.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[10,11)' + RETURNING id, valid_at, name +) +SELECT * + FROM for_portion_of_test AS t, update_apr + WHERE t.id = update_apr.id; +SELECT * FROM for_portion_of_test WHERE id = '[10,11)' ORDER BY id, valid_at; + +-- UPDATE FOR PORTION OF with current_date +-- (We take care not to make the expectation depend on the timestamp.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[99,100)', '[2000-01-01,)', 'foo'); +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM current_date TO null + SET name = 'bar' + WHERE id = '[99,100)'; +SELECT name, lower(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date - 1; +SELECT name, upper(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date + 1; + +-- UPDATE FOR PORTION OF with clock_timestamp() +-- fails because the function is volatile: +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM clock_timestamp()::date TO null + SET name = 'baz' + WHERE id = '[99,100)'; + +-- clean up: +DELETE FROM for_portion_of_test WHERE id = '[99,100)'; + +-- Not visible to UPDATE: +-- Tuples updated/inserted within the CTE are not visible to the main query yet, +-- but neither are old tuples the CTE changed: +-- (This is the same behavior as without FOR PORTION OF.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[11,12)', '[2018-01-01,2020-01-01)', 'eleven'); +WITH update_apr AS ( + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-05-01' + SET name = 'Apr 2018' + WHERE id = '[11,12)' + RETURNING id, valid_at, name +) +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + AS t + SET name = 'May 2018' + FROM update_apr AS j + WHERE t.id = j.id; +SELECT * FROM for_portion_of_test WHERE id = '[11,12)' ORDER BY id, valid_at; +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)', '[11,12)'); + +-- UPDATE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_update(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + SET name = concat(_target_from::text, ' to ', _target_til::text) + WHERE id = $1; +END; +$$; +SELECT fpo_update('[10,11)', '2015-01-01', '2019-01-01'); +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; + +-- UPDATE FOR PORTION OF in a compiled SQL function +CREATE FUNCTION fpo_update() +RETURNS text +BEGIN ATOMIC + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = 'one^1' + RETURNING name; +END; +\sf+ fpo_update() +CREATE OR REPLACE function fpo_update() +RETURNS text +BEGIN ATOMIC + UPDATE for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01')) + SET name = 'one^1' + RETURNING name; +END; +\sf+ fpo_update() +DROP FUNCTION fpo_update(); + +DROP TABLE for_portion_of_test; + +-- +-- DELETE tests +-- + +CREATE TABLE for_portion_of_test ( + id int4range NOT NULL, + valid_at daterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-01-05)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'), + ('[4,5)', '(,2018-04-01)', 'four'), + ('[5,6)', '(,)', 'five'), + ('[6,7)', '[2018-01-01,)', 'six'), + ('[7,8)', '(,2018-04-01)', 'seven'), + ('[8,9)', '[2018-01-02,2018-02-03)', 'eight'), + ('[8,9)', '[2018-02-03,2018-03-03)', 'eight'), + ('[8,9)', '[2018-03-03,2018-04-04)', 'eight') + ; +\set QUIET false + +-- Deleting with a missing column fails +DELETE FROM for_portion_of_test + FOR PORTION OF invalid_at FROM '2018-06-01' TO NULL + WHERE id = '[5,6)'; + +-- The wrong start type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM 1 TO '2020-01-01' + WHERE id = '[3,4)'; + +-- The wrong end type fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2000-01-01' TO 4 + WHERE id = '[3,4)'; + +-- Deleting with timestamps reversed fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO '2018-01-01' + WHERE id = '[3,4)'; + +-- Deleting with a subquery fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM (SELECT '2018-01-01') TO '2018-06-01' + WHERE id = '[3,4)'; + +-- Deleting with a column fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM lower(valid_at) TO NULL + WHERE id = '[3,4)'; + +-- Deleting with timestamps equal does nothing +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-04-01' TO '2018-04-01' + WHERE id = '[3,4)'; + +-- Deleting a finite/open portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-06-01' TO NULL + WHERE id = '[3,4)'; +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + +-- Deleting a finite/open portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-03-01' + WHERE id = '[6,7)'; +SELECT * FROM for_portion_of_test WHERE id = '[6,7)' ORDER BY id, valid_at; + +-- Deleting an open/finite portion with an open/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-02-01' + WHERE id = '[4,5)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +-- Deleting an open/finite portion with a finite/open target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2017-01-01' TO NULL + WHERE id = '[7,8)'; +SELECT * FROM for_portion_of_test WHERE id = '[7,8)' ORDER BY id, valid_at; + +-- Deleting a finite/finite portion with an exact fit +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-04-01' + WHERE id = '[4,5)'; +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +-- Deleting an enclosed span +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + +-- Deleting an open/open portion with a finite/finite target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-01' TO '2019-01-01' + WHERE id = '[5,6)'; +SELECT * FROM for_portion_of_test WHERE id = '[5,6)' ORDER BY id, valid_at; + +-- Deleting an enclosed span with separate protruding spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-03' TO '2018-03-03' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Deleting multiple enclosed spans +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO NULL + WHERE id = '[8,9)'; +SELECT * FROM for_portion_of_test WHERE id = '[8,9)' ORDER BY id, valid_at; + +-- Deleting with a direct target +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-03-10', '2018-03-15')) + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Deleting with a direct target, coerced from a string +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at ('[2018-03-15,2018-03-17)') + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- Deleting with a direct target of the wrong range subtype fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (int4range(1, 4)) + WHERE id = '[1,2)'; + +-- Deleting with a direct target of a non-rangetype fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (4) + WHERE id = '[1,2)'; + +-- Deleting with a direct target of NULL fails +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (NULL) + WHERE id = '[1,2)'; + +-- Deleting with a direct target of empty does nothing +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at ('empty') + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +-- DELETE with no WHERE clause +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2030-01-01' TO NULL; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; +\set QUIET true + +-- UPDATE ... RETURNING returns only the updated values +-- (not the inserted side values, which are added by a separate "statement"): +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 *; + +-- UPDATE ... RETURNING supports NEW and OLD valid_at +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-10' TO '2018-02-20' + SET name = 'three^4' + WHERE id = '[3,4)' + RETURNING OLD.name, NEW.name, OLD.valid_at, NEW.valid_at; + +-- DELETE FOR PORTION OF with current_date +-- (We take care not to make the expectation depend on the timestamp.) +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[99,100)', '[2000-01-01,)', 'foo'); +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM current_date TO null + WHERE id = '[99,100)'; +SELECT name, lower(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date - 1; +SELECT name, upper(valid_at) FROM for_portion_of_test + WHERE id = '[99,100)' AND valid_at @> current_date + 1; + +-- DELETE FOR PORTION OF with clock_timestamp() +-- fails because the function is volatile: +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM clock_timestamp()::date TO null + WHERE id = '[99,100)'; + +-- clean up: +DELETE FROM for_portion_of_test WHERE id = '[99,100)'; + +-- DELETE ... RETURNING returns the deleted values, regardless of bounds +-- (not the inserted side values, which are added by a separate "statement"): +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-02' TO '2018-02-03' + WHERE id = '[3,4)' + RETURNING *; + +-- DELETE FOR PORTION OF in a PL/pgSQL function +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[10,11)', '[2018-01-01,2020-01-01)', 'ten'); +CREATE FUNCTION fpo_delete(_id int4range, _target_from date, _target_til date) +RETURNS void LANGUAGE plpgsql AS +$$ +BEGIN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM $2 TO $3 + WHERE id = $1; +END; +$$; +SELECT fpo_delete('[10,11)', '2015-01-01', '2019-01-01'); +SELECT * FROM for_portion_of_test WHERE id = '[10,11)'; +DELETE FROM for_portion_of_test WHERE id IN ('[10,11)'); + +-- DELETE FOR PORTION OF in a compiled SQL function +CREATE FUNCTION fpo_delete() +RETURNS text +BEGIN ATOMIC + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + RETURNING name; +END; +\sf+ fpo_delete() +CREATE OR REPLACE function fpo_delete() +RETURNS text +BEGIN ATOMIC + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at (daterange('2018-01-15', '2020-01-01') * daterange('2019-01-01', '2022-01-01')) + RETURNING name; +END; +\sf+ fpo_delete() +DROP FUNCTION fpo_delete(); + + +-- test domains and CHECK constraints + +-- With a domain on a rangetype +CREATE DOMAIN daterange_d AS daterange CHECK (upper(VALUE) <> '2005-05-05'::date); +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at daterange_d, + name text +); +INSERT INTO for_portion_of_test2 VALUES + (1, '[2000-01-01,2020-01-01)', 'one'), + (2, '[2000-01-01,2020-01-01)', 'two'); +INSERT INTO for_portion_of_test2 VALUES + (1, '[2000-01-01,2005-05-05)', 'nope'); +-- UPDATE works: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05' + SET name = 'one^1' + WHERE id = 1; +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)') + SET name = 'one^2' + WHERE id = 1; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; +-- The target is allowed to violate the domain: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + SET name = 'miss' + WHERE id = -1; +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)') + SET name = 'miss' + WHERE id = -1; +-- test the updated row violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + SET name = 'one^3' + WHERE id = 1; +-- test inserts violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01' + SET name = 'one^3' + WHERE id = 1; +-- test updated row violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-11' + SET name = 'one^3' + WHERE id = 1; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02' + SET name = 'one^3' + WHERE id = 1; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; +-- DELETE works: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2010-01-01' TO '2010-01-05' + WHERE id = 2; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('[2010-01-07,2010-01-09)') + WHERE id = 2; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; +-- The target is allowed to violate the domain: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '1999-01-01' TO '2005-05-05' + WHERE id = -1; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('[1999-01-01,2005-05-05)') + WHERE id = -1; +-- test inserts violating the domain +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2005-05-05' TO '2010-01-01' + WHERE id = 2; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (lower(valid_at) <> '2002-02-02'); +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-02-02' + WHERE id = 2; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; +DROP TABLE for_portion_of_test2; + +-- With a domain on a multirangetype +CREATE FUNCTION multirange_lowers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$ + SELECT array_agg(lower(r)) FROM UNNEST(mr) u(r); +$$; +CREATE FUNCTION multirange_uppers(mr anymultirange) RETURNS anyarray LANGUAGE sql AS $$ + SELECT array_agg(upper(r)) FROM UNNEST(mr) u(r); +$$; +CREATE DOMAIN datemultirange_d AS datemultirange CHECK (NOT '2005-05-05'::date = ANY (multirange_uppers(VALUE))); +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at datemultirange_d, + name text +); +INSERT INTO for_portion_of_test2 VALUES + (1, '{[2000-01-01,2020-01-01)}', 'one'), + (2, '{[2000-01-01,2020-01-01)}', 'two'); +INSERT INTO for_portion_of_test2 VALUES + (1, '{[2000-01-01,2005-05-05)}', 'nope'); +-- UPDATE works: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}') + SET name = 'one^2' + WHERE id = 1; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; +-- The target is allowed to violate the domain: +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + SET name = 'miss' + WHERE id = -1; +-- test the updated row violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + SET name = 'one^3' + WHERE id = 1; +-- test inserts violating the domain +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}') + SET name = 'one^3' + WHERE id = 1; +-- test updated row violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (upper(valid_at) <> '2001-01-11'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2000-01-01,2001-01-11)}') + SET name = 'one^3' + WHERE id = 1; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at))); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}') + SET name = 'one^3' + WHERE id = 1; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 1 ORDER BY valid_at; +-- DELETE works: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2010-01-07,2010-01-09)}') + WHERE id = 2; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; +-- The target is allowed to violate the domain: +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[1999-01-01,2005-05-05)}') + WHERE id = -1; +-- test inserts violating the domain +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2005-05-05,2010-01-01)}') + WHERE id = 2; +-- test inserts violating CHECK constraints +ALTER TABLE for_portion_of_test2 + ADD CONSTRAINT fpo2_check CHECK (NOT '2002-02-02'::date = ANY (multirange_lowers(valid_at))); +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2001-01-01,2002-02-02)}') + WHERE id = 2; +ALTER TABLE for_portion_of_test2 DROP CONSTRAINT fpo2_check; +SELECT * FROM for_portion_of_test2 WHERE id = 2 ORDER BY valid_at; +DROP TABLE for_portion_of_test2; + +-- test on non-range/multirange columns + +-- With a direct target and a scalar column +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at date, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '2020-01-01', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('2010-01-01') + SET name = 'one^1'; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('2010-01-01'); +DROP TABLE for_portion_of_test2; + +-- With a direct target and a non-{,multi}range gistable column without overlaps +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at point, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '0,0', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('1,1') + SET name = 'one^1'; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('1,1'); +DROP TABLE for_portion_of_test2; + +-- With a direct target and a non-{,multi}range column with overlaps +CREATE TABLE for_portion_of_test2 ( + id integer, + valid_at box, + name text +); +INSERT INTO for_portion_of_test2 VALUES (1, '0,0,4,4', 'one'); +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('1,1,2,2') + SET name = 'one^1'; +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('1,1,2,2'); +DROP TABLE for_portion_of_test2; + +-- test that we run triggers on the UPDATE/DELETEd row and the INSERTed rows + +CREATE FUNCTION dump_trigger() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + RAISE NOTICE '%: % % %:', + TG_NAME, TG_WHEN, TG_OP, TG_LEVEL; + + IF TG_ARGV[0] THEN + RAISE NOTICE ' old: %', (SELECT string_agg(old_table::text, '\n ') FROM old_table); + ELSE + RAISE NOTICE ' old: %', OLD.valid_at; + END IF; + IF TG_ARGV[1] THEN + RAISE NOTICE ' new: %', (SELECT string_agg(new_table::text, '\n ') FROM new_table); + ELSE + RAISE NOTICE ' new: %', NEW.valid_at; + END IF; + + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN + RETURN NEW; + ELSIF TG_OP = 'DELETE' THEN + RETURN OLD; + END IF; +END; +$$; + +-- statement triggers: + +CREATE TRIGGER fpo_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_stmt + AFTER INSERT ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_update_stmt + AFTER UPDATE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_delete_stmt + AFTER DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +-- row triggers: + +CREATE TRIGGER fpo_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2021-01-01' TO '2022-01-01' + SET name = 'five^3' + WHERE id = '[5,6)'; + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2023-01-01' TO '2024-01-01' + WHERE id = '[5,6)'; + +SELECT * FROM for_portion_of_test ORDER BY id, valid_at; + +-- Triggers with a custom transition table name: + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test VALUES ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); + +-- statement triggers: + +CREATE TRIGGER fpo_before_stmt + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_stmt + AFTER INSERT ON for_portion_of_test + REFERENCING NEW TABLE AS new_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(false, true); + +CREATE TRIGGER fpo_after_update_stmt + AFTER UPDATE ON for_portion_of_test + REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, true); + +CREATE TRIGGER fpo_after_delete_stmt + AFTER DELETE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table + FOR EACH STATEMENT EXECUTE PROCEDURE dump_trigger(true, false); + +-- row triggers: + +CREATE TRIGGER fpo_before_row + BEFORE INSERT OR UPDATE OR DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + REFERENCING NEW TABLE AS new_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, true); + +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, true); + +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + REFERENCING OLD TABLE AS old_table + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(true, false); + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +ROLLBACK; + +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +ROLLBACK; + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +ROLLBACK; + +-- Deferred triggers +-- (must be CONSTRAINT triggers thus AFTER ROW with no transition tables) + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'); + +CREATE CONSTRAINT TRIGGER fpo_after_insert_row + AFTER INSERT ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE CONSTRAINT TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +CREATE CONSTRAINT TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW EXECUTE PROCEDURE dump_trigger(false, false); + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-01-15' TO '2019-01-01' + SET name = '2018-01-15_to_2019-01-01'; +COMMIT; + +BEGIN; +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-21'; +COMMIT; + +BEGIN; +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM NULL TO '2018-01-02' + SET name = 'NULL_to_2018-01-01'; +COMMIT; + +SELECT * FROM for_portion_of_test; + +-- test FOR PORTION OF from triggers during FOR PORTION OF: + +DROP TABLE for_portion_of_test; +CREATE TABLE for_portion_of_test ( + id int4range, + valid_at daterange, + name text +); +INSERT INTO for_portion_of_test (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-01,2020-01-01)', 'one'), + ('[2,3)', '[2018-01-01,2020-01-01)', 'two'), + ('[3,4)', '[2018-01-01,2020-01-01)', 'three'), + ('[4,5)', '[2018-01-01,2020-01-01)', 'four'); + +CREATE FUNCTION trg_fpo_update() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-02-01' TO '2018-03-01' + SET name = CONCAT(name, '^') + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; + +CREATE FUNCTION trg_fpo_delete() +RETURNS TRIGGER LANGUAGE plpgsql AS +$$ +BEGIN + IF pg_trigger_depth() = 1 THEN + DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-03-01' TO '2018-04-01' + WHERE id = OLD.id; + END IF; + RETURN CASE WHEN 'TG_OP' = 'DELETE' THEN OLD ELSE NEW END; +END; +$$; + +-- UPDATE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF + +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[1,2)'; + +SELECT * FROM for_portion_of_test WHERE id = '[1,2)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; + +-- UPDATE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF + +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_update(); + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test WHERE id = '[2,3)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; + +-- DELETE FOR PORTION OF from a trigger fired by UPDATE FOR PORTION OF + +CREATE TRIGGER fpo_after_update_row + AFTER UPDATE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); + +UPDATE for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + SET name = CONCAT(name, '*') + WHERE id = '[3,4)'; + +SELECT * FROM for_portion_of_test WHERE id = '[3,4)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_update_row ON for_portion_of_test; + +-- DELETE FOR PORTION OF from a trigger fired by DELETE FOR PORTION OF + +CREATE TRIGGER fpo_after_delete_row + AFTER DELETE ON for_portion_of_test + FOR EACH ROW EXECUTE PROCEDURE trg_fpo_delete(); + +DELETE FROM for_portion_of_test + FOR PORTION OF valid_at FROM '2018-05-01' TO '2018-06-01' + WHERE id = '[4,5)'; + +SELECT * FROM for_portion_of_test WHERE id = '[4,5)' ORDER BY id, valid_at; + +DROP TRIGGER fpo_after_delete_row ON for_portion_of_test; + +-- Test with multiranges + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at datemultirange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03)'), daterange('2018-02-04', '2018-03-03')), 'one'), + ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04)')), 'one'), + ('[2,3)', datemultirange(daterange('2018-01-01', '2018-05-01)')), 'two'), + ('[3,4)', datemultirange(daterange('2018-01-01', null)), 'three'); + ; + +-- Updating with FROM/TO +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- Updating with multirange +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)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at; +-- Updating with string coercion +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[2018-03-05,2018-03-10)}') + SET name = 'one^2' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at; +-- Updating with the wrong range subtype fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + SET name = 'one^3' + WHERE id = '[1,2)'; +-- Updating with a non-multirangetype fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at (4) + SET name = 'one^3' + WHERE id = '[1,2)'; +-- Updating with NULL fails +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at (NULL) + SET name = 'one^3' + WHERE id = '[1,2)'; +-- Updating with empty does nothing +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at ('{}') + SET name = 'one^3' + WHERE id = '[1,2)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[1,2)' ORDER BY valid_at; + +-- Deleting with FROM/TO +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2000-01-01' TO '2010-01-01' + SET name = 'one^1' + WHERE id = '[1,2)'; +-- Deleting with multirange +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 WHERE id = '[2,3)' ORDER BY valid_at; +-- Deleting with string coercion +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[2018-03-05,2018-03-20)}') + WHERE id = '[2,3)'; +SELECT * FROM for_portion_of_test2 WHERE id = '[2,3)' ORDER BY valid_at; +-- Deleting with the wrong range subtype fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{[1,4)}'::int4multirange) + WHERE id = '[2,3)'; +-- Deleting with a non-multirangetype fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at (4) + WHERE id = '[2,3)'; +-- Deleting with NULL fails +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at (NULL) + WHERE id = '[2,3)'; +-- Deleting with empty does nothing +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at ('{}') + WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; + +-- Test with a custom range type + +CREATE TYPE mydaterange AS range(subtype=date); + +CREATE TABLE for_portion_of_test2 ( + id int4range NOT NULL, + valid_at mydaterange NOT NULL, + name text NOT NULL, + CONSTRAINT for_portion_of_test2_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +INSERT INTO for_portion_of_test2 (id, valid_at, name) VALUES + ('[1,2)', '[2018-01-02,2018-02-03)', 'one'), + ('[1,2)', '[2018-02-03,2018-03-03)', 'one'), + ('[1,2)', '[2018-03-03,2018-04-04)', 'one'), + ('[2,3)', '[2018-01-01,2018-05-01)', 'two'), + ('[3,4)', '[2018-01-01,)', 'three'); + ; + +UPDATE for_portion_of_test2 + FOR PORTION OF valid_at FROM '2018-01-10' TO '2018-02-10' + SET name = 'one^1' + WHERE id = '[1,2)'; + +DELETE FROM for_portion_of_test2 + FOR PORTION OF valid_at FROM '2018-01-15' TO '2018-02-15' + WHERE id = '[2,3)'; + +SELECT * FROM for_portion_of_test2 ORDER BY id, valid_at; + +DROP TABLE for_portion_of_test2; +DROP TYPE mydaterange; + +-- Test FOR PORTION OF against a partitioned table. +-- temporal_partitioned_1 has the same attnums as the root +-- temporal_partitioned_3 has the different attnums from the root +-- temporal_partitioned_5 has the different attnums too, but reversed + +CREATE TABLE temporal_partitioned ( + id int4range, + valid_at daterange, + name text, + CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +) PARTITION BY LIST (id); +CREATE TABLE temporal_partitioned_1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); +CREATE TABLE temporal_partitioned_3 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); +CREATE TABLE temporal_partitioned_5 PARTITION OF temporal_partitioned FOR VALUES IN ('[5,6)', '[6,7)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_3; +ALTER TABLE temporal_partitioned_3 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_3 ADD COLUMN id int4range NOT NULL, ADD COLUMN valid_at daterange NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_3 FOR VALUES IN ('[3,4)', '[4,5)'); + +ALTER TABLE temporal_partitioned DETACH PARTITION temporal_partitioned_5; +ALTER TABLE temporal_partitioned_5 DROP COLUMN id, DROP COLUMN valid_at; +ALTER TABLE temporal_partitioned_5 ADD COLUMN valid_at daterange NOT NULL, ADD COLUMN id int4range NOT NULL; +ALTER TABLE temporal_partitioned ATTACH PARTITION temporal_partitioned_5 FOR VALUES IN ('[5,6)', '[6,7)'); + +INSERT INTO temporal_partitioned (id, valid_at, name) VALUES + ('[1,2)', daterange('2000-01-01', '2010-01-01'), 'one'), + ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'), + ('[5,6)', daterange('2000-01-01', '2010-01-01'), 'five'); + +SELECT * FROM temporal_partitioned; + +-- Update without moving within partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'one^1' + WHERE id = '[1,2)'; + +-- Update without moving within partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'three^1' + WHERE id = '[3,4)'; + +-- Update without moving within partition 5 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-03-01' TO '2000-04-01' + SET name = 'five^1' + WHERE id = '[5,6)'; + +-- Move from partition 1 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'one^2', + id = '[4,5)' + WHERE id = '[1,2)'; + +-- Move from partition 3 to partition 1 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'three^2', + id = '[2,3)' + WHERE id = '[3,4)'; + +-- Move from partition 5 to partition 3 +UPDATE temporal_partitioned FOR PORTION OF valid_at FROM '2000-06-01' TO '2000-07-01' + SET name = 'five^2', + id = '[3,4)' + WHERE id = '[5,6)'; + +-- Update all partitions at once (each with leftovers) + +SELECT * FROM temporal_partitioned ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_1 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_3 ORDER BY id, valid_at; +SELECT * FROM temporal_partitioned_5 ORDER BY id, valid_at; + +DROP TABLE temporal_partitioned; + +RESET datestyle; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 9f21c2945bd..95a46854b37 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -783,6 +783,33 @@ 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) +); +-- UPDATE requires select permission on the valid_at column (but not update): +GRANT SELECT (c1) ON t1 TO regress_priv_user2; +GRANT UPDATE (c1) ON t1 TO regress_priv_user2; +GRANT SELECT (c1, valid_at) ON t1 TO regress_priv_user3; +GRANT UPDATE (c1) 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; +-- DELETE requires select permission on the valid_at column: +GRANT DELETE ON t1 TO regress_priv_user2; +GRANT DELETE ON t1 TO regress_priv_user3; +SET SESSION AUTHORIZATION regress_priv_user2; +DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01'; +SET SESSION AUTHORIZATION regress_priv_user3; +DELETE FROM t1 FOR PORTION OF valid_at FROM '2000-01-01' TO '2001-01-01'; +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 1635adde2d4..f7646999bd4 100644 --- a/src/test/regress/sql/updatable_views.sql +++ b/src/test/regress/sql/updatable_views.sql @@ -1889,6 +1889,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 order by id, valid_at; +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 order by id, valid_at; +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 order by id, valid_at; + -- 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 77be6953575..b15679d675e 100644 --- a/src/test/regress/sql/without_overlaps.sql +++ b/src/test/regress/sql/without_overlaps.sql @@ -2,7 +2,7 @@ -- -- We leave behind several tables to test pg_dump etc: -- temporal_rng, temporal_rng2, --- temporal_fk_rng2rng. +-- temporal_fk_rng2rng, temporal_fk2_rng2rng. SET datestyle TO ISO, YMD; @@ -632,6 +632,20 @@ INSERT INTO temporal3 (id, valid_at, id2, name) ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01' + SET name = name || '1'; +UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01' + SET name = name || '2' + WHERE id = '[2,3)'; +SELECT * FROM temporal3 ORDER BY id, valid_at; +-- conflicting id only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3'); +-- conflicting id2 only: +INSERT INTO temporal3 (id, valid_at, id2, name) + VALUES + ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3'); DROP TABLE temporal3; -- @@ -667,9 +681,23 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- temporal UNIQUE: @@ -685,9 +713,23 @@ INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); -SELECT * FROM temporal_partitioned ORDER BY id, valid_at; -SELECT * FROM tp1 ORDER BY id, valid_at; -SELECT * FROM tp2 ORDER BY id, valid_at; +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + SET name = 'one2' + WHERE id = '[1,2)'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25' + SET id = '[4,5)' + WHERE name = 'one'; +UPDATE temporal_partitioned + FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' + SET id = '[2,3)' + WHERE name = 'three'; +DELETE FROM temporal_partitioned + FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15' + WHERE id = '[3,4)'; +SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at; DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY @@ -1291,6 +1333,18 @@ COMMIT; -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); +-- changing an unreferenced part is okay: +UPDATE temporal_rng + FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_rng + FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' + SET id = '[7,8)' + WHERE id = '[5,6)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') @@ -1338,6 +1392,18 @@ BEGIN; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); COMMIT; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03' +WHERE id = '[5,6)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; +-- deleting just a part fails: +DELETE FROM temporal_rng +FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10' +WHERE id = '[5,6)'; +SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at; +SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at; -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); @@ -1356,12 +1422,13 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE RESTRICT; -- --- test ON UPDATE/DELETE options +-- rng2rng test ON UPDATE/DELETE options -- -- test FK referenced updates CASCADE +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1369,8 +1436,9 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE CASCADE ON UPDATE CASCADE; -- test FK referenced updates SET NULL -INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'), '[9,10)'); +TRUNCATE temporal_rng, temporal_fk_rng2rng; +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) @@ -1378,9 +1446,10 @@ ALTER TABLE temporal_fk_rng2rng ON DELETE SET NULL ON UPDATE SET NULL; -- test FK referenced updates SET DEFAULT +TRUNCATE temporal_rng, temporal_fk_rng2rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); -INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021-01-01')); -INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('2018-01-01', '2021-01-01'), '[12,13)'); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01')); +INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)'); ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', ADD CONSTRAINT temporal_fk_rng2rng_fk @@ -1716,6 +1785,20 @@ BEGIN; WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; -- changing the scalar part fails: +UPDATE temporal_mltrng SET id = '[7,8)' + WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); +-- changing an unreferenced part is okay: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- changing just a part fails: +UPDATE temporal_mltrng + FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) + SET id = '[7,8)' + WHERE id = '[5,6)'; +-- then delete the objecting FK record and the same PK update succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); @@ -1760,6 +1843,17 @@ BEGIN; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; +-- deleting an unreferenced part is okay: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03'))) +WHERE id = '[5,6)'; +-- deleting just a part fails: +DELETE FROM temporal_mltrng +FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10'))) +WHERE id = '[5,6)'; +-- then delete the objecting FK record and the same PK delete succeeds: +DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)'; +DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); -- -- FK between partitioned tables: ranges diff --git a/src/test/subscription/t/034_temporal.pl b/src/test/subscription/t/034_temporal.pl index 66955e1b799..f572a105a0f 100644 --- a/src/test/subscription/t/034_temporal.pl +++ b/src/test/subscription/t/034_temporal.pl @@ -137,6 +137,7 @@ 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"); +# No need to test again with FOR PORTION OF ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); @@ -144,6 +145,13 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_no_key DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_no_key DEFAULT"); $node_publisher->wait_for_catchup('sub1'); @@ -165,16 +173,24 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'" +); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk DEFAULT'); # replicate with a unique key: @@ -192,6 +208,7 @@ 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"); +# No need to test again with FOR PORTION OF ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); @@ -199,6 +216,13 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_unique DEFAULT"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_unique DEFAULT"); $node_publisher->wait_for_catchup('sub1'); @@ -287,16 +311,24 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_no_key SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_no_key FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'" +); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_no_key ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_no_key FULL'); # replicate with a primary key: @@ -310,16 +342,24 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'" +); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk FULL'); # replicate with a unique key: @@ -333,16 +373,24 @@ $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 FULL'); # cleanup @@ -425,16 +473,24 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_pk SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_pk FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'" +); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_pk ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_pk USING INDEX'); # replicate with a unique key: @@ -448,16 +504,24 @@ $node_publisher->safe_psql( $node_publisher->safe_psql('postgres', "UPDATE temporal_unique SET a = 'b' WHERE id = '[2,3)'"); +$node_publisher->safe_psql('postgres', + "UPDATE temporal_unique FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET a = 'c' WHERE id = '[2,3)'" +); $node_publisher->safe_psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); +$node_publisher->safe_psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); $node_publisher->wait_for_catchup('sub1'); $result = $node_subscriber->safe_psql('postgres', "SELECT * FROM temporal_unique ORDER BY id, valid_at"); is( $result, qq{[1,2)|[2000-01-01,2010-01-01)|a -[2,3)|[2000-01-01,2010-01-01)|b +[2,3)|[2000-01-01,2001-01-01)|b +[2,3)|[2001-01-01,2002-01-01)|c +[2,3)|[2003-01-01,2010-01-01)|b [4,5)|[2000-01-01,2010-01-01)|a}, 'replicated temporal_unique USING INDEX'); # cleanup @@ -543,6 +607,7 @@ 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"); +# No need to test again with FOR PORTION OF ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_no_key WHERE id = '[3,4)'"); @@ -550,6 +615,13 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_no_key NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_no_key FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_no_key" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE temporal_no_key NOTHING"); $node_publisher->wait_for_catchup('sub1'); @@ -575,6 +647,7 @@ 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"); +# No need to test again with FOR PORTION OF ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_pk WHERE id = '[3,4)'"); @@ -582,6 +655,13 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_pk NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_pk FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_pk" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE temporal_pk NOTHING"); $node_publisher->wait_for_catchup('sub1'); @@ -607,6 +687,7 @@ 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"); +# No need to test again with FOR PORTION OF ($result, $stdout, $stderr) = $node_publisher->psql('postgres', "DELETE FROM temporal_unique WHERE id = '[3,4)'"); @@ -614,6 +695,13 @@ is( $stderr, qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), "can't DELETE temporal_unique NOTHING"); +($result, $stdout, $stderr) = $node_publisher->psql('postgres', + "DELETE FROM temporal_unique FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01' WHERE id = '[2,3)'" +); +is( $stderr, + qq(psql::1: ERROR: cannot delete from table "temporal_unique" because it does not have a replica identity and publishes deletes +HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.), + "can't DELETE FOR PORTION OF temporal_unique NOTHING"); $node_publisher->wait_for_catchup('sub1'); diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 8e9c06547d6..5bc517602b1 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -865,6 +865,9 @@ ForBothState ForEachState ForFiveState ForFourState +ForPortionOfClause +ForPortionOfExpr +ForPortionOfState ForThreeState ForeignAsyncConfigureWait_function ForeignAsyncNotify_function -- 2.39.5