v53-0002-Document-temporal-foreign-keys.patch
application/octet-stream
Filename: v53-0002-Document-temporal-foreign-keys.patch
Type: application/octet-stream
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v53-0002
Subject: Document temporal foreign keys
| File | + | − |
|---|---|---|
| doc/src/sgml/images/Makefile | 2 | 1 |
| doc/src/sgml/images/temporal-references.svg | 37 | 0 |
| doc/src/sgml/images/temporal-references.txt | 21 | 0 |
| doc/src/sgml/temporal.sgml | 67 | 0 |
From 2b8451f1de2d0a30bebbceddd46c0729ade0df29 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 23:23:28 -0700
Subject: [PATCH v53 02/10] Document temporal foreign keys
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
doc/src/sgml/images/Makefile | 3 +-
doc/src/sgml/images/temporal-references.svg | 37 ++++++++++++
doc/src/sgml/images/temporal-references.txt | 21 +++++++
doc/src/sgml/temporal.sgml | 67 +++++++++++++++++++++
4 files changed, 127 insertions(+), 1 deletion(-)
create mode 100644 doc/src/sgml/images/temporal-references.svg
create mode 100644 doc/src/sgml/images/temporal-references.txt
diff --git a/doc/src/sgml/images/Makefile b/doc/src/sgml/images/Makefile
index 1d99d4e30c8..fd55b9ad23f 100644
--- a/doc/src/sgml/images/Makefile
+++ b/doc/src/sgml/images/Makefile
@@ -6,7 +6,8 @@ ALL_IMAGES = \
genetic-algorithm.svg \
gin.svg \
pagelayout.svg \
- temporal-entities.svg
+ temporal-entities.svg \
+ temporal-references.svg
DITAA = ditaa
DOT = dot
diff --git a/doc/src/sgml/images/temporal-references.svg b/doc/src/sgml/images/temporal-references.svg
new file mode 100644
index 00000000000..f9091ac9b0a
--- /dev/null
+++ b/doc/src/sgml/images/temporal-references.svg
@@ -0,0 +1,37 @@
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 970 350" width="970" height="350" 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="970" height="350" style="fill: #ffffff"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 133.0 L925.0 133.0 L925.0 63.0 L385.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#ffff33" d="M205.0 133.0 L205.0 203.0 L655.0 203.0 L655.0 133.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#ffff33" d="M845.0 203.0 L845.0 273.0 L425.0 273.0 L425.0 203.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M385.0 63.0 L385.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="M205.0 294.0 L205.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M385.0 294.0 L385.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 294.0 L25.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M565.0 294.0 L565.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M745.0 294.0 L745.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M925.0 294.0 L925.0 307.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, [1 Jan 2020,1 Jan 2022))</text>
+ <text x="200" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+ <text x="220" y="166" font-family="Courier" font-size="15" stroke="none" fill="#000000">variants</text>
+ <text x="220" y="180" font-family="Courier" font-size="15" stroke="none" fill="#000000">(8, 5, 'Medium', [1 Jan 2021,1 Jun 2023))</text>
+ <text x="20" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+ <text x="440" y="236" font-family="Courier" font-size="15" stroke="none" fill="#000000">variants</text>
+ <text x="440" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">(9, 5, 'XXL', [1 Mar 2022,1 Jun 2024))</text>
+ <text x="400" 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, [1 Jan 2022,))</text>
+ <text x="560" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+ <text x="380" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+ <text x="929" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+ <text x="740" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+ </g>
+</svg>
diff --git a/doc/src/sgml/images/temporal-references.txt b/doc/src/sgml/images/temporal-references.txt
new file mode 100644
index 00000000000..01cad1bcf8b
--- /dev/null
+++ b/doc/src/sgml/images/temporal-references.txt
@@ -0,0 +1,21 @@
+
+
++-----------------------------------+-----------------------------------------------------+
+| cGRE | cGRE |
+| products | products |
+| (5, $5, [1 Jan 2020,1 Jan 2022)) | (5, $8, [1 Jan 2022,)) |
+| | |
++-----------------+-----------------+--------------------------+--------------------------+
+ | cYEL |
+ | variants |
+ | (8, 5, 'Medium', [1 Jan 2021,1 Jun 2023)) |
+ | |
+ +---------------------+----------------------+------------------+
+ | cYEL |
+ | variants |
+ | (9, 5, 'XXL', [1 Mar 2022,1 Jun 2024)) |
+ | |
+ +-----------------------------------------+
+
+| | | | | |
+2020 2021 2022 2023 2024 ...
diff --git a/doc/src/sgml/temporal.sgml b/doc/src/sgml/temporal.sgml
index 5d1f697fee8..4c4febb94b4 100644
--- a/doc/src/sgml/temporal.sgml
+++ b/doc/src/sgml/temporal.sgml
@@ -162,6 +162,73 @@ command.
</para>
</sect2>
+ <sect2 id="application-time-foreign-keys">
+ <title>Temporal Foreign Keys</title>
+
+ <para>
+ 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 <literal>products</literal> table is
+ referenced by a <literal>variants</literal> table, and a variant of
+ product 5 has an application-time of
+ <literal>[2020-01-01,2026-01-01)</literal>, then product 5 must exist
+ throughout that period.
+ </para>
+
+ <figure id="temporal-references-figure">
+ <title>Temporal Foreign Key Example</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/temporal-references.svg" format="SVG" width="100%"/>
+ </imageobject>
+ </mediaobject>
+ </figure>
+
+ <para>
+ <xref linkend="temporal-references-figure"/> 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.
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ The syntax to declare a temporal foreign key is:
+
+<programlisting>
+ALTER TABLE variants
+ ADD CONSTRAINT variants_id_valid_at_fkey
+ FOREIGN KEY (id, PERIOD valid_at)
+ REFERENCES products (id, PERIOD valid_at);
+</programlisting>
+
+ Note that the keyword <literal>PERIOD</literal> must be used for application-time column
+ in both the referencing and referenced table.
+ </para>
+
+ <para>
+ A temporal primary key or unique constraint matching the referenced columns
+ must exist on the referenced table.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> supports <literal>NO ACTION</literal> temporal foreign keys,
+ but not <literal>RESTRICT</literal>, <literal>CASCADE</literal>, <literal>SET NULL</literal>,
+ or <literal>SET DEFAULT</literal>.
+ </para>
+ </sect2>
+
<sect2 id="application-time-update-delete">
<title>Temporal Update and Delete</title>
--
2.45.0