From 7e8ff60f2f04ecd3d28bff02c0e4c25b4ad84f72 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 17:12:10 -0700 Subject: [PATCH v57 01/11] Add docs chapter for temporal tables This commit tries to outline the complete functionality described by the SQL:2011 standard, which we hope to achieve. So it includes sections for Application Time and System Time, but it notes that System Time is not yet implemented. Likewise it covers temporal primary keys and unique constraints in detail, but it only notes that temporal update/delete are not yet supported. Temporal foreign keys and periods are documented in the next commits. This commit also adds glossary entries for temporal table, application time, and system time. Author: Paul A. Jungwirth --- doc/src/sgml/filelist.sgml | 1 + 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 ++ doc/src/sgml/postgres.sgml | 1 + doc/src/sgml/temporal.sgml | 213 ++++++++++++++++++++++ 7 files changed, 314 insertions(+), 1 deletion(-) create mode 100644 doc/src/sgml/images/temporal-entities.svg create mode 100644 doc/src/sgml/images/temporal-entities.txt create mode 100644 doc/src/sgml/temporal.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index ac66fcbdb57..c7905ae0052 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -30,6 +30,7 @@ + diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml index 8651f0cdb91..f4d5783d1d5 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..d5ee6a77e77 --- /dev/null +++ b/doc/src/sgml/images/temporal-entities.svg @@ -0,0 +1,34 @@ + + + + + + + + + + + + + + + + + + + + + products + (5, $5, [1 Jan 2020,1 Jan 2022)) + 2021 + products + (6, $9, [1 Jan 2021,1 Jan 2024)) + 2020 + products + (5, $8, [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..b869682efd7 --- /dev/null +++ b/doc/src/sgml/images/temporal-entities.txt @@ -0,0 +1,16 @@ + + ++-----------------------------------+-----------------------------------------------------+ +| cGRE | cGRE | +| products | products | +| (5, $5, [1 Jan 2020,1 Jan 2022)) | (5, $8, [1 Jan 2022,)) | +| | | ++-----------------+-----------------+-----------------------------------+-----------------+ + | cGRE | + | products | + | (6, $9, [1 Jan 2021,1 Jan 2024)) | + | | + +-----------------------------------------------------+ + +| | | | | | +2020 2021 2022 2023 2024 ... diff --git a/doc/src/sgml/postgres.sgml b/doc/src/sgml/postgres.sgml index af476c82fcc..5eb03505cf1 100644 --- a/doc/src/sgml/postgres.sgml +++ b/doc/src/sgml/postgres.sgml @@ -112,6 +112,7 @@ break is not needed in a wider output rendering. &textsearch; &mvcc; &perform; + &temporal; ∥ diff --git a/doc/src/sgml/temporal.sgml b/doc/src/sgml/temporal.sgml new file mode 100644 index 00000000000..5d1f697fee8 --- /dev/null +++ b/doc/src/sgml/temporal.sgml @@ -0,0 +1,213 @@ + + + + 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. + + + + 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 starting January 1, 2020, but then became $8 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, + then canceled January 1, 2024. + + +
+ Application Time Example + + + + + +
+ + + In a table, these records would be: + + id | price | valid_at +----+-------+------------------------- + 5 | 5 | [2020-01-01,2023-01-01) + 5 | 8 | [2023-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 , 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. + + + + + Temporal Update and Delete + + + PostgreSQL does not yet support special + syntax to update and delete portions of history in temporal tables. + + + + + + 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. + + + + Like application time, system time has two timestamps: a start time + and an end time. The start time shows when the row was added (either by + an insert or an update), and the end time shows when it stopped being + asserted (either by an update or a delete). The database maintains + these values automatically; the user is not able to set them. + + + + If a query filters rows for those with a system time containing a + given moment in time, the result is equivalent to a non-temporal table + from that moment. In that way, you can ask what the table asserted at + different times in the past. This is useful for auditing, compliance, + and debugging. + + + + 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. + + + + -- 2.39.5