期待 PostgreSQL 19：是时候了

# 期待 Postgres 19：是时候了 来源：https://www.pgedge.com/blog/looking-forward-to-postgres-19-its-about-time 最近，数据库领域出现了一种新型问题：这些数据在*上周二*看起来是什么样？也许是节前促销开始前的产品价格，或是那次没人想要的部门重组之前员工所属的部门。如果不增加一个完整的审计触发器系统，我们怎么知道在某个具体日期前后数据是什么样子？ SQL:2011 标准早在十多年前就通过临时表（temporal tables）正式确立了一个合适的解决方案。其他数据库引擎相对较快地采纳了其中一部分。特点鲜明地，Postgres 花了些时间。但 Postgres 19 终于将原生的临时表支持带到了舞台上——而且值得等待。 让我们看看我们有什么可用的。 ## 老派方法 在展示闪亮的新功能之前，我们先看看陈旧的老方法，以作对比。假设我们想跟踪产品价格随时间的变化。一个合理的初步尝试可能如下： ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE products ( product_id INT NOT NULL, product_name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, valid_from DATE NOT NULL, valid_to DATE NOT NULL, CONSTRAINT no_time_travel CHECK (valid_from < valid_to) ); ``` 很简单。我们有一个产品、一个价格以及一个价格的有效日期范围。不幸的是，没有什么能阻止我们为同一产品插入两个具有重叠日期范围的行。产品编号 42 可能在同一周二既是 9.99 美元*又*是 14.99 美元。你的会计发现后可能要说些不中听的话了。 这里传统的 Postgres 答案是使用 `btree_gist` (https://www.postgresql.org/docs/current/btree-gist.html) 扩展和一个排除约束： ```sql ALTER TABLE products ADD CONSTRAINT no_overlapping_prices EXCLUDE USING gist ( product_id WITH =, daterange(valid_from, valid_to) WITH && ); ``` 这有效。如果我们尝试插入一个冲突的行，Postgres 会捕获它： ```sql INSERT INTO products VALUES (1, 'Widget', 9.99, '2025-01-01', '2025-07-01'); INSERT INTO products VALUES (1, 'Widget', 12.99, '2025-06-01', '2026-01-01'); ERROR: conflicting key value violates exclusion constraint "no_overlapping_prices" ``` 通过使用 `btree_gist` 解决了问题！那么问题是什么？嗯，有几个问题： - 每个人都知道 BTREE 和一般索引，但 GiST (https://www.postgresql.org/docs/current/gist.html) 是 Postgres 特有的，因此需要有经验才能理解。更不用说它是一个*可选*扩展。 - 排除约束语法相当不直观。文档里能找到，但除此之外没人会认为这是标准方法。 - 表本身没有内置时态感知能力。 基本上，Postgres 并不*理解*这是时态数据。它只是列和一个使用花哨索引类型的深奥约束。每次改变时间范围的更新都需要手动分割和拼接行，这意味着应用程序必须承担时态正确性的全部负担。 这是最低限度，坦白说我们可以做得更好。 ## 时间简史 在 Postgres 中实现适当时态支持的愿望并不新鲜。SQL:2011 标准引入了 `APPLICATION TIME` 时间段、`WITHOUT OVERLAPS` 约束和用于时态 DML 的 `FOR PORTION OF` 语法。2011 年已经是*很久*以前了。 Henrietta Dombrovskaya（朋友们叫她 Hetti）是 Postgres 生态系统中时态数据最早的支持者之一。她与 Chad Slaughter 一起开发了 `pg_bitemporal` (https://github.com/scalegenius/pg_bitemporal) 扩展。这是一个完全在 Postgres 内部使用 PL/pgSQL 管理双时态表的框架。自 2015 年以来，她在多个会议上展示了这些概念，演示了如何同时跟踪*有效时间*（这个事实在现实世界中何时为真？）和*事务时间*（数据库在何时记录了这个事实？）。 这个区别很重要。有效时间表示“这个价格从一月到六月有效”。事务时间是从数据库角度说的，“这一行在 3 月 12 日下午 3:47 被插入，并在 4 月 3 日上午 9:01 被取代”。两者结合产生一个双时态表，可以回答诸如“根据我们当时所知，我们*认为*上周二的价格是多少？” `pg_bitemporal` 方法严重依赖我们之前讨论过的相同 `EXCLUDE USING gist` 机制，但加倍实现：一个排除用于 `effective` 范围（有效时间），另一个用于 `asserted` 范围（事务时间）。表定义看起来像这样： ```sql CREATE TABLE bi_temporal.customers ( cust_nbr INTEGER, cust_nm TEXT, cust_type TEXT, effective_range TSTZRANGE, asserted_range TSTZRANGE, row_created_at TIMESTAMPTZ, EXCLUDE USING gist ( cust_nbr WITH =, effective_range WITH &&, asserted_range WITH && ) ); ``` 这是一个由单个排除约束强制实施的两个时态维度。该扩展还引入了用于双时态插入、更新、更正、停用和删除的函数，以及 Allen 区间关系 (https://en.wikipedia.org/wiki/Allen%27s_interval_algebra) 的实现，用于时态推理。这是在 Postgres 当时提供的基础上构建的*大量*机制。 而且它有效！但扩展只能做到一定程度。它无法改变查询规划器看待时态谓词的方式，无法在引擎级别与约束系统集成，也无法提供原生的 DML 语法。为此，该功能需要进入核心。 现在 Postgres 19 容纳了双时态系统的一半——应用时间。这不是全部，但仍然是朝着正确方向迈出的*巨大*一步。 ## 范围来救场 让我们用 Postgres 19 的方式重建我们的产品表。不再使用单独的 `valid_from` 和 `valid_to` 列，而是使用一个单一的 range 类型 (https://www.postgresql.org/docs/devel/rangetypes.html) 列： ```sql CREATE TABLE products ( product_id INT NOT NULL, product_name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, valid_at DATERANGE NOT NULL, PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) ); ``` 就是这样。不需要 `btree_gist` 扩展。不需要排除约束。主键中的 `WITHOUT OVERLAPS` 子句告诉 Postgres，`product_id` 必须在*任意时间点*唯一，但同一产品可以有多个行，只要它们的 `valid_at` 范围不重叠。 这与我们旧方法相比如何？让我们把它们并排放置。旧方法： ```sql CREATE EXTENSION IF NOT EXISTS btree_gist; -- 范围的单独两列 valid_from DATE NOT NULL, valid_to DATE NOT NULL, -- 约束中的手动范围构造 EXCLUDE USING gist ( product_id WITH =, daterange(valid_from, valid_to) WITH && ) ``` 新方法： ```sql -- 单个范围列，无扩展 valid_at DATERANGE NOT NULL, -- 主键内置时态感知 PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) ``` 更简洁、更具表现力，而且 Postgres 实际上*理解*我们在做什么。在内部，`WITHOUT OVERLAPS` 约束仍然使用 GiST 索引，并且仍然需要 `btree_gist` 来处理键中的非时态列。但 Postgres 在初始化约束时会自动处理该依赖。非常方便。 让我们用一些数据填充表，以便在本文的其余部分使用： ```sql INSERT INTO products VALUES (1, 'Widget', 9.99, '[2025-01-01, 2025-07-01)'), (1, 'Widget', 12.99, '[2025-07-01, 2026-01-01)'), (1, 'Widget', 11.99, '[2026-01-01, 2026-07-01)'), (2, 'Gadget', 24.99, '[2025-01-01, 2025-04-01)'), (2, 'Gadget', 22.99, '[2025-04-01, 2026-01-01)'), (2, 'Gadget', 26.99, '[2026-01-01,)'); SELECT * FROM products ORDER BY product_id, valid_at; product_id | product_name | price | valid_at ------------+--------------+-------+------------------------- 1 | Widget | 9.99 | [2025-01-01,2025-07-01) 1 | Widget | 12.99 | [2025-07-01,2026-01-01) 1 | Widget | 11.99 | [2026-01-01,2026-07-01) 2 | Gadget | 24.99 | [2025-01-01,2025-04-01) 2 | Gadget | 22.99 | [2025-04-01,2026-01-01) 2 | Gadget | 26.99 | [2026-01-01,) ``` 注意范围表示法：`[` 表示包含，`)` 表示排除。所以 `[2025-01-01, 2025-07-01)` 包含 1 月 1 日但*不*包含 7 月 1 日。最后一个 Gadget 行有一个开放式的范围 `[2026-01-01,)`，表示当前价格没有定义结束日期。并且重叠保护完全按我们预期的那样工作： ```sql -- 尝试添加无效范围 INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-01-01)'); ERROR: range lower bound must be less than or equal to range upper bound LINE 1: INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-0...' -- 尝试添加重叠 INSERT INTO products VALUES (1, 'Widget', 99.99, '[2025-03-01, 2025-09-01)'); ERROR: conflicting key value violates exclusion constraint "products_pkey" DETAIL: Key (product_id, valid_at)=(1, [2025-03-01,2025-09-01)) conflicts with existing key (product_id, valid_at)=(1, [2025-01-01,2025-07-01)). ``` 一次获得两个验证检查！这就是我们使用范围而不是两个独立无关列所得到的。 ## 切分与调整 现在来看真正有趣的部分。假设 Widget 的价格需要改为 10.99 美元，但只在 2025 年 3 月到 9 月之间。在旧世界里，我们必须手动将现有行分割成几部分：删除或更新原始行，插入新的价格范围，然后为没有更改的部分插入剩余行。任何一个环节出错，就会在时间线上出现间隙或重叠。 有了临时表，我们只需说出我们的意思： ```sql UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1; ``` 让我们看看现在这些行长什么样： ```sql SELECT * FROM products WHERE product_id = 1 ORDER BY valid_at; product_id | product_name | price | valid_at ------------+--------------+-------+------------------------- 1 | Widget | 9.99 | [2025-01-01,2025-03-01) 1 | Widget | 10.99 | [2025-03-01,2025-07-01) 1 | Widget | 10.99 | [2025-07-01,2025-09-01) 1 | Widget | 12.99 | [2025-09-01,2026-01-01) 1 | Widget | 11.99 | [2026-01-01,2026-07-01) ``` 等等……发生了什么？我们从三行 Widget 开始，现在我们有**五行**！ 嗯，Postgres 看到范围 `[2025-01-01, 2025-07-01)` 价格为 9.99 美元和范围 `[2024-07-01, 2025-01-01)` 价格为 12.99 美元的元组，需要纠正重叠以容纳新行。结果发生了以下几件事： - Postgres 修改现有的 9.99 美元行，使其仅覆盖 `[2025-01-01, 2025-03-01)` 范围。 - 然后它在剩余的 `[2025-03-01, 2024-07-01)` 范围添加一个新行，价格为 10.99 美元。 - 然后它修改现有的 12.99 美元行，使其仅覆盖 `[2025-09-01, 2026-01-01)` 范围。 - 最后，它在剩余的 `[2025-07-01, 2025-09-01)` 范围添加另一个新行，价格为 10.99 美元。 为什么是两行价格为 10.99 美元，而不是在合并的 `[2024-03-01, 2024-09-01)` 范围中只有一行？因为 `FOR PORTION OF` 独立作用于每个匹配的行。它之后不会合并相邻的范围。最终结果是无间隙且无重叠的，这是我们仅使用排除逻辑所没有的。 这是一个 `UPDATE` 语句所蕴含的强大功能。 边界情况呢？如果 `FOR PORTION OF` 范围完全落在单个现有行内，Postgres 会创建最多两个剩余行（一个在之前，一个在之后）。如果它完美对齐现有边界，则不需要剩余行。它就是这样工作的。 有趣的是，新引入的临时剩余行不需要 `INSERT` 权限。它们是在保留现有数据，而不是添加新信息。但它们*确实*会触发现有的 `INSERT` 触发器。这对于审计日志或 `SECURITY DEFINER` 触发器函数来说是需要注意的一点。 ## 擦除历史 `FOR PORTION OF` 子句也适用于 `DELETE` 语句。假设 Gadget 在 2025 年 6 月到 10 月期间临时从目录中下架： ```sql DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2; ``` 让我们检查一下影响： ```sql SELECT * FROM products WHERE product_id = 2 ORDER BY valid_at; product_id | product_name | price | valid_at ------------+--------------+-------+------------------------- 2 | Gadget | 24.99 | [2025-01-01,2025-04-01) 2 | Gadget | 22.99 | [2025-04-01,2025-06-01) 2 | Gadget | 22.99 | [2025-10-01,2026-01-01) 2 | Gadget | 26.99 | [2026-01-01,) ``` 删除操作刻出了 6 月到 10 月的窗口。原本覆盖 `[2025-04-01, 2026-01-01)` 的 22.99 美元行被分割成两个剩余行：一个在 6 月结束，一个在 10 月开始。间隙前后的价格数据被保留并保持原始值。有点难以理解一个 `DELETE` 会导致行数*增加*，但我们确实如此。 无论如何，临时表管理的底层机制意味着这一切都是自动处理的。不再有不小心删除太多或在应用层留下孤立片段的风险。 ## 真实广告 临时表如果没有临时外键就不完整。Postgres 19 使用 `PERIOD` 关键字支持这些： ```sql CREATE TABLE variants ( variant_id INT NOT NULL, product_id INT NOT NULL, variant_name TEXT NOT NULL, valid_at DATERANGE NOT NULL, PRIMARY KEY (variant_id, valid_at WITHOUT OVERLAPS), FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at) ); ``` `PERIOD` 关键字告诉 Postgres 外键*本身*是临时的。因此，被引用的产品必须存在于变体的 `valid_at` 范围的*整个*持续时间内。仅仅在时间上某个地方存在一个匹配的产品行是不够的。被引用表中所有匹配行的组合必须完全覆盖引用行的时期。 如果我们尝试创建一个超出产品已知时间线的变体： ```sql INSERT INTO variants VALUES (100, 1, 'Widget Deluxe', '[2025-01-01, 2027-01-01)'); ERROR: insert or update on table "variants" violates foreign key constraint "variants_product_id_valid_at_fkey" DETAIL: Key (product_id, valid_at)=(1, [2025-01-01,2027-01-01)) is not present in table "products". ``` Widget 的定价只定义到 2026 年中，因此声称有效期到 2027 年的变体被拒绝。Postgres 检查了*完整*的时间覆盖，验证了父表中的匹配行覆盖了变体的整个有效期。 这里有一个显著的限制：临时外键只支持 `NO ACTION` 作为引用动作。这意味着排除了 `CASCADE`、`SET NULL` 和 `SET DEFAULT`。这意味着删除变体所依赖的产品行总是导致错误。考虑到级联临时操作的复杂性，这是可以理解的，但这意味着应用程序需要明确处理这些情况。目前是这样。 ## 小步前进 所以我们有了带有防止重叠、临时 DML 和临时外键的应用时间临时表。还有什么剩下的呢？ 主要的遗漏是系统时间，有时也称为事务时间。我们已经提到，应用时间跟踪事实在现实世界中何时为真，而系统