From 1b5948d582f7162c2f3ba719fd9ea43334c09555 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 21:38:40 -0700 Subject: [PATCH v57 04/11] Document temporal update/delete The FOR PORTION OF syntax will also be documented in the reference pages for UPDATE and DELETE, but this commit adds a conceptual description to the Temporal Tables chapter, as well as a glossary term for "temporal leftovers". Author: Paul A. Jungwirth --- doc/src/sgml/glossary.sgml | 15 +++ doc/src/sgml/images/Makefile | 4 +- doc/src/sgml/images/temporal-delete.svg | 41 ++++++++ doc/src/sgml/images/temporal-delete.txt | 12 +++ doc/src/sgml/images/temporal-update.svg | 45 +++++++++ doc/src/sgml/images/temporal-update.txt | 12 +++ doc/src/sgml/temporal.sgml | 123 +++++++++++++++++++++++- 7 files changed, 249 insertions(+), 3 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 diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index f4d5783d1d5..e009ede5943 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1916,6 +1916,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 where 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/temporal-delete.svg b/doc/src/sgml/images/temporal-delete.svg new file mode 100644 index 00000000000..3665750d7d6 --- /dev/null +++ b/doc/src/sgml/images/temporal-delete.svg @@ -0,0 +1,41 @@ + + + + + + + + + + + + + + + + + + + + + + + products + (5, $5, + [1 Jan 2020,1 Aug 2021)) + 2020 + 2021 + 2022 + 2023 + products + (5, $12, + [1 Sep 2023,1 Mar 2025)) + 2024 + 2025 + products + (5, $8, + [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..84b610f8f58 --- /dev/null +++ b/doc/src/sgml/images/temporal-delete.txt @@ -0,0 +1,12 @@ + + ++----------------------------+ +-------------------------------+--------------------------+ +| cGRE | | cGRE | cGRE | +| products | | products | products | +| (5, $5, | | (5, $12, | (5, $8, | +| [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..4f137558272 --- /dev/null +++ b/doc/src/sgml/images/temporal-update.svg @@ -0,0 +1,45 @@ + + + + + + + + + + + + + + + + + + + + + + + + products + (5, $5, + [1 Jan 2020,1 Jan 2022)) + 2020 + 2021 + products + (5, $8, + [1 Jan 2022,1 Sep 2023)) + 2022 + 2023 + products + (5, $12, + [1 Sep 2023,1 Mar 2025)) + 2024 + 2025 + products + (5, $8, + [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..70c31b5e050 --- /dev/null +++ b/doc/src/sgml/images/temporal-update.txt @@ -0,0 +1,12 @@ + + ++-----------------------------------+-----------------------------+----------------------------+------------------------------+ +| cGRE | cGRE | cGRE | cGRE | +| products | products | products | products | +| (5, $5, | (5, $8, | (5, $12, | (5, $8, | +| [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/temporal.sgml b/doc/src/sgml/temporal.sgml index f54b0a6b284..ef2701e8670 100644 --- a/doc/src/sgml/temporal.sgml +++ b/doc/src/sgml/temporal.sgml @@ -262,8 +262,127 @@ ALTER TABLE variants Temporal Update and Delete - PostgreSQL does not yet support special - syntax to update and delete portions of history in temporal tables. + Special syntax is available to update and delete from temporal + tables. (No extra syntax is required to insert into them: the user just + provides the application time like any other attribute.) 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 interval, and new rows are inserted to preserve the history + that was not targeted. + + + + The syntax for a temporal update is: + + +UPDATE products + FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01' + AS p + SET price = 12 + WHERE id = 5; + + + Using the example table introduced already, this command will + update the second record for product 5. It will set the price to 12 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 three rows for product 5: + + + id | price | valid_at +----+-------+------------------------- + 5 | 5 | [2020-01-01,2022-01-01) + 5 | 8 | [2022-01-01,2023-09-01) + 5 | 12 | [2023-09-01,2025-03-01) + 5 | 8 | [2025-03-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' + AS p +WHERE id = 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 for product 5 would now be: + + + id | price | valid_at +----+-------+------------------------- + 5 | 5 | [2020-01-01,2021-08-01) + 5 | 12 | [2023-09-01,2025-03-01) + 5 | 8 | [2025-03-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 rangetype 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. -- 2.39.5