先检查再写入会出错，用约束就能守住

一句话总结

如果由应用来“先检查再写入”，不变量就会在检查与写入之间的缝隙里被破坏。如果由数据库用约束来持有，就没有这道缝隙。只有约束无法表达的多行规则，才交给 SERIALIZABLE，而与之配套的重试循环，只应重试 40001。

为什么需要它

把“如果这个邮箱不存在就注册”写成 SELECT 之后再 INSERT，两个请求会同时看到“不存在”，然后都插入。检查预订时间重叠，也是同样的形状。隔离级别与写偏斜，由“数据库概念”课程的事务实验讲；行锁，由“PostgreSQL 进阶”课程讲。本模块看的是下一个问题——要把规则本身交给数据库，需要知道什么？对幂等键使用 UNIQUE 和 ON CONFLICT 的设计，由“生产级后端 API 综合项目”讲，所以这里只讲这条语句究竟返回什么，并把范围扩大到 UNIQUE 无法表达的规则。

工作原理

UNIQUE 与 ON CONFLICT。 按 INSERT 文档，ON CONFLICT DO NOTHING 是不插入而什么也不做，DO UPDATE 则保证在高并发下，INSERT 或 UPDATE 二者之一会原子地发生。陷阱在于 RETURNING。文档写道，它只返回实际插入或修改的行。因 DO NOTHING 而跳过的行不会出现，所以“结果为空 = 已经存在”，如果需要现有行的 id，就得用 DO UPDATE。在 SET 子句里，现有行用表名（别名）来指代，想插入的行用 EXCLUDED 来指代。如果把二者弄混，写成 EXCLUDED.visits + 1，访问数就不会增加，每次都是同一个值。约束文档说，默认情况下两个 NULL 不被视为相等，所以即使有 UNIQUE，含 NULL 的行也可能重复，可以用 NULLS NOT DISTINCT 改变这一点。

重叠交给 EXCLUDE。 “同一个房间的预订时间不重叠”不是相等，而是重叠关系，所以无法用 UNIQUE 来写。排除约束的规则是：用指定的运算符比较两行时，至少有一个结果为假或 NULL，一旦加上，就会自动创建该类型的索引。范围类型文档用 [ ] 表示包含、( ) 表示排除的边界，并写道，两个参数的构造函数会生成下界包含、上界排除的 [) 标准形式。10–11 点与 11–12 点的预订，在 [) 下不重叠，但如果用 []，就会在 11 点这一个点上重叠。要在 GiST 中使用像房间号这样的标量的 =，需要 btree_gist，这样才能用 EXCLUDE USING gist (room WITH =, 시간범위 WITH &&)（占位符为时间范围）这一个约束把两者绑在一起。给 CREATE TABLE 加上 WHERE (predicate)，就能像已取消的预订那样，只对表的一部分加约束（内部会创建部分索引）。

语句中途暂时被破坏的变更，用 DEFERRABLE。 同一份文档的兼容性一节写道，对不可延迟的 UNIQUE，PostgreSQL 每次插入或修改行时都立即检查，而标准要求在语句结束时检查。这就是为什么交换两位乘客座位的第一条 UPDATE 会立刻收到 23505。DEFERRABLE INITIALLY DEFERRED 只在事务结束时检查。接受它的只有 UNIQUE、PRIMARY KEY、EXCLUDE 和外键，NOT NULL 和 CHECK 不能延迟。ALTER TABLE 的 ALTER CONSTRAINT 目前只能修改外键，所以 UNIQUE 要删除后重建。而且，可延迟约束不能成为 ON CONFLICT 的判定依据（arbiter）——如果加在注册邮箱上，upsert 就会坏掉。

多行的规则靠隔离与重试。 约束文档明确写道，CHECK 不支持引用正在检查的行之外的其他行。“家庭钱包合计不能为负”就卡在这里。这样的规则由 SERIALIZABLE 来守护，而失败总是以 SQLSTATE 40001 的形式到来。序列化失败处理一节解释说，包含决定发出哪些 SQL 的逻辑在内，必须重做整个事务，所以 PostgreSQL 不提供自动重试。要重做的对象是 40001 和死锁（40P01），而对于 23505、23P01，它们可能是永久性的错误，要更谨慎。psycopg 3 通过异常的 sqlstate 给出代码，错误码附录建议根据代码而不是消息措辞来分支。

不变量	放在哪里	工具
一个邮箱对应一个会员	约束	UNIQUE + ON CONFLICT
同一房间的时间不重叠	约束	EXCLUDE + btree_gist + [)
一个座位一名乘客	约束	DEFERRABLE UNIQUE
多行的合计条件	隔离	SERIALIZABLE + 40001 重试
外部邮件只发一次	应用	幂等键、outbox

在现场相遇的样子

约束也会检查已有的行。ALTER TABLE 文档写道，添加约束时，通常会扫描整张表，检查所有行是否满足条件。所以给生产表加一个 UNIQUE，要从“已经进去的重复数据怎么清理”开始。像预订这样不能删除的记录，不要删，而是改成取消状态，再只对活动行加约束。重试循环中最常见的事故，是用 except Exception 把所有错误都重做的代码。违反约束重做一百次，结果也一样，次数用完之后，甚至会出现吞掉失败、却报告成功的代码。

下一项实验要做什么

用两个会话重现重复注册，然后清理已有的重复，并用 UNIQUE 和 ON CONFLICT 挡住。在预订表里找出已有的重叠并取消，加上排除约束，用 DEFERRABLE 解开座位互换。用 psycopg 3 写一个只重试 40001 的重试循环，确认它在评分器故意制造的错误面前能正确停下来，最后把每个不变量该放在哪里整理成表。