4 量体裁衣：数据类型、约束与可靠数据表达

逻辑模型说明“系统要保存什么事实”，物理模式则必须回答“这些事实允许以什么二进制表示、何时判错、怎样迁移、付出多少读写成本”。把 price numeric、status text 和 created_at timestamptz 写进表，只是选了类型类别；若没有精度、值域、时区、状态转换和失败语义，它们仍不是可靠的数据合同。

本章把 ch03 的逻辑模型 v0 原地迁移为 ch04-v1。范围内四项未决被关闭：人民币金额改用“分”的整数表示，事件瞬间统一为 timestamptz(3)，内部键接入 identity 序列，订单与支付状态改由查找表、迁移图和伴随字段共同约束。与此同时，本章明确留下两条边界：支付总额与订单总额等跨表不变量将在后续事务/数据库逻辑章节处理；当前没有体量和生命周期证据，因此不预先分区。

本章目标

完成本章后，读者应当能够：

• 根据业务精度、范围、运算与序列化合同选择整数、numeric 或其他数值类型；
• 区分文本的存储、比较、排序、大小写折叠与 Unicode 正规化语义；
• 区分瞬间、当地民事时间、业务日期与持续时间，正确使用 timestamptz；
• 为内部键、业务键、外部引用和公开标识选择不同生成策略；
• 在布尔、枚举、CHECK、查找表和状态机之间作有证据的选择；
• 明确 SQL NULL、JSON null、缺席与不适用的差别；
• 正确使用 default、identity、sequence 与 stored generated column；
• 用命名 PK/UK/FK/CHECK/EXCLUDE 表达不变量，并读懂对应 SQLSTATE；
• 只对支持的约束使用 DEFERRABLE，理解延迟检查与 ON CONFLICT 的冲突；
• 从行宽、TOAST、索引和写放大评估类型/约束的物理成本；
• 通过体量、生命周期或裁剪证据进入分区决策，而不是把分区当默认模板；
• 在 Pigsty L1 完成 v0→v1 事务迁移、反例审查、复位和 verify:state 取证。

开始之前

本章的升级路径要求已经运行 ch03 的 setup + seed，当前摘要应为：

model_version=ch03-v0
relation_checksum=cd7daa66543a6b5e0a5d7fc269558a6c

沿用 ch02 的私有 PGSERVICEFILE 与 pg36-admin service。实验基线是 PostgreSQL 18.6、Pigsty v4.5.0、Ubuntu 24.04；本章实际使用的 DDL 保持 PostgreSQL 14–18 可用。PG18 新增但旧版本没有的能力会单独标注，不能倒推成全版本事实。

下载资产：

• 物理决策登记
• 新环境 schema-v1 入口
• v0→v1 事务迁移
• v1 确定性样例
• v1 状态验证
• 类型与约束反例
• 排他/可延迟约束实验
• 分区 ADR
• v1 Mermaid 模型源文件
• 安全复位
• 综合任务入口

从 v0 到 v1

flowchart LR
  A["ch03-v0<br/>逻辑关系可运行"] --> B["迁移前证明<br/>可表示、无越界"]
  B --> C["事务 DDL<br/>类型 + 约束 + 序列"]
  C --> D["反例<br/>错误类型与约束名"]
  D --> E["verify:state<br/>ch04-v1"]
  E --> F["ADR<br/>暂不分区"]

v1 的“可靠”是有范围的：它保证本章登记的行内、值域、引用与状态边规则；它不声称一个本地约束能够证明外部支付真实发生，也没有把“订单至少一行、已付金额等于应付金额”伪装成普通 CHECK。

本章目录

4.1 金额、文本与时间

• 4.1.1 整数、numeric 与金额精度
• 4.1.2 text、排序规则与大小写语义
• 4.1.3 date、timestamptz、时区与业务时间

先定义单位、比较和时间语义，再选类型名；本章案例把 CNY 金额闭合到整数“分”，把事件瞬间闭合到毫秒精度。

4.2 标识、状态与半结构化数据

• 4.2.1 bigint、UUID 与标识生成
• 4.2.2 布尔、枚举、查找表与状态机
• 4.2.3 数组、范围、JSONB 与拆表边界

内部引用键、外部标识和状态码不是同一种数据；半结构化类型也不能代替需要独立约束与生命周期的关系事实。

4.3 NULL、默认值与生成值

• 4.3.1 “未知”“不存在”与空值语义
• 4.3.2 默认值、身份列与序列
• 4.3.3 生成列与数据库派生事实

默认值、identity 与生成列分别回答“缺省输入”“键生成”和“行内派生”，不能互换。

4.4 用约束表达不变量

• 4.4.1 主键、唯一、外键与检查约束
• 4.4.2 排他约束与 btree_gist 的适用条件
• 4.4.3 仅对支持类型使用 DEFERRABLE，并说明事务末校验代价

命名约束既是数据库防线，也是稳定的错误定位和目录审计接口；实验会实际验证 EXCLUDE 与延迟唯一检查。

4.5 类型与约束的物理代价

• 4.5.1 行宽、对齐、TOAST 与更新成本
• 4.5.2 隐式转换、操作符与索引可用性
• 4.5.3 约束、索引与写放大的关系

类型与约束会改变行宽、索引数量、TOAST 访问和每次写入的工作量；可靠性设计必须把这些成本显式记账。

4.6 分区决策门

• 4.6.1 先证明生命周期、体量或裁剪需求再决定分区
• 4.6.2 分区键与主键、唯一约束必须共同设计
• 4.6.3 外键、引用方式与未来在线改造代价
• 4.6.4 产出“现在分区 / 暂不分区”的可复查 ADR

分区是一项针对大表生命周期和访问路径的物理决策。当前模型选择不分区，并保留可触发复查的证据门槛。

4.7 实战：把逻辑模型落成可靠物理模式

• 4.7.1 闭合金额与时间表达
• 4.7.2 闭合状态与标识生成
• 4.7.3 用反例验证类型、约束与错误语义
• 4.7.4 在 Pigsty L1 输出可靠 DDL、分区决策与 verify:state

升级路径从真实 v0 数据出发，迁移前拒绝无法无损转成“分”的金额，迁移后逐项验证错误语义、应用角色路径、可重入和安全复位。

章节产物

task.sh all 在已经存在 ch03-v0 时执行：

manifest → migrate → verify → negative → constraint-lab

新环境可用 task.sh install 通过同一条迁移链建立空 v1、加载确定性 v1 样例并执行全部审查。两条路径最终必须得到同一摘要：

status=ok
model_version=ch04-v1
money_unit=CNY-fen
session_timezone=UTC
customer_count=2
product_count=3
order_count=2
item_count=3
payment_count=2
order_transition_count=4
partition_decision=not-now
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014

迁移已在 PostgreSQL 18.6 实测以下路径：首次升级、重复升级跳过、复位后从 v0 重建、空库 fresh install、fresh install 重跑、应用角色生成 identity 与执行状态转换、两位以上金额迁移前拒绝且事务完整回滚。

章节验收

1. 能解释为什么本案例选择整数“分”，也能说出何时应改用 numeric(p,s)；
2. 能证明 timestamptz 保存瞬间但不保存原始 zone name，并复现 DST 双重 01:30；
3. 能区分 identity、sequence 与 PK 的责任，迁移后不会产生键碰撞；
4. 非法状态值、非法转换和缺少伴随时间分别由不同规则拒绝；
5. 能说明 array、range、JSONB 与拆表的边界，而不是统一套用“灵活”；
6. 能从 pg_constraint 识别约束类型、是否验证和是否可延迟；
7. 能复现排他冲突和事务内唯一值交换；
8. 能列出 v1 新增的隐式/显式索引及写放大；
9. 分区决定有数据、生命周期和查询证据门，而不是凭行数拍脑袋；
10. all、install、拒绝路径与 reset 都有独立证据，最终 checksum 一致；
11. 明确 v1 尚未关闭的跨表/外部事实，不把本章 DDL夸大为完整电商生产模型。

下一章 ch05《运筹帷幄：查询、事务与锁的核心心智模型》 将在这套可靠类型合同上建立查询执行、并发可见性与锁等待的共同原理地图；ch10 与 ch13 再处理并发状态转换和跨表数据库逻辑。

参考资料

• PostgreSQL 18：数值类型
• PostgreSQL 18：日期/时间类型
• PostgreSQL 18：identity column
• PostgreSQL 18：generated column
• PostgreSQL 18：约束
• PostgreSQL 18：表分区
• Pigsty v4.5：默认 meta 模板

上一章：正本清源：从业务规则到关系模型 · 返回上卷导读 · 下一章：运筹帷幄：查询、事务与锁的核心心智模型 · 查看全书目录 · 查看索引中心

4.1 金额、文本与时间

类型选择不是从 PostgreSQL 类型表中挑一个“看起来像”的名字。先写单位、允许范围、比较规则、输入输出协议和舍入时点，类型才有答案。本节先关闭三个最容易产生静默歧义的合同：金额的单位、文本的相等性、事件时间的瞬间语义。

4.1.1 整数、numeric 与金额精度

“精确金额”至少包含币种、最小单位、范围和舍入规则。88.00 这个字面量没有告诉数据库它是人民币元、美元，还是精度为两位的比率。

PostgreSQL 的主要选择是：

numeric 是正确工具，但“金额一律 numeric”仍然太粗。声明 numeric(12,2) 时，超出两位的小数会先被舍入，而不是天然拒绝：

CREATE TEMP TABLE amount_probe (v numeric(12,2));
INSERT INTO amount_probe VALUES (1.239);
SELECT v FROM amount_probe;  -- 1.24

如果业务要求“客户端不得提交超过两位”，应在 API/域层先拒绝，并在迁移中证明可表示性；不能把数据库舍入误读为输入验证。unconstrained numeric 还允许特殊值。尤其 PostgreSQL 为了可排序，把 NaN 视为等于自身且大于普通数，因此 CHECK (amount > 0) 不是排除 NaN 的可靠方法。

本案例为什么用整数“分”

pg36_shop v1 明确限定单币种人民币：

currency_code = CNY
storage unit   = fen
100 fen        = 1 yuan
refund         = a separate future fact

