# 编写可靠 SQL 脚本

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

---

可靠脚本不是“把终端历史保存成 `.sql`”。它必须定义输入，验证上下文，遇错停止，选择事务边界，区分可重跑与可回退，并把结果传给调用者。这里建立的约定会贯穿全书实验。

## 2.3.1 `ON_ERROR_STOP`、退出码与失败即停 {#item-2-3-1}

`psql` 默认面向交互使用：一条 SQL 失败后，它通常报告错误并继续读取后续输入。对人来说便于修正，对自动化来说却可能把“步骤二失败、步骤三成功”误报为任务完成。

所有本书脚本都在文件内设置：

```text
\set ON_ERROR_STOP on
```

调用方仍显式传入：

```bash
psql -X -w \
  "service=pg36-admin" \
  --set=ON_ERROR_STOP=1 \
  --file=setup.sql
```

双重设置不是为了炫技：文件自带安全默认，调用方又表明自己依赖失败即停语义。`-X` 去掉隐含 `psqlrc`，`-w` 禁止无人值守任务等待密码。

### 四类退出状态

PostgreSQL 18 的 `psql` 约定：

| 状态码 | 含义 | 调用方应怎样解释 |
|---:|---|---|
| `0` | 正常完成 | 仍需执行状态验证，不能只看返回码 |
| `1` | `psql` 自身致命错误，如文件不存在 | 先检查客户端输入与运行环境 |
| `2` | 非交互会话的服务器连接中断 | 状态未知，先取证再决定是否重跑 |
| `3` | 脚本内发生错误，且启用了 `ON_ERROR_STOP` | 按预期中止；检查事务是否回滚 |

状态码 `3` 依赖 `ON_ERROR_STOP`。没有它时，脚本可能在服务端报错后继续，最终甚至返回 `0`。因此不能用 `grep ERROR` 代替退出码，也不能只看退出码而省略状态验证。

Shell 中应保留原始状态：

```bash
set +e
psql -X -w \
  "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f broken.sql \
  >broken.stdout \
  2>broken.stderr
status=$?
set -e

printf 'exit_code=%s\n' "$status"
test "$status" -eq 3
```

不要写成：

```bash
psql ... | tee task.log
```

若 shell 未启用 `pipefail`，管道状态可能来自成功的 `tee`，从而吞掉 `psql` 失败。可以启用 `set -o pipefail`，或像综合任务那样分别重定向标准输出与标准错误。

### 失败即停不等于原子回滚

`ON_ERROR_STOP` 只控制客户端是否继续发送后续命令，不会自动回滚之前已经提交的语句。若每条语句都处于自动提交模式，第一条 `INSERT` 成功、第二条语法错误时，第一条仍可能永久存在。

对可放进同一事务的脚本，使用：

```bash
psql -X -w \
  --single-transaction \
  "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f broken.sql
```

`--single-transaction`（`-1`）会在所有 `-c`/`-f` 输入之前发送 `BEGIN`，成功后 `COMMIT`，失败且启用 `ON_ERROR_STOP` 时 `ROLLBACK`。本章故障注入正是用它证明标记行数量保持为零。

并非所有命令都允许在事务块内执行。`CREATE DATABASE`、`VACUUM`、`CREATE INDEX CONCURRENTLY` 等动作需要单独设计阶段、前置断言与补偿路径。遇到这类命令，不能为了追求“一个事务”而忽略 PostgreSQL 的语义。

## 2.3.2 变量、条件、包含文件与事务包装 {#item-2-3-2}

`psql` 变量是客户端文本替换机制，不是服务端绑定参数。正确引用方式取决于变量代表“值”还是“标识符”：

```bash
psql -X "service=pg36-admin" \
  -v expected_db=pg36_shop \
  -v owner_role=pg36_owner \
  -f context.sql
```

脚本内：

```sql
SELECT current_database() = :'expected_db';
SET ROLE :"owner_role";
```

| 写法 | 语义 | 例子展开 | 安全边界 |
|---|---|---|---|
| `:'name'` | SQL 字符串字面量 | `'pg36_shop'` | 由 `psql` 正确引用值 |
| `:"name"` | SQL 标识符 | `"pg36_owner"` | 由 `psql` 正确引用对象或角色名 |
| `:name` | 原样文本替换 | `pg36_shop` | 只适用于完全受控的 SQL 片段 |

