v52-0004-Document-temporal-update-delete.patch

From 23b1f7dcc84df54082cb21c3358f529826b2d5d9 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 21:38:40 -0700
Subject: [PATCH v52 04/10] 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 <pj@illuminatedcomputing.com>
---
 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              | 123 +++++++++++++++++++++++-
 7 files changed, 255 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 13766a3947d..cab3647d7eb 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1913,6 +1913,27 @@
    </glossdef>
   </glossentry>
 
+  <glossentry id="glossary-temporal-leftovers">
+   <glossterm>Temporal leftovers</glossterm>
+   <glossdef>
+    <para>
+     <glossterm linkend="glossary-table">Tables</glossterm> that exist either
+     for the lifetime of a
+     <glossterm linkend="glossary-session">session</glossterm> or a
+     <glossterm linkend="glossary-transaction">transaction</glossterm>, as
+     specified at the time of creation.
+     The data in them is not visible to other sessions, and is not
+     <glossterm linkend="glossary-logged">logged</glossterm>.
+     Temporary tables are often used to store intermediate data for a
+     multi-step operation.
+    </para>
+    <para>
+     For more information, see
+     <xref linkend="sql-createtable"/>.
+    </para>
+   </glossdef>
+  </glossentry>
+
   <glossentry id="glossary-temporal-table">
    <glossterm>Temporal table</glossterm>
    <glossdef>
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 @@
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1330 224" width="1330" height="224" shape-rendering="geometricPrecision" version="1.0">
+  <defs>
+    <filter id="f2" x="0" y="0" width="200%" height="200%">
+      <feOffset result="offOut" in="SourceGraphic" dx="5" dy="5"/>
+      <feGaussianBlur result="blurOut" in="offOut" stdDeviation="3"/>
+      <feBlend in="SourceGraphic" in2="blurOut" mode="normal"/>
+    </filter>
+  </defs>
+  <g stroke-width="1" stroke-linecap="square" stroke-linejoin="round">
+    <rect x="0" y="0" width="1330" height="224" style="fill: #ffffff"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M685.0 63.0 L685.0 147.0 L1005.0 147.0 L1005.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M315.0 63.0 L315.0 147.0 L25.0 147.0 L25.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M1005.0 63.0 L1005.0 147.0 L1275.0 147.0 L1275.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M205.0 168.0 L205.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 168.0 L25.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M385.0 168.0 L385.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M565.0 168.0 L565.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M745.0 168.0 L745.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M925.0 168.0 L925.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1105.0 168.0 L1105.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1285.0 168.0 L1285.0 181.0 "/>
+    <text x="46" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="40" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $5,</text>
+    <text x="83" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2020,1 Aug 2021))</text>
+    <text x="20" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+    <text x="200" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+    <text x="380" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+    <text x="560" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+    <text x="712" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="722" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $12,</text>
+    <text x="743" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Sep 2023,1 Mar 2025))</text>
+    <text x="740" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+    <text x="920" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2025</text>
+    <text x="1026" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="1020" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $8,</text>
+    <text x="1056" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Mar 2025,))</text>
+    <text x="1100" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2026</text>
+    <text x="1289" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+  </g>
+</svg>
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 @@
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1330 224" width="1330" height="224" shape-rendering="geometricPrecision" version="1.0">
+  <defs>
+    <filter id="f2" x="0" y="0" width="200%" height="200%">
+      <feOffset result="offOut" in="SourceGraphic" dx="5" dy="5"/>
+      <feGaussianBlur result="blurOut" in="offOut" stdDeviation="3"/>
+      <feBlend in="SourceGraphic" in2="blurOut" mode="normal"/>
+    </filter>
+  </defs>
+  <g stroke-width="1" stroke-linecap="square" stroke-linejoin="round">
+    <rect x="0" y="0" width="1330" height="224" style="fill: #ffffff"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 63.0 L385.0 147.0 L25.0 147.0 L25.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M1285.0 63.0 L1285.0 147.0 L975.0 147.0 L975.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 147.0 L685.0 147.0 L685.0 63.0 L385.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M685.0 63.0 L685.0 147.0 L975.0 147.0 L975.0 63.0 z"/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M205.0 168.0 L205.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 168.0 L25.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M385.0 168.0 L385.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M565.0 168.0 L565.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M745.0 168.0 L745.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M925.0 168.0 L925.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1105.0 168.0 L1105.0 181.0 "/>
+    <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M1285.0 168.0 L1285.0 181.0 "/>
+    <text x="46" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="40" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $5,</text>
+    <text x="86" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2020,1 Jan 2022))</text>
+    <text x="20" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+    <text x="200" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+    <text x="406" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="400" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $8,</text>
+    <text x="445" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Jan 2022,1 Sep 2023))</text>
+    <text x="380" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+    <text x="560" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+    <text x="712" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="722" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $12,</text>
+    <text x="743" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Sep 2023,1 Mar 2025))</text>
+    <text x="740" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+    <text x="920" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2025</text>
+    <text x="996" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+    <text x="990" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, $8,</text>
+    <text x="1026" y="124" font-family="Courier" font-size="15" stroke="none" fill="#000000">[1 Mar 2025,))</text>
+    <text x="1100" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">2026</text>
+    <text x="1289" y="194" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+  </g>
+</svg>
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 ecaba2eed2f..04f25afc385 100644
--- a/doc/src/sgml/temporal.sgml
+++ b/doc/src/sgml/temporal.sgml
@@ -262,8 +262,127 @@ ALTER TABLE variants
    <title>Temporal Update and Delete</title>
 
    <para>
