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

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 4.2.1 `bigint`、UUID 与标识生成 {#item-4-2-1}

先沿用 ch03 的标识分类：

| 标识 | 例子 | 作用域与承诺 |
|---|---|---|
| 内部主键 | `order_id` | 数据库关系内稳定引用 |
| 业务键 | `order_no`、SKU | 业务可识别，规则可能演进 |
| 外部引用 | provider payment ref | 必须连 provider 一起解释 |
| 幂等键 | request/idempotency key | 特定命令与调用方作用域 |
| 追踪标识 | trace ID | 可观测关联，不承担实体身份 |

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

### bigint identity 的取舍

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

```sql
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 不够。

目录证据：

```sql
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 布尔、枚举、查找表与状态机 {#item-4-2-2}

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

常见值域表达各有边界：

| 方式 | 优点 | 代价 | 适合 |
|---|---|---|---|
| `CHECK (status IN (...))` | 就地、简单、无 join | 改值域需改表约束；无元数据 | 小而稳定的行内值域 |
| PostgreSQL enum | 强类型、4 字节、固定顺序 | 删除值或重排需重建类型；跨域不可直接比较 | 真正静态、顺序有意义的集合 |
| lookup table + FK | 可附带 terminal/description；可审计 | 多一条引用与发布顺序 | 需要元数据或可演进值域 |
| 无约束 text | 发布最轻 | 任意拼写永久进入数据 | 暂存原始外部输入，不适合规范状态 |

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

### 允许值不等于允许转换

订单值域：

```text
draft, placed, paid, cancelled
```

允许边：

```text
draft  -> placed
draft  -> cancelled
placed -> paid
placed -> cancelled
```

payment 则是：

```text
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` 继续维护：

```text
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 非空
```

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

| 错误 | 防线 |
|---|---|
| 插入未知 status | FK 或状态/时间 CHECK |
| 已有行走一条图外边 | transition trigger，约束名 `*_status_transition` |
| 走合法边但缺伴随字段 | `sales_order_state_time_consistent` 等 CHECK |

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

### 为什么触发函数是 SECURITY DEFINER

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

```sql
SET search_path = pg_catalog, shop_private
```

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

[`negative-cases.sql`](/labs/ch04/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 与拆表边界 {#item-4-2-3}

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`](/labs/ch04/constraint-lab.sql)在临时表中定义：

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

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

```sql
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`：

```sql
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](https://www.postgresql.org/docs/18/ddl-identity-columns.html)
- [PostgreSQL 18：UUID 类型](https://www.postgresql.org/docs/18/datatype-uuid.html)
- [PostgreSQL 18：UUID 生成函数](https://www.postgresql.org/docs/18/functions-uuid.html)
- [PostgreSQL 18：enum 类型](https://www.postgresql.org/docs/18/datatype-enum.html)
- [PostgreSQL 18：array](https://www.postgresql.org/docs/18/arrays.html)
- [PostgreSQL 18：range](https://www.postgresql.org/docs/18/rangetypes.html)
- [PostgreSQL 18：JSON 类型与文档设计](https://www.postgresql.org/docs/18/datatype-json.html)
- [PostgreSQL 18：`btree_gist`](https://www.postgresql.org/docs/18/btree-gist.html)
- [Pigsty 扩展目录：`btree_gist`](https://ext.pigsty.io/e/btree_gist/)

---

[上一节：金额、文本与时间](../01/) · [返回本章目录](../) · [下一节：NULL、默认值与生成值](../03/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)