13 言出法随：函数、触发器与存储过程

数据库端逻辑最危险的误解，是把“PostgreSQL 能做”当成“应该放进 PostgreSQL”。函数、触发器和过程都能执行复杂逻辑，但三者不是更高级的 应用框架。它们首先是不同的数据库对象，各有调用方式、事务语义、规划 承诺、权限边界和可观测性。

本章只追问一个工程问题：

一条规则由谁负责，才能在所有写入口下保持正确，同时仍然能够测试、 发布、观测和回退？

答案不是“全部放应用”或“全部放数据库”。更可靠的分层是：

单列与单行合法域
  └─ NOT NULL / CHECK / 类型

表间引用与可声明关系
  └─ UNIQUE / FOREIGN KEY / EXCLUDE

旧行到新行的数据库状态跃迁
  └─ BEFORE ROW trigger（确实无法声明时）

事务最终点的跨表断言
  └─ deferred constraint trigger（知道并发边界时）

应用可调用的窄数据库命令
  └─ SECURITY INVOKER / SECURITY DEFINER function

需要分批提交的数据库维护动作
  └─ top-level CALL + procedure

跨系统工作流、重试策略与调度
  └─ 应用、outbox、worker 与平台

越靠上越声明式、越容易由 PostgreSQL 自动维护；越靠下越需要显式协议。 触发器不是把跨系统工作流藏起来的捷径，过程也不是调度器。

本章完成后

你应当能够：

• 先用约束、普通 SQL 和事务表达规则，再判断是否真的需要例程；
• 区分 SQL function、PL/pgSQL function、trigger function 与 procedure；
• 设计标量、复合、集合返回和多态函数，并控制重载歧义；
• 把 VOLATILE、STABLE、IMMUTABLE 当成给优化器的承诺；
• 正确声明 STRICT、PARALLEL SAFE/RESTRICTED/UNSAFE、COST 与 ROWS；
• 使用稳定 SQLSTATE、DETAIL 与 HINT 定义机器可消费的错误合同；
• 理解 EXCEPTION 块为什么形成子事务，以及它不能替代正常控制流；
• 区分行级、语句级、BEFORE、AFTER 与 INSTEAD OF 触发器；
• 用 transition table 对批量变更做一次集合处理；
• 解释 deferred constraint trigger 检查的是事务最终状态，而不是 任意并发历史；
• 识别递归、触发顺序、每行放大与隐藏 I/O；
• 准确说明 function 与 procedure 的调用和事务控制边界；
• 让批处理可重入、可续跑、可限批，而不把过程误当作 scheduler；
• 安全编写 SECURITY DEFINER：NOLOGIN owner、固定 search_path、 全限定对象名、撤销 PUBLIC EXECUTE、输入收窄与最小授权；
• 从 pg_proc、pg_trigger、ACL、SQLSTATE 和函数统计中取得证据；
• 在 Pigsty L1 中交付声明、SQL 变更、测试证据、观察窗口和回退入口。

贯穿实验：订单状态护栏

本章不使用只展示语法的零散对象，而是维护一个完整的 shop_ch13 实验：

夹具刻意让不同机制叠在同一事务里：

pg36_app
  │ EXECUTE only
  ▼
capture_payment(...)
  ├─ lock order
  ├─ insert captured payment
  └─ update order: created -> paid
       ├─ BEFORE ROW validates edge + version
       ├─ AFTER STATEMENT writes row history + statement audit
       └─ deferred constraint triggers validate final payment total
            ▼
          COMMIT or SQLSTATE P3614

这条链路同时说明两个事实：

1. SECURITY DEFINER 不是绕开约束；提升后的命令仍然经过触发器和提交点 验证；
2. 触发器只能参与当前 PostgreSQL 事务，不能证明外部副作用已经完成。

实验入口由 ch13 实验合同 统一说明：

• 实验合同
• 对象与权限安装
• 完整验收入口
• 例程目录证据
• 触发器目录证据
• ACL 证据
• 状态与校验和
• 自动审校器
• 精确复位
• Pigsty 声明片段
• v1.1 proposal

正式实验在 PostgreSQL 18.6 直连路径运行，同时把适用范围限制为 PostgreSQL 14–18。它没有经过 PgBouncer，因此不能声称 pooler 路径已验证。

快速运行

沿用前章的受控管理 service：

export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

PG36_EVIDENCE_DIR="$PWD/evidence/ch13" \
  ./static/labs/ch13/task.sh all

all 会：

1. 验证 ch04-v1 模型与 ch05 业务 checksum；
2. 精确重建 shop_ch13；
3. 采集 pg_proc、pg_trigger 和 ACL；
4. 穷举七个状态的 49 个有序对，证明恰好六条合法边；
5. 以 pg36_app 调用成功命令；
6. 验证七个失败 case、六类 SQLSTATE；
7. 证明异常子事务、transition table 与函数计数；
8. 证明显式事务里的过程以 2D000 失败；
9. 顶层调用过程取得 2/2/1，重跑取得 0；
10. 拒绝错误 token、错误 target 和活跃 worker 下的 reset；
11. 精确复位，再完整重建和复验第二遍。

成功摘要为：

status=ok
business=orders:13/payments:1/history:10/audit:6
boundary=check+before-row+deferred-constraint+security-definer
failure=42501/P3613/P3614/P3616/P3618/2D000
transaction=exception-subtransaction+commit-time-check+procedure-batches
observability=transition-table+function-stats+sqlstate
release=1.1-proposal
release_candidate_checksum=32377d82a7ce958aa50b0077ebe99c47d27672223c3c77fd9f91072d3745de9d

计时和生成的 identity 值不是 golden。验收比较状态分布、权限矩阵、 SQLSTATE、批次关系和 canonical proposal checksum。

失败合同

自定义 P36xx 只属于本书实验合同；真实项目必须建立自己的错误注册表， 避免不同模块复用同一码位。调用方匹配 SQLSTATE，而不是匹配可能被翻译、 改写或补充上下文的 message。

学习路径

13.1 先决定逻辑放在哪里

• 13.1.1 数据不变量、批处理与接口封装
• 13.1.2 数据库内聚与应用可演进性的权衡
• 13.1.3 不用触发器隐藏跨系统工作流

先建立决策算法。如果跳过这一节，后面的语法很容易变成“看到锤子，到处 找钉子”。

13.2 SQL 与 PL/pgSQL 函数

• 13.2.1 参数、返回值、集合与多态
• 13.2.2 波动性、严格性、并行安全与规划影响
• 13.2.3 异常、子事务与错误契约

函数是查询表达式的一部分，因此必须同时理解类型系统、优化器承诺和 调用者事务。

13.3 触发器与约束触发器

• 13.3.1 行级、语句级与 transition table
• 13.3.2 BEFORE、AFTER 与 INSTEAD OF
• 13.3.3 递归、顺序、批量写入与隐藏成本

触发器要从“自动执行”还原为“写语句执行计划中隐藏的一段同步代码”。

13.4 过程、任务与事务控制

• 13.4.1 procedure 与 function 的边界
• 13.4.2 批处理、维护任务与显式事务
• 13.4.3 调度属于平台职责，不由过程本身解决

过程最独特的能力是受限的事务控制，不是“函数的加强版”。

13.5 安全、测试与观测

• 13.5.1 SECURITY DEFINER、固定 search_path 与最小权限
• 13.5.2 单元测试、属性测试与并发测试
• 13.5.3 函数级统计、日志与慢调用定位

例程一旦成为权限边界，就必须按 API 和安全敏感代码来发布，而不是当成 一段随手粘贴的 SQL。

13.6 实战：为订单状态建立数据库端护栏

• 13.6.1 比较约束、函数、触发器与应用实现
• 13.6.2 注入绕过应用的错误写入
• 13.6.3 在 Pigsty L1 输出实现选择、测试证据与回退脚本

最后把决策、对象、失败、证据、声明和回退压成一份可评审交付物。

版本与证据边界

本章使用 PostgreSQL 14–18 共有的核心能力；anycompatible 多态类型族从 14 开始，因此实验下限设为 14。PostgreSQL 18.6 是本次实际验证版本， 不是暗示 14–17 会自动通过所有环境差异。

权威语义以以下文档为准：

• User-Defined Functions
• CREATE FUNCTION
• Function Volatility Categories
• Overview of Trigger Behavior
• CREATE TRIGGER
• User-Defined Procedures
• PL/pgSQL Transaction Management
• Pigsty Monitoring System

本章会明确区分“官方定义”“本章设计选择”和“本地实验观察”。只有第三类 结论能够由当前 evidence 目录证明。

上一章：一气呵成：从数据库契约到后端服务 · 返回上卷导读 · 下一章：博采众长：内核分支与扩展生态 · 查看全书目录 · 查看索引中心

13.1 先决定逻辑放在哪里

写函数之前，先写一句可以被反驳的责任声明：

这条规则必须位于数据库，因为……

如果理由只是“这样少写几行应用代码”或“数据库更快”，先停下来。逻辑位置 决定的不只是延迟，还决定谁能绕过规则、谁负责版本兼容、错误怎样传播、 副作用何时提交、故障在哪里观测。

本节给出一个从声明式机制向外扩展的决策顺序。

13.1.1 数据不变量、批处理与接口封装

从最窄、最声明式的机制开始

同一条规则可能有多种写法：

-- 声明式
total_minor bigint NOT NULL CHECK (total_minor > 0)

-- 触发式
CREATE TRIGGER validate_total
BEFORE INSERT OR UPDATE ON sales_order
FOR EACH ROW EXECUTE FUNCTION validate_total();

-- 命令式
IF p_total_minor <= 0 THEN
    RAISE EXCEPTION ...;
END IF;

三者都能拒绝负数，但并不等价。CHECK：

• 对所有普通写入口生效；
• 由系统目录公开表达；
• 能被 schema diff、dump、迁移工具和错误字段识别；
• 不需要人为维护触发器执行顺序；
• 让 PostgreSQL 自己生成稳定的约束拒绝。

因此第一条规则是：

能由类型、NOT NULL、CHECK、UNIQUE、FOREIGN KEY 或 EXCLUDE 正确表达的规则，不先写触发器。

“正确表达”也有限制。PostgreSQL 假定 CHECK 对同一行是不可变判断； 它不会持续重新检查约束表达式引用的其他行。跨行、跨表查询不应伪装成 普通 CHECK。这类语义要重新建模、用原生唯一/引用约束，或在确实必要时 进入事务逻辑。参见 Constraints。

用规则形状选工具

表里的“首选”不是绝对答案，而是评审起点。每次偏离都要留下理由和测试。

批处理先问能否是一条 SQL

PL/pgSQL 循环很直观：

FOR target IN
    SELECT order_id FROM sales_order WHERE ...
LOOP
    UPDATE sales_order
    SET status = 'expired'
    WHERE order_id = target.order_id;
END LOOP;

但一条集合更新通常更清楚：

UPDATE sales_order
SET status = 'expired'
WHERE status = 'created'
  AND created_at < $1;

集合 SQL 给优化器更多空间，也避免一次业务动作产生 N 次解析、执行和触发 边界。只有当批次需要独立提交、外部节流、checkpoint、队列竞争或每项错误 隔离时，才进入过程或外部 worker。即使如此，每一批内部仍应尽量使用集合 SQL。

function 是接口，不是代码收纳箱

把 SQL 包进 function 只有在形成明确合同后才有意义：

name + input types
  -> privilege
  -> transaction and lock behavior
  -> result shape
  -> SQLSTATE set
  -> observable identity
  -> compatible replacement / rollback

本章的应用角色没有 shop_ch13.sales_order 的 SELECT 或 UPDATE， 只得到：

GRANT EXECUTE ON FUNCTION
    shop_ch13.order_snapshot(bigint)
TO pg36_app;

GRANT EXECUTE ON FUNCTION
    shop_ch13.transition_order(bigint, bigint, text, text)
TO pg36_app;

这是真正的接口封装：底表权限被拿走，函数签名、结果和错误成为协议。若应用 仍有任意底表 DML，函数往往只是可选的便利封装，不能被宣称为唯一护栏。

一次决策走查

对“订单进入 paid 前必须完成支付”逐层判断：

