# 模式与 DDL 候选规则

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

---

DDL 不是把 ER 图翻译成 `CREATE TABLE`，而是在数据库中发布一组长期合同：名称怎样解析、谁拥有对象、什么输入被拒绝、什么标识保持稳定、旧应用与新 schema 能否共存。表一旦有数据和调用方，类型、约束和默认值就同时影响写入语义、锁、WAL、复制和恢复。

本节把 ch03 的逻辑问题与 ch04 的物理实现收敛成三组评审规则。具体的在线 schema change 编排留到第 11 章；这里先建立每个变更都必须携带的前提、停止线和证据。

## 6.3.1 命名、所有权、注释与对象边界 {#item-6-3-1}

`pg36_shop` 用三个 schema 表达不同稳定性边界：

| Schema | 内容 | 调用约束 |
|---|---|---|
| `shop` | 核心关系模型与业务写入对象 | application 通过精确 GRANT 读写 |
| `shop_api` | 对外稳定 view/query interface | application/reader 只依赖发布列 |
| `shop_private` | migration 元数据与内部函数 | 不向普通 runtime role 暴露 |

schema 不是项目文件夹。它同时参与名称解析、`USAGE`/`CREATE` 权限与对象归属。边界成立至少要检查四件事：

```sql
SELECT
    n.nspname,
    pg_get_userbyid(n.nspowner) AS owner,
    has_schema_privilege('pg36_app', n.oid, 'USAGE') AS app_usage,
    has_schema_privilege('pg36_app', n.oid, 'CREATE') AS app_create
FROM pg_catalog.pg_namespace AS n
WHERE n.nspname IN ('shop', 'shop_api', 'shop_private', 'public');
```

预期不是“schema 名字存在”，而是 owner、USAGE、CREATE 和 `search_path` 共同符合合同。`shop_private` 即使名字带 private，若 application 有 USAGE/EXECUTE，仍不私有。

### owner、migration identity 与 runtime identity 分离

本书采用：

```text
pg36_owner  NOLOGIN  持有 database/schema/table/function
pg36_app    LOGIN    只获得应用所需 DML/USAGE/EXECUTE
pg36_ro     LOGIN    只获得对外读取权限
dbuser_dba  LOGIN    通过受审计 direct service 执行 migration，
                     必要时 SET ROLE pg36_owner
```

对象所有者可以修改或删除自己拥有的对象，也能改变授权；把 owner 直接作为 application login，会让 SQL injection 或应用缺陷越过显式 GRANT。`SAFE-ROLE-003` 因此是 safety，而不是命名偏好。

NOLOGIN owner 也不等于“不需要保护”：能 `SET ROLE` 到 owner 的 membership、migration identity 和 SECURITY DEFINER function 都是进入该权限域的路径，必须在 catalog 中验证。日常应用不能为了省事获得 owner、superuser、`CREATEDB`、`CREATEROLE` 或 `BYPASSRLS`。

### 名称要支持定位，不要假装表达全部语义

默认使用不需双引号的小写 `snake_case`，原因是客户端、migration 和 catalog 查询更稳定，而不是 PostgreSQL 不支持其他命名。名称应回答：

- relation 表达什么事实，不以当前 UI 页面命名；
- column 的单位或时间语义是否可见，例如 `_minor`、`_at`、`_date`；
- constraint 出错时能否定位业务不变量；
- index 名能否关联 key/order/predicate；
- function 名是否表明它是 command、calculation 还是 trigger implementation。

例如：

```sql
CONSTRAINT sales_order_order_no_key UNIQUE (order_no)
CONSTRAINT sales_order_total_minor_nonnegative
  CHECK (total_minor >= 0)
```

稳定的 constraint name 使负向测试可以同时断言 SQLSTATE 与 `CONSTRAINT_NAME`，不会把任何 `23505` 都误认为目标唯一约束。命名本身不保证正确，但让错误、catalog、migration 和 incident evidence 可以指向同一对象。

