2.3 编写可靠 SQL 脚本

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

2.3.1 ON_ERROR_STOP、退出码与失败即停

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

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

\set ON_ERROR_STOP on

调用方仍显式传入：

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

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

四类退出状态

PostgreSQL 18 的 psql 约定：

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

Shell 中应保留原始状态：

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

不要写成：

psql ... | tee task.log

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

失败即停不等于原子回滚

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

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

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 变量、条件、包含文件与事务包装

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

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

脚本内：

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

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

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

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

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

默认值与客户端条件

检测变量是否存在：

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

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

相对包含保证可搬迁

\ir context.sql

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

本章文件关系是：

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

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

两种事务包装

文件内部显式包装：

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

调用方包装：

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

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

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

2.3.3 幂等、重入与执行前预览

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

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

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

本章 setup.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

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

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

让计划与应用分阶段

一个成熟任务通常分为：

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

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

2.3.4 日志、清单与机器可读结果

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

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

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

机器输出要主动收窄

最简单的单值：

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"

多列结果使用：

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。关系结果没有默认顺序；一次输出“碰巧稳定”不能成为校验依据。

清单记录复现所需条件

至少保存：

date -u +%Y-%m-%dT%H:%M:%SZ
psql --version
pgbench --version
sha256sum context.sql setup.sql verify.sql workload.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 命令行选项与退出状态
• PostgreSQL 18：psql 变量
• PostgreSQL 18：psql 条件块
• PostgreSQL 18：事务隔离与原子提交

上一节：用 psql 探索与取证 · 返回本章目录 · 下一节：输入、输出与确定性数据 · 查看全书目录 · 查看索引中心