于是：

current_unit_price_minor bigint
unit_price_minor         bigint
amount_minor             bigint
line_total_minor         bigint

88.00 元迁移为 8800 分，39.90 × 2 精确得到 7980 分。列名带 _minor，避免调用方把整数误当元；每个订单聚合又携带 currency_code。订单行与支付通过 (order_id, currency_code) 复合外键引用订单，不能在同一订单下悄悄混入另一币种。

这项选择不是普遍定律。若一个系统同时支持 JPY、CNY、KWD，最小单位的小数位并不相同；若保存汇率、利率或高精度计量，numeric(p,s) 往往更清楚。正确问题是“这一列的量纲与运算合同是什么”，不是“哪种类型更快”。

迁移必须先证明，而不是直接 cast

从 numeric 转 bigint 有一个危险细节：1.5::numeric::bigint 会舍入成 2。因此 migrate-v0-to-v1.sql先检查：

value * 100 = trunc(value * 100)

它验证“以分表示时没有残余”，又允许 88.000 这种只有尾随零的输入；只检查 scale(value) <= 2 会错误拒绝后者。迁移还显式拒绝 numeric 特殊值和越界值，然后才做：

(value * 100)::bigint

列约束把商品/订单行单价限制在 0..10^12 分、quantity 限制在 1..10^6，从而让生成乘积最多 10^18，仍在 signed bigint 的范围内。即使极端输入先在生成表达式中溢出，PostgreSQL 也会失败而不是环绕；边界的价值是让批准范围可读、可测试。

负金额也不是自动等于退款。payment v1 要求正数；退款需要自己的 provider reference、状态与生命周期，将在业务范围扩展时另建事实。用 -amount 复用 payment 会把两个不同事件压进一列符号。

金额验收

SELECT
    order_id,
    item_subtotal_minor,
    captured_amount_minor,
    currency_code
FROM shop_api.order_summary
WHERE order_id = 1001;

预期两项金额均为 16780、币种为 CNY。再把 v0 某价格改成 88.001 后运行迁移，脚本应以状态 3 返回，错误为：

product price cannot be represented as bounded integer minor units

整个事务回滚，v0 view 仍存在，v1 version marker 不存在。这才叫无损迁移门。

4.1.2 text、排序规则与大小写语义

text 解决的是可变长字符串存储，不会自动解决“两个字符串是否代表同一业务身份”。相等、排序、大小写转换和正则字符分类都会受 collation 影响。

PostgreSQL 中 text、varchar 与无长度限制的 varchar 都能保存变长字符串；varchar(n) 额外强制字符数上限。不要为了“数据库优化”给所有列随意加 varchar(255)。只有协议或业务确实存在上限时，长度才是不变量；否则 text 加针对语义的 CHECK 更直接。

把机器标识与人类文本分开

v1 对两类文本采用不同策略：

机器键使用 COLLATE "C" 的正则检查，例如：

CHECK (sku COLLATE "C" ~ '^SKU-[A-Z0-9-]+$')

C 采用传统字节/ASCII 行为，适合这里刻意受限的标识。自然语言列表若需要中文拼音、德语或重音规则，应在查询/列上选择经批准的 ICU collation；不能让某台 OS 的默认 locale 偶然决定全局业务键。

“大小写不敏感”不是一个完整需求

至少要回答：

• 只覆盖 ASCII，还是完整 Unicode？
• 重音、全半角、Unicode 不同正规形是否等价？
• 比较等价是否也要影响排序、LIKE 与正则？
• collation provider/版本升级后怎样重建受影响索引？
• API 返回原始写法，还是规范写法？

v1 的 email 只是教学范围内的小写 ASCII 联系地址：

CHECK (
  email = lower(email COLLATE "C")
  AND email COLLATE "C"
      ~ '^[a-z0-9][a-z0-9._+%-]*@[a-z0-9][a-z0-9.-]*$'
)

这不是 RFC 完整 email 验证，更不是全球通用账户身份算法。它只确保样例系统的所有写入口先规范化，并让原有 exact unique constraint 足以拒绝重复。UpperCase@example.test 会触发 customer_email_canonical。

真正的 Unicode case-insensitive 唯一性可以考虑 ICU nondeterministic collation、citext，或规范化生成键；三者的比较、索引、pattern matching 和升级代价不同。PostgreSQL 文档明确指出 nondeterministic collation 会带来性能成本、关闭 B-tree deduplication，并限制部分模式匹配。没有写清这些取舍时，不要只加一个 lower(email) 索引就宣称问题解决。

unique 继承相等性

unique constraint 依赖列/索引采用的相等语义。若 collation 认为两个不同字节串相等，唯一约束也会据此冲突。相反，在确定性默认 collation 下，Alice 与 alice 通常是不同值。业务必须先决定相等，再让 constraint 与 API 使用同一规则。

检查当前数据库可用 collation：

\dOS+

SELECT
    collname,
    collprovider,
    collisdeterministic,
    collversion
FROM pg_catalog.pg_collation
ORDER BY collname
LIMIT 20;

可用名称依赖数据库编码、构建选项和系统/ICU 环境。DDL 不应引用只在开发机存在的 locale 而没有部署前置检查。

4.1.3 date、timestamptz、时区与业务时间

“2026-11-01 01:30”可能是一个日期上的当地钟表读数，也可能是某个已经发生的全球瞬间。在纽约夏令时回拨日，这个读数甚至对应两个不同瞬间。类型必须反映要保存的事实：

只写 timestamp 在 SQL/PostgreSQL 中表示 timestamp without time zone。timestamptz 是 timestamp with time zone 的 PostgreSQL 别名。

timestamptz 保存瞬间，不保存原始时区

timezone-aware 时间在内部按 UTC 瞬间保存，查询输出时再按会话 TimeZone 转换。下面两个显示不同，但值相等：

SET TimeZone = 'UTC';
SELECT '2026-07-29 17:00:00+08'::timestamptz;

SET TimeZone = 'Asia/Shanghai';
SELECT '2026-07-29 09:00:00+00'::timestamptz;

因此 timestamptz 不会记住输入使用 Asia/Shanghai、CST 还是 +08。如果业务必须保留“用户选择的 IANA zone”，另存并验证 zone name；不要从显示偏移反推。

v1 的验证脚本固定：

SET TimeZone = 'UTC';

这让证据输出与执行机器无关。展示给用户时可以：

SELECT placed_at AT TIME ZONE 'Asia/Shanghai'
FROM shop.sales_order;

AT TIME ZONE 的结果类型取决于输入类型；应用边界要明确输出是否仍携带 offset。

精度也是合同

PostgreSQL 时间精度 p 允许 0–6 位秒后小数。v0 没写，v1 明确使用 timestamptz(3)，与常见毫秒 API 对齐。更高精度不是免费“更准确”：上游时钟可能根本没有微秒真实性，跨系统序列化也可能截断。若审计要求微秒，改合同并验证每个生产者，而不是只改数据库列。

字段语义被拆开：

• created_at：数据库接受记录的事务时间，默认 transaction_timestamp()；
• placed_at：订单被业务接受的瞬间；draft 时为 NULL；
• paid_at / cancelled_at：状态伴随事件；
• payment.occurred_at：支付 provider 事件时间。

transaction_timestamp()（亦即事务中的 now()）在同一事务内保持不变；statement_timestamp() 在每条语句开始变化，clock_timestamp() 才读取实际墙钟。创建时间默认值使用事务时间可让同一原子命令一致；外部事件时间则必须显式传入，不能用插库时钟覆盖 provider 事实。

DST 必须用反例验证

negative-cases.sql创建纽约回拨日的两个显式 offset：

'2026-11-01 01:30:00-04'::timestamptz
'2026-11-01 01:30:00-05'::timestamptz

两者相差一小时。若只传无 offset 的 01:30，解析依赖会话 zone 规则并产生歧义。事件 API 应接受带 offset 的 ISO 8601，或者同时接收受验证的当地时间和 IANA zone，并定义 DST gap/overlap 策略。

不要写 CHECK (occurred_at <= now()) 来维护“不能来自未来”。当前时间会变化，restore/replay 时语义也不同；时钟漂移和允许窗口属于命令验证/运营策略。数据库适合维护同一行中稳定的关系，例如：

paid_at >= placed_at
cancelled_at >= placed_at (如果已经 placed)

v1 的 sales_order_state_time_consistent 同时约束状态与这三个时间。非法的 paid 但无 paid_at 会被明确拒绝。

本节验收

• 金额单位、币种、范围和退款语义均有书面合同；
• 迁移先验证可表示性，不依赖会舍入的 numeric→bigint cast；
• 机器键的 ASCII 语义与人类文本的 Unicode 语义分开；
• 大小写不敏感需求包含正规化、collation、索引和升级策略；
• 能说明 timestamptz 保存什么、没有保存什么；
• DST 双重时间和 UTC/Shanghai 投影均由 SQL 反例验证；
• 不使用 volatile 当前时间伪装成永久 CHECK。

参考资料

• PostgreSQL 18：数值类型
• PostgreSQL 18：money 类型
• PostgreSQL 18：字符类型
• PostgreSQL 18：collation 支持
• PostgreSQL 18：日期/时间类型与时区
• PostgreSQL 18：日期/时间函数与当前时间

返回本章目录 · 下一节：标识、状态与半结构化数据 · 查看全书目录 · 查看索引中心

4.2 标识、状态与半结构化数据

标识回答“是哪一个”，状态回答“现在允许处于什么条件”，半结构化类型回答“一个值内部允许有多灵活”。它们都容易被一个技术名词替代设计：UUID 不自动成为好 API，enum 不自动成为状态机，JSONB 也不自动成为可演进模式。

4.2.1 bigint、UUID 与标识生成

先沿用 ch03 的标识分类：

“用 UUID 还是 bigint”只涉及第一行的一部分。把 trace ID 当唯一键、把可重复使用的 request key 当主键，类型再高级也救不了作用域错误。

bigint identity 的取舍

v1 在一个 PostgreSQL 主写者内运行，引用多、样例迁移需要保留旧键，因此选择：