PostgreSQL identifier 最长受 `NAMEDATALEN` 限制，默认最多存储 63 bytes；过长名称会被截断。规约应保证关键语义在截断前仍可辨识，并用 catalog 检查真实名称，而不是依赖生成器在内存中的原始字符串。

### comment 是运行目录的一部分

`COMMENT ON` 应覆盖关键 database、role、schema、relation、column 与非显然约束，至少说明：

```text
owner / purpose / unit or semantic / external contract / lifecycle
```

comment 不是放完整设计文档，也不能包含 secret、个人数据或随请求变化的值。它的优势是跟对象一起出现在 catalog、`\d+` 和元数据工具中。设计文档说明“为什么”，comment 则帮助值班者快速确认“这是什么、谁负责”。

由此形成 `DEFAULT-NAME-003`：边界由 schema、owner、稳定名称和 comment 共同表达；legacy 例外必须有 mapping、owner、迁移计划和 expiry。

## 6.3.2 类型、约束和默认值的审查问题 {#item-6-3-2}

“应该用哪个 PostgreSQL 类型”不能只靠一张类型对照表。评审时先完成语义句，再选类型：

| 维度 | 要回答的问题 | `pg36_shop` 示例 |
|---|---|---|
| 单位 | 数值代表什么，能否相加 | `amount_minor` + `currency_code` |
| 范围/精度 | 是否允许负数、上限、舍入点在哪里 | nonnegative named `CHECK` |
| 时间 | 瞬间、民事时间、日期还是持续时间 | `placed_at timestamptz` |
| 文本 | identity、大小写、Unicode、排序和长度合同 | `order_no text` + format/unique |
| 标识 | 谁生成、唯一范围、稳定期、是否公开 | internal ID / order no / request key |
| 缺失 | `NULL` 是未知、不适用还是尚未发生 | `paid_at` only after payment |
| 演进 | 新值、新状态和旧客户端怎样共存 | status transition contract |

### 类型名不等于业务合同

`numeric` 不知道币种和舍入规则，`text` 不知道 Unicode identity，`timestamptz` 不保存原始时区名称，`jsonb` 不自动提供领域约束。DDL 需要组合 type、column name、`NOT NULL`、default、named constraint、reference 与 comment。

本书把金额写成 minor unit integer 并显式保存 currency。这适合当前教学订单，但并非所有财务系统通用：支持任意精度计量、汇率、税务舍入或多币种分摊时，应重新建模，不能从“整数没有浮点误差”推出“整数能表达所有货币语义”。

若业务没有真实长度上限，`PREF-TEXT-001` 倾向 `text`，而不是习惯性 `varchar(255)`。若外部协议明确限制 64 个字符，则必须说明计数单位是字符、bytes 还是规范化后的 code points，并定义超长输入的错误合同。

### 约束优先保证单库可表达的不变量

适合进入数据库约束的包括：

- 值域与行内一致性：`CHECK`、`NOT NULL`；
- 候选键和幂等键：`UNIQUE`；
- 引用完整性：`FOREIGN KEY`；
- 时间/空间排斥：`EXCLUDE`；
- 需要 transaction 末尾成立的关系：可延迟 constraint。

每个关键约束至少有一个反例，且验证“因预期约束失败”：

```text
SQLSTATE=23514
CONSTRAINT_NAME=sales_order_total_minor_nonnegative
```

只检查“INSERT 失败”可能掩盖权限错误、类型解析错误或另一个约束先触发。只测试正向 seed 则无法证明数据库真的拒绝坏状态。

跨 database、外部支付系统或时间变化事实通常不能靠单个 declarative constraint 完整表达。此时 `SAFE-CONS-005` 允许受控 breakglass，但必须记录 application control、reconciliation、owner 和到期复查；“数据库做不了”不是“不需要保证”。

### 四种标识不要混成一列

`DEFAULT-KEYS-005` 区分：

```text
order_id       内部 join/physical identity
order_no       用户可见业务编号
provider_ref   外部系统引用
request_key    请求幂等键
```