1. status 属于有限集合：CHECK；
2. created → paid 是旧行到新行的边：条件更新或 transition guard；
3. 捕获金额等于订单金额：跨 sales_order/payment 的事务最终断言；
4. 应用要原子完成插支付和改状态：command function 或应用事务；
5. 支付成功后通知履约：同事务写 outbox；
6. 调用远端履约 API：提交后由 worker 执行。

不同部分由不同机制负责，不必强迫一条“业务规则”只有一个物理位置。

13.1.2 数据库内聚与应用可演进性的权衡

“把规则放近数据”能减少绕过路径；“把流程放在应用”能获得更好的协议演进 和跨系统编排。真正的权衡不是数据库与应用谁更强，而是变化与失败在哪一层 最容易被控制。

六个评审维度

1. 覆盖所有写入口

如果写入来自 API、ETL、管理脚本、批处理和多个语言栈，数据库约束覆盖面 最大。只在一个应用 handler 中校验，其他入口可能绕过。

但覆盖面也有前提：

• 超级用户、表 owner 和复制/恢复路径拥有更高能力；
• session_replication_role 等管理开关会改变触发行为；
• 逻辑复制默认重放的是行变化，不是在订阅端重新执行发布端所有业务逻辑；
• 管理员仍可能删除或禁用对象。

所以“数据库保证”是权限与部署合同下的保证，不是对所有特权行为的魔法。

2. 并发仲裁

唯一性、引用完整性、行锁和 MVCC 由 PostgreSQL 掌握最终事实。应用先 SELECT 再判断通常有竞态；原子条件写、唯一约束或数据库事务更可靠。

但触发器也不会自动解决并发：

T1 reads aggregate A
T2 reads aggregate A
T1 writes based on A
T2 writes based on A

如果规则依赖聚合或多行集合，仍要设计锁顺序、隔离级别、唯一仲裁点或整 事务重试。deferred trigger 只是晚检查，不等于串行化。

3. 发布耦合

数据库函数签名和触发器行为是应用依赖。变更时要回答：

• 旧应用与新函数能否共存？
• 默认参数是否改变调用解析？
• 返回列新增、删除、改名会不会破坏驱动映射？
• trigger 在 expand 阶段会不会让旧写入失败？
• function replacement 会不会拿到等待中的对象锁？
• 回退应用时，旧数据库行为是否还兼容？

第 11 章的 expand/migrate/validate/switch 思路同样适用于例程：先增加兼容 能力，再迁移调用，观察后才收缩旧接口。

4. 调试与可见性

应用调用链通常天然有 trace、请求参数、部署版本和统一日志。数据库函数 可能只在 SQL 文本中显示为一次调用；触发器甚至不出现在原始业务 SQL 里。

如果选择数据库端逻辑，必须补回：

• 稳定 function/trigger identity；
• 低基数 application_name；
• SQLSTATE、约束名和 routine context；
• pg_stat_user_functions 或事务级计数；
• pg_stat_statements、慢日志与 lock/wait 证据；
• 业务 actor、request/trace ID 的安全关联。

看不见的正确逻辑，在事故中仍然是风险。

5. 团队所有权

例程不是“DBA 的代码”或“开发的 SQL”。需要明确：

• 谁评审业务语义；
• 谁评审权限与 search_path；
• 谁维护迁移顺序；
• 谁运行负面和并发测试；
• 谁响应慢调用或递归事故；
• 谁批准回退。

所有权不清时，隐藏自动行为尤其危险。

6. 可移植性

PL/pgSQL、transition table、constraint trigger、SECURITY DEFINER 和 过程事务控制都有 PostgreSQL 特定语义。若产品确实要求多数据库运行， 应用实现可能更易移植。

反过来，为不存在的迁移目标牺牲当前数据库的原生正确性也没有价值。把 “未来也许换库”转化为明确概率、成本和退出计划，而不是口号。

一个可执行评分卡

对候选规则逐项打分：

评分卡不替团队做决定；它迫使理由显式化。

推荐的职责声明

本章实验采用：

database owns:
  state domain
  legal transition edges
  version step
  payment/order commit-time invariant
  history and statement audit in the same transaction

application owns:
  authentication and authorization context
  command choice
  optimistic-conflict retry policy
  API response
  outbox consumption and external side effects

Pigsty/platform owns:
  role/database declaration
  primary routing and pooling
  secret delivery
  metrics/logs/alerts
  scheduled invocation and overlap prevention

这份声明比“业务逻辑在数据库”精确得多。

13.1.3 不用触发器隐藏跨系统工作流

触发器与原语句同步成败

普通 DML trigger 在触发它的语句和事务中执行。触发函数报错，原语句也 失败；事务回滚，触发器写入也回滚。这正适合：

• 派生同数据库内的审计行；
• 验证 OLD → NEW；
• 同事务维护局部冗余；
• 写入 outbox 事实。

它不适合直接完成：

• HTTP 请求；
• 发邮件；
• 发 Kafka/RabbitMQ 消息后等待确认；
• 调用支付或履约系统；
• 写入另一个无法参与同一 PostgreSQL 事务的数据源。

这些动作不具备与 PostgreSQL 提交相同的原子边界。

“触发器里调用 HTTP”为什么会失败

假设触发器同步调用远端服务：

UPDATE order
  -> trigger calls remote API
  -> remote succeeds
  -> PostgreSQL COMMIT fails

外部动作已经发生，数据库却回滚。反过来：

UPDATE order
  -> remote times out
  -> unknown whether remote succeeded
  -> database transaction holds locks while waiting

此时重试可能重复副作用，数据库连接、行锁和事务快照还被远程尾延迟拖住。 把网络调用包装成 extension function 并不会改变分布式事务事实。

正确边界：同事务写 outbox

第 12 章使用：

BEGIN;

UPDATE order ...;
INSERT INTO outbox (...);

COMMIT;

数据库只保证“状态与待发布事实一起提交”。提交后 worker：

1. 读取/领取 outbox；
2. 调用外部系统；
3. 使用幂等键处理至少一次投递；
4. 记录成功、失败、重试与死信；
5. 暴露 backlog、age 和错误指标。

这不是把分布式问题消掉，而是把不可控的同步双写改造成可恢复状态机。

NOTIFY 也不是 durable queue

LISTEN/NOTIFY 适合低延迟提示，但通知不是持久任务队列。消费者断开、事务 提交边界、payload 限制与处理确认都需要额外设计。可靠工作仍应以表中 durable fact 为准，通知只用于“醒来看看”。

不把 scheduler 藏进 procedure

procedure 只定义“被调用时做什么”。它不会决定：

• 每天几点执行；
• failover 后由哪台 primary 执行；
• 上一轮未结束是否跳过；
• 失败重试几次；
• 超期多久告警；
• 如何暂停、补跑和审计。

这些属于 pg_cron、OS cron、systemd timer、作业平台或应用 worker。 数据库过程可以是 job body，但不是 job control plane。

进入触发器前的停止线

若候选触发器满足任一项，先重新设计：

• 发起远端 I/O；
• 吞掉异常后继续提交；
• 根据 wall-clock 或不稳定配置伪装为 IMMUTABLE；
• 每行再次扫描整张大表；
• 修改触发表并依赖 pg_trigger_depth() 阻止递归；
• 依赖另一个同类 trigger 的名字顺序才能正确；
• 失败没有稳定 SQLSTATE；
• 无法在绕过应用的 SQL 下测试；
• 无法说明 bulk load 的放大倍数；
• 无法提供停用、兼容和回退方案。

触发器的价值是让数据库不变量覆盖所有写入口；一旦它变成隐藏工作流引擎， 这个优势很快会被运维风险抵消。

本节结论

选择逻辑位置时按以下顺序停靠：

declarative constraint
  -> set-based SQL
  -> explicit application transaction
  -> narrow function boundary
  -> trigger for unavoidable implicit invariant
  -> procedure for controlled multi-transaction maintenance
  -> external worker/scheduler for cross-system lifecycle

不是每条规则都必须走到最后。成熟设计往往在最早能够正确表达的位置停止。

返回本章目录 · 下一节：SQL 与 PL/pgSQL 函数 · 查看全书目录 · 查看索引中心

13.2 SQL 与 PL/pgSQL 函数

PostgreSQL function 可以出现在 SELECT 列表、WHERE、索引表达式、 生成列、约束、触发器和另一个例程中。正因为它嵌入查询，函数声明不只是 文档；优化器会相信波动性、严格性、并行安全、成本和预估行数。

本节先把函数看成一个带类型和规划属性的数据库 API，再进入 PL/pgSQL 控制流。

13.2.1 参数、返回值、集合与多态

先选最小语言

如果函数只需要一条或几条集合查询，优先 LANGUAGE sql：

CREATE FUNCTION shop_ch13.allowed_transition(
    p_from text,
    p_to text
)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $function$
    SELECT (p_from, p_to) IN (
        ('created', 'paid'),
        ('created', 'canceled'),
        ('created', 'expired'),
        ('paid', 'packing'),
        ('packing', 'shipped'),
        ('shipped', 'completed')
    )
$function$;

需要局部变量、分支、循环、动态 SQL、异常处理或多条命令编排时，才使用 LANGUAGE plpgsql。语言选择和 function/procedure 选择是两个维度： PL/pgSQL 既可以实现 function，也可以实现 procedure。

PostgreSQL 还支持其他过程语言和 C 扩展；它们引入安装、信任、二进制兼容 与崩溃边界，不属于“为了少写 SQL”就启用的选项。参见 User-Defined Functions。

参数模式与调用方式

常见参数模式：

命名参数允许：

SELECT *
FROM shop_ch13.transition_order(
    p_order_id        => 101,
    p_expected_version => 0,
    p_target_status   => 'canceled',
    p_actor           => 'api:user-42'
);

命名调用提高可读性，却也把参数名变成外部兼容面。CREATE OR REPLACE FUNCTION 不能随意改已有输入参数名；驱动和 SQL 可能已经按名调用。

默认参数必须位于无默认输入参数之后。增加默认参数看似兼容，却可能与已有 重载产生歧义。发布前要用实际调用类型测试解析，而不是只看 DDL 成功。

标量、复合与集合返回

标量

RETURNS boolean

适合纯判断或单一计算。调用者可把它嵌入表达式。

多列单行

本章使用 RETURNS TABLE：

CREATE FUNCTION shop_ch13.order_snapshot(p_order_id bigint)
RETURNS TABLE (
    result_order_id bigint,
    result_order_ref text,
    result_total_minor bigint,
    result_status text,
    result_version bigint,
    result_updated_at timestamptz
)
...

调用时把函数放在 FROM：

SELECT *
FROM shop_ch13.order_snapshot(102);

不要依赖 SELECT function(...) 返回的匿名复合显示格式；明确列形状更适合 驱动映射和版本评审。

集合

RETURNS SETOF some_type 或 RETURNS TABLE (...) 可以返回多行。集合函数 应回答：

• 顺序是否有合同；若有，函数内部或调用方必须显式 ORDER BY；
• 最大行数是多少；
• 能否被谓词下推或内联；
• ROWS 预估是否合理；
• 空集与一行 NULL 是否被清楚区分。

无 ORDER BY 的集合没有稳定顺序。把测试机当前顺序冻结为 API 行为，会在 计划、并行度或版本变化时失败。

表的复合类型

RETURNS shop_ch13.sales_order 很方便，但把函数 API 与整张表的物理列强 绑定。新增、删除、重排列会改变结果类型。对外接口通常更适合命名输出列或 专用复合类型。

多态类型

多态函数让实参类型决定返回类型。PostgreSQL 14+ 的 anycompatible 类型族会为多个实参选择共同类型：

CREATE FUNCTION clamp_value(
    value anycompatible,
    low   anycompatible,
    high  anycompatible
)
RETURNS anycompatible
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
AS $function$
    SELECT greatest($2, least($1, $3))
$function$;

调用：

SELECT clamp_value(12, 0, 10);              -- integer 10
SELECT clamp_value(12.5::numeric, 0, 10);   -- numeric 10

anyelement/anyarray 要求相关参数是同一具体类型族；anycompatible* 允许 寻找可隐式转换的共同类型。多态并不表示动态类型逃逸：解析阶段必须能从 输入推导出实际类型。