order_id bigint GENERATED BY DEFAULT AS IDENTITY
         PRIMARY KEY

bigint 是固定 8 字节，B-tree 和外键较紧凑；identity 将隐式 sequence 与列关联，并以 SQL 标准语法表达“省略时生成”。但要分清三层责任：

• identity：定义默认生成机制；
• sequence：分配候选数值；
• PK/UNIQUE：真正保证不重复。

identity 文档明确说明它不会自动保证唯一性，所以仍需 PK。sequence 也不承诺无缝连续：nextval 分配的值不会因事务回滚而归还，缓存、故障转移和手工 setval 都会产生洞。ID 是身份，不是行数、会计序号或“绝对提交顺序”。

ALWAYS 与 BY DEFAULT

GENERATED ALWAYS 默认拒绝显式值，除非 OVERRIDING SYSTEM VALUE；BY DEFAULT 允许显式值覆盖生成值。本章用 BY DEFAULT，因为：

1. v0 已经有历史 customer_id/product_id/order_id/payment_id；
2. 确定性实验需要固定样例键；
3. 普通应用 INSERT 仍省略 ID，走 sequence。

代价是有权写表的调用方可以显式提交 ID，PK 只能拒绝重复，不能禁止“越权选号”。生产接口若不需要导入历史键，可以改成 ALWAYS，或只给应用列级 INSERT 权限。不要把教学迁移便利当成所有系统的默认选择。

为已有列添加 identity 后，隐式 sequence 不知道表中已经有 order_id=1002。迁移脚本用 pg_get_serial_sequence 找到实际 sequence，再把它推进到现有 max(id)；反例随后以 pg36_app 省略 ID 插入，证明新值越过历史最大值。应用角色还必须拥有 sequence 的 USAGE，只有 table INSERT 不够。

目录证据：

SELECT
    c.relname,
    a.attname,
    a.attidentity,
    pg_catalog.pg_get_serial_sequence(
        format('%I.%I', n.nspname, c.relname),
        a.attname
    ) AS sequence_name
FROM pg_catalog.pg_attribute AS a
JOIN pg_catalog.pg_class AS c ON c.oid = a.attrelid
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
  AND a.attidentity <> '';

attidentity='d' 表示 BY DEFAULT。

什么时候选 UUID

PostgreSQL uuid 是 128-bit 原生类型，适合多写者离线生成、跨系统合并或需要不可顺序猜测的公开标识。不要用 36 字符 text 代替原生 uuid；后者输入会规范化，存储与比较也有明确类型。

还要选择 UUID 版本和生成位置：

• v4 随机，分布式生成简单，但 B-tree 写入局部性较弱；
• v7 带时间顺序特征，通常改善索引局部性，但时间信息可被提取，且仍不是数据库提交顺序；
• 客户端生成可在入库前拿到 ID，数据库生成则集中规则。

PostgreSQL 18 原生提供 uuidv4()/gen_random_uuid() 与 uuidv7()；uuidv7() 不能写进本书 PG14–18 的共同 DDL。若要兼容 PG14–17，应明确使用可用的 v4 函数、扩展或应用生成，并在部署前探测。版本条件不应藏在“PG 支持 UUID”这句话里。

本案例保留紧凑内部 bigint，把 order_no 等业务键作为外部接口候选。未来增加 public UUID 是新增一项合同，不需要把现有全部外键重写。

4.2.2 布尔、枚举、查找表与状态机

boolean 适合真正只有两个稳定状态的命题，例如 product 是否 active。若开始出现 pending、reason、时间和转换，增加 is_paid、is_cancelled、is_failed 会制造互相矛盾的布尔组合；这已经是状态域。

常见值域表达各有边界：

PostgreSQL enum 是静态、有序集合；可增加或改名，但不能直接删除既有值，也不能在不重建类型的情况下重排。状态频繁演进、需要 terminal flag 或运营说明时，lookup table 更合适。本章因此不是宣称“enum 不好”，而是根据订单/支付状态的元数据与演进需求选择查找表。

允许值不等于允许转换

订单值域：

draft, placed, paid, cancelled

允许边：

draft  -> placed
draft  -> cancelled
placed -> paid
placed -> cancelled

payment 则是：

pending -> captured
pending -> declined

FK 只能证明目标状态存在，无法阻止 paid -> draft。v1 用四层表达：

1. *_status_catalog 保存允许值和 terminal 元数据；
2. status 列 FK 限制值域；
3. *_status_transition 保存有向边；
4. BEFORE UPDATE OF status trigger 查询边并拒绝非法转换。

状态伴随字段由行级 CHECK 继续维护：

draft       placed_at/paid_at/cancelled_at 全空
placed      placed_at 非空，其余空
paid        placed_at、paid_at 非空且 paid_at >= placed_at
cancelled   cancelled_at 非空，paid_at 为空
declined    failure_code 非空

于是三种错误被分开定位：

错误语义比笼统的“状态不合法”更能支持 API 映射和排障。

为什么触发函数是 SECURITY DEFINER

pg36_app 被刻意禁止 USAGE shop_private，却要通过 trigger 读取私有 transition table。函数因此由 pg36_owner 拥有，以 SECURITY DEFINER 执行，并固定：

SET search_path = pg_catalog, shop_private

函数内部仍使用 schema-qualified 名称，且撤销 PUBLIC 的直接 EXECUTE。若 definer 函数沿用调用者可控 search_path，攻击者可能放置同名对象劫持解析。这里的安全边界由 owner、固定路径、最小函数体和真实 app-role 测试共同成立，不是看到 SECURITY DEFINER 四个字就自动安全。

negative-cases.sql会切换到 pg36_app，依次执行 draft→placed→paid 与 pending→captured；应用角色不具 private schema 权限仍能成功。反向 placed→draft 则捕获 SQLSTATE 23514 和 sales_order_status_transition。

本章仍没有强制“paid 必须有足额 captured payment”。那是跨表、并发敏感不变量，普通 CHECK 做不到；ch10/ch13 会在锁、事务和数据库逻辑语境中处理。状态图解决的是边，不应被夸大成完整支付正确性。

4.2.3 数组、范围、JSONB 与拆表边界

PostgreSQL 的丰富类型可以把多个值放进一列，但“能存”不是“应该存”。判断边界时问：内部元素是否有独立身份、约束、引用、更新、权限、生命周期或高频查询？

array：一个值里的同类序列

array 适合有限、整体拥有、通常整体读写的同类值，例如固定传感器通道或一次计算输出。它不是多对多关系的快捷替代。官方文档直接提醒“arrays are not sets”；如果不断按元素搜索、去重、引用或更新，单独的 child table 通常更易约束和扩展。

还有两个容易误读的点：

• DDL 中写 integer[3] 并不会强制长度 3；
• 维数声明也不形成运行时限制。

若长度是业务不变量，需要 CHECK (cardinality(v)=3)；若元素有身份/外键，拆表。

range：把区间当成原子值

range 能同时表达下界、上界、开闭和空区间，适合预约、有效期和价格带。tstzrange 以 timestamptz 为 subtype；&& 表示重叠，@> 表示包含。

constraint-lab.sql在临时表中定义：

slot tstzrange NOT NULL,
EXCLUDE USING gist (slot WITH &&)

插入 [09:00,10:00) 后，[09:30,10:30) 触发 exclusion_violation，而 [10:00,11:00) 因半开边界可以相邻。若要求“同一房间内不重叠”，还需：

EXCLUDE USING gist (
  room_id WITH =,
  slot    WITH &&
)

普通 bigint/text 的 GiST equality operator class 可由 btree_gist 提供。它是 PostgreSQL 随附的 trusted contrib extension，Pigsty 扩展仓库覆盖 PG14–18；但 extension 仍是数据库对象，应先查 pg_available_extensions、声明 owner/schema/升级策略，再 CREATE EXTENSION。本章单列 range 的实验不需要安装它。

JSONB：灵活文档，不是免模式

jsonb 在写入时解析为二进制结构，支持运算符与 GIN 索引；通常比保留原始文本格式的 json 更适合查询。但它仍有模式，只是默认不由列定义完全强制。官方设计建议 JSON 文档保持可预测结构，并提醒更新大文档仍会锁整行。

合适候选包括：

• 第三方 provider 的原始响应快照；
• 随版本演进但整体拥有的配置；
• 很少查询、无需独立引用的稀疏扩展属性。

应该拆表/列的信号包括：

• 字段参与 PK/UK/FK 或金额/时间约束；
• 子项有独立身份、权限或生命周期；
• 需要按子项频繁更新、连接或统计；
• 每个写者都要靠不同 JSON path 才能维护规则；
• 已经为大量固定 key 建 expression index。

还要区分 SQL NULL 与 JSON null：

SELECT
    NULL::jsonb IS NULL,       -- true：SQL 值缺席
    'null'::jsonb IS NULL;     -- false：存在一个 JSON null 值

把二者混用会让“字段缺席、字段为 null、列为 NULL”出现三种状态而无人负责。

v1 的决定

订单行、状态和支付都是独立关系事实，v1 不把它们塞进 array/JSONB；核心五表也不增加“以后备用”的 metadata jsonb。范围类型只用于独立排他约束实验。未来若保存 provider payload，应另外定义大小上限、敏感字段脱敏、结构版本、索引预算和保留期。

本节验收

• identity、sequence 与 PK 的责任明确，迁移后 sequence 已对齐；
• 能基于写者拓扑与公开性选择 bigint/UUID，而不是按潮流；
• 允许状态值、允许转换和伴随字段由三种机制分别表达；
• app role 的 definer-trigger 路径与非法反向路径都被实测；
• 能说出 array 应拆表、range 应使用、JSONB 应拒绝的各三条信号；
• 不把 core JSONB 视为“以后总能兼容”的免费保险。

参考资料

• PostgreSQL 18：identity column
• PostgreSQL 18：UUID 类型
• PostgreSQL 18：UUID 生成函数
• PostgreSQL 18：enum 类型
• PostgreSQL 18：array
• PostgreSQL 18：range
• PostgreSQL 18：JSON 类型与文档设计
• PostgreSQL 18：btree_gist
• Pigsty 扩展目录：btree_gist