不要把用户输入拼进原样变量：

```sql
-- 危险：变量可改变 SQL 结构
SELECT * FROM shop.ch02_fixture WHERE fixture_id = :raw_input;
```

应用程序应使用驱动的绑定参数；`psql` 脚本至少用 `:'value'` 后再由服务端转换为目标类型：

```sql
SELECT *
FROM shop.ch02_fixture
WHERE fixture_id = :'fixture_id'::integer;
```

### 默认值与客户端条件

检测变量是否存在：

```text
\if :{?expected_db}
\else
  \set expected_db pg36_shop
\endif
```

`\if` 接受可以解释为布尔值的结果，未执行分支中的 SQL 不会发送给服务器。它适合控制脚本装配，不应承担复杂业务逻辑。需要数据库事务、异常和类型系统时，使用 SQL 或 PL/pgSQL。

### 相对包含保证可搬迁

```text
\ir context.sql
```

`\ir`（`\include_relative`）相对于当前脚本所在目录寻找文件；`\i` 通常相对于 `psql` 的当前工作目录。一个从任意目录调用的实验包，应优先用 `\ir` 组织内部依赖。

本章文件关系是：

```text
setup.sql ─┐
verify.sql ├──> context.sql
broken.sql ┘
```

每个入口都独立设置 `ON_ERROR_STOP`，再包含同一上下文保护，避免复制三份逐渐漂移的断言。

### 两种事务包装

文件内部显式包装：

```sql
\set ON_ERROR_STOP on
BEGIN;
-- 一组允许在事务块内的变更
COMMIT;
```

调用方包装：

```bash
psql -X -1 -v ON_ERROR_STOP=1 -f task.sql "service=pg36-admin"
```

前者让事务意图跟随文件，后者便于对故障注入或多个 `-f` 输入统一包裹。不要混用嵌套 `BEGIN` 来制造虚假的双重保险；PostgreSQL 没有普通嵌套事务，只有保存点。若脚本本身控制事务，就不再额外传 `-1`。

一旦脚本主动执行 `COMMIT`、`\connect` 或事务块外命令，调用方就不能再假设 `-1` 提供全局原子性。事务边界必须是任务接口的一部分，而不是隐藏实现。

## 2.3.3 幂等、重入与执行前预览 {#item-2-3-3}

三个常被混用的目标需要分开：

- **幂等**：对同一起点重复执行，最终状态不因执行次数改变；
- **可重入**：上次在某个中间点失败后，能识别现状并安全继续或重新开始；
- **可回退**：有明确动作恢复到先前状态，且已经验证其适用范围。

一条 `CREATE TABLE IF NOT EXISTS` 只能避免“同名关系已经存在”的错误，并不证明现有表的列、类型、约束和 owner 正确。若错误对象占用了名称，它反而会掩盖漂移。

本章 `setup.sql` 采用“收敛 + 断言”：

```sql
CREATE TABLE IF NOT EXISTS shop.ch02_fixture (...);

DO $shape_guard$
BEGIN
    -- 从 pg_attribute 计算实际列形状；
    -- 若与期望数组不同，RAISE EXCEPTION。
END
$shape_guard$;

TRUNCATE TABLE shop.ch02_fixture;
INSERT INTO shop.ch02_fixture ...
```

这使脚本在形状正确时可以重复生成同一夹具，在形状漂移时失败，而不是偷偷接受未知对象。`TRUNCATE` 会删除本章夹具的现有行，因此整个 setup 是 `R1·可逆变更`，只允许作用于明确的教学表。

### SQL 没有通用 dry-run

可靠预览应针对动作设计：

| 动作 | 可用预览 | 局限 |
|---|---|---|
| 目录变更 | 查询当前定义并生成计划清单 | 清单正确不代表执行时没有并发变化 |
| `UPDATE` / `DELETE` | 用同一谓词先 `SELECT` 主键、数量和样本 | 预览与执行间可能发生状态变化 |
| 事务性 DDL | 在隔离环境或 `BEGIN` 后执行再 `ROLLBACK` | 锁、序列、外部副作用等不一定完全消失 |
| 查询 | `EXPLAIN` 查看计划 | 某些函数在规划期仍可能执行；不证明结果正确 |
| 生成式 DDL | 先输出生成 SQL，再人工审查后 `\gexec` | 审查和执行之间仍需控制漂移 |