使用多态前问三个问题：

1. 不同类型是否真的共享相同语义，而不只是共享运算符名字？
2. 隐式转换会不会丢精度或选到意外类型？
3. 错误是否比几个显式重载更难理解？

重载是类型解析协议

同一 schema 可以有同名、不同输入类型的函数：

quote_id(bigint)
quote_id(uuid)

PostgreSQL 根据参数数量、类型、隐式转换、首选类型和 search_path 解析。 未定型字符串字面量、默认参数和 VARIADIC 会增加歧义：

SELECT quote_id('42');          -- '42' 初始类型 unknown
SELECT quote_id(42::bigint);    -- 明确

对安全敏感调用：

• schema-qualify function；
• 给不明确的实参加显式 cast；
• 不在不受信 schema 中暴露可劫持的同名重载；
• 避免依赖微妙的隐式转换优先级。

官方 Function Overloading 明确提醒：重载在存在不可信用户的数据库中带来额外安全注意事项。

SQL body 的两种写法

字符串 body：

AS $function$
    SELECT ...
$function$;

在函数执行时解析。SQL-standard body：

RETURN expression;

或 BEGIN ATOMIC ... END 在创建时解析，能更早发现对象与类型错误，也能建立 更明确的依赖，但不适用于所有动态场景。无论使用哪一种，都要把 source 纳入版本库；从 pg_get_functiondef() dump 出来的结果是运行态证据，不是 源代码评审的替代品。

13.2.2 波动性、严格性、并行安全与规划影响

波动性是承诺，不是优化提示

三类波动性：

VOLATILE 是默认值。不要为了“让它更快”错误标成 IMMUTABLE。优化器可对 不可变常量调用做预计算，prepared statement 还可能复用已折叠结果。

本章：

allowed_transition(text,text) -> IMMUTABLE
order_snapshot(bigint)        -> STABLE
transition_order(...)         -> VOLATILE
capture_payment(...)          -> VOLATILE
trigger functions             -> VOLATILE

波动性也决定可见快照

对 SQL 和标准过程语言函数：

• STABLE / IMMUTABLE 内部查询使用调用语句建立的快照；
• VOLATILE 函数执行的每条查询可取得更新的快照；
• STABLE / IMMUTABLE 不能直接包含非 SELECT SQL 命令。

从表读取的函数通常最多是 STABLE，不是 IMMUTABLE。PostgreSQL 不会 彻底证明你对 IMMUTABLE 的承诺；错误标签可能返回过期或不一致结果。

依赖 TimeZone、lc_*、配置参数或 collation 的转换也往往不是 IMMUTABLE。例如时间文本解析在不同设置下可能不同。

完整语义见 Function Volatility Categories。

STRICT 的精确含义

STRICT 等价于 RETURNS NULL ON NULL INPUT：

任一输入为 NULL
  -> 不执行函数 body
  -> 直接返回 NULL

它不是“做严格校验”。如果 NULL 应返回业务错误、空集合或默认值，就不能 声明 STRICT。

本章的纯判断和 snapshot 是 strict；command function 需要自己给出输入 错误合同，因此没有用 STRICT 静默短路。

并行标签

默认是 UNSAFE。修改数据库、改事务状态、访问 sequence、持久改配置的 函数必须 unsafe；访问临时表、cursor、prepared statement 或 backend-local 状态通常 restricted。

把不安全函数误标 safe 不只是性能问题，可能报错或产生错误结果。拿不准就 保留默认 UNSAFE。规则由 CREATE FUNCTION 定义。

COST 与 ROWS

规划器不知道自定义函数真实成本，只能使用声明：

ALTER FUNCTION expensive_match(text)
COST 1000;

ALTER FUNCTION expand_tokens(text)
ROWS 20;

• COST 使用 cpu_operator_cost 单位；
• 对 set-returning function，cost 是每行成本；
• ROWS 只用于集合返回，默认估算可能与实际相差很大。

错误估算会改变 join 顺序、调用次数和计划形状。先用真实计划和数据证明偏差， 再调整；不要把 COST 当成强制 hint。

SQL function 内联与可观测性

满足条件的简单 SQL function 可能被优化器内联，调用形态会融入外层查询。 这通常有利于谓词优化，但意味着：

• 不要依赖函数一定作为独立执行节点；
• 函数级计数不等于完整调用 trace；
• 观察时同时看外层 query、plan 和 pg_stat_statements；
• 安全敏感函数不能靠“看起来像独立调用”建立边界。

SECURITY DEFINER、配置属性和更复杂 body 会限制可用的优化。不要为了内联 牺牲权限正确性。

从目录审计声明

routine-catalog.sql 读取：

SELECT
    p.oid::regprocedure,
    p.prokind,       -- f=function, p=procedure
    p.provolatile,   -- i/s/v
    p.proisstrict,
    p.proparallel,   -- s/r/u
    p.prosecdef,
    p.proconfig
FROM pg_proc AS p
...

DDL source 说明意图；pg_proc 证明目标数据库实际装了什么。发布门禁要比较 两者，而不是二选一。

13.2.3 异常、子事务与错误契约

错误是接口结果的一部分

不稳定的做法：

RAISE EXCEPTION 'bad order';

它默认使用通用 P0001，调用方只能解析 message。更好的合同：

RAISE EXCEPTION USING
    ERRCODE = 'P3613',
    MESSAGE = 'order status transition rejected',
    DETAIL = format(
        'order_id=%s transition=%s->%s',
        OLD.order_id,
        OLD.status,
        NEW.status
    ),
    HINT = 'Use an allowed transition through the command API.';

客户端判断：

SQLSTATE P3613 -> domain transition rejected
SQLSTATE 40001 -> retry whole transaction within budget
SQLSTATE 42501 -> deployment/privilege defect, do not retry

message 给人读，SQLSTATE 给程序判断。命名约束、schema/table/column 和 routine context 也应保留给诊断。

自定义 SQLSTATE 可以使用除 00000 之外的五字符编码，但应维护集中注册表。 不要使用以 000 结尾的 category code，因为异常处理只能匹配整个类别， 难以精确捕获。

默认传播通常是正确答案

没有 EXCEPTION 块时，函数错误向外传播，调用语句失败；调用者事务进入 相应失败状态。这保留了原子性。

不要在底层函数中这样写：

EXCEPTION WHEN OTHERS THEN
    RETURN NULL;

它会：

• 把权限错误、数据损坏和编程错误伪装成“无结果”；
• 丢掉 SQLSTATE 和上下文；
• 可能让外层事务提交部分工作；
• 让告警与重试策略失去依据。

尤其注意：OTHERS 不捕获 QUERY_CANCELED 和 ASSERT_FAILURE；显式捕获 它们通常也不明智。

EXCEPTION 块形成子事务

PL/pgSQL：

BEGIN
    -- inner block
    UPDATE ...;
    PERFORM risky_call();
EXCEPTION
    WHEN SQLSTATE 'P3613' THEN
        ...
END;

进入带 handler 的 block 后，内部持久化修改在错误时回滚；局部变量保持错误 发生时的值，handler 继续执行。底层由子事务实现，进入/退出比普通 block 昂贵。

本章 exception-probe.sql 证明：

event=caught-inner-subtransaction
sqlstate=P3613
status_after=created
version_after=0

非法更新没有逃出 inner block，外层仍取得错误字段。整个 probe 最后 ROLLBACK，不污染 fixture。

读取原始错误字段

在 handler 中：

GET STACKED DIAGNOSTICS
    caught_state   = RETURNED_SQLSTATE,
    caught_message = MESSAGE_TEXT,
    constraint_id  = CONSTRAINT_NAME,
    detail_text    = PG_EXCEPTION_DETAIL,
    hint_text      = PG_EXCEPTION_HINT,
    context_text   = PG_EXCEPTION_CONTEXT;

优先保留结构化字段；不要用正则从 message 提取约束名。控制结构与可用字段 见 PL/pgSQL Control Structures。

只捕获能解决的错误

合理用途：

• 把已知底层约束错误转换成稳定领域 SQLSTATE，同时保留 cause；
• 对一项可跳过的批任务记录失败后继续；
• 实现确有必要的补偿分支；
• 测试某个失败后内部修改确实回滚。

不合理用途：

• 用 unique violation 实现常规 upsert，而不用 ON CONFLICT；
• 在函数里无限重试 serialization failure；
• 捕获所有错误并写一条 NOTICE；
• 把 statement timeout 当成空结果；
• 在 trigger 中吞错，让非法主写入提交。

重试属于更外层的整事务协议

一个 function 调用可能读写多张表、触发多个 trigger。若收到 40001 或 40P01，重试其中某条内部 SQL 不能还原事务入口快照。应由知道完整业务 意图的一层，在有界预算内重放整个事务。

自定义领域拒绝 P3613/P3614/P3616/P3618 不是瞬态数据库错误：

• P3613：调用命令错误；
• P3614：事务最终事实不一致；
• P3616：先重新读取，再由业务决定；
• P3618：支付前置条件错误。

把所有错误都自动重试只会放大负载和隐藏缺陷。

本节检查表

发布一个 function 前确认：

1. 输入类型、参数名与默认值是否是有意的兼容面；
2. 返回标量、单行、多行和顺序是否明确；
3. 多态与重载能否对实际实参唯一解析；
4. volatility 是否真能兑现；
5. NULL 是否应该 strict 短路；
6. parallel 标签是否符合内部行为；
7. COST/ROWS 是否有证据；
8. 成功、空结果、领域拒绝和系统错误是否可区分；
9. handler 是否只捕获能处理的 SQLSTATE；
10. 失败是否保持调用者事务原子性；
11. 目录属性、ACL 与 source 是否一致；
12. 能否在应用角色下执行正负路径测试。

上一节：先决定逻辑放在哪里 · 返回本章目录 · 下一节：触发器与约束触发器 · 查看全书目录 · 查看索引中心

13.3 触发器与约束触发器

trigger 是“当某类事件发生时，在同一 PostgreSQL 事务中自动调用函数”的 对象。自动不等于异步，也不等于免费：

original DML
  + trigger function SQL
  + trigger locks
  + trigger WAL
  + trigger errors
= caller latency and transaction outcome

设计 trigger 时，必须同时说明事件、粒度、时机、返回语义、权限、顺序、 批量成本和失败合同。

13.3.1 行级、语句级与 transition table

行级：一次处理一对 OLD/NEW

FOR EACH ROW 对每个受影响行调用一次：

CREATE TRIGGER a_guard_order_transition
BEFORE UPDATE OF status, version
ON shop_ch13.sales_order
FOR EACH ROW
EXECUTE FUNCTION shop_ch13.guard_order_transition();

一条更新三行的 SQL，会进入 trigger function 三次。PL/pgSQL trigger function 通过特殊变量取得上下文：

本章 guard 比较：

IF NEW.status IS DISTINCT FROM OLD.status THEN
    IF NOT shop_ch13.allowed_transition(
               OLD.status,
               NEW.status
           ) THEN
        RAISE ... ERRCODE = 'P3613';
    END IF;

    IF NEW.version IS DISTINCT FROM OLD.version + 1 THEN
        RAISE ... ERRCODE = 'P3615';
    END IF;
END IF;

这是行级 trigger 的合适形状：判断只依赖一对旧、新行和纯 transition matrix，没有为每行扫描整张表。

语句级：一次处理整个命令

FOR EACH STATEMENT 对一条符合事件的语句调用一次，即使最终影响零行也可能 调用。它没有单行 OLD/NEW。如果需要看到受影响集合，使用 transition relations：

CREATE TRIGGER z_audit_order_transition
AFTER UPDATE
ON shop_ch13.sales_order
REFERENCING
    OLD TABLE AS old_rows
    NEW TABLE AS new_rows
FOR EACH STATEMENT
EXECUTE FUNCTION shop_ch13.audit_order_transition();

trigger function 将它们当只读关系使用：

INSERT INTO shop_ch13.order_history (...)
SELECT ...
FROM old_rows
JOIN new_rows USING (order_id)
WHERE old_rows.status IS DISTINCT FROM new_rows.status;

