v59-0001-Add-docs-section-for-temporal-tables-with-primar.patch
text/x-patch
Filename: v59-0001-Add-docs-section-for-temporal-tables-with-primar.patch
Type: text/x-patch
Part: 2
From f14fefcf8764b96c75afa076312d34d25977c49c Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 17:12:10 -0700
Subject: [PATCH v59 01/11] Add docs section for temporal tables, with primary
keys
This section introduces temporal tables, with a focus on Application Time (which
we support) and only a brief mention of System Time (which we don't). It covers
temporal primary keys and unique constraints. Temporal foreign keys are
documented in the next commit. We will document temporal update/delete and
periods as we add those features.
This commit also adds glossary entries for temporal table, application
time, and system time.
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
doc/src/sgml/ddl.sgml | 198 ++++++++++++++++++++++
doc/src/sgml/glossary.sgml | 47 +++++
doc/src/sgml/images/Makefile | 3 +-
doc/src/sgml/images/temporal-entities.svg | 34 ++++
doc/src/sgml/images/temporal-entities.txt | 16 ++
5 files changed, 297 insertions(+), 1 deletion(-)
create mode 100644 doc/src/sgml/images/temporal-entities.svg
create mode 100644 doc/src/sgml/images/temporal-entities.txt
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 65bc070d2e5..74b55005ffe 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1585,6 +1585,204 @@ CREATE TABLE circles (
</para>
</sect1>
+
+ <sect1 id="ddl-temporal-tables">
+ <title>Temporal Tables</title>
+
+ <indexterm zone="ddl-temporal-tables">
+ <primary>temporal</primary>
+ </indexterm>
+
+ <para>
+ Temporal tables allow users to track different dimensions of
+ history. Application time tracks the history of a thing out in the
+ world, and system time tracks the history of the database itself. This
+ chapter describes how to express and manage such histories in temporal
+ tables.
+ </para>
+
+ <sect2 id="ddl-application-time">
+ <title>Application Time</title>
+
+ <indexterm zone="ddl-application-time">
+ <primary>application time</primary>
+ </indexterm>
+
+ <para>
+ <firstterm>Application time</firstterm> refers to a history of the
+ entity described by a table. In a typical non-temporal table, there is
+ single row for each entity. In a temporal table, an entity may have
+ multiple rows, as long as those rows describe non-overlapping periods
+ from its history. Application time requires each row to have a start
+ and end time, expressing when the row is true.
+ </para>
+
+ <para>
+ The following SQL creates a temporal table that can store application time:
+<programlisting>
+CREATE TABLE products (
+ id integer NOT NULL,
+ price decimal NOT NULL,
+ valid_at daterange NOT NULL
+);
+</programlisting>
+ </para>
+
+ <para>
+ Records in a temporal table can be plotted on a timeline, as in
+ <xref linkend="temporal-entities-figure"/>. Here we show three records
+ describing two products. Each record is a tuple with three attributes:
+ the id, the price, and the application time. So product 5 was first
+ offered for 5.00 starting January 1, 2020, but then became 8.00 starting
+ January 1, 2022. Its second record has no specified end time,
+ indicating that it is true indefinitely, or for all future time. The
+ last record shows that product 6 was introduced January 1, 2021 for 9.00,
+ then canceled January 1, 2024.
+ </para>
+
+ <figure id="temporal-entities-figure">
+ <title>Application Time Example</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/temporal-entities.svg" format="SVG" width="100%"/>
+ </imageobject>
+ </mediaobject>
+ </figure>
+
+ <para>
+ In a table, these records would be:
+<programlisting>
+ 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)
+</programlisting>
+ </para>
+
+ <para>
+ We show the application time using rangetype notation, because it
+ is stored as a single column (either a range or multirange). By
+ convention ranges include their start point but exclude their end
+ point. That way two adjacent ranges cover all points without
+ overlapping.
+ </para>
+
+ <para>
+ In principle, a table with application-time ranges/multiranges is
+ equivalent to a table that stores application-time "instants": one for
+ each second, millisecond, nanosecond, or whatever finest granularity is
+ available. But such a table would contain far too many rows, so
+ ranges/multiranges offer an optimization to represent the same
+ information in a compact form. In addition, ranges and multiranges
+ offer a more convenient interface for typical temporal operations,
+ where records change infrequently enough that separate "versions"
+ persist for extended periods of time.
+ </para>
+
+ <sect3 id="ddl-application-time-primary-keys">
+ <title>Temporal Primary Keys and Unique Constraints</title>
+
+ <para>
+ A table with application time has a different concept of entity
+ integrity than a non-temporal table. Temporal entity integrity can be
+ enforced with a temporal primary key. A regular primary key has at
+ least one element, all elements are <literal>NOT NULL</literal>, and
+ the combined value of all elements is unique. A temporal primary key
+ also has at least one such element, but in addition it has a final
+ element that is a rangetype or multirangetype that shows when it was
+ true. The regular parts of the key must be unique for any moment in
+ time, but non-unique records are allowed if their application time does
+ not overlap.
+ </para>
+
+ <para>
+ The syntax to create a temporal primary key is as follows:
+
+<programlisting>
+ALTER TABLE products
+ ADD CONSTRAINT products_pkey
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
+</programlisting>
+
+ In this example, <literal>id</literal> is the non-temporal part of
+ the key, and <literal>valid_at</literal> is a range column containing
+ the application time. You can also create the primary key as part of
+ the <link linkend="sql-createtable"><literal>CREATE
+ TABLE</literal></link> command.
+ </para>
+
+ <para>
+ The <literal>WITHOUT OVERLAPS</literal> column must be <literal>NOT
+ NULL</literal> (like the other parts of the key). In addition it may
+ not contain empty values: a rangetype of <literal>'empty'</literal> or
+ a multirange of <literal>{}</literal>. An empty application time would
+ have no meaning.
+ </para>
+
+ <para>
+ It is also possible to create a temporal unique constraint that is
+ not a primary key. The syntax is similar:
+
+<programlisting>
+ALTER TABLE products
+ ADD CONSTRAINT products_id_valid_at_key
+ UNIQUE (id, valid_at WITHOUT OVERLAPS);
+</programlisting>
+
+ You can also create the unique constraint as part of the <link
+ linkend="sql-createtable"><literal>CREATE TABLE</literal></link>
+ command.
+ </para>
+
+ <para>
+ Temporal unique constraints also forbid empty ranges/multiranges
+ for their application time, although that column is permitted to be
+ null (like other elements of the key).
+ </para>
+
+ <para>
+ Temporal primary keys and unique constraints are backed by
+ <link linkend="gist">GiST indexes</link> rather than B-Tree indexes. In
+ practice, creating a temporal primary key or exclusion constraint
+ requires installing the <xref linkend="btree-gist"/> extension, so that
+ the database has opclasses for the non-temporal parts of the key.
+ </para>
+
+ <para>
+ Temporal primary keys and unique constraints have the same behavior
+ as <link linkend="ddl-constraints-exclusion">exclusion constraints</link>,
+ where each regular key part is compared with equality, and the application
+ time is compared with overlaps, for example <literal>EXCLUDE USING gist
+ (id WITH =, valid_at WITH &&)</literal>. The only difference is
+ that they also forbid an empty application time.
+ </para>
+ </sect3>
+ </sect2>
+
+ <sect2 id="ddl-system-time">
+ <title>System Time</title>
+
+ <indexterm zone="ddl-system-time">
+ <primary>system time</primary>
+ </indexterm>
+
+ <para>
+ <firstterm>System time</firstterm> refers to the history of the
+ database table, not the entity it describes. It captures when each row
+ was inserted/updated/deleted.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> does not currently support
+ system time, but there are several extensions that provide its
+ functionality. See
+ <ulink url="https://wiki.postgresql.org/wiki/SQL2011Temporal">the SQL:2011
+ Temporal wiki page</ulink> for possibilities.
+ </para>
+ </sect2>
+ </sect1>
+
<sect1 id="ddl-alter">
<title>Modifying Tables</title>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index 8651f0cdb91..a76cf5c383f 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -81,6 +81,21 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-application-time">
+ <glossterm>Application time</glossterm>
+ <glossdef>
+ <para>
+ In a <glossterm linkend="glossary-temporal-table">temporal table</glossterm>,
+ the dimension of time that represents when the entity described by the table
+ changed (as opposed to the table itself).
+ </para>
+ <para>
+ For more information, see
+ <xref linkend="ddl-temporal-tables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-aio">
<glossterm>Asynchronous <acronym>I/O</acronym></glossterm>
<acronym>AIO</acronym>
@@ -1847,6 +1862,22 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-system-time">
+ <glossterm>System time</glossterm>
+ <glossdef>
+ <para>
+ In a <glossterm linkend="glossary-temporal-table">temporal table</glossterm>,
+ the dimension of time that represents when the table itself was changed
+ (as opposed to the entity the table describes).
+ Often used for auditing, compliance, and debugging.
+ </para>
+ <para>
+ For more information, see
+ <xref linkend="ddl-temporal-tables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-table">
<glossterm>Table</glossterm>
<glossdef>
@@ -1885,6 +1916,22 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-temporal-table">
+ <glossterm>Temporal table</glossterm>
+ <glossdef>
+ <para>
+ <glossterm linkend="glossary-table">Tables</glossterm>
+ that track <glossterm linkend="glossary-application-time">application time</glossterm>
+ or <glossterm linkend="glossary-system-time">system time</glossterm> (or both).
+ Not to be confused with <glossterm linkend="glossary-temporary-table">temporary tables</glossterm>.
+ </para>
+ <para>
+ For more information, see
+ <xref linkend="ddl-temporal-tables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-temporary-table">
<glossterm>Temporary table</glossterm>
<glossdef>
diff --git a/doc/src/sgml/images/Makefile b/doc/src/sgml/images/Makefile
index 645519095d0..1d99d4e30c8 100644
--- a/doc/src/sgml/images/Makefile
+++ b/doc/src/sgml/images/Makefile
@@ -5,7 +5,8 @@
ALL_IMAGES = \
genetic-algorithm.svg \
gin.svg \
- pagelayout.svg
+ pagelayout.svg \
+ temporal-entities.svg
DITAA = ditaa
DOT = dot
diff --git a/doc/src/sgml/images/temporal-entities.svg b/doc/src/sgml/images/temporal-entities.svg
new file mode 100644
index 00000000000..7355be472e8
--- /dev/null
+++ b/doc/src/sgml/images/temporal-entities.svg
@@ -0,0 +1,34 @@
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1020 280" width="1020" height="280" 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="1020" height="280" style="fill: #ffffff"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M215.0 133.0 L215.0 203.0 L785.0 203.0 L785.0 133.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M405.0 133.0 L965.0 133.0 L965.0 63.0 L405.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M405.0 63.0 L405.0 133.0 L25.0 133.0 L25.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 224.0 L25.0 237.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M405.0 224.0 L405.0 237.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M215.0 224.0 L215.0 237.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M595.0 224.0 L595.0 237.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M785.0 224.0 L785.0 237.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M975.0 224.0 L975.0 237.0 "/>
+ <text x="40" 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.00, [1 Jan 2020,1 Jan 2022))</text>
+ <text x="210" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+ <text x="230" y="166" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="230" y="180" font-family="Courier" font-size="15" stroke="none" fill="#000000">(6, 9.00, [1 Jan 2021,1 Jan 2024))</text>
+ <text x="20" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+ <text x="420" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="420" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 8.00, [1 Jan 2022,))</text>
+ <text x="590" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+ <text x="400" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+ <text x="780" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+ <text x="979" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+ </g>
+</svg>
diff --git a/doc/src/sgml/images/temporal-entities.txt b/doc/src/sgml/images/temporal-entities.txt
new file mode 100644
index 00000000000..15a86d2a276
--- /dev/null
+++ b/doc/src/sgml/images/temporal-entities.txt
@@ -0,0 +1,16 @@
+
+
++-------------------------------------+-------------------------------------------------------+
+| cGRE | cGRE |
+| products | products |
+| (5, 5.00, [1 Jan 2020,1 Jan 2022)) | (5, 8.00, [1 Jan 2022,)) |
+| | |
++------------------+------------------+-------------------------------------+-----------------+
+ | cGRE |
+ | products |
+ | (6, 9.00, [1 Jan 2021,1 Jan 2024)) |
+ | |
+ +--------------------------------------------------------+
+
+| | | | | |
+2020 2021 2022 2023 2024 ...
--
2.39.5