上一节：金额、文本与时间 · 返回本章目录 · 下一节：NULL、默认值与生成值 · 查看全书目录 · 查看索引中心

4.3 NULL、默认值与生成值

NULL、default、identity 和 generated column 都会让 INSERT 语句“少提供一些东西”，但它们表达四种不同事实：值缺席、缺省输入、键分配和行内派生。混用后最常见的结果是“不知道”被一个假默认覆盖，或者可以计算的值被多个写者分别维护。

4.3.1 “未知”“不存在”与空值语义

SQL NULL 不是空字符串、0、false，也不是一个能用 = 比较的普通值。它表示该列在这一行没有一个已知 SQL 值。缺席的业务原因可能不同：

只要不同原因会导致不同命令、权限、统计或展示，就不要把它们都压成无法区分的 NULL。

三值逻辑

涉及 NULL 的普通比较产生 UNKNOWN：

SELECT
    NULL = NULL,        -- NULL / UNKNOWN
    NULL <> 1,          -- NULL / UNKNOWN
    NULL IS NULL,       -- true
    NULL IS DISTINCT FROM NULL;  -- false

WHERE 只保留条件为 TRUE 的行，FALSE 与 UNKNOWN 都被过滤。因此：

WHERE status <> 'paid'

不会包含 status 为 NULL 的行。需要把 NULL 当一个可比较分支时，显式使用 IS NULL 或 IS [NOT] DISTINCT FROM。ch05 会在查询与并发语境中继续三值逻辑，本章先把它当模式设计合同。

CHECK 不会自动拒绝 NULL

PostgreSQL 的 CHECK 在表达式为 TRUE 或 NULL 时都视为通过。下面仍允许 NULL：

price bigint CHECK (price > 0)

若值必须存在，还要 NOT NULL。若 nullable 列与状态联动，应把所有分支写完，而不是指望 UNKNOWN 代替业务语义。

v1 的订单规则近似：

CHECK (
  (order_status = 'draft'
   AND placed_at IS NULL
   AND paid_at IS NULL
   AND cancelled_at IS NULL)
  OR
  (order_status = 'placed'
   AND placed_at IS NOT NULL
   AND paid_at IS NULL
   AND cancelled_at IS NULL)
  OR ...
)

这让每个状态的空值形状是封闭集合。payment 同样规定 declined 才有且必须有 failure_code，pending/captured 必须为 NULL。NULL 不再是“调用方忘了填也没关系”，而是由另一列解释的合法状态。

SQL NULL 与 JSON null

SELECT
    NULL::jsonb IS NULL AS sql_value_absent,
    'null'::jsonb IS NULL AS json_value_absent,
    '{"x":null}'::jsonb ? 'x' AS key_exists;

结果是 true、false、true：列值缺席、存在 JSON null、对象中存在一个值为 null 的 key 是三件事。若 API PATCH 还把 key 缺席解释为“不修改”，就有第四种命令语义。接口层必须显式映射，不能依赖驱动猜测。

NULL 设计清单

对每个 nullable column 写下：

1. 哪些业务状态允许 NULL；
2. NULL 表示尚未发生、不适用还是未知；
3. 谁能把它从 NULL 改为非 NULL，能否改回；
4. unique、join、aggregate 与 API 如何处理；
5. 是否需要伴随 reason/status 才能解释。

答不出来时优先 NOT NULL。PostgreSQL 官方也建议多数列应为 not null；允许 NULL 应是一项积极设计，而不是省略约束的默认。

4.3.2 默认值、身份列与序列

default 是“INSERT 省略该列或显式写 DEFAULT 时使用的表达式”，不是缺失业务信息的修复器：

created_at timestamptz(3)
           NOT NULL
           DEFAULT transaction_timestamp()

如果调用方显式传 NULL，default 不会替换它；NOT NULL 会拒绝。default 也不会持续维护列值，后续其他列变化时它不重新计算。

默认值的权威时钟

customer.created_at 与 product.created_at 表示数据库记录创建时间，因此可以由数据库默认产生。placed_at 与 provider occurred_at 表示业务/外部事件，必须由相应命令显式提交，不能用 default 掩盖事件时间遗失。

PostgreSQL 允许 default 使用 volatile 表达式。transaction_timestamp() 在整个事务内固定，适合同一事务产生一致的 recorded-at；clock_timestamp() 会在语句执行期间变化。选择哪一个是审计语义，不是风格偏好。

identity 是有生命周期的 default 机制

identity 列背后有隐式 sequence。INSERT 省略 ID 时等价于请求 sequence 的下一个值，但 sequence 状态与普通表事务不同：

• nextval() 的值即使事务回滚也不会归还；
• 并发会交错分配；
• sequence cache 和故障切换可能留下空洞；
• 手工 setval 可改变后续位置；
• identity 本身不替代 PK。

因此不应从连续 ID 推算“没有删除”、订单数量或严格提交先后。需要法定连续票号时，要单独建模分配、作废和审计，接受对应串行化成本。

数据迁移中的 sequence 对齐

从已有手工 bigint 添加 identity 时，下面操作还不够：

ALTER TABLE shop.sales_order
  ALTER COLUMN order_id
  ADD GENERATED BY DEFAULT AS IDENTITY;

新 sequence 通常从 1 开始，下一次自动 INSERT 会撞历史 PK。本章迁移对四张 identity 表执行：

SELECT pg_catalog.setval(
  pg_catalog.pg_get_serial_sequence(
    'shop.sales_order', 'order_id'
  ),
  (SELECT max(order_id) FROM shop.sales_order),
  true
);

空表要使用 setval(seq, 1, false)，这样下一次返回 1；非空表用 max 与 is_called=true，下一次返回 max+1。脚本通过循环同时处理空/非空情况。

还要授权：

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA shop
TO pg36_app;

table INSERT 和 sequence USAGE 是不同权限。verify-v1.sql用 has_sequence_privilege 检查，反例脚本再以真实 app role 插入，防止“owner 测得通，应用却报 permission denied”。

确定性种子

seed-v1.sql先 TRUNCATE ... RESTART IDENTITY，显式插入固定 ID，再把 sequence 对齐到最大值。它只适用于隔离教学数据；生产数据库不应为了重放 fixture 重置 identity。BY DEFAULT 让这种导入可行，但也意味着运行权限设计要阻止不受信调用方自行选号。

4.3.3 生成列与数据库派生事实

生成列是“由同一行其他列永远计算出来”的事实。本章订单行：

line_total_minor bigint
GENERATED ALWAYS AS (
  unit_price_minor * quantity::bigint
) STORED

应用不能直接给它赋值；base column 插入或更新时，PostgreSQL 重新计算。本章反例显式提交 line_total_minor=1，应得到 SQLSTATE 428C9，证明不存在第二个写者。

default、generated、view 的边界

PostgreSQL 14–17 只支持 stored generated column；PG18 增加 virtual，并把省略 kind 的默认行为改为 virtual。本书共同基线因此始终显式写 STORED，不依赖版本默认。

generation expression 只能使用 immutable 函数，不能含 subquery，也不能引用另一 generated column；它适合 unit_price_minor * quantity，不适合：

sum(all lines of this order)
current product price
captured payments
now()

这些值依赖其他行、其他表或时间。订单 subtotal 继续放在 shop_api.order_summary view 中；若未来缓存，必须有独立一致性与刷新合同。

存储不是免费

stored generated column占行空间，并在 base field 更新时增加计算与 WAL/写入。它可能被索引，读取也不必重复计算；是否值得由读写比例和行宽证明。本例是教学上的小而确定派生：金额整数相乘便宜，结果被 summary 使用，并用边界约束防溢出。

注意生成列 attnotnull 不会因为表达式看起来非空而自动变 true。本例由 unit_price_minor 和 quantity 的 NOT NULL 保证结果非 NULL，再由 bounds CHECK 保证批准范围。验证同时检查：

a.attgenerated = 's'
line_total_minor = unit_price_minor * quantity::bigint

什么时候不保存派生值

优先查询时计算，除非至少有一项证据：

• 表达式昂贵且读远多于写；
• 需要对派生值建立索引；
• 派生值是经批准的写时快照，而非随源事实变化；
• 性能测试证明存储收益超过行宽与写放大。

不要以“以后查询方便”为理由复制 subtotal、captured amount 到 order 头。每多一个存储副本，就要回答谁在并发、失败与恢复后维护一致。

本节验收

• 每个 nullable 列都有状态解释，CHECK 分支不会被 UNKNOWN 穿透；
• 能区分 SQL NULL、JSON null、JSON key 缺席与 PATCH 不修改；
• default 只用于权威可缺省输入，不覆盖外部事件事实；
• identity sequence 在迁移与 seed 后都对齐，app 拥有精确权限；
• generated column 只维护 immutable 行内派生，跨行聚合仍在 view；
• PG18 virtual generated 没有误写成 PG14–18 共同行为。

参考资料

• PostgreSQL 18：约束与 NULL
• PostgreSQL 18：default value
• PostgreSQL 18：identity column
• PostgreSQL 18：sequence 函数
• PostgreSQL 18：generated column
• PostgreSQL 18：JSON null 与 SQL NULL

上一节：标识、状态与半结构化数据 · 返回本章目录 · 下一节：用约束表达不变量 · 查看全书目录 · 查看索引中心

4.4 用约束表达不变量

约束把一句业务命题变成所有写入口共享、并发下原子执行的失败条件。它的价值不止是“挡脏数据”：命名、类型、检查时点、支持索引与 SQLSTATE 共同组成可审计合同。本节从常用约束走到排他与延迟检查，同时严格说明每种机制不能做什么。

4.4.1 主键、唯一、外键与检查约束

选择约束先看规则的作用域：

命名约束就是命名失败

v1 不依赖 PostgreSQL 自动生成名：

CONSTRAINT product_price_minor_bounds
  CHECK (current_unit_price_minor BETWEEN 0 AND 1000000000000)

调用方可以按 SQLSTATE 类别处理，又能记录 constraint name 定位具体不变量：

不要依赖英文 error message 文本，它会随版本、locale 和上下文变化。也不要假设多项同时违反时必定先报告某一个：约束检查顺序不是 API 合同。本章反例每次只制造一个目标错误，并验证 constraint name。