随后写一条 statement audit：

INSERT INTO shop_ch13.statement_audit (...)
SELECT
    pg_current_xact_id(),
    actor,
    session_user,
    count(*)::integer,
    array_agg(new_rows.order_id ORDER BY new_rows.order_id),
    statement_timestamp()
FROM old_rows
JOIN new_rows USING (order_id)
WHERE old_rows.status IS DISTINCT FROM new_rows.status;

实验中：

UPDATE shop_ch13.sales_order
SET status = 'canceled', version = version + 1
WHERE order_id IN (105, 106, 107);

得到：

affected_count=3
order_ids={105,106,107}
statement_audit rows added=1
order_history rows added=3

这比 row trigger 内每行再做聚合更符合集合模型。

transition table 的边界

transition relations：

• 只用于 AFTER trigger；
• 捕获一条原始 SQL 对该关系形成的旧/新行集合；
• 可以给 AFTER ROW 或 AFTER STATEMENT trigger 使用；
• 不能与 constraint trigger 结合；
• PostgreSQL 当前不允许带 transition relations 的 UPDATE trigger 同时使用 UPDATE OF column_list；
• 会物化变更集合，因此大批量语句要评估内存、临时文件与延迟。

它们不是跨事务 change stream，也不是 logical decoding 的替代品。

constraint trigger

用户定义的 constraint trigger：

• 使用 CREATE CONSTRAINT TRIGGER；
• 必须是 plain table 上的 AFTER ROW trigger；
• 可声明 DEFERRABLE 和 INITIALLY DEFERRED；
• 可被 SET CONSTRAINTS 调整到事务末尾或立即检查；
• 同样在当前事务中执行。

本章分别挂在订单与支付表：

CREATE CONSTRAINT TRIGGER z_validate_paid_order
AFTER INSERT OR UPDATE OF status, total_minor
ON shop_ch13.sales_order
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION shop_ch13.validate_paid_order();

CREATE CONSTRAINT TRIGGER z_validate_payment
AFTER INSERT OR UPDATE OR DELETE
ON shop_ch13.payment
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION shop_ch13.validate_paid_order();

command function 先插入 captured payment，再把订单改为 paid。两个中间瞬间 分别不满足最终关系，但提交点满足：

inside transaction:
  payment captured + order created   -- 暂时不一致
  payment captured + order paid      -- 最终一致
COMMIT:
  deferred checks run                -- 通过

只把订单改成 paid，则提交点返回 P3614，订单更新、history 和 statement audit 全部回滚。

延迟不等于并发安全

constraint trigger 能检查当前事务看到的最终状态，却不会自动选择正确锁。 例如两个事务并发改变同一聚合的不同明细，如果没有共同仲裁行、适当锁或 serializable 协议，双方可能基于不完整视图判断。

本章 capture_payment() 先：

SELECT ...
FROM shop_ch13.sales_order
WHERE order_id = p_order_id
FOR UPDATE;

同一订单的支付命令在订单行上串行化。这是显式并发设计，不是 deferred 关键字赠送的能力。复杂跨行断言必须单独做并发测试。

13.3.2 BEFORE、AFTER 与 INSTEAD OF

BEFORE：拒绝、规范化或改写当前行

row-level BEFORE 在行写入前运行，可以：

• 检查 OLD/NEW；
• 修改 INSERT/UPDATE 的 NEW；
• 返回 NEW 继续；
• 返回 NULL 跳过该行。

本章在合法状态变化时统一：

NEW.updated_at := statement_timestamp();
RETURN NEW;

返回 NULL 会让当前行操作被静默跳过，还会影响后续 row trigger 和命令 影响行数。除非“跳过”本身就是明确合同，通常应抛出带 SQLSTATE 的错误， 而不是让调用方误以为写入成功。

row-level BEFORE DELETE 返回 OLD 才能继续删除。trigger function 若要 复用于多个事件，必须逐个写清返回规则。

UPDATE OF 看 SET 列表，不看最终差异

BEFORE UPDATE OF status

在 status 出现在 SET 目标列表时触发，即使：

SET status = status

它也会触发。反过来，另一个 BEFORE trigger 修改 NEW.status 并不会让原本 未列出 status 的 column-specific trigger 补触发。

真正判断值是否变化要使用：

WHEN (OLD.status IS DISTINCT FROM NEW.status)

或在 body 内判断。IS DISTINCT FROM 对 NULL 有确定语义。

AFTER：观察已完成变化

AFTER 运行时：

• 当前行操作和即时约束已经完成；
• 其他 trigger 造成的变化可见；
• 返回值被忽略；
• 抛错仍会回滚原语句和事务。

适合：

• 同事务 audit/history；
• 基于最终行值派生另一张表；
• transition table 集合处理；
• deferred constraint check。

不适合远端 I/O，原因仍是它属于原事务同步延迟。

INSTEAD OF：为 view 定义写语义

INSTEAD OF 只用于 view 的 row trigger。它收到 view 的 OLD/NEW，由 trigger function 决定对底表做什么。

先确认 view 是否已经自动可更新。对简单单表 view，PostgreSQL 可以自动把 DML 映射到底表；不需要 trigger。只有复杂 join、聚合或有意设计的 view command surface 才考虑 INSTEAD OF。

示意：

CREATE VIEW order_command AS
SELECT order_id, status, version
FROM private_order;

CREATE TRIGGER route_order_command
INSTEAD OF UPDATE ON order_command
FOR EACH ROW
EXECUTE FUNCTION route_order_command();

trigger function 必须：

• 定义哪些 view 列可写；
• 拒绝其余列；
• 处理并发 version；
• 返回符合 view 形状的 NEW；
• 给出稳定 SQLSTATE；
• 保持权限边界。

如果实际意图是一个显式命令，SELECT transition_order(...) 往往比伪装成 view UPDATE 更清楚。

同类 trigger 的顺序

同一表、同一事件、同一时机的多个 trigger 按名字字母顺序执行。这个事实可 用于确定性，但不应构建脆弱流水线：

a_normalize
b_validate
c_audit

一旦正确性依赖命名，重命名、extension trigger 或迁移合并都可能改变行为。 更稳妥的选择：

• 合并强耦合逻辑到一个 trigger function；
• 让各 trigger 彼此独立、幂等；
• 用约束表达真正的最终条件；
• 在目录测试中冻结 trigger inventory。

官方顺序与语义见 CREATE TRIGGER。

运行角色

trigger 与触发语句属于同一事务。PostgreSQL 18 对 queued trigger 明确保留 排队时的 active role；若 trigger function 是 SECURITY DEFINER，则以 function owner 执行。14–17 的延迟触发角色细节必须按目标版本验证。

本章把会写保护表的 trigger function 显式设为 SECURITY DEFINER，固定 search_path，撤销应用对内部函数的 EXECUTE。这样权限意图不依赖嵌套 command function 返回后的角色状态。

创建 trigger 时，创建者需要表的 TRIGGER privilege 和 trigger function 的 EXECUTE privilege。运行态权限设计还必须结合 function 的 SECURITY INVOKER/DEFINER。

13.3.3 递归、顺序、批量写入与隐藏成本

trigger 是写路径的一部分

评估成本不要只看原 SQL：

rows affected
× row triggers per row
× SQL issued per trigger
+ statement triggers
+ deferred trigger queue
+ indexes/WAL on derived tables
+ contention introduced by trigger queries

一条 COPY 或无过滤 UPDATE 可能把平时每次一行的隐藏成本放大百万倍。

递归不会自动终止

trigger function 再写同一表，可能再次触发自己：

UPDATE t
  -> trigger
       -> UPDATE t
            -> trigger
                 -> ...

PostgreSQL 允许 cascading trigger；终止责任在设计者。

pg_trigger_depth() 能告诉当前嵌套深度，适合诊断。把：

IF pg_trigger_depth() > 1 THEN
    RETURN NEW;
END IF;

当作主要正确性机制往往掩盖模型问题：另一个合法 trigger 链也可能让深度 大于一，而真正递归仍可能从其他路径进入。优先：

• 不在 trigger 中更新触发表；
• 在 BEFORE 中直接修改 NEW；
• 将派生写放到不同关系；
• 让操作幂等并用明确状态终止；
• 对递归反例做受控测试。

ON CONFLICT 与 MERGE 会组合多个事件

INSERT ... ON CONFLICT DO UPDATE 可能先运行 row-level BEFORE INSERT， 冲突后再运行 BEFORE UPDATE。statement-level INSERT/UPDATE trigger 也有 定义好的组合顺序，即使 UPDATE 分支最终没有影响行。

因此：

• INSERT normalization 必须考虑其结果会进入 EXCLUDED；
• 两组 trigger 不应重复不可幂等副作用；
• 测试要覆盖 insert 成功、conflict update、conflict no-op；
• 不能从“最终是 UPDATE”推断只执行 UPDATE trigger。

PostgreSQL 15+ 的 MERGE 同样需要按实际 action 路径测试，不凭类比； 14 环境没有该语句。

每行查询导致 N+1

反模式：

-- 每个更新行都扫描一次 history
SELECT count(*)
INTO n
FROM order_history
WHERE order_id = NEW.order_id;

批量更新 N 行就产生 N 次查询。替代方案：

• 用原生约束；
• 在原 UPDATE 中 join/CTE；
• 用 transition table 一次集合处理；
• 为不可避免的 lookup 建正确索引；
• 把可延后的分析移到异步 worker。

审计不是“复制整行就完成”

可靠 audit 要定义：

• 记录业务变化还是所有 UPDATE；
• old/new 哪些列，是否包含敏感数据；
• actor 是认证主体、数据库 session 还是服务；
• request/trace ID 如何传递和防伪；
• transaction ID 与 statement 时间是什么语义；
• 审计表谁能改、保留多久、如何分区；
• 失败时是否必须与主写入一起回滚。

本章保存 actor 与 session_actor，但 actor 来自受控 command function 设置 的 transaction-local custom setting。由于应用没有底表 DML，不能仅靠 设置该值伪造一次写入；真正系统还要把 actor 与认证层可信上下文绑定。

分区表的额外行为

在 partitioned table 上创建 row trigger，会在已有和后续 partition 上建立 clone trigger。attach/detach、同名冲突和 major version 行为都需要目录测试。行因更新 partition key 被移动时，源 partition 的 DELETE 与目标 partition 的 INSERT trigger 也会参与。

不要只在 root table 的 \d 输出上推断所有 partition 的实际 trigger。

禁用 trigger 是高风险动作

ALTER TABLE ... DISABLE TRIGGER、replication role 或恢复路径可能绕开 业务 trigger。批量导入前“先关 trigger 提速”意味着暂时取消不变量，必须 有：

• 明确授权与维护窗口；
• 隔离写入口；
• 导入后全量验证；
• 恢复 trigger 的 finally 路径；
• 失败时数据修复方案；
• 目录与配置证据。

若规则应是不可绕过的约束，优先用原生 constraint，而不是依赖所有人永不 禁用 trigger。

从目录取得事实

trigger-catalog.sql 读取：

SELECT
    c.relname,
    t.tgname,
    t.tgfoid::regprocedure,
    t.tgdeferrable,
    t.tginitdeferred,
    t.tgoldtable,
    t.tgnewtable,
    pg_get_triggerdef(t.oid, true)
FROM pg_trigger AS t
JOIN pg_class AS c ON c.oid = t.tgrelid
WHERE NOT t.tgisinternal;

实验冻结四个 user trigger：

payment:
  z_validate_payment          AFTER ROW, deferred

sales_order:
  a_guard_order_transition    BEFORE ROW
  z_audit_order_transition    AFTER STATEMENT, old_rows/new_rows
  z_validate_paid_order       AFTER ROW, deferred

tgisinternal 过滤了外键等系统内部 trigger；不要把它们误认成“没有 trigger”。 用户定义 constraint trigger 还会在 pg_constraint 中留下 contype='t' 记录。

发布检查表

