From f14fefcf8764b96c75afa076312d34d25977c49c Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" 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 --- 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 ( + + + Temporal Tables + + + temporal + + + + 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. + + + + Application Time + + + application time + + + + Application time 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. + + + + The following SQL creates a temporal table that can store application time: + +CREATE TABLE products ( + id integer NOT NULL, + price decimal NOT NULL, + valid_at daterange NOT NULL +); + + + + + Records in a temporal table can be plotted on a timeline, as in + . 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. + + +
+ Application Time Example + + + + + +
+ + + In a table, these records would be: + + 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) + + + + + 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. + + + + 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. + + + + Temporal Primary Keys and Unique Constraints + + + 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 NOT NULL, 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. + + + + The syntax to create a temporal primary key is as follows: + + +ALTER TABLE products + ADD CONSTRAINT products_pkey + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); + + + In this example, id is the non-temporal part of + the key, and valid_at is a range column containing + the application time. You can also create the primary key as part of + the CREATE + TABLE command. + + + + The WITHOUT OVERLAPS column must be NOT + NULL (like the other parts of the key). In addition it may + not contain empty values: a rangetype of 'empty' or + a multirange of {}. An empty application time would + have no meaning. + + + + It is also possible to create a temporal unique constraint that is + not a primary key. The syntax is similar: + + +ALTER TABLE products + ADD CONSTRAINT products_id_valid_at_key + UNIQUE (id, valid_at WITHOUT OVERLAPS); + + + You can also create the unique constraint as part of the CREATE TABLE + command. + + + + 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). + + + + Temporal primary keys and unique constraints are backed by + GiST indexes rather than B-Tree indexes. In + practice, creating a temporal primary key or exclusion constraint + requires installing the extension, so that + the database has opclasses for the non-temporal parts of the key. + + + + Temporal primary keys and unique constraints have the same behavior + as exclusion constraints, + where each regular key part is compared with equality, and the application + time is compared with overlaps, for example EXCLUDE USING gist + (id WITH =, valid_at WITH &&). The only difference is + that they also forbid an empty application time. + + + + + + System Time + + + system time + + + + System time refers to the history of the + database table, not the entity it describes. It captures when each row + was inserted/updated/deleted. + + + + PostgreSQL does not currently support + system time, but there are several extensions that provide its + functionality. See + the SQL:2011 + Temporal wiki page for possibilities. + + + + Modifying Tables 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 @@ + + Application time + + + In a temporal table, + the dimension of time that represents when the entity described by the table + changed (as opposed to the table itself). + + + For more information, see + . + + + + Asynchronous I/O AIO @@ -1847,6 +1862,22 @@ + + System time + + + In a temporal table, + 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. + + + For more information, see + . + + + + Table @@ -1885,6 +1916,22 @@ + + Temporal table + + + Tables + that track application time + or system time (or both). + Not to be confused with temporary tables. + + + For more information, see + . + + + + Temporary table 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 @@ + + + + + + + + + + + + + + + + + + + + + products + (5, 5.00, [1 Jan 2020,1 Jan 2022)) + 2021 + products + (6, 9.00, [1 Jan 2021,1 Jan 2024)) + 2020 + products + (5, 8.00, [1 Jan 2022,)) + 2023 + 2022 + 2024 + ... + + 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