pg_constraint.conname 不是数据库全局唯一；定位时至少带 relation/schema。目录查询：

SELECT
    c.conrelid::regclass AS relation,
    c.conname,
    c.contype,
    c.convalidated,
    c.condeferrable,
    c.condeferred,
    pg_catalog.pg_get_constraintdef(c.oid, true) AS definition
FROM pg_catalog.pg_constraint AS c
WHERE c.conrelid IN (
    'shop.sales_order'::regclass,
    'shop.sales_order_item'::regclass,
    'shop.payment'::regclass
)
ORDER BY relation::text, c.conname;

PK、unique 与 identity 不是同义词

identity 生成候选值，PK 维护唯一/非空并声明主要行身份。一个表只能有一个 PK，却可以有多个业务 unique：

sales_order_pkey                    (order_id)
sales_order_order_no_key            (order_no)
sales_order_request_key             (customer_id, request_key)
sales_order_order_currency_key      (order_id, currency_code)

最后一个复合 unique 看似冗余，因为 order_id 已唯一；它是复合 FK 的合法目标，使 line/payment 必须携带与 order 相同的 currency。这个语义换来一个额外 unique index，成本在 4.5 明确记账。

默认情况下，unique constraint 把多个 NULL 视为互不相等，所以 nullable unique column 仍可有多个 NULL。PG15+ 提供 NULLS NOT DISTINCT，但本书 PG14–18 共同行为不能无条件使用。最简单的业务键通常应 NOT NULL。

FK 既是存在性，也是生命周期

本章延续：

• order→customer：ON DELETE RESTRICT；
• line→product：ON DELETE RESTRICT，历史快照仍保留引用；
• line→order：ON DELETE CASCADE，line 是 order 的组成部分；
• payment→order：ON DELETE RESTRICT，支付记录有独立保留要求。

FK 自动要求被引用列可唯一查找，却不会自动为 child referencing columns 建索引。删除/更新 parent 时 PostgreSQL 需要在 child 找引用；数据大时通常要为 child FK 设计索引，但索引列序和其他查询可以合并考虑，不能由 FK 机械生成器盲加。

CHECK 只维护当前行的稳定命题

PostgreSQL 明确不支持把其他行/表查询塞进 CHECK 并承诺持续一致。下面不是合法方向：

CHECK (
  amount_minor <= (
    SELECT sum(line_total_minor)
    FROM shop.sales_order_item
    WHERE order_id = payment.order_id
  )
)

其他行后来变化时不会自动重检，dump/restore 也可能破坏假设。跨行“互不重叠”可用 EXCLUDE，引用用 FK，唯一用 UNIQUE；真正跨表业务命令留给事务/trigger/constraint trigger 等受验证实现。

CHECK 还把 NULL 结果视为通过，所以必填列另加 NOT NULL。v1 的 money bounds、ASCII 格式和状态时间都是只引用当前行、对同一输入稳定的表达式，符合边界。

4.4.2 排他约束与 btree_gist 的适用条件

UNIQUE 只能表达“这些键不能相等”。许多业务冲突是“时间段不能重叠”“圆不能相交”“同笼不能出现不同动物”。EXCLUDE 接受一组 operator，要求任意两行比较时，至少有一个 operator 结果为 false 或 NULL。

单资源预约：

CREATE TEMP TABLE booking_window (
  booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slot       tstzrange NOT NULL,
  CONSTRAINT booking_window_nonempty
    CHECK (NOT isempty(slot)),
  CONSTRAINT booking_window_no_overlap
    EXCLUDE USING gist (slot WITH &&)
);

&& 为 range overlap。排他约束自动建立 GiST index，两个重叠 slot 触发 23P01。把边界统一为 [) 很重要：09:00–10:00 与 10:00–11:00 不重叠，避免相邻时段同时包含 10:00。

多资源为什么需要 btree_gist

若每个 room 分别不能重叠：

EXCLUDE USING gist (
  room_id WITH =,
  slot    WITH &&
)

GiST 原生理解 range overlap，但普通 bigint/text equality 需要相应 GiST operator class。btree_gist 为常见标量类型提供类似 B-tree 的 GiST operator classes，适合这种“标量相等 + 空间/范围运算”的多列 GiST。

它不是“更快的 B-tree”：

• 官方文档明确说通常不会优于标准 B-tree；
• 它不能像 B-tree unique index 那样维护普通唯一性；
• 它的价值是让不同运算共存于 GiST/EXCLUDE。

在 Pigsty L1 先观察：

SELECT
    name,
    default_version,
    installed_version
FROM pg_catalog.pg_available_extensions
WHERE name = 'btree_gist';

本章实测 btree_gist_available=true，但单列 slot 实验无需创建 extension，因此不改变数据库 extension 状态。若真实模式需要它，再由配置/迁移明确：

CREATE EXTENSION btree_gist;

Pigsty 负责把所需软件包/内核扩展交付到节点；CREATE EXTENSION 仍是目标 database 中的 DDL，要纳入 owner、schema、版本和备份恢复合同。

并发正确性

EXCLUDE 的优势不是语法短，而是把冲突交给 access method 与约束在并发写入中仲裁，避免应用“先查没有重叠，再插入”之间的竞态。应用仍可预查给友好提示，但最终以数据库 exclusion_violation 为准。

选择前确认：

• 冲突关系可由可索引 operator 精确表达；
• NULL/空 range 是否允许；
• 边界是 [) 还是其他形式；
• 是否需要按资源、租户等额外等值维度隔离；
• 索引/锁竞争在目标写入量下可接受。

4.4.3 仅对支持类型使用 DEFERRABLE，并说明事务末校验代价

DEFERRABLE 不是“让所有约束最后再查”的通用开关。PostgreSQL 只允许：

• UNIQUE；
• PRIMARY KEY；
• EXCLUDE；
• REFERENCES / FOREIGN KEY。

NOT NULL 与 CHECK 不可延迟。当前文档若写出 CHECK (...) DEFERRABLE，DDL 本身就是错的。

两个维度

DEFERRABLE / NOT DEFERRABLE
    能否由事务改变检查时点

INITIALLY IMMEDIATE / INITIALLY DEFERRED
    每个事务开始时的默认检查时点

NOT DEFERRABLE 是默认，不能用 SET CONSTRAINTS 推迟。deferrable 且 initially immediate 默认在每条语句后检查，可以在事务中改为 deferred；initially deferred 默认到提交时检查。

实验中有两个唯一 slot：

('A', 1), ('B', 2)

要在一条/一组操作中交换为 A=2、B=1，中间状态可能碰到唯一值。定义：

CONSTRAINT display_slot_slot_key
UNIQUE (slot_no)
DEFERRABLE INITIALLY IMMEDIATE

事务内：

SET CONSTRAINTS display_slot_slot_key DEFERRED;
UPDATE display_slot
SET slot_no = CASE slot_no WHEN 1 THEN 2 WHEN 2 THEN 1 END;
SET CONSTRAINTS display_slot_slot_key IMMEDIATE;

最后一条会立即检查当前状态；若仍冲突，就在此处失败，不必等 COMMIT。这既是实验验收，也是长事务中提早暴露错误的方法。

代价与限制

延迟检查会把失败推到更远位置，事务可能做了大量工作后才回滚，并在事务期间保留用于待检查状态的资源。官方还指出：

• deferrable constraint 不能作为 INSERT ... ON CONFLICT 的 conflict arbiter；
• deferrable uniqueness 可能显著慢于 immediate uniqueness；
• FK 被引用的 unique/PK 必须是 non-deferrable 合格键。

所以“以后批量导入方便”不足以把所有键改成 deferrable。先有一个需要事务内暂时不一致、提交时恢复的具体流程，再付成本。

v1 所有业务约束保持 immediate。状态转换也在每次 UPDATE 时立即拒绝；订单 command 没有证明需要暂时进入图外状态。隔离的 constraint-lab.sql只演示正确适用点，不改变核心模式。

目录验收：

SELECT
    conrelid::regclass,
    conname,
    contype,
    condeferrable,
    condeferred,
    convalidated
FROM pg_catalog.pg_constraint
WHERE conrelid = 'display_slot'::regclass;

临时 unique 应为 deferrable=true、deferred-by-default=false；临时 CHECK 不应出现 deferrable=true。

本节验收

• 每条不变量根据行内、唯一、引用或跨行冲突选对约束；
• error handling 使用 SQLSTATE + constraint identity，不匹配英文全文；
• 知道 unique/PK/EXCLUDE 会建索引，FK child 不自动建；
• 排他实验真实拒绝 overlap，且没有无谓安装 extension；
• 能列出四类可延迟约束与两类不可延迟约束；
• 能解释延迟检查对 ON CONFLICT、失败时点和性能的影响；
• 核心 v1 没有为假想需求滥用 DEFERRABLE。

参考资料

• PostgreSQL 18：约束
• PostgreSQL 18：CREATE TABLE 与 DEFERRABLE
• PostgreSQL 18：SET CONSTRAINTS
• PostgreSQL 18：range 与 exclusion constraint
• PostgreSQL 18：btree_gist
• PostgreSQL 18：pg_constraint

上一节：NULL、默认值与生成值 · 返回本章目录 · 下一节：类型与约束的物理代价 · 查看全书目录 · 查看索引中心

4.5 类型与约束的物理代价

可靠性不是无成本的，但“为了性能去掉约束”也不是成本分析。类型决定每行布局和可用运算，PK/UK/EXCLUDE 带来索引，FK/CHECK/trigger 增加写时检查；这些成本必须测量并与它们阻止的错误一起评估。

4.5.1 行宽、对齐、TOAST 与更新成本

一行不等于各列声明大小简单相加。heap tuple 还有 header、NULL bitmap 与对齐 padding；text、numeric、JSONB、array 等 varlena 值有长度头，足够宽时可能压缩或移到 TOAST table。列顺序、空值分布和具体内容都会改变实际大小。

先用 PostgreSQL 测，而不是凭类型名猜：