1. 为什么不是原生 constraint 或原 SQL？
2. event、row/statement、timing 与返回语义是什么？
3. 零行、单行、批量和 ON CONFLICT 路径是否测试？
4. transition table 会物化多少数据？
5. deferred check 的锁与并发协议是什么？
6. 有没有写触发表或递归链？
7. 同类 trigger 是否依赖名字顺序？
8. 运行角色和 definer owner 是否最小权限？
9. 错误是否有稳定 SQLSTATE？
10. bulk load、partition、复制和恢复行为是否明确？
11. pg_trigger inventory 是否进入 release gate？
12. 回退时是撤销新调用、禁用、替换还是删除，顺序是什么？

trigger 只有在这些问题都能回答时，才称得上数据库护栏。

上一节：SQL 与 PL/pgSQL 函数 · 返回本章目录 · 下一节：过程、任务与事务控制 · 查看全书目录 · 查看索引中心

13.4 过程、任务与事务控制

procedure 与 function 都是 routine，但 procedure 不是“返回 void 的 function”。最重要的差别是调用位置与受限的事务控制。

本节把三个经常混在一起的概念拆开：

procedure = database routine body
job       = one intended execution with identity and state
scheduler = decides when/where/how often a job executes

PostgreSQL procedure 只解决第一项。

13.4.1 procedure 与 function 的边界

调用方式决定语义

function：

SELECT *
FROM shop_ch13.transition_order(101, 0, 'canceled', 'api');

procedure：

CALL shop_ch13.expire_stale_orders(
    timestamptz '2024-02-01 00:00:00+00',
    500,
    0
);

最后一个 0 对应 INOUT p_total，调用完成后 PostgreSQL 返回包含 p_total 的一行。它不是可放进 join 的集合函数。

function 属于调用者事务

function 不能 COMMIT 或 ROLLBACK。它的所有写入、trigger 与异常和外层 语句/事务一起成败：

BEGIN;
SELECT command_function(...);
UPDATE another_table ...;
COMMIT;

这非常适合原子业务命令。若 function 尝试结束事务，会报错；不要用动态 SQL 绕过。

procedure 的事务控制有严格前提

PL/pgSQL procedure 和顶层 DO 可以结束事务，结束后 PostgreSQL 自动开始 新事务。但必须满足：

1. CALL/DO 从 top level 调用，或调用栈只有连续的 CALL/DO；
2. 外面没有显式 transaction block；
3. 中间没有 SELECT function() 等其他命令打断 procedure 调用链；
4. 当前不在带 EXCEPTION handler 的子事务 block 内；
5. procedure 不是 SECURITY DEFINER；
6. procedure 定义没有附加 SET configuration_parameter clause。

允许：

CALL p1()
  -> CALL p2()
       -> COMMIT

不允许：

CALL p1()
  -> SELECT f2()
       -> CALL p3()
            -> COMMIT

也不允许：

BEGIN;
CALL procedure_that_commits();
COMMIT;

本章故意运行后一种形式，得到：

SQLSTATE 2D000
invalid transaction termination

随后用独立 top-level CALL 成功。规则由 PL/pgSQL Transaction Management 和 CALL 定义。

SECURITY DEFINER 与事务控制不能兼得

SECURITY DEFINER procedure 不能执行 transaction control。附加 SET search_path = ... 等 configuration clause 的 procedure 也不能。

这造成一个有意的设计压力：

• 需要提权的窄业务命令：通常用原子 function；
• 需要多次提交的维护过程：使用 SECURITY INVOKER，由受控运维角色调用；
• 不要给应用一个既提权又跨事务的万能入口。

本章的 procedure：

prosecdef=false
proconfig=[]
EXECUTE for pg36_app=false

它只能由 owner/受控管理路径调用。

何时选 function

选择 function，当：

• 必须嵌入 query；
• 整个业务动作要原子提交；
• 需要返回集合；
• 要作为 trigger function；
• 需要 STRICT、volatility、parallel 等查询规划属性；
• 要用窄 SECURITY DEFINER 接口授予能力。

何时选 procedure

选择 procedure，当：

• 操作天然分成多个可独立提交批次；
• 单事务会造成不可接受的 WAL、锁、快照或恢复成本；
• 调用就是一个独立维护命令；
• 能接受部分批次已经提交；
• body 能设计为可重入、可续跑；
• 调用路径满足 transaction-control 限制。

如果 procedure 不需要结束事务，选择它的理由应是调用语义或组织方式，而不 是“名字更企业级”。

13.4.2 批处理、维护任务与显式事务

从失败恢复目标反推批次

假设要过期一亿张陈旧订单。单事务可能：

• 长时间持有 row/table locks；
• 维持旧 snapshot，阻碍 vacuum；
• 产生巨量 WAL 和 replica lag；
• 失败时回滚很久；
• 超过 statement timeout 或维护窗口。

批处理把恢复单位缩小：

select bounded candidates
  -> update one batch
  -> validate/record checkpoint
  -> commit
  -> repeat

但它放弃“全有或全无”。第 1–10 批已经提交，第 11 批失败时不能假装任务 未发生。

本章过程

setup.sql 中：

CREATE PROCEDURE shop_ch13.expire_stale_orders(
    p_before timestamptz,
    p_batch_size integer,
    INOUT p_total integer
)
LANGUAGE plpgsql
SECURITY INVOKER
AS $procedure$
DECLARE
    batch_count integer;
BEGIN
    ...
    LOOP
        PERFORM set_config(
            'pg36.actor',
            'ch13-maintenance',
            true
        );

        WITH candidate AS MATERIALIZED (
            SELECT order_id
            FROM shop_ch13.sales_order
            WHERE status = 'created'
              AND created_at < p_before
            ORDER BY order_id
            FOR UPDATE SKIP LOCKED
            LIMIT p_batch_size
        )
        UPDATE shop_ch13.sales_order AS target
        SET
            status = 'expired',
            version = target.version + 1
        FROM candidate
        WHERE target.order_id = candidate.order_id;

        GET DIAGNOSTICS batch_count = ROW_COUNT;
        p_total := p_total + batch_count;

        EXIT WHEN batch_count = 0;
        COMMIT AND CHAIN;
    END LOOP;
END
$procedure$;

实验有五张陈旧订单、batch size 2，statement audit 证明：

batch affected counts = [2, 2, 1]
p_total = 5

为什么 ORDER BY

bounded candidate 没有顺序，重跑时每批成员不可预测。ORDER BY order_id 提供稳定领取方向，也便于 evidence 和 checkpoint。

这不承诺全局处理完成顺序：并发 worker、SKIP LOCKED 和事务提交会改变 观察次序。若业务要求严格全局顺序，不能同时假设自由并发领取。

SKIP LOCKED 的精确含义

FOR UPDATE SKIP LOCKED 跳过当前无法立即取得行锁的候选，适合 queue-like 多 worker 领取。它提供的是不一致视图，因此不适合普通报表或必须看到所有 匹配行的判断。

设计 worker 时必须有终止与重扫策略：

• 本轮跳过不代表永远处理；
• 长期被锁行需要 age/backlog 告警；
• worker 崩溃后事务锁会释放；
• 已提交状态必须让重跑跳过；
• 最终扫尾不能只看某一轮 ROW_COUNT=0 就断言全局完成，除非保证没有其他 worker 和锁。

本章只运行一个受控 worker，因此 0 可作为夹具终止条件；生产并发作业要 定义更强协议。

可重入比内存计数更重要

p_total 只报告本次调用处理量，不是 durable checkpoint。真正的恢复依据是：

WHERE status = 'created'

已提交行成为 expired，重跑不会重复变化。状态跃迁和 audit 同事务提交。

复杂 backfill 应维护 durable progress：

• job/run identity；
• range 或 high-water mark；
• source/target row counts；
• last committed key；
• attempts 与 last error；
• started/heartbeat/completed time；
• release/schema version。

checkpoint 必须与对应批次数据在同一事务提交，否则会“数据已写而进度未记” 或“进度已记而数据未写”。

COMMIT AND CHAIN

普通 COMMIT 后也会自动开始新事务；COMMIT AND CHAIN 让下一事务继承 上一事务的 transaction characteristics，例如 isolation level。

它不会保留 transaction-local 状态：

• SET LOCAL 在 commit 后结束；
• transaction-level advisory lock 释放；
• row/table locks 释放；
• snapshot 更换。

所以本章每轮重新设置 transaction-local actor。生产代码也不能假设一个 procedure body 就是一个事务。

cursor loop 的陷阱

在 cursor-driven loop 中第一次 COMMIT 后，cursor 可能转为 holdable， 查询在该点被完整求值；cursor 原先取得的锁也不再持续持有。这可能：

• 把“流式处理”变成一次物化；
• 增加内存/临时文件；
• 让后续数据变化不再出现在 cursor；
• 失去预期锁保护。

本章每批重新执行 bounded query，不跨提交持有 cursor。

异常与部分完成

procedure 第三批失败时：

batch 1 committed
batch 2 committed
batch 3 rolled back
CALL returns error

调用方必须把“CALL 报错”与“没有变化”分开。运维输出要报告：

• 已提交批次/行数；
• 当前 checkpoint；
• 失败 SQLSTATE；
• 是否可重入；
• 下一动作；
• 数据一致性验证。

不要在最外层 WHEN OTHERS 把错误吞掉后返回 p_total，否则 scheduler 会 误判成功。

事务预算

批大小不是拍脑袋常数。基于：

• 每行写放大、索引数与 WAL；
• 单批 lock hold time；
• replica apply lag；
• autovacuum 和 bloat；
• statement timeout；
• worker 数量；
• 业务并发延迟；
• maintenance window。

使用关系而非固定耗时 golden：

batch rows <= configured maximum
checkpoint and rows commit together
next run starts after last committed boundary
lag/lock budget stays below stop line

跨机器的“每批必须 200ms”通常不是可靠测试。

13.4.3 调度属于平台职责，不由过程本身解决

一个可运维 job 至少有五层

schedule
  -> leader / target routing
  -> overlap and lease control
  -> procedure/worker body
  -> evidence, retry, alert, pause

procedure 只实现 body。把其余四层留空，任务虽然能手工 CALL，却还不能 上线。

Pigsty 中的入口选择

Pigsty 可管理 PostgreSQL 所在主机的 postgres 用户 cron，参数 pg_crontab 用于声明 OS crontab 项。Pigsty 扩展生态也提供 pg_cron；需要按 扩展配置 确认安装、preload、目标 database 和参数。

两者不是同一个机制：

选择后绑定实际版本和行为，不从“装了 extension”推断任务已安全运行。

primary routing 与 failover

写任务必须回答：

• 连接的是 current primary service，还是固定节点？
• failover 时旧连接如何退出？
• 新 primary 何时允许接管？
• 同一 schedule 是否会在两台主机同时触发？
• procedure 内部 commit 后，连接是否仍在正确实例？
• recovery instance 上是否 hard refuse？

本章 context.sql 在写前检查：

NOT pg_is_in_recovery()

但单次 preflight 不能证明整个多事务 procedure 期间永不发生 role change。 生产 job 还要处理中断、重连和幂等续跑。

overlap control

定时任务可能上一轮未结束，下一轮又启动。可选控制：

• scheduler 的 Forbid / single-flight policy；
• durable job lease row；
• session-level advisory lock；
• 唯一 active-run constraint；
• 任务状态机。

procedure 内含 COMMIT 时，transaction-level advisory lock 每批都会释放， 不能保护整个调用。session-level advisory lock 能跨 commit，但依赖同一 session，必须确保错误/断线释放并验证 pooler 路径。通常把 overlap policy 放 scheduler，并用数据库 durable lease 作为第二道防线。

pooler 边界

多事务 maintenance procedure 不是普通短 OLTP 请求。通过 PgBouncer transaction pool 前必须在目标组合上验证：

• 一个 CALL 内部多次 commit 的协议行为；
• statement timeout 与 cancel；
• session-local setting、advisory lock 和 temp object；
• 长任务是否占住 server connection；
• 管理流量是否挤压应用 pool。

默认更清楚的做法是经 Pigsty direct 管理 service 运行受控维护，应用事务 经 primary + PgBouncer。第 12 章已说明服务端口只是参考映射，必须读取目标 inventory。

调度证据

一次可审计运行至少保存：

