From 7e5bfeaf66b7b93f9c5ec511aa83b25cfe759125 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 21:38:40 -0700 Subject: [PATCH v55 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 | 21 +++ 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 | 181 ++++++++++++++++++++---- 7 files changed, 284 insertions(+), 32 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..e917438628a 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1916,6 +1916,27 @@ + + Temporal leftovers + + + Tables that exist either + for the lifetime of a + session or a + transaction, as + specified at the time of creation. + The data in them is not visible to other sessions, and is not + logged. + Temporary tables are often used to store intermediate data for a + multi-step operation. + + + For more information, see + . + + + + 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 aab8917ac0a..4c8fa93d9a0 100644 --- a/doc/src/sgml/temporal.sgml +++ b/doc/src/sgml/temporal.sgml @@ -23,23 +23,23 @@ - A period is metadata attached to a table - uniting two columns, a start time and end time, into one range-like - construct. Periods are used to represent system time and application time. A system-time - period must be named system_time, and an - application-time period can be named anything else. Their names must + A period is metadata attached to a table + uniting two columns, a start time and end time, into one range-like + construct. Periods are used to represent system time and application time. A system-time + period must be named system_time, and an + application-time period can be named anything else. Their names must not conflict with column names from the same table. - Periods are referenced in several temporal operations described in - this chapter: temporal primary keys, unique constraints, foreign keys, - update commands, and delete commands. - PostgreSQL does not yet support periods. + Periods are referenced in several temporal operations described in + this chapter: temporal primary keys, unique constraints, foreign keys, + update commands, and delete commands. + PostgreSQL does not yet support periods. Commands that accept periods instead accept columns with a - rangetype or multirangetype. + rangetype or multirangetype. Support for periods is planned for the future. @@ -195,14 +195,14 @@ command. Temporal Foreign Keys - A temporal foreign key is a reference from one application-time - table to another application-time table. Just as a non-temporal - reference requires a referenced key to exist, so a temporal reference - requires a referenced key to exist, but during whatever history the - reference exists. So if the products table is - referenced by a variants table, and a variant of - product 5 has an application-time of - [2020-01-01,2026-01-01), then product 5 must exist + A temporal foreign key is a reference from one application-time + table to another application-time table. Just as a non-temporal + reference requires a referenced key to exist, so a temporal reference + requires a referenced key to exist, but during whatever history the + reference exists. So if the products table is + referenced by a variants table, and a variant of + product 5 has an application-time of + [2020-01-01,2026-01-01), then product 5 must exist throughout that period. @@ -216,19 +216,19 @@ command. - plots product 5 (in - green) and two variants referencing it (in yellow) on the same - timeline. Each variant tuple is shown with its id, a product id, a - name, and an application-time. So variant 8 (Medium) was introduced - first, then variant 9 (XXL). Both satisfy the foreign key constraint, + plots product 5 (in + green) and two variants referencing it (in yellow) on the same + timeline. Each variant tuple is shown with its id, a product id, a + name, and an application-time. So variant 8 (Medium) was introduced + first, then variant 9 (XXL). Both satisfy the foreign key constraint, because the referenced product exists throughout their entire history. - Note that a temporal reference need not be fulfilled by a single - row in the referenced table. Product 5 had a price change in the middle - of variant 8's history, but the reference is still valid. The - combination of all matching rows is used to test whether the referenced + Note that a temporal reference need not be fulfilled by a single + row in the referenced table. Product 5 had a price change in the middle + of variant 8's history, but the reference is still valid. The + combination of all matching rows is used to test whether the referenced history contains the referencing row. @@ -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