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、默认值与生成值 · 查看全书目录 · 查看索引中心