job name and release
schedule / manual initiator
target cluster, database, server identity
primary/recovery state
application_name
start/end/heartbeat
input cutoff and batch size
committed rows and checkpoint
SQLSTATE and retry decision
replication/lock/resource stop lines
artifact checksum

敏感 DSN 和密码不进入 evidence。

告警不是“exit != 0”就结束

同时监控：

• last successful completion age；
• current run age；
• overlap/lease conflict；
• backlog rows 与 oldest age；
• processed rate；
• per-SQLSTATE failure；
• replica lag、WAL、locks、connections；
• skipped/poison item 数；
• repeated no-progress run。

procedure 正常返回但处理零行，可能是“没有 backlog”，也可能是过滤条件、 权限或连接目标错误。用前置 target identity 和业务关系区分。

发布与停用

上线顺序：

1. 部署向后兼容的 table/function/procedure；
2. 以受控角色手工运行小范围；
3. 验证 SQLSTATE、batch、locks、WAL、replica；
4. 建 schedule，但先 disabled 或一次性；
5. 启用并观察至少一个完整周期；
6. 冻结 source、manifest 和 run evidence。

停用顺序：

1. 先禁止新的 schedule；
2. 等待或有界取消当前 run；
3. 验证没有 active worker；
4. 保留 procedure 供兼容/恢复窗口；
5. 观察期后再撤权和删除对象。

直接 DROP PROCEDURE 不会取消外部 scheduler；下一轮只会开始报错。

本节结论

procedure 是一个允许显式 CALL、在严格条件下结束事务的 routine。它适合 可重入多批维护，不适合：

• 原子业务命令；
• 查询表达式；
• 自动调度；
• 提权后跨事务万能操作；
• 隐藏部分提交；
• 远端工作流。

把 body、job state 和 scheduler 分开设计，才有可恢复性。

上一节：触发器与约束触发器 · 返回本章目录 · 下一节：安全、测试与观测 · 查看全书目录 · 查看索引中心

13.5 安全、测试与观测

数据库例程一旦拥有底表或管理能力，就同时是：

• 可执行代码；
• SQL API；
• 权限边界；
• 查询计划输入；
• 写事务的一部分；
• 生产观测对象。

因此评审标准不能停在“函数能调用、trigger 会触发”。本节把安全、测试和 观测合并，因为三者都在回答同一个问题：运行态是否真的是我们声明的对象。

13.5.1 SECURITY DEFINER、固定 search_path 与最小权限

invoker 与 definer

默认 SECURITY INVOKER：

function uses caller privileges

SECURITY DEFINER：

function uses owner privileges

后者可以给应用一个窄能力，而不授予底表权限：

pg36_app:
  no SELECT/UPDATE on shop_ch13.sales_order
  no INSERT on shop_ch13.payment
  EXECUTE capture_payment(...)

capture_payment owner:
  pg36_owner NOLOGIN
  owns only intended database objects

这比把 pg36_owner grant 给应用安全得多，但前提是 function 本身无法被 劫持或滥用。

threat model：名字解析

危险函数：

CREATE FUNCTION admin.check_secret(...)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    SELECT ... FROM password_table ...;
END
$$;

如果运行时 search_path 先命中调用者可写 schema 或临时关系，攻击者可以 创建同名对象，让 definer 权限访问错误目标。函数、operator、type 和隐式 cast 的解析也可能成为入口。

官方 Writing SECURITY DEFINER Functions Safely 要求排除不可信可写 schema，并把 pg_temp 放在可信路径最后。

本章使用：

SECURITY DEFINER
SET search_path = pg_catalog, pg_temp

并在 body 中全限定业务对象：

UPDATE shop_ch13.sales_order ...
INSERT INTO shop_ch13.payment ...

pg_catalog 明确位于前面，pg_temp 明确位于最后；没有 public 或应用可写 schema。

固定 path 还不够

逐项检查：

1. 所有 table/view/sequence/function/operator/type 是否解析到可信 owner；
2. 动态 SQL 的 identifier 是否来自 allowlist，并用 %I；
3. value 是否通过 USING 绑定，不拼接；
4. 是否调用可被不可信角色替换的同名重载；
5. 临时对象能否遮蔽未限定 relation；
6. 默认参数表达式是否依赖不可信对象；
7. function owner 能否被低权限用户 SET ROLE；
8. owner 是否拥有超出需求的 cluster 能力。

本章 owner 是：

pg36_owner:
  NOLOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS

NOLOGIN 阻止它成为应用连接身份；但能 SET ROLE pg36_owner 的成员仍等于 拥有其能力，membership 必须受控。

创建时立即撤销 PUBLIC

新 function 默认可能给 PUBLIC EXECUTE。若先创建、稍后再 revoke，中间 存在可调用窗口。把 DDL 与 ACL 放在一个事务：

BEGIN;

CREATE FUNCTION shop_ch13.transition_order(...)
...
SECURITY DEFINER;

REVOKE ALL ON FUNCTION
    shop_ch13.transition_order(bigint, bigint, text, text)
FROM PUBLIC;

GRANT EXECUTE ON FUNCTION
    shop_ch13.transition_order(bigint, bigint, text, text)
TO pg36_app;

COMMIT;

本章 setup 最终执行：

REVOKE ALL ON ALL FUNCTIONS IN SCHEMA shop_ch13 FROM PUBLIC;

GRANT EXECUTE ON FUNCTION order_snapshot(bigint) TO pg36_app;
GRANT EXECUTE ON FUNCTION transition_order(...) TO pg36_app;
GRANT EXECUTE ON FUNCTION capture_payment(...) TO pg36_app;

内部 trigger functions 和 maintenance procedure 不授给应用。

参数不是授权

危险接口：

admin.run_sql(command text)
admin.read_table(schema_name text, table_name text)
admin.set_role(role_name text)

即使用 %I 防注入，调用者仍可能选择不应访问的合法对象。安全接口必须 收窄业务能力：

transition_order(order_id, expected_version, target_status, actor)

body 自己决定：

• 只写哪张表；
• 允许哪些边；
• 取得什么锁；
• version 如何推进；
• 返回哪些列；
• 哪些 SQLSTATE 暴露。

“防 SQL injection”只是必要条件，不等于授权正确。

输入与资源预算

definer function 应限制：

• identifier 长度与字符集；
• array/JSON 最大大小；
• batch size；
• 正则或全文检索复杂度；
• 可查询时间范围；
• 动态 identifier 集合；
• statement/lock timeout；
• 单次返回行数。

本章 actor：

IF p_actor IS NULL
   OR p_actor !~ '^[A-Za-z0-9][A-Za-z0-9._:@/-]{0,63}$' THEN
    RAISE ... ERRCODE = 'P3617';
END IF;

actor 仍不是认证机制；它只保证安全形状。可信服务必须从已认证上下文生成， 而不是把任意用户输入原样传入。

RLS 不是自动叠加

table owner 通常绕过 row-level security，除非 FORCE ROW LEVEL SECURITY； superuser 和 BYPASSRLS 也有特殊能力。definer function 以 owner 运行时， 不能假设 caller 的 RLS policy 继续隔离行。

若 command API 需要 tenant isolation：

• 显式把 tenant identity 绑定到可信 session/参数；
• 在 body 的每条 SQL 中加入 tenant predicate；
• 评审 owner 与 FORCE ROW LEVEL SECURITY；
• 测试跨 tenant 读取、更新和错误差异；
• 防止通过存在性、timing 或错误字段泄露其他 tenant。

“底表有 RLS”不是 definer function 的完整安全证明。

trigger function 也是代码入口

应用通常不会直接调用 trigger function，但：

• trigger 创建者需要相应权限；
• function source 仍可能被替换；
• function owner 和 path 决定运行能力；
• 其他表可能误挂同一 trigger function；
• 默认 PUBLIC EXECUTE 仍扩大无意义攻击面。

所以本章也 revoke 内部函数 direct execute，并冻结：

trigger name
parent table
function regprocedure
SECURITY DEFINER
search_path
marker

安全目录测试

security-catalog.sql 验证：

app_schema_usage=true
app_order_select=false
app_order_update=false
app_payment_insert=false
app_snapshot_execute=true
app_transition_execute=true
app_capture_execute=true
app_guard_execute=false
app_procedure_execute=false
public_transition_execute=false

ACL 是发布 artifact，不是手工配置备注。

13.5.2 单元测试、属性测试与并发测试

测试从目录到事务逐层增加

1. DDL/目录合同

验证：

• exact signature 与 prokind；
• language、volatility、strict、parallel；
• prosecdef 与 proconfig；
• trigger event/timing/level；
• deferred、transition table；
• owner、ACL、marker；
• pg_get_functiondef() / pg_get_triggerdef() 与 release source。

这能发现“装错对象”，不能证明业务行为。

2. 纯函数单元测试

对 transition matrix 枚举所有状态对；实验由 transition-matrix.sql 固化：

WITH state(value) AS (
    VALUES
      ('created'), ('paid'), ('packing'), ('shipped'),
      ('completed'), ('canceled'), ('expired')
)
SELECT
    old.value,
    new.value,
    shop_ch13.allowed_transition(old.value, new.value)
FROM state AS old
CROSS JOIN state AS new
ORDER BY 1, 2;

断言允许边恰好是六条，反向边和 terminal outward 全部 false。对纯函数， 这种穷举 property test 比几个 happy example 更强。

3. command 正负路径

成功：

created v0 -> canceled v1
created v0 + exact payment -> paid v1

失败：

created -> shipped        -> P3613
paid without payment      -> P3614 at commit
delete captured payment   -> P3614 at commit
expected v0 after v1      -> P3616
wrong payment amount      -> P3618
direct app UPDATE         -> 42501

每个失败都同时断言：

• order status/version unchanged；
• payment count unchanged；
• history/audit unchanged；
• transaction can only continue when error is intentionally caught in a subtransaction。

只检查“报错了”不够；错误前的隐藏写也必须回滚。

4. trigger 粒度测试

单条三行 UPDATE：

BEFORE ROW calls = 3
history rows     = 3
AFTER STATEMENT  = 1
affected_count   = 3
order_ids        = {105,106,107}

再测试零行 UPDATE，确认 statement trigger 是否执行以及 body 是否避免写空 audit。

5. deferral 测试

在同一事务中分别执行：

INSERT captured payment;
UPDATE order TO paid;
SET CONSTRAINTS ALL IMMEDIATE;

应通过。只做其中一步应在 SET CONSTRAINTS 或 commit 时报 P3614。这能 区分“语句成功”与“事务可提交”。

6. exception 子事务

exception-probe.sql 精确捕获 P3613， 使用 GET STACKED DIAGNOSTICS，并证明 inner persistent change 回滚。

7. procedure 事务边界

同一 fixture 先运行：

BEGIN;
CALL expire_stale_orders(...);
COMMIT;

必须是 2D000 且候选仍为 created。再以 top-level CALL 运行，取得 2/2/1 与 total 5；第二次 CALL 必须取得 total 0 且 audit 不增长。

以真实角色测试

owner 测试不能证明应用 ACL。实验分别建立连接：

admin connection:
  session_user=postgres
  SET ROLE pg36_owner

application connection:
  session_user=pg36_app
  no SET ROLE

正向 API 和直接写拒绝必须在 application connection 运行。测试 DSN 不应 因为本机 trust 就被误认为生产认证已验证。

绕过应用是必测路径

如果 trigger 声称覆盖所有普通写入口，测试必须直接：

SET ROLE pg36_owner;
UPDATE shop_ch13.sales_order
SET status = 'shipped', version = version + 1
WHERE order_id = 103;

它绕过 command function，仍应收到 P3613。只从应用 API 测 trigger， 无法区分是应用校验还是数据库护栏生效。

并发属性

至少覆盖：

1. 两个 command 使用同一 expected version；
2. 两个支付引用争同一订单；
3. 相反顺序锁多张表是否 deadlock；
4. deferred aggregate 在并发明细下是否遗漏；
5. procedure 与在线命令争同一行时 SKIP LOCKED 是否可恢复；
6. function 在 READ COMMITTED / REPEATABLE READ / SERIALIZABLE 的错误集合；
7. cancel/timeout 后锁、连接和事务是否释放。