-    <productname>PostgreSQL</productname> 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.
+   </para>
+
+   <para>
+    The syntax for a temporal update is:
+
+<programlisting>
+UPDATE products
+  FOR PORTION OF valid_at FROM '2023-09-01' TO '2025-03-01'
+  AS p
+  SET price = 12
+  WHERE id = 5;
+</programlisting>
+
+    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 <literal>[2023-09-01,2025-03-01)</literal>. 
+    Then, since the row's application time was originally 
+    <literal>[2022-01-01,)</literal>, the command must insert two 
+    <glossterm linkend="glossary-temporal-leftovers">temporal 
+    leftovers</glossterm>: 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:
+
+<programlisting>
+ 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,)
+</programlisting>
+
+    The new history could be plotted as in <xref linkend="temporal-update-figure"/>.
+   </para>
+
+   <figure id="temporal-update-figure">
+    <title>Temporal Update Example</title>
+    <mediaobject>
+     <imageobject>
+      <imagedata fileref="images/temporal-update.svg" format="SVG" width="100%"/>
+     </imageobject>
+    </mediaobject>
+   </figure>
+
+   <para>
+    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
+    <glossterm linkend="glossary-temporal-leftovers">temporal leftovers</glossterm> 
+    are inserted to preserve the untouched history. The syntax for a 
+    temporal delete is:
+
+<programlisting>
+DELETE FROM products
+  FOR PORTION OF valid_at FROM '2021-08-01' TO '2023-09-01'
+  AS p
+WHERE id = 5;
+</programlisting>
+
+    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:
+
+<programlisting>
+ 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,)
+</programlisting>
+
+    The new history could be plotted as in <xref linkend="temporal-delete-figure"/>.
+   </para>
+
+   <figure id="temporal-delete-figure">
+    <title>Temporal Delete Example</title>
+    <mediaobject>
+     <imageobject>
+      <imagedata fileref="images/temporal-delete.svg" format="SVG" width="100%"/>
+     </imageobject>
+    </mediaobject>
+   </figure>
+
+   <para>
+    Instead of using the <literal>FROM ... TO ...</literal> syntax, 
+    temporal update/delete commands can also give the targeted 
+    range/multirange directly, inside parentheses. For example: 
+    <literal>DELETE FROM products FOR PORTION OF valid_at ('[2028-01-01,)') ...</literal>.
+    This syntax is required when application time is stored 
+    in a multirange column.
+   </para>
+
+   <para>
+    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 
+    <literal>range_minus_multi</literal> and 
+    <literal>multirange_minus_multi</literal>
+    (see <xref linkend="functions-range"/>).
+   </para>
+
+   <para>
+    The bounds given to <literal>FOR PORTION OF</literal> must be 
+    constant. Functions like <literal>NOW()</literal> are allowed, but 
+    column references are not.
+   </para>
+
+   <para>
+    When temporal leftovers are inserted, all <literal>INSERT</literal> 
+    triggers are fired, but permission checks for inserting rows are 
+    skipped.
    </para>
   </sect2>
   </sect1>
-- 
2.39.5