# 数据库中的代码
> 把逐行工作移出主库，让规划器看得见函数内部，诚实地标注函数，并了解每个触发器付出的代价与隐藏的行为。
---
LLMS 索引： [llms.txt](/llms.txt)
---
两个函数返回相同的行：某个账户的所有事件。一个用 SQL 编写，另一个用 PL/pgSQL 编写，内部查询相同。让它们分别查这个账户最近一天的事件，SQL 版本通过索引读取 7 页；PL/pgSQL 版本读取 2,040 页，取出该账户的全部 2,084 个事件，却丢掉其中 2,080 个。
这两个函数都没有问题。差别在于规划器能看见什么。数据库中的代码发挥得最好时，是你能拥有的最快代码：它在数据旁边运行，不需要往返通信；发挥得最差时，它会隐藏一个把写入者串行化的锁、一个过时的计划，或一个让每次批量更新都付出代价的触发器，而这一切都发生在那台无法继续横向扩展的服务器上。
## 主库是你最昂贵的 CPU {#the-primary-is-your-most-expensive-cpu}
系统的其他层都可以靠增加机器来扩展：Web 服务器、工作进程、缓存、只读副本。主库是唯一无法横向扩展的那一层。每次写入都要经过同一台机器，而把它换成更大的机器，是扩容阶梯上最昂贵的一步（[先用数据证明，该换更大的机器了](/37-scale/#earn-the-bigger-box)）。函数或触发器在那里占用的每一个 CPU 周期，都在和每个查询竞争：花 1 毫秒格式化报表，就意味着结账插入要多等 1 毫秒才能得到一个核心。
我们的立场是：**数据库负责让数据变少、保持数据正确，或节省一次往返通信。其他事情都交给应用层，即使 SQL 能够表达它。尤其如此。**
- **留在数据库中：** 过滤、连接和聚合（在应用中统计两百万个事件，意味着把整张表传过去，只为了算出一个数字）；基于集合的写入；必须与写入保持原子性的约束；以及任何能节省往返通信的工作。
- **移到应用中：** 逐行格式化（按用户地区设置显示日期、格式化货币或 HTML）；经常变化的业务规则，这些规则应该用功能开关管理，并能在几分钟内回滚，而不是靠一次迁移；任何会调用外部世界的事情，因为事务回滚时，函数无法把已经发出的邮件收回来（[把工作移出请求路径](/37-scale/#move-work-off-the-request-path)）；以及任何你希望用单元测试覆盖的事情。
下面看看格式化的代价。一个报表端点用 SQL 的 `format()` 和 `to_char()` 把 200,000 个事件变成句子；另一种做法只返回组成句子的六个字段：
| 200,000 行 | 原始字段 | 在 SQL 中格式化 |
| ------------------------------ | ---------- | --------------- |
| 我们机器上的服务器时间 | 约 60 ms | 约 230 ms |
| 发送的文本 | 9.2 MB | 18 MB |
| 端到端、经过 psql | 约 110 ms | 约 280 ms |
**Table 24-1.** 主库是你最昂贵的 CPU
服务器时间是四倍，字节数是两倍。应用本来也会花费大致相同的 CPU；区别在于 CPU 花在哪里。应用服务器一分钟就能加机器。每秒调用 10 次时，多出来的 170 毫秒会让主库将近两个核心整天都在格式化日期，其他查询只能排在它们后面等待。
如果逻辑是在保护数据或缩小数据，就把它放在数据旁边。如果逻辑是在服务某项功能或某个人，就把它和功能放在一起。（用 `jsonb_agg` 返回结果、替代一串查询循环，这是在节省往返通信，而不是做格式化。）
> [!DETAILS]- 深入阅读：格式化查询，以及寻找受 CPU 限制的语句
>
> ```sql
> select format('%s: %s by user %s in project %s took %s ms (%s)',
> to_char(created_at at time zone 'America/New_York',
> 'FMDay, FMMonth FMDD YYYY at FMHH12:MI AM'),
> initcap(kind), coalesce(user_id::text, 'system'), project_id,
> to_char((payload ->> 'duration_ms')::int, 'FM999,999'),
> upper(payload ->> 'status')) as line
> from events
> where id <= 200000;
> ```
>
> ```text
> Sunday, October 5 2025 at 8:00 PM: Build by user 46936 in project 6705
> took 3,454 ms (FAILED)
> ```
>
> （这里把一行输出换行显示。）
>
> 我们用 `explain (analyze, timing off)` 分别计时，各取五次中的最佳结果；并把原始字段转换为文本，这样两边都会为服务器本来就要执行的行发送转换付出代价。格式化每行大约多花 0.9 µs。在应用中，`Intl.DateTimeFormat` 会在用户自己的地区设置下完成同样的工作。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select exec_ms(:'raw_rows') as raw_ms \gset
> > select exec_ms(:'formatted_rows') as fmt_ms \gset
> >
> > select lab.prove(
> > 'formatting 200,000 rows into sentences in SQL costs the server over 2.5 times the time of returning the fields',
> > :fmt_ms > 2.5 * :raw_ms);
> >
> > select lab.prove(
> > 'and the formatted text is over 1.5 times the bytes of the raw fields',
> > :fmt_bytes > 1.5 * :raw_bytes);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
>
> 要寻找候选对象：在 `pg_stat_statements` 中，如果一条语句总耗时很高，但每次调用访问的共享块很少，那么它不是在读取页，而是在计算（[用四种方式对同一个视图排序，先看总耗时](/31-observability/#rank-the-same-view-four-ways-total-time-first)）。在等待样本中，如果很多会话都没有等待事件，说明它们正在占用 CPU（[采样等待，看看时间花在哪里](/31-observability/#sample-waits-to-see-where-the-time-goes)）。而 `track_functions` 会统计每个函数的耗时（[函数不可见，除非你开启跟踪](#functions-are-invisible-until-you-turn-tracking-on)）。
## SQL 函数会内联；PL/pgSQL 函数不会 {#sql-functions-are-inlined-plpgsql-functions-are-not}
下面是开头提到的两个函数，它们使用 `(account_id, created_at)` 上的索引：
```sql
create function account_events(acct bigint) returns setof events
language sql stable
as $$ select * from events where account_id = acct $$;
create function account_events_pl(acct bigint) returns setof events
language plpgsql stable
as $$ begin return query select * from events where account_id = acct; end $$;
```
调用者要查一天的数据：`select * from account_events(42) where created_at > '2026-10-05'`；两个孪生函数各自的计划如下：
```text
Index Scan using events_account_id_created_at_idx on events (actual rows=4.00 ...)
Buffers: shared hit=7
Function Scan on account_events_pl ... rows=333 ... (actual rows=4.00 loops=1)
Rows Removed by Filter: 2080
Buffers: shared hit=2040
```
SQL 版本的计划中没有函数：规划器把查询粘贴进来，合并调用者的过滤条件，然后使用索引。**Function Scan** 会先把函数运行完，再进行过滤；规划器只能猜测函数内部做了什么（[函数会隐藏你的统计信息](/20-planner/#functions-hide-your-statistics)）。对于最大的租户，它必须读取该租户曾经拥有的每个事件，才能展示其中一天的数据。
> [!EXAMPLE]- 动手验证 · lab `23-functions`
>
> ```sql
> select lab.prove(
> 'the SQL function is inlined: an index scan on (account_id, created_at), under 20 pages',
> lab.nodes($$ select * from account_events(42) where created_at > '2026-10-05' $$)
> = array['Index Scan']
> and lab.buffers($$ select * from account_events(42)
> where created_at > '2026-10-05' $$) < 20);
>
> select lab.prove(
> 'the PL/pgSQL twin is a Function Scan that reads the whole account, over 1,000 pages',
> lab.nodes($$ select * from account_events_pl(42) where created_at > '2026-10-05' $$)
> = array['Function Scan']
> and lab.buffers($$ select * from account_events_pl(42)
> where created_at > '2026-10-05' $$) > 1000);
> ```
>
> [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
**如果函数只包含一个查询，就用 SQL 编写。** 需要变量、循环或异常处理时，再使用 PL/pgSQL，并且要预期它表现得像一次调用，而不是一个视图。
### STRICT、SECURITY DEFINER 或 SET 子句会阻止内联 {#strict-security-definer-or-a-set-clause-stops-inlining}
只有在以下条件同时满足时，返回集合的 SQL 函数才会内联：函数体是单个 `SELECT`，易变性是 `STABLE` 或 `IMMUTABLE`（默认值 `VOLATILE` 不行），不是 `STRICT`，不是 `SECURITY DEFINER`，并且没有 `SET` 子句。我们逐项尝试了这些会阻止内联的设置；每种情况都得到一个 `Function Scan`。因此，带有固定 `search_path` 的定义者函数（[本来就应该如此](/33-security/#security-definer-functions-need-a-pinned-search_path)）是一个黑盒：把这类函数留给范围狭窄的操作。
> [!DETAILS]- 深入阅读：标量函数也会内联，但规则更严格
>
> 标量 SQL 函数的函数体是单个表达式（`select `，没有 `FROM`）时可以内联，前提是它声明的易变性不能比函数体本身更严格。函数 `is_failed(p jsonb)` 的函数体是 `select p ->> 'status' = 'failed'`，它在计划中会显示为 `((payload ->> 'status'::text) = 'failed'::text)`，因此可以使用 `(payload ->> 'status')` 上的表达式索引。PL/pgSQL 版本会显示为 `Filter: is_failed_pl(payload)`，并且每行调用一次。PostgreSQL wiki 的 [Inlining of SQL functions](https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions) 页面列出了完整的条件。
## PL/pgSQL 会按会话缓存计划 {#plpgsql-caches-plans-per-session}
PL/pgSQL 会在每个会话中准备每条语句一次，并保留该计划，因此也会继承 [预备语句](/20-planner/#prepared-statements-switch-plans-after-five-runs)的切换规则：先使用五次自定义计划；如果通用计划的代价看起来不比这些自定义计划的平均代价高，就切换到通用计划。同一个陷阱现在藏进了函数内部。把规划器章中的 `latest` 查询封装到 `latest_ids(k text)` 中，针对常见的 kind 调用五次，第六次调用就会锁定通用计划：对于罕见的 `incident` kind，它要读取 34,536 页，而不是 23 页。
函数本身没有变化；变化的只有会话历史，这就是这个问题如此难以复现的原因。把修复固定到函数上：`alter function latest_ids(text) set plan_cache_mode = force_custom_plan`，这样每次调用都会根据实际值规划。
> [!DETAILS]- 深入阅读：自 PostgreSQL 18 起，SQL 函数也会缓存计划
>
> ```sql
> create function latest_ids(k text) returns setof bigint
> language plpgsql stable
> as $$
> begin
> return query
> select id from events where kind = k order by created_at desc limit 10;
> end
> $$;
> ```
>
> PostgreSQL 18 把计划缓存扩展到了不会内联的 SQL 函数：声明为 `strict` 的 SQL 版本（因此无法内联）在五次调用后也会发生同样的切换，对于 `incident` 要读取超过 30,000 页。动态 SQL（`return query execute ... using k`）每次调用都会重新规划。根据 [PL/pgSQL 文档](https://www.postgresql.org/docs/18/plpgsql-implementation.html)，创建函数时，语句只会做语法检查；第一次运行时才会进行分析。此前从未运行过的分支中拼错的表名或列名，只有等到该分支最终运行时才会暴露。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'after five calls the function switched to a generic plan: the rare kind now reads over 30,000 pages',
> > lab.buffers($$ select * from latest_ids('incident') $$) > 30000);
> >
> > alter function latest_ids(text) set plan_cache_mode = force_custom_plan;
> >
> > select lab.prove(
> > 'with SET plan_cache_mode = force_custom_plan on the function, the rare kind is cheap on every call',
> > lab.buffers($$ select * from latest_ids('incident') $$) < 50);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
## 易变性是给规划器的承诺 {#volatility-is-a-promise-to-the-planner}
每个函数都属于 `IMMUTABLE`（参数相同，结果永远相同）、`STABLE`（在同一条语句内结果相同，不执行写入）或 `VOLATILE`（不作任何承诺，也是默认值）之一，规划器会照单全收。下面是同一个纯函数：它计算向前 `n` 天的截止时间，依次使用每种标记，并用于过滤 `projects`：
```text
-> Index Only Scan using projects_created_at_idx on projects ...
Index Cond: (created_at > '2026-09-29 00:00:00+00'::timestamp ...)
-> Index Only Scan using projects_created_at_idx on projects ...
Index Cond: (created_at > days_ago_s(7))
-> Seq Scan on projects ...
Filter: (created_at > days_ago_v(7))
```
第一种 IMMUTABLE 在规划时被折叠成了一个字面值（[常量会被折叠](/21-plans/#constants-are-folded)）。STABLE 保留为一次调用，却驱动了同一个索引。VOLATILE 可能随行变化，所以顺序扫描会调用它 20,000 次。要为每个函数选择事实允许的最严格标记。
### 虚假的 IMMUTABLE 会返回过时结果 {#a-false-immutable-serves-stale-answers}
永远不要给函数贴比事实更严格的标签。一个读取表中设置的查找函数被标记为 `IMMUTABLE`，它供一个统计过期项目的 PL/pgSQL 函数使用：30 天时结果是 19,271。把设置改成 365 天后，同一会话仍然返回 19,271，因为“常量”30 已经被折叠进缓存计划；新会话返回 10,913。只要连接池中的这些连接还在，这个错误就会持续存在。`IMMUTABLE` 函数绝不能读取表、设置、时区或时钟。
> [!DETAILS]- 深入阅读：三个计划、调用次数和过时的查找
>
> ```sql
> create function days_ago_i(n int) returns timestamptz
> language plpgsql immutable
> as $$ begin return timestamptz '2026-10-06' - n * interval '1 day'; end $$;
> -- days_ago_s: the same body, labeled stable
> -- days_ago_v: the same body, labeled volatile
> ```
>
> STABLE 版本的估算来自对其值的窥探：直方图给出 173 行（实际数量是 163）。VOLATILE 版本退回到三分之一的默认值，即 6,667 行。设置 `track_functions = 'pl'` 后，IMMUTABLE 版本运行一次，STABLE 版本运行两次（一次用于估算，一次用于扫描），VOLATILE 版本运行 20,000 次。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'IMMUTABLE is folded to a constant at plan time: the index condition holds a literal timestamp',
> > lab.plan_text($$ select count(*) from projects where created_at > days_ago_i(7) $$)
> > ~ 'Index Cond: \(created_at > ''\d{4}-\d\d-\d\d [^'']+''::timestamp with time zone\)');
> >
> > select lab.prove(
> > 'VOLATILE cannot be an index condition: a sequential scan with a filter',
> > lab.plan_text($$ select count(*) from projects where created_at > days_ago_v(7) $$)
> > like '%Seq Scan on projects%Filter: (created_at > days_ago_v(7))%');
> >
> > select lab.prove(
> > 'calls per query: IMMUTABLE once, STABLE at most twice, VOLATILE once per row (20,000)',
> > (select array_agg(calls order by funcname) from pg_stat_xact_user_functions
> > where funcname in ('days_ago_i', 'days_ago_s', 'days_ago_v'))
> > in (array[1, 1, 20000]::bigint[], array[1, 2, 20000]::bigint[]));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
>
> 过时的查找：
>
> ```sql
> create function setting(k text) returns int
> language sql immutable -- a lie: it reads a table
> as $$ select value from settings where key = k $$;
> ```
>
> `alter function setting(text) stable` 会使缓存计划失效，下一次调用就会得到正确结果。在索引表达式中，虚假的 `IMMUTABLE` 会留下过时的索引项，直到执行 `REINDEX`。还有一个区别：`VOLATILE` 函数会为其中的每个查询获取新的快照，因此能看到调用它的语句所做的更改；`STABLE` 和 `IMMUTABLE` 使用调用者的快照。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'a mislabeled IMMUTABLE function goes stale: the session keeps the old answer, a new session sees the new one',
> > :same_session = :before_change and :other_session < :before_change);
> >
> > alter function setting(text) stable;
> > select lab.prove(
> > 'relabeling it STABLE invalidates the cached plan and the answer is right again',
> > expired_projects() = :other_session);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
## 并行安全需要显式选择 {#parallel-safety-is-opt-in}
用户函数默认是 `PARALLEL UNSAFE`，因此，在 `where` 子句中只要有一个未标注的辅助函数，两百万行的扫描就会在一个进程中运行（[一个未标注的函数会让查询保持串行](/21-plans/#one-unlabeled-function-keeps-a-query-serial)）。把纯辅助函数标记为 `PARALLEL SAFE`。不要撒谎：执行写入的函数必须保持 `UNSAFE`（标成安全后，我们的函数因 `cannot execute INSERT during a parallel operation` 而失败）；接触临时表或后端本地状态的函数则是 `RESTRICTED`。
## 规划器还会相信另外三个标记 {#the-planner-believes-three-more-labels}
易变性不是规划器会照单全收的唯一标记。只有超级用户才能设置的 **`LEAKPROOF`**，允许函数先于 [行级安全](/43-glossary/#row-level-security)过滤器运行，这样策略就可以使用索引（[不防泄漏的函数会在策略之后运行](/33-security/#a-function-that-isnt-leakproof-runs-after-the-policy)）。较高的 **`COST`** 会让规划器先运行代价更低的条件。**`ROWS`** 会替换返回集合的函数默认 1,000 行估算（[FROM 中的函数会被估算为 1,000 行](/20-planner/#a-function-in-from-is-guessed-at-1000-rows)）；更好的做法是把它写成可内联的 SQL。
## 触发器逐行运行，除非你另有指定 {#triggers-run-once-per-row-unless-you-say-otherwise}
触发器是数据库在表发生变化时调用的函数，而你拥有的每个外键本身已经带着几个触发器。`BEFORE ... FOR EACH ROW` 会在行处理过程中调整行；`AFTER` 会对已经发生的变化作出反应。`FOR EACH STATEMENT` 触发器只运行一次，并通过**过渡表**读取所有发生变化的行。`WHEN` 子句让触发器变得再便宜不过：不触发的触发器。
### 行触发器每行大约花费一微秒 {#a-row-trigger-costs-about-a-microsecond-a-row}
我们在 `events` 的一份不记录 WAL 的表副本上更新了 100,000 行，取五次中的最佳结果：
| 100,000 行更新 | 我们机器上的时间 | 相对基线每行增加 |
| --- | --- | --- |
| 没有触发器，语句中使用 `updated_at = now()` | 150 到 185 ms | |
| `BEFORE` 行触发器设置 `updated_at` | 240 到 300 ms | 约 1 µs |
| 写入一条审计行的 `AFTER` 行触发器 | 360 到 400 ms | 约 2 µs |
**Table 24-2.** 行触发器每行大约花费一微秒
每行约一微秒：一次只处理一行时只是噪声；但对每晚处理 5,000 万行的任务来说，会耗掉将近一分钟的 CPU 时间。`BEFORE` 触发器还会为每行增加一条 [WAL](/43-glossary/#wal) 记录，用于记录在触发器看到该行之前取得的锁。在语句中设置该列，可以完成同样的工作，却不必承担触发器的代价。
> [!DETAILS]- 深入阅读：统计触发器增加的 WAL 记录
>
> `BEFORE` 触发器只有一行：`new.updated_at := now()`。在记录 WAL 的表副本上，每次运行处理 20,000 行；根据 `pg_stat_get_backend_wal`（PostgreSQL 18+）：没有触发器时写入 60,264 条 WAL 记录；`BEFORE` 触发器、行审计触发器和语句审计触发器各自都写入 80,264 条。审计触发器每写一条审计行，就会增加一次堆插入；无论逐行还是逐语句，都是如此。`BEFORE` 触发器本身不会写入任何东西；`pg_walinspect` 显示，它增加的记录是 `Heap LOCK`：裸更新有 19,994 条，带触发器时有 39,994 条。（裸更新也有锁记录，因为这里的每个新版本都会写到不同的页。）
>
> 对于普通审计日志，语句级触发器从过渡表写入全部审计行，耗时 270 到 320 ms，即每行约 1.3 µs：节省了 10% 到 30%，因为写入的行仍然相同。真正的收益来自逐行工作发生合并的情况，例如下面的计数器。`AFTER` 行事件会在语句结束前排队留在内存中，因此一次巨大的更新会排出一张巨大的队列。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'a one-line BEFORE row trigger makes a 100,000-row update measurably slower (over 15%)',
> > :t_before > :t_none * 1.15);
> >
> > select lab.prove(
> > 'a row-level audit trigger makes the same update at least 1.4x slower',
> > :t_row > :t_none * 1.4);
> >
> > select lab.prove(
> > 'a BEFORE UPDATE row trigger adds one WAL record per row: it locks each row before updating it',
> > :recs_before - :recs_none between 19000 and 21000);
> >
> > select lab.prove(
> > 'an audit trigger adds one insert per row to the WAL, row-level or statement-level alike',
> > :recs_row - :recs_none between 19000 and 21500
> > and abs(:recs_row - :recs_stmt) < :recs_row * 0.02);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
### 计数器触发器会让写入者串行化 {#a-counter-trigger-serializes-your-writers}
保持每个项目精确计数的通常做法是（[精确计数会读取全部数据](/22-queries/#exact-counts-read-everything)）在 `events` 上设置行触发器，运行 `update projects set event_count = event_count + 1`。它是正确的，但有两个问题，你在开发环境中看不到。
**现在每次插入都会锁住父行。** 两个会话向项目 7 插入事件时，过去可以并行进行。现在每个会话都会更新项目 7，而该行会一直锁到提交，因此会话 B 要等待会话 A 的整个事务结束（[行锁存在行中，而不在 pg_locks 中](/25-concurrency/#row-locks-live-in-the-row-not-in-pg_locks)）。最繁忙的项目会产生最多竞争。
**一次批量插入会把同一行重写数千次**，每次更新都要遍历此前的行版本。为一个项目插入 500 个事件大约耗时 16 ms；插入 4,000 个则耗时约 260 ms。行数是八倍，时间却是十六倍。
语句级触发器可以解决批量场景。锁的问题却无从解决。需要时用索引计数，或者把增量追加到一张窄表中，这种追加操作不会阻塞，再稍后汇总这些增量（[把热点计数器移到窄表](/07-hot/#move-hot-counters-to-a-narrow-table)）。
> [!DETAILS]- 深入阅读：计数器触发器的两种写法，以及 HOT 做了什么
>
> ```sql
> create function bump_event_counts() returns trigger
> language plpgsql
> as $$
> begin
> update projects p
> set event_count = p.event_count + n.added
> from (select project_id, count(*) as added from new_rows group by project_id) n
> where p.id = n.project_id;
> return null;
> end
> $$;
>
> create trigger events_count after insert on events
> referencing new table as new_rows
> for each statement execute function bump_event_counts();
> ```
>
> 每条语句只执行一次 `update`，计算结果来自过渡表 `new_rows`。从第三个会话观察被阻塞的会话：
>
> ```text
> wait_event_type | wait_event | blocked_by
> -----------------+---------------+------------
> Lock | transactionid | {3798}
> ```
>
> 行级版本的 1,000 次更新大多是 HOT 更新（1,000 次中有 988 次）：一个 [HOT](/07-hot/#basics-chapter-7) 链会在一页上不断增长，直到该页塞满了暂时无法清理的版本；此时一次更新会移到新页，链也重新开始。这个过程发生了 12 次，`projects` 从 191 页增长到 202 页，以容纳同一行的 1,000 个版本。代价不在于未能采用 HOT 更新，而在于锁、增长和二次方时间。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'without a counter trigger, a second insert for the same project does not wait',
> > dblink_is_busy('b') = 0);
> >
> > -- after create trigger events_count ... for each row execute function bump_event_count()
> > select lab.prove(
> > 'with the counter trigger, the second insert waits on the first transaction (a transactionid lock)',
> > dblink_is_busy('b') = 1
> > and (select wait_event_type = 'Lock' and wait_event = 'transactionid'
> > from pg_stat_activity where pid = :b_pid)
> > and cardinality(pg_blocking_pids(:b_pid)) = 1);
> >
> > select lab.prove(
> > 'a 1,000-row insert for one project makes 1,000 updates of one counter row, and projects grows',
> > :upd = 1000 and :pages_after > :pages_before);
> >
> > select lab.prove(
> > 'with a row-level counter trigger, eight times the rows for one project takes over 12 times as long',
> > :t_4000 > :t_500 * 12);
> >
> > select lab.prove(
> > 'a statement-level trigger over the transition table updates the counter once, HOT',
> > :upd2 = 1 and :hot2 = 1 and (select event_count from projects where id = 9) = 1000);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
### COPY 会触发触发器；复制角色会跳过触发器 {#copy-fires-triggers-replication-roles-dont}
`COPY FROM` 和 `INSERT` 一样会触发触发器，因此加载一百万行要为触发器付出一百万次代价。`session_replication_role = replica` 会跳过触发器（设置它需要超级用户权限，或者在 PostgreSQL 15+ 中被授予对它的 `SET` 权限，而托管服务往往不会授予这种权限）；[逻辑复制](/43-glossary/#logical-replication)会设置这个值，这样触发器就不会执行两遍。
> [!DANGER] replica 也会关闭外键
>
> 外键也是触发器，而 `replica` 会把它们全部关闭。我们为不存在的项目 999,999 插入了一条事件，插入成功了。用这种方式加速批量加载，留下的孤儿行也不会有任何约束再报告。
> [!DETAILS]- 深入阅读：逻辑复制订阅端上的触发器
>
> 对于每个触发器，`alter table ... enable replica trigger t` 只会在 `replica` 模式下触发，`enable always trigger t` 则会在两种模式下都触发。在逻辑复制订阅端，如果某个触发器应该在复制行到达时运行（比如维护一张本地汇总表），就必须使用其中一种设置，否则它会悄无声息地什么也不做。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > set session_replication_role = replica;
> > insert into events (account_id, project_id, user_id, kind) values (9, 10, 8, 'build');
> > insert into events (account_id, project_id, user_id, kind) values (9, 999999, 8, 'orphan');
> > reset session_replication_role;
> >
> > select lab.prove(
> > 'with session_replication_role = replica, ordinary triggers do not fire',
> > (select event_count from projects where id = 10) = 2);
> >
> > select lab.prove(
> > 'and neither do foreign keys: an event for a project that does not exist got in',
> > exists (select 1 from events where project_id = 999999));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
## 触发器必须证明自己值得存在 {#a-trigger-has-to-earn-its-place}
本章中的每个触发器都做了它声称要做的事。但它是否应该存在，是另一个问题。我们的答案是：触发器应该比你想象的少。
**它们带来的好处。**
- **原子性。** 触发器的工作会和更改一起提交或回滚。审计行不会只写了一半。
- **不怕调用者漏做。** 每次写入都会触发它：应用、第二个服务、迁移、凌晨两点有人从 psql 运行的修复命令，都一样。
- **覆盖每个写入者的历史**（[历史触发器捕获每个写入者，每行插入一次](/14-relationships/#a-history-trigger-catches-every-writer-at-an-insert-per-row)）。
**它们隐藏的东西。**
- **行为。** 一条报告插入一行的 `insert`，可能还增加了计数器、写入了审计行、排队了一个任务。调用位置没有任何说明，搜索应用代码也找不到这些行为。
- **代价与串行化。** 每次回填都要支付逐行代价；更新父行的触发器会把并行插入变成排队。
- **顺序。** 同一事件上的触发器按名称顺序运行，而不是按创建顺序。我们依次创建了 `z_normalize`、`a_validate` 和 `m_stamp`，但它们的运行顺序是 a、m、z。重命名一个触发器，就会重新排列业务逻辑。
- **部署与调试。** 它们以迁移的形式发布，一次改变所有会话的行为；出错时，`INSERT` 报告的是一行 PL/pgSQL 上下文，而不是堆栈跟踪。
**我们的规则。** 当工作必须与更改保持原子性，并且必须对每个写入者发生时，使用触发器：审计和历史行、你不能指望调用者设置的派生列、以及任何约束无法表达的跨行不变量。很可能会有批量写入时，使用语句级触发器。
其他事情都在提交之后交给应用处理：通知、邮件、webhook、缓存失效、搜索索引、热点行上的计数器、业务工作流。如果副作用不能丢失，就在事务中写一行 [outbox](/09-acid/#put-the-message-in-the-same-transaction-as-the-order)。触发器是隐藏的写入，每行都要永远为它付费，因此每个触发器都应该经得起设计评审。
> [!DETAILS]- 深入阅读：触发顺序、触发器链和约束触发器
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > create trigger z_normalize before insert on trig_order
> > for each row execute function append_note('z');
> > create trigger a_validate before insert on trig_order
> > for each row execute function append_note('a');
> > create trigger m_stamp before insert on trig_order
> > for each row execute function append_note('m');
> > insert into trig_order (id) values (1);
> >
> > select lab.prove(
> > 'triggers on the same event fire in name order, not creation order',
> > (select note from trig_order where id = 1) = 'amz');
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
>
> 一个会写入另一张表的触发器，也会触发那张表上的触发器，因此一条触发器链可能在无人注意前连续执行好几层。
>
> **约束触发器**可以延迟到提交时执行（`deferrable initially deferred`），适合用于某条跨行规则在事务内部可能短暂不成立的情况。两个并发事务仍然可能同时通过一个会读取其他行的检查（[有目的地选择隔离级别](/25-concurrency/#pick-an-isolation-level-on-purpose)）。
## 过程可以提交事务 {#procedures-can-commit}
**过程**（PostgreSQL 11+）通过 `CALL` 调用，可以边执行边提交；函数不能这样做。因此，对于回填和清理任务，过程是更合适的形式：如果只有一个长事务，它会持有锁、阻止 [vacuum](/10-vacuum/#basics-chapter-10) 清理，并在任何错误发生时丢掉全部结果。我们的回填分十个已提交批次更新了 100,000 行。要在自动提交模式下运行过程：如果处于 `BEGIN` 内部（许多驱动程序会替你添加它），第一次 `COMMIT` 会因 `invalid transaction termination` 而失败。
> [!DETAILS]- 深入阅读：分批回填过程
>
> ```sql
> create procedure backfill_kind_upper(batch int)
> language plpgsql
> as $$
> declare
> last_id bigint := 0;
> max_id bigint;
> begin
> select max(id) into max_id from backfill_demo;
> while last_id < max_id loop
> update backfill_demo set kind_upper = upper(kind)
> where id > last_id and id <= last_id + batch;
> last_id := last_id + batch;
> commit;
> end loop;
> end
> $$;
>
> call backfill_kind_upper(10000);
> ```
>
> 像 [用主键逐步遍历的小批次回填](/26-migrations/#backfill-in-short-batches-that-walk-the-primary-key)那样，遍历键的范围，不要使用 `OFFSET`。过程不能从带有 `EXCEPTION` 子句的块中提交。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > call backfill_kind_upper(10000);
> >
> > select lab.prove(
> > 'the procedure filled every row, in ten separately committed transactions',
> > (select count(*) filter (where kind_upper is null) = 0
> > and count(distinct xmin::text) = 10
> > from backfill_demo));
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
## EXCEPTION 块就是子事务 {#exception-blocks-are-subtransactions}
带有 `EXCEPTION` 子句的 PL/pgSQL 块每次进入时都会设置一个保存点，形成一个子事务，并付出 [子事务并非没有代价](/25-concurrency/#subtransactions-are-not-free)所描述的成本。进入这样的块 100,000 次大约耗时 18 ms，而普通块只需 3 ms，即使没有抛出错误也是如此。循环插入 1,000 行，每次插入都放在异常保护块中，就会使用 1,000 个子事务 ID，从第 65 行开始就超过了 64 个条目的缓存。使用 `on conflict do nothing`，让错误根本不会发生；或者用一层异常处理包住整个循环。
> [!DETAILS]- 深入阅读：受保护的插入
>
> ```sql
> create function insert_guarded(n int) returns void
> language plpgsql
> as $$
> begin
> for i in 1..n loop
> begin
> insert into guarded_insert values (i);
> exception when unique_violation then
> null;
> end;
> end loop;
> end
> $$;
> ```
>
> 这 1,000 行带有 1,000 个不同的 `xmin` 值；没有异常块时，它们会共享同一个值。
>
> > [!EXAMPLE]- 动手验证 · lab `23-functions`
> >
> > ```sql
> > select lab.prove(
> > 'entering a block with an EXCEPTION clause costs at least 3x a plain block',
> > :t_guarded > :t_plain * 3);
> >
> > select insert_guarded(1000);
> > select lab.prove(
> > 'with one per row, each insert gets its own subtransaction ID: 1,000 of them',
> > (select count(distinct xmin::text) from guarded_insert) = 1000);
> > ```
> >
> > [完整实验脚本](https://www.flagon.io/books/eight-kilobytes/lab/23-functions.sql)
## 函数不可见，除非你开启跟踪 {#functions-are-invisible-until-you-turn-tracking-on}
本章中的每个问题，一旦能看见它就更容易修复；而默认统计信息会让函数不可见。**`track_functions`** 在 `pg_stat_user_functions` 中统计每个函数的调用次数和耗时。**`pg_stat_statements.track = all`** 会为函数内的语句建立独立条目，而不是把它们隐藏在一条缓慢的 `INSERT` 中（[从 pg_stat_statements 开始](/31-observability/#start-with-pg_stat_statements)）。**`auto_explain.log_nested_statements`** 会记录函数内部的计划，而 `EXPLAIN` 本身不会显示这些计划。
> [!DETAILS]- 深入阅读：用 auto_explain 捕获通用计划
>
> ```sql
> load 'auto_explain';
> set auto_explain.log_min_duration = 10;
> set auto_explain.log_nested_statements = on;
> set auto_explain.log_level = notice; -- send plans to this session, not the log
> select count(*) from latest_ids('incident');
> select count(*) from latest_ids('build'); -- five times
> select count(*) from latest_ids('incident');
> ```
>
> 快速调用不会记录任何内容。第七次调用会记录嵌套计划：
>
> ```text
> NOTICE: duration: 242.283 ms plan:
> Query Text: select id from events where kind = k order by created_at desc limit 10
> Limit (cost=0.43..2.73 rows=10 width=16)
> -> Index Scan Backward using events_created_at_idx on events ...
> Filter: (kind = $1)
> ```
>
> `Filter: (kind = $1)` 就是通用计划，它在实际运行中暴露出来了。在生产环境中，要按慢查询阈值将计划记入服务器日志，并寻找本应是字面值却出现 `$1` 的地方。
## 本章要点 {#the-short-version}
- 主库是唯一不能横向扩展的 CPU。让它承担能缩小数据、保持规则原子性或节省往返通信的工作。
- 如果函数只包含一个查询，就用 SQL 编写，并标记为 `STABLE` 或 `IMMUTABLE`。
- PL/pgSQL 五次调用后可能切换到通用计划；`force_custom_plan` 可以解决参数倾斜带来的计划问题。
- 在事实允许的范围内尽可能严格地标注易变性，绝不超出这个范围。
- 只有当工作必须具有原子性并且必须对每个写入者发生时，才使用触发器。
- 计数器触发器会把所有写入者串行化到父行上。
- `session_replication_role = replica` 也会禁用外键。
- 不要在逐行循环中使用 `EXCEPTION` 块。
## 延伸阅读 {#further-reading}
- [User-Defined Functions](https://www.postgresql.org/docs/18/xfunc.html)，PostgreSQL 文档。SQL 函数、过程，以及 `SUPPORT`、`ROWS` 和 `COST` 钩子。
- [Function Volatility Categories](https://www.postgresql.org/docs/18/xfunc-volatility.html)，PostgreSQL 文档。每种标记背后的承诺，包括过时计划的风险。
- [Inlining of SQL functions](https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions)，PostgreSQL wiki。内联的每个条件。
- [PL/pgSQL: Plan Caching](https://www.postgresql.org/docs/18/plpgsql-implementation.html)，PostgreSQL 文档。PL/pgSQL 如何准备语句，以及何时切换到通用计划。
- [Transaction Management in PL/pgSQL](https://www.postgresql.org/docs/18/plpgsql-transactions.html)，PostgreSQL 文档。过程内部何时可以使用 `COMMIT`。
- [Triggers](https://www.postgresql.org/docs/18/triggers.html) 和 [CREATE TRIGGER](https://www.postgresql.org/docs/18/sql-createtrigger.html)，PostgreSQL 文档。触发顺序、过渡表和约束触发器。
- [auto_explain](https://www.postgresql.org/docs/18/auto-explain.html)，PostgreSQL 文档。记录嵌套语句的计划。