SELECT
    pg_column_size(8800::bigint) AS bigint_bytes,
    pg_column_size(88.00::numeric) AS small_numeric_bytes,
    pg_column_size(
      12345678901234567890.1234567890::numeric
    ) AS wide_numeric_bytes;

本章 PG18.6 样例分别得到 8、8、22。它说明“小 numeric 有时与 bigint 同样紧凑”，不说明两者物理/运算成本等价；numeric 是变长、按四位十进制一组存储并带额外开销，值越宽占用越多。选择 integer minor unit 的首要理由仍是单位与范围合同，固定宽度只是可预期的附带收益。

测完整行：

SELECT
    round(avg(pg_column_size(t))) AS avg_row_payload
FROM shop.sales_order AS t;

在当前两行确定性 fixture 上约为 187 bytes；这不是生产容量估算。生产要取有代表性的长文本、NULL 比例和状态，结合：

pg_relation_size(...)
pg_table_size(...)
pg_indexes_size(...)
pg_total_relation_size(...)

区分 heap、TOAST、索引与总占用。pg_column_size(row) 也不包含页面空闲、dead tuple、FSM/VM 和索引。

TOAST 解决页限制，不消除宽值成本

PostgreSQL 常见 page size 为 8 KiB，单个 tuple 不能跨页。TOAST 会对可 TOAST 类型压缩和/或拆成外置 chunk；触发阈值通常约 2 KiB。主 heap 只留 pointer，查询不读取宽列时可少拉取数据。

但：

• 宽值仍占磁盘、WAL、备份和网络；
• 读取它需要 detoast/decompress；
• 更新宽值会产生新版本及新的 TOAST 数据；
• 一个 table 有 toast relation 不代表当前已经有值被外置。

本章五表都有 text，因此目录显示 reltoastrelid；固定短样例并未因此“免费存储无限文本”。给 provider payload 一个无限 JSONB 列，会把更新竞争、保留期与敏感数据一起带进主行。

更新会创建新行版本

PostgreSQL MVCC 的 UPDATE 通常写一个新 tuple version。若没有修改 indexed column 且同页有空间，可能使用 HOT 降低索引更新；列变宽、索引过多或页面太满会降低机会。stored generated line_total_minor 又增加 8 bytes，并在单价/数量变化时重算。

不要为省几个 padding byte 就随意重排成熟表的列：重写表、应用兼容和迁移锁的代价常远大于收益。新表可以把固定宽、常用非空列放在合理位置，但最终仍用真实数据测量。

4.5.2 隐式转换、操作符与索引可用性

SQL 中的 = 不是一个能比较任意两值的万能函数。PostgreSQL 根据两边类型、可见 operator、implicit cast 和 preferred type 选择具体实现。unknown string literal 常能借另一边类型推断：

WHERE order_id = '1001'

这里 literal 可以解析为 bigint。但 driver parameter 一旦被声明成 text，就不再是 unknown：

PREPARE bad(text) AS
SELECT * FROM shop.sales_order WHERE order_id = $1;
-- operator does not exist: bigint = text

正确做法是让 driver 绑定 bigint，或在确定输入已经验证时显式 cast parameter：

WHERE order_id = $1::bigint

不要为了“兼容所有输入”cast indexed column：

WHERE order_id::text = $1

普通 sales_order_pkey(order_id) 索引保存 bigint operator class；对列包一层 text cast 后，表达式不同，除非另有 matching expression index，否则通常不能用原 PK index 作为相同条件。

三件事必须一致

索引可用性取决于：

1. query expression；
2. 解析出的 operator 与类型；
3. index key expression、collation 与 operator class。

文本大小写查询若写 lower(email)，普通 UNIQUE(email) 不是该表达式的索引。若创建 expression index，查询又必须使用可匹配的表达式与 collation。一个隐式 collation 或 cast 的变化，既可能改变语义，也可能改变计划。

检查 parameter 类型：

SELECT
    name,
    parameter_types,
    statement
FROM pg_catalog.pg_prepared_statements;

检查 cast 策略：

SELECT
    castsource::regtype,
    casttarget::regtype,
    castcontext
FROM pg_catalog.pg_cast
WHERE castsource IN ('text'::regtype, 'bigint'::regtype)
   OR casttarget IN ('text'::regtype, 'bigint'::regtype);

castcontext 区分 implicit、assignment 和 explicit；不是目录里存在 cast 就能自动应用。

由计划验证，不靠规则口诀

在小 fixture 上 planner 选择 seq scan 很正常，不能据此判定索引“失效”。ch07 会用有规模的数据与 EXPLAIN (ANALYZE, BUFFERS)。本章先保留方法：

• 确认 column/parameter 精确类型；
• 查看 predicate 中是否对 indexed column 做函数/cast；
• 查看实际 operator 和 index definition；
• 在代表性数据量、统计信息与配置下比较计划；
• 不用长期关闭 enable_seqscan 来“逼出答案”。

金额 API 同理：把 amount_minor 作为整数传输，不能在 SQL 中反复 amount_minor / 100.0 再与 numeric 参数比较并期待原索引语义不变。展示单位转换放投影层，过滤/连接使用存储单位。

4.5.3 约束、索引与写放大的关系

每个 INSERT/UPDATE 不只写 heap：

• PK/UNIQUE 要维护 B-tree 并检查冲突；
• EXCLUDE 要维护指定 index 并检查 operator 冲突；
• FK 要查询 referenced key，parent 更新/删除还要查 child；
• CHECK 计算表达式；
• transition trigger 查询私有边表；
• WAL、replica、backup 和 cache 都会承受更多字节。

v1 的五张业务表合计只有 12 行 fixture，却已经有 13 个 constraint-backed indexes：

SELECT
    tablename,
    indexname,
    pg_size_pretty(
      pg_relation_size(
        format('%I.%I', schemaname, indexname)::regclass
      )
    ) AS size
FROM pg_catalog.pg_indexes
WHERE schemaname = 'shop'
ORDER BY tablename, indexname;

在本章 PG18.6 空间分配下，每个小 index 即使只有数行也显示 16 KiB。这是页面级最低分配的演示，不应线性外推；但它直观说明“多一个 unique”永远不是零成本。

给每个索引一个理由

最后一个是有意冗余 index：order_id 已全局唯一，但 PostgreSQL 要求复合 FK 指向合格 unique key。我们用额外索引换取数据库可声明的跨表币种一致性。若生产写入证明它太贵，可重审多币种建模或约束实现，不能只删索引后假装规则仍在。

FK child columns没有自动 index。本章 fixture 很小，暂不为每条 FK 增加可能重复的索引；ch07 根据查询与 parent delete/update 路径统一设计。漏建和盲建同样是问题。

约束的收益也要计量

一次 23505 可能阻止两个并发请求生成重复订单，一次 FK 可能避免数月后才暴露的孤儿，一次 migration precheck 可能阻止静默舍入历史金额。把它们只归类为“写性能开销”会漏掉修复、对账和事故成本。

优化顺序应当是：

1. 证明具体写路径受哪个检查/索引限制；
2. 检查冗余 index、错误列序和不必要更新；
3. 批量写入遵守事务/锁/WAL预算；
4. 在不改变不变量时优化表达；
5. 若必须改变合同，走业务 ADR，而不是 DBA 私删约束。

后续用 pg_stat_user_indexes、pg_stat_all_tables、WAL 与 latency 指标验证长期成本。刚创建的 index “scan count=0”也不能立即判废，它可能只为 rare integrity path 或 FK parent delete 服务。

本节验收

• 能用 pg_column_size 与 relation size 函数区分值、heap、index、TOAST 和总量；
• 不把 TOAST 误解为宽字段免费，也不从 toast relation 存在推断已经外置；
• driver parameter 使用列的真实类型，indexed column 不被无谓 cast；
• 能从 expression/operator/collation/opclass 四层解释索引匹配；
• 列出 v1 的 13 个索引及每一个不变量理由；
• 明确复合币种 unique 的可靠性收益与写放大；
• 性能优化以证据为入口，不用删约束代替建模。

参考资料

• PostgreSQL 18：数值物理存储
• PostgreSQL 18：TOAST
• PostgreSQL 18：数据库对象大小函数
• PostgreSQL 18：operator type resolution
• PostgreSQL 18：索引类型
• PostgreSQL 18：约束与索引

上一节：用约束表达不变量 · 返回本章目录 · 下一节：分区决策门 · 查看全书目录 · 查看索引中心

4.6 分区决策门

分区把一个逻辑关系拆成多组物理存储。它能让按生命周期整批删除、冷热分层和特定查询裁剪非常有效，也会把唯一键、外键、索引、统计信息和运维对象成倍展开。正因为改造晚了有成本，团队常想“先分了再说”；本节用决策门阻止这种没有收益证据的确定复杂度。

4.6.1 先证明生命周期、体量或裁剪需求再决定分区

分区解决的典型问题是：

• 按月/日保留期需要快速 DROP 或 DETACH PARTITION，避免海量 DELETE 与 VACUUM；
• 热查询稳定命中少数分区，planner 能裁剪其余分区；
• 单表/索引维护窗口、冷热存储或批量加载已经不可接受；
• 数据分布天然按 list/hash 隔离，并有清楚路由与对象数量上限。

“以后数据会很多”不在其中。行数本身也不是充分证据：一亿条窄 append-only 记录和一千万条宽、频繁更新记录的物理问题不同；内存、索引、查询选择性和保留策略都会改变拐点。官方给出的只能是宽泛经验——通常要表非常大才值得——不是一个可复制的固定阈值。

决策输入

至少收集：

current heap/index/TOAST size
daily/monthly growth
retention and legal hold
largest maintenance window
representative slow queries
predicates that can carry partition key
candidate key cardinality and null behavior
expected partition count over 3 years
backup/restore and failover objectives

查询裁剪必须用计划证明：

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
FROM candidate_partitioned_table
WHERE placed_at >= $1
  AND placed_at <  $2;

看实际 scanned partitions、planning time、execution buffers；不要只看“有 Partition Pruning 字样”。prepared statement、parameter、函数包装和 join 条件都可能影响静态/运行时裁剪。enable_partition_pruning 还必须开启。

生命周期比“查询更快”更强