“先 `BEGIN`，最后 `ROLLBACK`”不是万能模拟器。序列值不会因事务回滚自动收回，通知可能在提交时发送，外部程序和远程系统更有自己的事务边界。正式变更应在预生产或可销毁克隆中演练，而不是在生产上借 `ROLLBACK` 试胆量。

### 让计划与应用分阶段

一个成熟任务通常分为：

1. `inspect`：只读采集现状；
2. `plan`：根据现状生成明确变更集合；
3. `apply`：再次检查前置条件后执行；
4. `verify`：独立查询目标状态；
5. `reset` 或 `rollback`：只处理任务拥有的对象。

本章的规模很小，`setup` 内部合并了 plan 与 apply，但仍保留形状断言；下卷涉及切换、备份和事故处理时会把阶段拆得更细。

## 2.3.4 日志、清单与机器可读结果 {#item-2-3-4}

一个任务至少有四类输出：

| 输出 | 受众 | 推荐格式 |
|---|---|---|
| 进度与人读结果 | 操作者 | 对齐文本，保留上下文 |
| 错误与警告 | 调用方、排障者 | 独立 stderr，保留 SQLSTATE 与位置 |
| 状态摘要 | 自动验收 | `key=value`、CSV 或 JSON |
| 运行清单 | 审计与复现 | 时间、版本、端点名、脚本哈希、参数和退出码 |

不要把所有内容重定向到一个文件后再靠正则猜哪一行是结果。本章综合任务分别生成：

```text
manifest.txt
setup.stdout
setup.stderr
verify.txt
verify.stderr
pgbench.txt
pgbench.stderr
broken.status
broken.stdout
broken.stderr
```

### 机器输出要主动收窄

最简单的单值：

```bash
row_count="$(
  psql -X -w "service=pg36-admin" \
    -v ON_ERROR_STOP=1 \
    --tuples-only \
    --no-align \
    -c 'SELECT count(*) FROM shop.ch02_fixture'
)"
test "$row_count" = "100"
```

多列结果使用：

```bash
psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  --csv \
  -c '
    SELECT fixture_id, sku, amount
    FROM shop.ch02_fixture
    ORDER BY fixture_id
  ' >fixture.csv
```

无论哪种格式，都要显式 `ORDER BY`。关系结果没有默认顺序；一次输出“碰巧稳定”不能成为校验依据。

### 清单记录复现所需条件

至少保存：

```bash
date -u +%Y-%m-%dT%H:%M:%SZ
psql --version
pgbench --version
sha256sum context.sql setup.sql verify.sql workload.sql
```

再从服务端记录：

```sql
SELECT current_setting('server_version');
SELECT current_database(), session_user, pg_is_in_recovery();
```

只写 `PostgreSQL 18` 不够：客户端与服务器可以是不同版本，端点也可能经过 Pigsty 服务路由。清单应保存 service 名称和脱敏后的连接上下文，不保存密码或完整 passfile。

若 Shell 开启 `set -x`，展开后的连接 URI、变量和命令可能进入日志。处理秘密前应关闭跟踪，或者从设计上确保命令行根本不含秘密。

### 本节验收

- 任意 SQL 错误都会使脚本停止并返回非零；
- 能解释状态码 `1`、`2`、`3` 的差异；
- 值变量使用 `:'name'`，标识符变量使用 `:"name"`；
- 内部文件使用 `\ir`，不依赖调用者当前目录；
- setup 重跑得到相同状态，形状漂移则明确失败；
- 标准输出、标准错误、状态摘要与运行清单彼此分离。

## 参考资料

- [PostgreSQL 18：psql 命令行选项与退出状态](https://www.postgresql.org/docs/18/app-psql.html)
- [PostgreSQL 18：psql 变量](https://www.postgresql.org/docs/18/app-psql.html#APP-PSQL-VARIABLES)
- [PostgreSQL 18：psql 条件块](https://www.postgresql.org/docs/18/app-psql.html#PSQL-METACOMMAND-IF)
- [PostgreSQL 18：事务隔离与原子提交](https://www.postgresql.org/docs/18/tutorial-transactions.html)

---

[上一节：用 psql 探索与取证](../02/) · [返回本章目录](../) · [下一节：输入、输出与确定性数据](../04/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)