From 248f969885e59d47c17fdcc0ae411118802c2d1c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 21:38:40 -0700 Subject: [PATCH v59 03/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 new section to the DML chapter, called "Updating and Deleting Temporal Data," giving a conceptual description, as well as a glossary term for "temporal leftovers". The SQL standard doesn't give any term for the supplementary INSERTs after an UPDATE/DELETE FOR PORTION OF, but it is really handy to have a name for them. Author: Paul A. Jungwirth --- doc/src/sgml/dml.sgml | 140 ++++++++++++++++++++++++ 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 ++ 7 files changed, 268 insertions(+), 1 deletion(-) 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/dml.sgml b/doc/src/sgml/dml.sgml index 458aee788b7..a899c9ab447 100644 --- a/doc/src/sgml/dml.sgml +++ b/doc/src/sgml/dml.sgml @@ -261,6 +261,146 @@ DELETE FROM products; + + Updating and Deleting Temporal Data + + + Special syntax is available to update and delete from application-time 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. + + + + Recall the example table from , + containing this data: + + + id | price | valid_at +----+-------+------------------------- + 5 | 5 | [2020-01-01,2022-01-01) + 5 | 8 | [2022-01-01,) + 6 | 9 | [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' + AS p + SET price = 12 + WHERE id = 5; + + + That 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 four 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. + + + Returning Data from Modified Rows diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index a76cf5c383f..1f74dca5897 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..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..611df521569 --- /dev/null +++ b/doc/src/sgml/images/temporal-delete.txt @@ -0,0 +1,12 @@ + + ++----------------------------+ +-------------------------------+--------------------------+ +| 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..7e862d89437 --- /dev/null +++ b/doc/src/sgml/images/temporal-update.txt @@ -0,0 +1,12 @@ + + ++-----------------------------------+-----------------------------+----------------------------+------------------------------+ +| 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 ... -- 2.39.5