按月保留三年是可操作合同：36 个活跃分区、每月建立一个、过期时 detach/archive/drop。相比之下，“大多数查询最近数据”若没有 predicate 和计划样本，只是一句愿望。

本章订单没有保留期、法定删除批次、生产增长和慢查询数据。普通表的 PK/unique/FK 清楚且样本极小，因此不满足进入门。先不分区不是缺少架构，而是证据导向的物理决策。

4.6.2 分区键与主键、唯一约束必须共同设计

PostgreSQL 分区表上的 unique/PK 由各 partition 的本地索引实现。为了保证不同 partition 之间不重复，unique/PK 的列必须包含全部 partition key columns，而且 partition key 不能是 expression/function。

假设按 placed_at 月分区：

CREATE TABLE shop.sales_order_p (...)
PARTITION BY RANGE (placed_at);

当前约束：

PRIMARY KEY (order_id)
UNIQUE (order_no)
UNIQUE (customer_id, request_key)

不能原样成为 partitioned parent 的全局约束，因为它们都不含 placed_at。可选方向各有语义代价：

1. 改成 (order_id, placed_at) 等复合键；
2. 接受 only-per-partition uniqueness；
3. 另建未分区 registry 表维护全局键；
4. 由应用/trigger 维护跨 partition 唯一性，并承担并发正确性；
5. 换一个能同时服务生命周期与唯一性的 partition key；
6. 不分区。

把 placed_at 机械加入每个 unique 不等于问题解决。订单号原本承诺全局唯一，加入时间后两个 partition 可拥有同一个 order_no；除非 API 唯一性合同也改为 (order_no, placed_at)，语义已经变了。

NULL 与草稿

v1 的 draft order placed_at IS NULL。range partitioning 对 NULL 没有普通 range 归属，需要 default partition 或不同 key。若用 created_at 路由，保留期可能与业务 placed/paid 生命周期不一致。分区键必须同时满足：

• 每行插入时可用、稳定；
• 业务生命周期/删除批次；
• 高频查询 predicate；
• unique/PK 与 FK 形状；
• 更新是否会导致跨 partition row movement。

一个“时间列存在”远不足以当分区键。

4.6.3 外键、引用方式与未来在线改造代价

若 order PK 从 (order_id) 变成 (order_id, placed_at)，引用它的 order item 与 payment 通常也要携带 placed_at：

sales_order_item(order_id, order_placed_at) -> sales_order(...)
payment(order_id, order_placed_at)          -> sales_order(...)

这增加键宽、索引宽、写入参数和更新路由；draft 的 NULL 又使引用更复杂。保留一个未分区 key registry 可避免传播时间键，却新增一张强一致写入热点与生命周期协调表。两种都不是免费。

PostgreSQL 已支持针对 partitioned table 的外键，但被引用键仍要满足 partitioned unique/PK 限制。应用 ORM “支持分区”也不能绕过数据库这一事实。

普通表不能原地变成分区表

官方文档明确：不能把 regular table 直接切换成 partitioned table，反之亦然。常见迁移需要：

1. 新建 partitioned parent 与 partitions；
2. 建立等价列、约束、索引、权限、trigger 与注释；
3. backfill 历史数据；
4. 捕获 backfill 期间增量（短暂停写、dual-write、trigger 或逻辑复制）；
5. 验证行数、checksum、FK 与查询计划；
6. 短锁窗口切换名称/view/service；
7. 保留前滚/回退与旧表清理门。

ATTACH PARTITION 可复用已经装载的普通表，但需要证明 partition constraint；没有匹配 CHECK 时会扫描验证并持有相应锁。partitioned index 也有自己的并发创建/attach 流程。ch11 会演练安全发布，ch28 再处理完整生命周期；本章只估算设计后果。

现在不分区，也要为未来保留边界

不应把应用 SQL 绑定具体 child table；所有读写面向逻辑 relation/view。业务标识不要编码当前 partition 名。持续记录时间分布、表/索引体积和保留期，让未来迁移有数据。

但不要为了“方便未来”现在就把 partition key 传播到所有 API：这会立刻锁定尚未证明的设计。可演进的关键是清楚接口与可验证迁移，不是提前暴露物理细节。

4.6.4 产出“现在分区 / 暂不分区”的可复查 ADR

partition-adr.md记录：

ADR-004
decision: ch04-v1 remains unpartitioned
status: accepted
review chapter: ch26

主要理由不是“数据还小”一句话，而是：

• 没有体量、增长、保留期或慢查询证据；
• 当前 order_id/order_no/request key 要求全局唯一；
• order item/payment 通过 FK 引用订单；
• 候选 placed_at 对 draft 为 NULL；
• 预分区会立即增加对象、维护和恢复复杂度。

触发复查

任一条件由真实证据满足时复查：

1. heap/index 已使单表维护窗口不可接受；
2. 有稳定、可按候选 key 整批执行的过期/归档政策；
3. representative query 携带 key，计划证明 pruning 收益；
4. 普通表无法满足写入、备份恢复或冷热分层目标。

复查包必须包含增长率、关系/索引大小、保留期、慢查询计划、候选键、预期 partition count，以及一次迁移/回退演练。结论可以仍是“暂不分区”；ADR 的价值是让新证据能推翻旧决定。

数据库验收

verify-v1.sql 不只在文档中说“不分区”，还检查五表都没有 pg_partitioned_table entry：

SELECT
    c.oid::regclass,
    c.relkind
FROM pg_catalog.pg_class AS c
WHERE c.oid IN (
  'shop.customer'::regclass,
  'shop.product'::regclass,
  'shop.sales_order'::regclass,
  'shop.sales_order_item'::regclass,
  'shop.payment'::regclass
);

预期 relkind='r'，状态摘要输出：

partition_decision=not-now

如果有人私自把某表换成 partitioned hierarchy，verify 失败，迫使代码与 ADR 一起评审。

ADR 模板

context
measured evidence
candidate keys and alternatives
PK/UK/FK consequences
query/lifecycle benefits
object and operation costs
decision and owner
review triggers/date
migration and rollback outline

不要记录“PostgreSQL 支持 range partition”这类产品事实；记录为什么这个模型在这个时点选择什么，以及什么证据会让决定失效。

本节验收

• 没有使用单一行数阈值替代体积、生命周期与计划证据；
• 候选 partition key 同时审查 NULL、稳定性、查询与保留期；
• 能解释为什么 PG partitioned unique 必须包含全部 key；
• order_no、request key 与 child FK 的语义后果已列出；
• 知道 regular→partitioned 不是原地 ALTER，迁移需新结构和切换；
• ADR 有 owner、反对方案、复查触发条件和数据库 verify；
• “暂不分区”被当成可复查的积极决定。

参考资料

• PostgreSQL 18：table partitioning
• PostgreSQL 18：partition pruning
• PostgreSQL 18：CREATE TABLE 的 partition/unique 限制
• PostgreSQL 18：ALTER TABLE / ATTACH PARTITION

上一节：类型与约束的物理代价 · 返回本章目录 · 下一节：实战：把逻辑模型落成可靠物理模式 · 查看全书目录 · 查看索引中心

4.7 实战：把逻辑模型落成可靠物理模式

本实验不是在空白数据库重抄一遍最终 CREATE TABLE，而是从 ch03-v0 的真实行与约束出发，先证明旧值可无损表示，再在一个事务中升级，最后用应用角色、反例和目录状态证明结果。新环境入口也复用同一迁移链，避免“新装 DDL”和“升级 DDL”长期分叉。

风险分级：

• verify：R0·观察，只读目录与数据；
• migrate：R2·受控迁移演练，持表锁、删除 v0 money columns、重建 view；
• negative / constraints：R2·破坏性演练，事务内写入后强制回滚；
• seed：R2·破坏性演练，TRUNCATE 五表并重建固定 fixture；
• reset：R2·破坏性演练，删除全部 ch03/ch04 模型对象，要求双重令牌。

本章 migrate/seed/reset 只在已确认可销毁的 Pigsty L1 教学库执行。生产迁移必须增加兼容发布、备份/PITR、锁时长、容量和回退评审。

4.7.1 闭合金额与时间表达

先确认上下文，不把“连得上”误当“目标正确”：

export PGSERVICEFILE="$PWD/pg_service.conf"
export PGSERVICE=pg36-admin

psql -X -w "service=$PGSERVICE" \
  -c '\conninfo' \
  -c "SELECT current_database(), pg_is_in_recovery();"

预期 database=pg36_shop、pg_is_in_recovery=false。再运行 ch03 verify，确认 v0 checksum：

cd static/labs/ch03
./task.sh verify
relation_checksum=cd7daa66543a6b5e0a5d7fc269558a6c

回到 ch04 资产目录：

cd ../ch04
export PG36_EVIDENCE_DIR="$PWD/evidence/ch04/migrate-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh all

all 顺序是：

manifest → migrate-v0-to-v1 → verify-v1
         → negative-cases   → constraint-lab

迁移前门

migrate-v0-to-v1.sql先确认六个 v0 relation/view 存在、旧 money columns 仍是 v0 形状，然后拒绝：

• numeric NaN / Infinity；
• value * 100 仍有小数残余；
• 转换后超出批准 bigint bounds；
• quantity 使行金额越界；
• 非规范 email；
• 不在 v1 catalog 的旧状态；
• paid order 找不到 captured payment 时间。

这些检查发生在事务内、DROP VIEW 之前。任一失败会回滚全部 DDL。已实测把商品价格改为 88.001 时，psql 状态为 3，v0 view 仍存在且 v1 marker 不存在。

expand、convert、constrain、contract

成功路径按顺序：

1. 创建 private schema version、status catalog 与 transition graph；
2. 添加 nullable currency_code / *_minor；
3. 用旧 numeric 精确换算并回填；
4. 改为 NOT NULL，增加 bounds/currency/复合 FK；
5. 删除旧 numeric columns；
6. 把事件列改为 timestamptz(3)，补 paid/cancelled time；
7. 重建 shop_api.order_summary；
8. 写入 version marker 后 COMMIT。

DDL 事务设置：

SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';