本章 deterministic suite 证明单订单 FOR UPDATE 与 optimistic version 合同，但没有声称覆盖生产并发规模。对真实模型应沿用第 10 章 gate worker 方法，保存 PID、backend_start、application_name、wait graph 和 SQLSTATE。

property 不只测输入

可冻结的关系：

sum(order versions) = history rows
sum(statement_audit.affected_count) = history rows
paid orders = orders with exact captured total
terminal statuses have no outgoing history edge
failed cases add zero durable rows
rerun procedure processes zero already-expired rows
business checksum stable across exact rebuild

这种关系比 identity sequence 恰好连续或耗时固定更耐环境变化。

migration 与 rollback 测试

例程发布还要验证：

• CREATE OR REPLACE 是否保持 OID/ACL/依赖和返回类型限制；
• 新旧签名是否同时存在并产生重载歧义；
• trigger 新旧版本是否会重复执行；
• 回退应用调用旧签名是否仍成功；
• drop 前是否还有依赖和活跃调用；
• reset 是否只作用于 marker 对象。

本章 reset 对错误 token、错误 target、活跃 worker 和对象 inventory 漂移 全部 fail closed。

13.5.3 函数级统计、日志与慢调用定位

track_functions

track_functions 控制用户函数累计统计：

开启有开销，应按观察目标和窗口决定。需要相应权限修改；生产上通过受控 配置流程，而不是应用连接临时打开。

累计视图：

SELECT
    schemaname,
    funcname,
    calls,
    total_time,
    self_time
FROM pg_stat_user_functions
WHERE schemaname = 'shop_ch13'
ORDER BY total_time DESC;

• total_time 包含被调函数时间；
• self_time 排除被调函数时间；
• 数值是累计量，不是分位数；
• stats 有 flush 延迟，并受 transaction 内 snapshot/cache 影响；
• restart、crash 或显式 stats reset 会影响统计连续性；PostgreSQL 18 的 pg_stat_user_functions 本身不提供每行 stats_reset 列，观察系统要另行 记录采集窗口。

当前事务可看：

SELECT *
FROM pg_stat_xact_user_functions;

本章在一笔 rollback-only probe 中打开 all，调用 snapshot 和 transition， 取得五个 routine 的 calls >= 1，然后回滚业务变化。官方定义见 Cumulative Statistics System。

function counters 不能回答什么

它们不能直接给出：

• p95/p99；
• 哪个 request 调用；
• 参数值；
• 哪条内部 SQL 最慢；
• 哪个 call 失败；
• lock/wait 分解；
• SQL function 内联后的完整逻辑边界。

所以它是定位入口，不是 trace。

把外层与内部 SQL 关联

组合：

application_name + trace/request id
  -> outer SELECT function(...)
  -> pg_stat_activity / wait_event
  -> pg_stat_statements
  -> nested statement stats/log
  -> function counters
  -> SQLSTATE + trigger context
  -> business audit/outbox

pg_stat_statements.track = all 可纳入嵌套语句，但会改变数据量；需按目标 配置验证。auto_explain.log_nested_statements 可在有界诊断窗口记录嵌套 计划，同样要控制 duration、sample rate、buffers 与日志敏感性。

不要长期把所有参数和完整 PL/pgSQL context 无筛选写日志。订单引用、用户 标识、token、payload 可能是敏感信息。

慢 routine 的诊断顺序

1. 确认目标 cluster/database/schema/signature；
2. 区分 outer call 慢还是在 pool/lock 等待；
3. 看 pg_stat_activity.state/wait_event；
4. 看 block graph 与长事务；
5. 对内部 SQL 取得规范化 query identity；
6. 用实际参数分布 EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)；
7. 检查 row-trigger 放大与 transition table 大小；
8. 检查 deferred queue 是否在 commit 集中爆发；
9. 比较 function total_time/self_time；
10. 最后才改 SQL、索引、batch 或逻辑位置。

不要看见高 total_time 就重写 PL/pgSQL。总时间可能只是调用次数高，或内部 SQL 在锁上等待。

trigger 的可见性

原始 query：

UPDATE sales_order SET ...

不会把所有 trigger body 展开在 pg_stat_activity.query。需要：

• pg_trigger inventory；
• function stats；
• nested statement statistics/logging；
• SQLSTATE context；
• derived audit relationship；
• 应用端命令与数据库 transaction ID 关联。

本章 statement audit 保存 pg_current_xact_id()，history 保存同一 xid8。 这是数据库内关联，不是全链路 trace。

Pigsty 观察面

Pigsty monitoring 以 metrics、logs、alerting 为三根支柱，并覆盖 PostgreSQL 实例、SQL、连接、复制、WAL 和基础设施。见 Monitoring System。

例程上线时至少增加或确认：

• command function rate/error by low-cardinality identity；
• SQLSTATE rate；
• function cumulative calls/time delta；
• outer SQL latency；
• lock/wait；
• job backlog/age/last success；
• audit/outbox growth；
• database/replica/WAL/connection resource；
• deployment/release annotation。

不要把 actor、order_id 或 function 参数做成 metrics label；高基数和敏感性 都不合适。它们应进入受控日志或数据库 evidence。

观察窗口

发布后按阶段：

catalog/ACL verified
  -> canary command
  -> negative path
  -> representative bulk
  -> lock/WAL/replica observation
  -> enable production callers
  -> watch one workload cycle
  -> only then remove old path

本地 suite 无法伪造生产 observation window。自动 review 应输出 “not observed”，而不是因为 unit test 通过就填绿。

本节安全门禁

进入发布前必须同时满足：

• owner NOLOGIN、非 superuser、能力最小；
• definer path 可信且 pg_temp 最后；
• source 中对象名和 dynamic SQL 已审计；
• PUBLIC 权限在同事务撤销；
• application ACL matrix 精确；
• 正向、负向、绕过应用、deferral、bulk、procedure 边界通过；
• 并发协议有实际 evidence 或明确未验证；
• function/trigger inventory 已冻结；
• metrics/log/alert 查询可执行；
• rollback 会先停调用者和 job，再处理对象；
• evidence 不包含 secret；
• 本地事实与 Pigsty/PgBouncer 事实没有混写。

安全、测试和观测缺一项，数据库端逻辑都还只是“能运行”，不是“可运营”。

上一节：过程、任务与事务控制 · 返回本章目录 · 下一节：实战：为订单状态建立数据库端护栏 · 查看全书目录 · 查看索引中心

13.6 实战：为订单状态建立数据库端护栏

本节把前五节压成一个可运行、可失败、可复位的 release proposal。目标不是 展示最多的 PL/pgSQL 特性，而是让每个机制只承担一种可解释责任。

环境边界

task.sh all 会精确删除并重建专用 shop_ch13 schema。它适合本书的 本地/开发夹具；不要把它当生产迁移直接执行。生产发布使用向前迁移、 canary、观察窗口和独立回退，不先删 schema。

13.6.1 比较约束、函数、触发器与应用实现

先冻结状态图

实验只允许六条边：

stateDiagram-v2
    [*] --> created
    created --> paid: capture_payment
    created --> canceled: cancel command
    created --> expired: maintenance procedure
    paid --> packing
    packing --> shipped
    shipped --> completed
    canceled --> [*]
    expired --> [*]
    completed --> [*]

图中没有：

created -> shipped
canceled -> paid
completed -> created

禁止边必须由数据库拒绝，而不是只在 UI 隐藏按钮。

规则拆分

局部合法域：约束

setup.sql：

CONSTRAINT sales_order_total_positive
    CHECK (total_minor > 0),

CONSTRAINT sales_order_status_domain
    CHECK (
        status IN (
            'created', 'paid', 'packing', 'shipped',
            'completed', 'canceled', 'expired'
        )
    ),

CONSTRAINT sales_order_version_nonnegative
    CHECK (version >= 0)

这些规则不需要 OLD，不查询其他行，原生 CHECK 最合适。

transition matrix：纯 SQL function

allowed_transition(text,text)
  IMMUTABLE
  STRICT
  PARALLEL SAFE
  SECURITY INVOKER

它没有表访问和副作用，既可由 transition-matrix.sql 穷举 49 个状态对， 也能被 guard trigger 复用。

所有普通写入口：BEFORE ROW

invalid edge       -> P3613
version not +1     -> P3615
valid edge         -> normalize updated_at, return NEW

应用 command function、owner 直接 SQL 和 maintenance procedure 都经过同一 guard。应用层仍可做更早校验以改善 UX，但数据库是最终护栏。

事务最终点：deferred constraint triggers

最终不变量：

status = paid
  <=> captured_minor = total_minor

实验为简单起见不建 partial payment/refund 状态机，因此非 paid 订单捕获金额 必须为 0。真实支付模型通常需要 authorization、capture、refund、chargeback 账本，不能照抄这个简化等式。

两个 constraint trigger 同时覆盖：

• 改订单状态/金额；
• 插入、修改或删除 payment。

只挂一边会留下绕过入口。

应用命令：definer functions

应用只能调用：

order_snapshot(order_id)
transition_order(order_id, expected_version, target, actor)
capture_payment(order_id, expected_version, payment_ref, amount, actor)

它没有底表 DML。capture_payment：

lock order row
  -> validate expected version/status/amount
  -> set transaction-local actor
  -> insert payment
  -> update order to paid and version +1
  -> row + statement triggers
  -> deferred checks at commit

支付引用有 UNIQUE；本章没有实现第 12 章那种完整幂等 response ledger， 因此 duplicate payment_ref 仍是约束错误。生产 API 应明确 duplicate request 是 replay 还是 conflict。

批量维护：invoker procedure

expire_stale_orders：

• 仅 owner/管理路径可调用；
• batch size 限制 1–1000；
• ORDER BY order_id FOR UPDATE SKIP LOCKED LIMIT ...；
• 每批集合 UPDATE；
• COMMIT AND CHAIN；
• 已 expired 行自然成为重跑断点。

它不提权、不调外部系统、不安排自己何时运行。

跨系统动作：应用与 outbox

订单 paid 后通知履约不在 trigger 内发送。本章只证明数据库护栏；完整 outbox 服务见第 12 章。

物理对象

专用 schema：

shop_ch13
├── schema_version
├── sales_order
├── payment
├── order_history
├── statement_audit
├── 7 functions
├── 1 procedure
└── 4 user triggers

身份 sequence 和系统内部 FK triggers 不算 user trigger inventory。

所有实验对象带同一 marker：

pg36 ch13 routine guard lab; safe to rebuild

setup/reset 遇到未知 relation、routine、user trigger 或 marker 漂移会拒绝， 不会用 CASCADE 把未知依赖带走。

权限模型

postgres/admin session
  └─ SET ROLE pg36_owner for reviewed DDL

pg36_owner
  ├─ NOLOGIN, non-superuser
  ├─ owns shop_ch13 objects
  └─ runs maintenance procedure

pg36_app
  ├─ LOGIN, constrained
  ├─ USAGE shop_ch13
  ├─ EXECUTE 3 public API functions
  └─ no table DML / internal function / procedure EXECUTE

所有 definer functions：

SET search_path = pg_catalog, pg_temp

业务对象全限定。

审计模型

每个状态变化写一行 order_history：

order_id
old_status/new_status
old_version/new_version
actor/session_actor
statement_timestamp
xid8

每个 UPDATE statement 写一行 statement_audit：

xid8
actor/session_actor
affected_count
ordered order_ids[]
statement_timestamp

关系：

sum(statement_audit.affected_count)
  = count(order_history)
  = sum(final order versions)
  = 10

这是一条可机器验收的不变量。

fixture 分工

最终：

orders=13
created=3
paid=1
canceled=4
expired=5
payments=1
history=10
statement_audit=6
affected_sum=10

设计选择对照

13.6.2 注入绕过应用的错误写入

前置条件

实验依赖前章建立的：

database=pg36_shop
owner=pg36_owner
application role=pg36_app
model=ch04-v1
business checksum=stable

准备受控 libpq service：

[pg36-admin]
host=/path/to/socket-or-host
port=5432
dbname=pg36_shop
user=postgres

然后：

export PGSERVICEFILE=/path/to/pg_service.conf
export PGSERVICE=pg36-admin