它们的 authority、生命周期、隐私和唯一范围不同。把可变外部字符串同时当 primary key、公开 URL 和 retry key，会让 provider 变化、内部迁移和 API 合同互相绑死。并非每个模型都需要四列；若用一个标识，review 必须证明这些责任确实一致。

### default 是写入规则，不是历史真相

default 回答“调用方省略列时写入什么”，不回答旧行原本是什么。常见审查点包括：

- `now()` 是 transaction start time；是否需要 statement/clock time；
- identity/sequence 生成的是唯一候选值，不承诺无间隙或按 commit 排序；
- 空字符串、空 JSON 与 `NULL` 是否真的同义；
- volatile default 是否触发表重写或让重跑结果不稳定；
- 新增 default 后，旧应用显式发送 `NULL` 时会发生什么；
- backfill 值能否从已有事实确定，还是在伪造历史。

默认值方便不应覆盖领域语义。一个字段若在业务上必须由调用方明确选择，省略 default 反而能尽早暴露错误。

### JSON、array、enum 与 partition 都要证据

`PREF-SEMI-002` 只把 shape 可变、整体读写、有明确 owner/validation/retention 的附属值放进 JSONB/array；需要独立引用、唯一、局部更新或生命周期的事实优先拆成 relation。这不是“永远范式化”，而是让数据库能对核心事实提供统计和约束。

分区同理。`PREF-PART-003` 要求 retention/drop lifecycle、可测规模瓶颈或稳定 pruning 证据先出现，再设计 partition key、unique/PK、FK 和迁移。预计未来会有一亿行不是设计完成；分区会立刻增加约束、索引和运维复杂度，收益却可能多年不出现。

## 6.3.3 可逆迁移与版本化 DDL {#item-6-3-3}

“所有 migration 都必须可回滚”听起来安全，实际上容易产生虚假承诺。删除列后，`down` 可以重新创建空列，却不能恢复已经丢失的业务语义；旧应用已经按新格式写入后，数据库回滚也不保证旧代码能理解数据。

更准确的目标是**服务可恢复**：

```text
兼容发布 + transaction rollback（尚可时）
  + 明确停止线
  + application rollback window
  + forward repair
  + backup/PITR 作为灾难恢复底线
```

### expand—backfill—validate—switch—contract

跨 application release 的变更按五阶段设计：

1. **expand**：添加旧代码可以忽略的新对象，避免立即收紧；
2. **backfill**：分批填充，限制 lock/WAL/replica lag，过程可重入；
3. **validate**：检查无坏值、约束成立、读写双路径一致；
4. **switch**：先切写路径，再切读路径，保留观测和回退窗口；
5. **contract**：确认旧代码/旧数据路径退出后再删旧对象。

PostgreSQL 的 transaction DDL 很强，但并非所有命令都能放在同一个事务，锁取得时机与强度也不同。`CREATE INDEX CONCURRENTLY`、`VACUUM` 等有自己的事务限制；大型 backfill 即使可回滚，也可能产生巨大 WAL、dead tuples 和 replica lag。不能用“BEGIN 包住了”替代容量与锁评估。

对新 `CHECK`/FK，可以在合适场景先 `NOT VALID`，使新写入受约束，再单独 `VALIDATE CONSTRAINT` 扫描旧数据；这不自动适用于 UNIQUE/PK，也不消除所有 lock。具体 lock mode、版本差异与 workload 影响必须在 ch11 用当前目标版本实测。

### 破坏动作之前先证明可表示

`SAFE-MIGR-006` 要求在类型收窄、列删除、表重写或约束收紧前完成：

```text
precheck bad rows / dependencies
lock_timeout + statement_timeout
兼容 application 版本范围
WAL / temporary space / lag 预算
最大批次与停止线
失败后 transaction rollback 或 forward repair
post-state catalog + checksum
```