它让 L1 演练不会无限等待；不是生产通用值。ALTER TABLE 会取锁，数据回填会产生写入/WAL，DROP old column 会打破仍在读取旧列的应用。真正在线发布应拆成多次兼容迁移：先新增+双写/回填，发布新读路径，观察，再删除旧列。这里单事务 contract 是为了在隔离环境展示完整物理决定。

时间闭合

迁移把所有事件列显式改为毫秒精度。paid order 的 paid_at 从现有 captured payment 最早 occurred_at 推导；若缺失就拒绝，而不是用当前时间编造历史。验证固定 UTC，反例另外检查：

2026-11-01 01:30-04
2026-11-01 01:30-05

是相差一小时的两个瞬间，并确认 09:00+00 AT TIME ZONE Asia/Shanghai = 17:00。

4.7.2 闭合状态与标识生成

物理决定的可下载记录是 physical-decisions.md，v1 图源是 model-v1.mmd：

erDiagram
  CUSTOMER ||--o{ SALES_ORDER : places
  SALES_ORDER ||--|{ SALES_ORDER_ITEM : contains
  PRODUCT ||--o{ SALES_ORDER_ITEM : snapshotted_as
  SALES_ORDER ||--o{ PAYMENT : receives
  ORDER_STATUS_CATALOG ||--o{ SALES_ORDER : permits
  PAYMENT_STATUS_CATALOG ||--o{ PAYMENT : permits

identity 关闭生成责任

四个内部键变为：

customer.customer_id
product.product_id
sales_order.order_id
payment.payment_id
    bigint GENERATED BY DEFAULT AS IDENTITY

迁移通过 pg_get_serial_sequence 找到隐式 sequence，空表设置 (1,false)，非空表设置 (max_id,true)；随后授权 app USAGE, SELECT。verify 从 pg_attribute.attidentity='d' 和 sequence privilege 双重检查。

固定 ID 的历史/fixture 仍可导入，普通 app INSERT 省略 ID。反例脚本以 pg36_app 新建 order/payment，实际证明 sequence 不碰撞。不要用 owner 成功代替 runtime 成功。

状态关闭值、边与伴随事实

列外键到 owner-only catalog：

FOREIGN KEY (order_status)
REFERENCES shop_private.order_status_catalog(status_code)

transition trigger 对 UPDATE 的 old/new 查表，图外边抛 23514 并设置稳定 constraint identity。行级 CHECK 再要求 paid/cancelled/failure 字段与状态一致。

查看图：

SELECT from_status, to_status
FROM shop_private.order_status_transition
ORDER BY from_status, to_status;

预期：

draft|cancelled
draft|placed
placed|cancelled
placed|paid

函数为 definer 是因为 app 无权使用 private schema。验证要求：

prosecdef = true
proconfig contains "search_path=pg_catalog, shop_private"
PUBLIC direct EXECUTE revoked
trigger tgenabled = O

然后 app 实走 draft→placed→paid。安全不是静态 DDL 扫描和动态测试二选一，两者都要。

新装入口不复制 DDL

schema-v1.sql是 canonical fresh-install entrypoint：

• 已是 v1：幂等跳过；
• 有完整 v0：走同一 migration；
• 无模型：先建立空 ch03-v0，再走同一 migration。

这样 constraint/function/view 只有一条权威升级定义。随后 seed-v1.sql加载最终列形状的 fixture。新环境完整验证：

export PG36_EVIDENCE_DIR="$PWD/evidence/ch04/install-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh install

fresh install 与 v0 upgrade 必须产生相同 checksum；只验证“两个脚本各自不报错”不足以证明收敛。

4.7.3 用反例验证类型、约束与错误语义

正向 seed 只能说明某些合法值能写入，不能证明边界存在。negative-cases.sql在一个事务里逐项制造：

PL/pgSQL block 只捕获预期 condition，并用 GET STACKED DIAGNOSTICS ... CONSTRAINT_NAME 比较。若写入意外成功、SQLSTATE 类别不对或另一个约束先失败，review 整体失败。

随后是正向边界：

• 省略 customer ID，identity 值必须大于历史 max；
• pending payment 合法转 captured；
• placed order 同一 UPDATE 带 paid_at 转 paid；
• shop_api.order_summary captured minor total正确；
• 切换成 pg36_app 再完整走一次 identity + 状态路径；
• 两个显式 offset 的 DST 瞬间保持一小时差。

所有写入最后：

ROLLBACK;

再次 verify 的行数与 checksum 不变。

排他与延迟约束独立实验

constraint-lab.sql也完全在事务/temporary tables 内：

1. tstzrange EXCLUDE USING gist (slot WITH &&) 拒绝 overlap；
2. UNIQUE(slot_no) DEFERRABLE 在事务中交换 1/2；
3. pg_constraint 证明 unique 可延迟而 CHECK 不可；
4. 只探测 btree_gist availability，不创建 extension；
5. ROLLBACK。

预期摘要：

exclusion_overlap_rejected=ok
deferrable_unique_swap=ok
btree_gist_available=true

最后一项依赖安装环境；如果是 false，单列 range lab 仍应通过，多资源 example 则要先交付 extension package。不要把“扩展不可用”混成 exclusion 语义失败。

分步运行并保存独立现场：

./task.sh verify
./task.sh negative
./task.sh constraints
./task.sh review

negative 会先 verify；review 执行 verify + 两类实验。stderr 为空是本章脚本的期望，预期异常已经在 SQL 内精确捕获。

4.7.4 在 Pigsty L1 输出可靠 DDL、分区决策与 verify:state

Pigsty 在本章提供：

• PostgreSQL 18.6 主库和统一 service endpoint；
• owner/app/readonly 运行角色与后续可观测环境；
• contrib/扩展软件交付能力；
• L1 可复现的实验边界。

类型、表、约束、trigger 和应用 migration 仍属于 PostgreSQL/应用模式发布。不要把业务 DDL塞进 Pigsty cluster topology，也不要因为 Pigsty 有 HA/PITR 就省略应用迁移的兼容性设计。

证据目录

task.sh 要求 private PGSERVICEFILE，使用 psql -X -w 避免个人 rc 和交互密码影响。manifest 记录：

• UTC capture time、action、service；
• psql client/server version、database、session user、recovery state；
• 12 个输入资产与 task script 的 SHA-256。

动作输出分别进入：

migrate.stdout / migrate.stderr
schema.stdout  / schema.stderr
seed.stdout    / seed.stderr
verify.txt     / verify.stderr
negative.txt   / negative.stderr
constraints.txt / constraints.stderr

最终 verify:state：

status=ok
model_version=ch04-v1
money_unit=CNY-fen
session_timezone=UTC
customer_count=2
product_count=3
order_count=2
item_count=3
payment_count=2
order_transition_count=4
partition_decision=not-now
relation_checksum=f8a7bfae59c6d16cd323abecfefe1014

该 checksum覆盖三条 order line 的 order/line/product/currency/unit price/quantity/generated total。它不是数据库备份校验和，只是固定 fixture 的快速漂移信号。

可重入与失败原子性

连续再执行：

export PG36_EVIDENCE_DIR="$PWD/evidence/ch04/rerun-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh all

migration 应输出：

ch04 physical model v1 is already installed

verify/negative/constraints 仍通过、checksum 相同。若 version marker 存在但对象漂移，migration 会跳过，严格 verify 必须失败；marker 不是“相信我已经正确”的免检标签。

reset 与重建

无令牌：

./task.sh reset

必须返回 64。确认要删除整个模型：

export PG36_RESET_TOKEN=RESET_CH04_MODEL
export PG36_EVIDENCE_DIR="$PWD/evidence/ch04/reset-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh reset
unset PG36_RESET_TOKEN

SQL 内还验证 confirm_reset。它显式删除五表、view、两个 transition function、五个 private tables 和空的 shop_api/shop_private schema；保留 database、roles、shop schema 和 Pigsty 集群。schema drop 使用默认 RESTRICT：若出现未知对象，事务整体失败，不会 CASCADE 带走。

复位后可以：

# 重演升级
../ch03/task.sh all
./task.sh all

# 或重演新装
./task.sh install

两条路径都应回到同一 f8a... checksum。

生产前不能省略

本章迁移在 L1 真实通过，不等于可直接复制到繁忙生产。至少补齐：

• 当前 PG/Pigsty 版本与 extension/collation inventory；
• 可用 PITR/backup 与实际 restore drill；
• 表大小、回填 WAL、replica lag 和锁等待预算；
• old/new application 双向兼容矩阵；
• expand/backfill/validate/switch/contract 分阶段脚本；
• 对 ALTER TABLE lock 的预演与 kill/timeout 策略；
• checksum、业务对账、监控与明确 rollback/forward-only 决定；
• 变更窗口、owner、审批与终止条件。

在生产删旧列通常是最后一个独立发布，不与第一次回填放在同一事务中。L1 的单事务脚本证明语义与原子性，生产 choreography 证明可用性；二者问题不同。

本章最终验收

• v0 checksum 与 prerequisite 正确；
• 不可表示金额在任何 contract DDL 前被拒绝且完整回滚；
• 成功迁移输出 v1 checksum；
• fresh install 与 upgrade 收敛到同一状态；
• migration/install 重跑稳定；
• identity catalog、sequence 对齐和 app privilege 均通过；
• 非法值、非法边、缺伴随时间分别失败；
• app 无 private USAGE 仍可安全走合法 transition；
• generated value 不能由应用覆盖；
• DST、range exclusion、deferrable unique 都有反例；
• partition ADR 与数据库实际状态一致；
• reset 无令牌拒绝、有令牌只删除声明范围；
• 清楚记录跨表金额不变量与生产在线迁移仍属后续工作。

通过后进入 ch05《运筹帷幄：查询、事务与锁的核心心智模型》。

参考资料

• PostgreSQL 18：ALTER TABLE
• PostgreSQL 18：information functions 与权限探测
• PostgreSQL 18：系统目录
• Pigsty v4.5：默认 meta 模板
• Pigsty v4.5：PostgreSQL 服务
• Pigsty v4.5：extension create

上一节：分区决策门 · 返回本章目录 · 下一章：运筹帷幄：查询、事务与锁的核心心智模型 · 查看全书目录 · 查看索引中心