不要把密码写进命令行或 evidence。生产使用受控 secret path。

先跑静态和单阶段入口

./static/labs/ch13/task.sh setup
./static/labs/ch13/task.sh catalog
./static/labs/ch13/task.sh behavior

catalog 和 behavior 会先重建 exact fixture，以保证结果不依赖上一轮。 正式验收直接运行 all。

正向路径

api-happy.sql 以 pg36_app：

SELECT *
FROM shop_ch13.transition_order(
    101, 0, 'canceled', 'app-cancel'
);

SELECT *
FROM shop_ch13.capture_payment(
    102, 0, 'pay-ch13-102', 2000, 'app-payment'
);

预期：

101,canceled,1
102,paid,1,pay-ch13-102

这同时证明 definer 权限、trigger、deferred check 和返回形状。

故障 1：绕过 command API 的直接写

以 pg36_app：

UPDATE shop_ch13.sales_order
SET status = 'canceled', version = version + 1
WHERE order_id = 105;

预期：

SQLSTATE 42501

失败发生在 ACL，trigger 无需承担应用授权。

故障 2：非法状态边

SELECT *
FROM shop_ch13.transition_order(
    103, 0, 'shipped', 'app-invalid'
);

预期：

SQLSTATE P3613
order 103 remains created v0
history delta=0
audit delta=0

再用 owner 直接 UPDATE 同一非法边，仍应由 guard 拒绝。这才证明护栏不依赖 应用 handler。

故障 3：提交点不一致

SELECT *
FROM shop_ch13.transition_order(
    104, 0, 'paid', 'app-no-payment'
);

BEFORE 认为 created→paid 是允许边，UPDATE 与 AFTER audit 会在事务内部 执行；到 deferred check 时发现 captured=0：

SQLSTATE P3614
order/history/audit all rolled back

这证明不能只看 function 的 RETURNING；事务必须成功提交才是完成。

反方向也必须覆盖：delete-payment.sql 删除 order 102 的 captured payment，会由 payment 表上的 constraint trigger 在提交点返回同一个 P3614，paid 订单与 payment 都保持原状。

故障 4：乐观版本冲突

order 101 已是 v1，再传 expected v0：

SQLSTATE P3616

这不是 blind retry 信号。调用方重新读取，判断业务意图是否仍成立。

故障 5：支付前置条件

order 103 金额 3000，传 1：

SQLSTATE P3618
payment delta=0
order remains created v0

前置条件在插 payment 前检查，且整笔 function 仍在一个事务。

故障 6：procedure 放进显式事务

procedure-in-transaction.sql：

BEGIN;
CALL shop_ch13.expire_stale_orders(..., 2, 0);
COMMIT;

过程第一次 COMMIT AND CHAIN：

SQLSTATE 2D000

显式事务回滚，201–205 仍 created。随后 procedure-run.sql 用 top-level CALL：

p_total=5
batches=[2,2,1]

立即第二次 top-level CALL：

p_total=0
audit delta=0

这证明恢复依据是已提交状态，而不是只存在过程局部变量中的计数。

异常子事务

exception-probe.sql 在 inner block 直接做非法 owner UPDATE，精确捕获 P3613：

caught_state=P3613
status_after=created
version_after=0

probe 外层最后 ROLLBACK。它证明 handler 的持久化回滚语义，不把捕获当作 生产容错建议。

函数统计

function-stats.sql：

RESET ROLE;
SET track_functions = 'all';
SET ROLE pg36_owner;

BEGIN;
-- rollback-only calls
...
SELECT ... FROM pg_stat_xact_user_functions;
ROLLBACK;

证据至少包含：

allowed_transition calls>=1
guard_order_transition calls>=1
audit_order_transition calls>=1
order_snapshot calls>=1
transition_order calls>=1

时间只要求非负，不做跨机器阈值。

完整 suite

evidence="$PWD/evidence/ch13/$(date -u +%Y%m%dT%H%M%SZ)"

PG36_EVIDENCE_DIR="$evidence" \
  ./static/labs/ch13/task.sh all

它额外验证 reset：

活跃 worker probe 只取消精确 PID、database、application_name 对应的 pg_sleep，不会广泛终止连接。

evidence 结构

evidence/
├── manifest.txt
├── preflight.txt
├── setup.txt
├── routine-catalog.csv
├── trigger-catalog.csv
├── security-catalog.csv
├── transition-matrix.csv
├── api-happy.csv
├── invalid-transition.{exit,stdout,stderr}
├── paid-without-payment.{exit,stdout,stderr}
├── version-conflict.{exit,stdout,stderr}
├── payment-mismatch.{exit,stdout,stderr}
├── delete-payment.{exit,stdout,stderr}
├── direct-write.{exit,stdout,stderr}
├── exception-probe.csv
├── function-stats.csv
├── bulk-update.csv
├── procedure-in-transaction.{exit,stdout,stderr}
├── procedure-run.csv
├── procedure-rerun.csv
├── final-state.csv
├── verify.txt
├── review.txt
├── reset-*.{exit,stdout,stderr}
├── reset.txt
└── rebuild/
    └── 同一套第二遍证据

review.py 读取原始 CSV/stderr/manifest，不从成功摘要自证成功。

最终 checksum

final-state.sql 对：

• order id/status/version；
• payment reference/amount/status；
• history edge/version/actor；
• statement affected set/actor；

做确定性排序和 MD5：

business_checksum=f045467816a9be6774f30312adc16402

时间、xid、identity sequence 不进入 checksum，因为它们每次合法运行都可能 变化。

13.6.3 在 Pigsty L1 输出实现选择、测试证据与回退脚本

L1 不是“本机换个 host”

本地 PostgreSQL 18.6 direct 成功只证明：

source + fixture + direct server behavior

Pigsty L1 还要绑定：

cluster identity
service route
primary/recovery role
PostgreSQL minor version
PgBouncer path if used
role/database declaration
secret delivery
HA behavior
metrics/logs/alerts
change window and rollback authority

没有这些证据，就输出 not-run，不能把参考架构当成已验证事实。

声明角色与 database

pigsty-declaration.example.yml 提供无凭据 fragment：

pg_users:
  - name: pg36_owner
    login: false
    superuser: false
    ...

  - name: pg36_app
    login: true
    pgbouncer: true
    pool_mode: transaction
    ...

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    schemas:
      - { name: shop_ch13, owner: pg36_owner }

它不包含 password。实际 secret 由受控 inventory/overlay 注入。

声明只负责 role/database/schema 基础对象；function source、ACL、marker 和 tests 仍由 reviewed SQL migration 管理。不要让两套系统同时争夺同一函数 定义。

接入路径

参考决策：

application routine calls
  -> Pigsty primary service
  -> PgBouncer transaction pool
  -> pg36_app

reviewed DDL, catalog, maintenance CALL
  -> Pigsty direct/default management service
  -> PostgreSQL
  -> controlled admin SET ROLE pg36_owner

端口和 DNS 必须从目标 inventory 读取，不能照抄示例数字。应用路径要实际 验证：

• function calls；
• transaction-local setting；
• deferred commit error；
• cancel/timeout；
• failover/reconnect；
• transaction pooling 下的协议与 latency。

本章正式 suite 记录：

validation_path=direct-postgresql

所以 PgBouncer 项仍为未验证。

把 setup 改造成生产 migration

生产 migration 不能运行“drop exact fixture + seed”：

1. 创建新 schema/table/constraints；
2. 创建纯 function 与内部 trigger functions；
3. 同事务创建 definer function、revoke PUBLIC、grant 精确 app；
4. 创建 trigger；
5. 运行 catalog/ACL contract；
6. 以 canary 业务行运行正负路径；
7. 启用新应用调用；
8. 观察；
9. 最后撤旧接口。

若改已有大表，先按第 11 章评估 lock、rewrite、backfill 和 validation。 CREATE FUNCTION 本身快，不代表挂 trigger 后的每次写入成本可忽略。

生产 canary 不使用教学 seed

选择：

• 隔离 tenant/test order；
• 有清晰清理合同；
• 不触发真实外部副作用；
• 可在 outbox consumer 侧隔离；
• 能用业务不变量验证；
• 不暴露敏感数据到 evidence。

同时执行 bypass test 需要额外 owner 权限，应在变更窗口和隔离对象上完成， 不是任意改生产订单。

观察查询

目录：

SELECT *
FROM pg_proc
WHERE oid IN (
  'shop_ch13.transition_order(bigint,bigint,text,text)'::regprocedure,
  'shop_ch13.capture_payment(bigint,bigint,text,bigint,text)'::regprocedure
);

调用：

SELECT *
FROM pg_stat_user_functions
WHERE schemaname = 'shop_ch13'
ORDER BY total_time DESC;

活跃与等待：

SELECT
    pid, backend_start, application_name,
    state, wait_event_type, wait_event,
    xact_start, query_start
FROM pg_stat_activity
WHERE datname = 'pg36_shop'
  AND application_name LIKE 'pg36-%';

业务关系：

SELECT
    count(*) FILTER (WHERE status = 'paid') AS paid_orders,
    count(*) FILTER (WHERE status = 'paid'
                     AND captured_minor <> total_minor) AS invalid
FROM reviewed_payment_projection;

最后一个 projection 需要按真实 schema 编写，示例名不是本章已创建对象。

release proposal

baseline-v1.1-proposal.json 冻结：

• target/version；
• 逻辑放置决策；
• SQLSTATE；
• 最终状态关系；
• 权限矩阵；
• rollback token/target；
• 未验证边界。

canonical SHA-256：

32377d82a7ce958aa50b0077ebe99c47d27672223c3c77fd9f91072d3745de9d

manifest 和 review 独立重算；不是手抄字符串就算通过。

实验复位

仅对专用开发夹具：

PG36_RESET_TOKEN=RESET_CH13_ROUTINE_GUARD \
PG36_RESET_TARGET=pg36_shop/shop_ch13 \
  ./static/labs/ch13/task.sh reset

reset.sql 检查：

• database pg36_shop；
• writable instance；
• effective owner；
• ch04-v1；
• schema/object marker；
• relation/routine/trigger 白名单；
• 没有 pg36-ch13-* active worker；
• exact token 与 target。

随后按 FK/dependency 顺序 drop 精确对象，最后 DROP SCHEMA；不使用 CASCADE。

生产回退不是 reset

生产回退顺序：

stop new callers / disable job schedule
  -> observe and drain active calls
  -> route application to compatible old API
  -> verify old writes still accepted
  -> revoke new EXECUTE
  -> disable/drop new trigger only if data remains valid
  -> preserve audit and migration evidence
  -> observation window
  -> later contract objects

若新逻辑已经产生旧应用无法理解的新状态，DDL 回退不能自动恢复语义；需要 数据补偿或 forward fix。发布前必须演练。

L1 交付包

一份完整交付至少包含：

1. 逻辑放置 ADR；
2. migration source 与 artifact checksum；
3. exact signatures、owners、ACL、paths；
4. transition/state diagram；
5. 正向、负向、bypass、bulk、deferral、并发测试；
6. target manifest；
7. direct 与 pooler 路径结果；
8. SQLSTATE → 应用行为映射；
9. dashboard/log/alert 查询；
10. canary 与观察窗口；
11. scheduler/overlap 设计；
12. rollback 与停用顺序；
13. 未验证事实。

本章验收

你应能在不看答案时解释：

• 为什么状态域是 CHECK，状态边是 trigger；
• 为什么 payment invariant 要延迟，但仍要 row lock；
• 为什么应用没底表 DML；
• 为什么 definer path 必须固定、PUBLIC 必须撤销；
• 为什么 bulk audit 用 transition table；
• 为什么 procedure 显式事务中返回 2D000；
• 为什么 procedure 不是 scheduler；
• 为什么 trigger 不调用远端系统；
• 为什么 function counters 不是 trace；
• 为什么本地 direct 成功不能冒充 Pigsty/PgBouncer 成功；
• 为什么生产回退不能运行教学 reset。

能回答并用 evidence 证明，才算真正掌握数据库端逻辑。

上一节：安全、测试与观测 · 返回本章目录 · 下一章：博采众长：内核分支与扩展生态 · 查看全书目录 · 查看索引中心