precheck 必须在真正写入前失败。例如把 text 转成 integer，先找出所有无法转换的值并固定 conversion rule；不要让 `ALTER TABLE ... TYPE` 运行数十分钟后才撞到最后一个坏值。precheck 与执行之间仍可能有竞态，所以迁移还需限制并发写、使用兼容约束或在同一受控边界重新确认。

高风险 contract 不因为有 backup 就可以随时执行。backup/PITR 是最大故障恢复证据，不是低成本 undo；恢复时间、数据丢失窗口和对其他 database 的影响都要进入风险说明。

### fresh install 与 upgrade 只有一条权威链

两套脚本最容易漂移：

```text
create-latest.sql     # 新环境
V001...V042.sql       # 旧环境升级
```

如果两者由人独立维护，很快会产生“同版本不同 schema”。`DEFAULT-VERS-010` 要求 fresh install 也消费同一条 versioned migration chain，或者由这条链可重复生成并验证 latest snapshot。数据库内要有可查询的 schema version 和 migration identity；重复执行要么幂等成功，要么在修改状态前明确拒绝。

本书使用 `shop_private.schema_version` 标记 ch04-v1，并由每章 context guard 检查。版本号本身不证明 schema 正确，所以还需要 catalog assertions 与稳定 relation checksum；checksum 又不能覆盖全部权限、function body 和运行参数，因此验收必须是多项事实，不是单个魔法哈希。

### 变更说明先于执行

使用 [`change-template.md`](/labs/ch06/change-template.md) 填写：

- target、owner、窗口与 application release；
- 当前 schema version、relation size、write rate 和依赖方；
- 五阶段迁移与重跑行为；
- lock/WAL/temporary space/lag 预算；
- transaction rollback、application rollback 与 forward repair；
- precheck、负向 SQLSTATE、post-state checksum；
- 最大可信故障、停止条件和审批。

无法填写的字段不是“文档以后补”，而是设计尚未完成。真正执行在线 DDL 前，还要在第 11 章为具体 PostgreSQL/Pigsty 环境补齐锁实验、监控窗口和发布编排。

## 本节验收问题

评审任意一个 DDL change，应能回答：

1. schema、owner、runtime role 和 API boundary 是否明确；
2. 名称/注释能否从 catalog 定位领域语义和 owner；
3. 每个类型是否闭合单位、范围、时间、文本与 NULL 语义；
4. 关键约束是否命名，并有精确 SQLSTATE/constraint 反例；
5. 内部、业务、外部和幂等标识是否被有意区分；
6. JSON/array/partition 的收益与边界是否有 workload 证据；
7. fresh install 与 upgrade 是否进入同一 version authority；
8. destructive step 前是否有可表示性 precheck、timeout 和停止线；
9. application rollback 时 schema 是否仍兼容；
10. 丢失语义时是否诚实声明只能 forward repair 或 restore。

如果其中任何高影响问题只能回答“应该没事”，该变更仍是 candidate，不应进入生产发布队列。

## 参考资料

- [PostgreSQL 18：Schemas](https://www.postgresql.org/docs/18/ddl-schemas.html)
- [PostgreSQL 18：Privileges](https://www.postgresql.org/docs/18/ddl-priv.html)
- [PostgreSQL 18：Data Definition](https://www.postgresql.org/docs/18/ddl.html)
- [PostgreSQL 18：Constraints](https://www.postgresql.org/docs/18/ddl-constraints.html)
- [PostgreSQL 18：Date/Time Types](https://www.postgresql.org/docs/18/datatype-datetime.html)
- [PostgreSQL 18：Numeric Types](https://www.postgresql.org/docs/18/datatype-numeric.html)
- [PostgreSQL 18：Transactional DDL Caveats](https://www.postgresql.org/docs/18/sql-commands.html)
- [PostgreSQL 18：ALTER TABLE](https://www.postgresql.org/docs/18/sql-altertable.html)

---

[上一节：连接与会话候选规则](../02/) · [返回本章目录](../) · [下一节：查询与事务候选规则](../04/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)