PHP小码农

Appearance

psql 事务控制（AUTOCOMMIT） ​

事务是 PostgreSQL 的立身之本，而 psql 作为最常用的客户端，给了你两套控制事务的开关：一套是 SQL 层面的 BEGIN/COMMIT/ROLLBACK（与任何客户端一致），另一套是 psql 自己的 AUTOCOMMIT 变量与 --single-transaction 命令行选项。前者在《事务基础》已经讲过语义，本页聚焦的是psql 特有的那部分：默认的自动提交到底怎么工作、AUTOCOMMIT off 模式下每一句话都发生了什么、以及如何用 -1 --single-transaction 把一整个迁移脚本包进单个事务。

这一页同时是「脚本安全」的一页。迁移脚本最常见的两类事故——改到一半发现错了，已经提交的回不来；以及以为成功了，实际什么都没进库——都能通过 ON_ERROR_STOP 与事务的正确组合避免。我们会把「批量脚本的安全模式」作为一等公民来写：什么时候该一个大事务包到底、什么时候必须分段提交、长事务的代价在哪里。

阅读前提是你已经理解事务的 ACID 语义与隐式提交概念；本页只谈 psql 客户端视角的行为差异，不再重复事务本身的理论。

前置知识

建议先阅读：psql 变量与 \gset

基础概念 ​

先厘清三个层次的东西，避免概念互相打架：

层次机制归属
服务器事务BEGIN/COMMIT/ROLLBACK/SAVEPOINTPostgreSQL 服务端语义，所有客户端通用
自动提交开关psql 变量 AUTOCOMMIT（默认 on）psql 客户端行为，决定「要不要替你发 BEGIN」
脚本事务包装命令行选项 -1 / --single-transactionpsql 客户端行为，把 -c/-f 的全部输入包进一个事务

AUTOCOMMIT = on（默认） 时，每条 SQL 一执行成功就自动提交，语句之间没有事务包裹。这并不意味着你开不了事务——显式敲 BEGIN 后，psql 会把后续语句交给这个事务，直到你 COMMIT 或 ROLLBACK。

AUTOCOMMIT = off 时，psql 会在「不在事务里的每条语句」之前替你发一个隐式 BEGIN，此后所有变更持续累积，直到你显式 COMMIT/ROLLBACK 才落盘。它模拟的是 Oracle 等数据库的默认行为。

--single-transaction（-1） 则面向「一次性脚本」：psql -1 -f migrate.sql 会在执行脚本前发 BEGIN，脚本全部执行完再发 COMMIT，把整个脚本变成一个原子操作。

text
AUTOCOMMIT on :   S1[提交] S2[提交] S3[提交]           —— 每句一事务
显式 BEGIN    :   BEGIN S1 S2 COMMIT                    —— 你自己画边界
AUTOCOMMIT off:   [BEGIN] S1 S2 S3 ... 你敲 COMMIT       —— psql 替你开，你负责收尾
--single-transaction : BEGIN 输入1 输入2 输入3 COMMIT     —— psql 替你开也替你收

语法与用法 ​

默认模式下的显式事务（与普通 SQL 无异）：

sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

切换自动提交模式：

sql
\set AUTOCOMMIT off        -- 关闭自动提交（注意是小写 off 即可）
INSERT INTO t VALUES (1);  -- 此时已被隐式 BEGIN 包住，未提交
SELECT * FROM t;           -- 本会话可见
COMMIT;                    -- 显式提交后才真正落盘

\set AUTOCOMMIT on         -- 改回来（不会自动提交当前未决事务！）

命令行选项：

bash
# 多条 -c：各自独立提交（默认）
psql -d appdb -c "TRUNCATE staging;" -c "INSERT INTO staging SELECT * FROM import;"

# --single-transaction（等价 -1）：两条 -c 包进一个事务
psql -d appdb -1 --set=ON_ERROR_STOP=1 \
  -c "TRUNCATE staging;" \
  -c "INSERT INTO staging SELECT * FROM import;"

脚本 + 错误即回滚的标准姿势：

bash
psql -X -d appdb -v ON_ERROR_STOP=1 --single-transaction -f migrate_0042.sql

退出码速查（脚本判断成败的依据）：

退出码含义
0正常结束
1psql 自身的致命错误（如内存不足、文件不存在）
2连接中断
3脚本执行出错且 ON_ERROR_STOP 处于开启状态

详细说明 ​

默认行为：AUTOCOMMIT on 下的边界感 ​

默认模式下，没有 BEGIN 包裹的每条语句就是一个事务。这带来的直接后果：

sql
DELETE FROM orders WHERE created_at < '2020-01-01';
-- 执行完的这一瞬间，删除已经提交，不存在“撤销”机会

而一旦进入显式事务，psql 的提示符会看到 BEGIN 回显，其间任何语句失败只会把事务置为「已中止」状态，后面的语句会被拒绝（current transaction is aborted, commands ignored until end of transaction block），直到 ROLLBACK。这个「失败后必须先回滚」的规则与客户端无关，是服务器的事务状态机决定的；psql 的 ON_ERROR_ROLLBACK 变量可以替你在交互模式里自动打 SAVEPOINT 缓解它（后面专述）。

对交互式使用，建议保持默认 AUTOCOMMIT on：临时 UPDATE/DELETE 改错了顶多一行，但忘了 COMMIT 的 off 模式会把「改动去哪了」的问题搅得很混乱。

显式 BEGIN/COMMIT/ROLLBACK 与 SAVEPOINT ​

显式事务在 psql 里没有任何特殊语法，就是 SQL。值得展开的是 SAVEPOINT 在长脚本里的价值：

sql
BEGIN;

SAVEPOINT before_risky;
UPDATE big_table SET status = 'done' WHERE batch = 42;
-- 发现不对劲，只回滚这一小段
ROLLBACK TO SAVEPOINT before_risky;

-- 换个写法重来
UPDATE big_table SET status = 'done' WHERE batch = 42 AND id % 2 = 0;

COMMIT;

SAVEPOINT 让「一个事务内部的局部失败」可以被局部撤销，在数据修复场景比「整段重来」高效得多。它与 psql 的配合没有任何额外语法，但注意 SAVEPOINT 是栈式管理，ROLLBACK TO 之后该保存点仍然存在（可重复回滚），RELEASE SAVEPOINT 则销毁它。

AUTOCOMMIT off 模式的运行细节 ​

\set AUTOCOMMIT off 之后，psql 的行为变成：

  1. 隐式 BEGIN：在执行任何「不在事务内、本身也不是事务控制命令」的语句前，psql 自动发一个 BEGIN；
  2. 白名单感知：本身是事务控制（BEGIN/COMMIT/ROLLBACK）或不能在事务块内执行的命令（VACUUM、CREATE DATABASE、CREATE INDEX CONCURRENTLY 等）不会触发隐式 BEGIN——psql 识别得出来，这些命令照常运行；
  3. 变更持续累积：所有 DML/DDL 都攒在同一个事务里，直到你显式敲 COMMIT（或 END）才提交，敲 ROLLBACK 则全部作废；
  4. 切换开关不收尾：从 off 改回 on 不会替你提交当前未决事务；未决事务依旧悬着，你必须自己 COMMIT；
  5. 退出即回滚：带着未提交事务 \q 退出或连接断开，服务器一律回滚。
sql
\set AUTOCOMMIT off
INSERT INTO audit_log(msg) VALUES ('step1');   -- 未提交
INSERT INTO audit_log(msg) VALUES ('step2');   -- 同一事务
SELECT * FROM audit_log;                        -- 本会话可见，其他会话看不到
ROLLBACK;                                       -- 两行全部消失

autocommit-off 模式的价值主要在两种场景：把多条相关 DML 自然地聚成一个事务而不想手写 BEGIN，以及从 Oracle 等数据库迁移过来的工程师保持肌肉记忆。代价是「忘记 COMMIT」的风险：交互式改完数据忘了提交，换一个会话查不到，还以为改丢了。因此它更适合在脚本里明确使用，交互环境保持默认。

AUTOCOMMIT off 下没有专属的提交元命令

psql 没有类似 \commit、\rollback 的元命令。AUTOCOMMIT off 模式下，提交与回滚就是敲 SQL 的 COMMIT/ROLLBACK 语句——psql 识别它们是事务控制语句，不会为其再包隐式 BEGIN。部分资料把它们写成「元命令」是不准确的。

退出、断连与未决事务 ​

服务器把「连接消失」视为回滚信号：Ctrl+C 退出 psql、网络断开、psql \q、甚至客户端机器断电，未提交事务一律回滚，绝无「半个事务」落地。这是事务模型给的安全网，但要警惕它的另一面——psql 正常退出时不会提醒你还有未决事务。在 autocommit-off 或显式 BEGIN 的长会话里，养成「收尾先 COMMIT/ROLLBACK 再 \q」的习惯；不确定时先查 SELECT pg_current_xact_id(); 之外更直观的手段——直接再查一次你改过的表确认预期。

补充两个与断连相关的工程细节：

  1. Ctrl+C 的两层含义：交互模式下第一次 Ctrl+C 中断当前正在执行的语句（服务器收到取消请求，SQLSTATE 57014），psql 回到输入提示符；如果当时正处于显式事务中，事务保持「已中止」状态，需要 ROLLBACK 复位——别把「语句被取消」当成「事务已回滚」。
  2. 长事务脚本对网络抖动敏感：psql 没有自动重连，脚本跑到一半网络闪断，未决事务由服务器回滚、脚本以非零码退出。跨机房跑长迁移时，把「断点续跑」设计进脚本（分段提交 + 幂等）比指望网络可靠更现实。

\gset、\gexec 与事务的组合 ​

上一页的 \gset 与本页的事务没有冲突，但有一个值得知道的细节：\gset 执行的查询同样遵守当前事务状态——在显式事务里 \gset 取到的是事务内未提交的视角，这恰好是你想要的「基于事实的分支」。

\gexec 则是把「查询结果逐值当作下一条命令执行」的利器，典型用法是批量 DDL：

sql
BEGIN;
SELECT 'COMMENT ON TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname)
       || ' IS ''owned by data team'''
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
\gexec
COMMIT;

\gexec 生成的每条命令默认各自独立提交（如果 AUTOCOMMIT on）；用显式 BEGIN 包住后，整个批量 DDL 变成一个原子操作——要么全部注释生效，要么全部不作数。批量加列、批量建索引、批量授权都可以套用这个模式。

事务里的 DDL：与 MySQL 完全不同的心智模型 ​

如果你从 MySQL 转来，第一个要刷新的认知是：PostgreSQL 的 DDL 是事务性的。CREATE TABLE、ALTER TABLE ... ADD COLUMN、CREATE INDEX（非 CONCURRENTLY）都可以放进事务，要么全部生效要么全部消失：

sql
BEGIN;
ALTER TABLE orders ADD COLUMN channel text;
UPDATE orders SET channel = 'unknown';
CREATE INDEX orders_channel_idx ON orders (channel);
COMMIT;   -- 三步一起生效，回滚则一起消失

这直接改变了迁移脚本的写法：PostgreSQL 的迁移可以放心用 --single-transaction 把 DDL 与数据订正原子化，失败即完整回滚，不存在「列加了但数据没刷」的中间态；MySQL 的 DDL 隐式提交则迫使迁移按语句分步。

两条边界要记住：

  1. CREATE INDEX CONCURRENTLY 不能在事务块里跑——它自身跨多个事务提交，语法上就禁止出现在显式事务与 --single-transaction 包装中，这类操作必须单独拆出脚本；
  2. DDL 也拿重锁：多数 ALTER TABLE 要 ACCESS EXCLUSIVE 锁，把 DDL 放在事务靠后的位置、缩短持锁时间，并警惕「DDL 排在长查询后面」把后面所有语句堵死——锁队列一旦被堵，受害面远不止本事务。

--single-transaction：把脚本包进一个事务 ​

-1 / --single-transaction 作用于本次 psql 进程的全部输入（多条 -c 或一次 -f，可混用）：

text
psql -1 -f part1.sql -f part2.sql -c "ANALYZE;"
等效于：BEGIN; part1.sql 的内容; part2.sql 的内容; ANALYZE; COMMIT;

它的语义要点：

  • BEGIN 在执行第一条输入前发出，COMMIT 在最后一条输入后发出；
  • 脚本里若自己写了显式 BEGIN/COMMIT，会与外层事务嵌套产生警告（PostgreSQL 不支持真正嵌套事务，内层 BEGIN 只是个空操作警告，内层 COMMIT 会提前结束外层事务）——使用 --single-transaction 时脚本内部不要再写事务控制语句；
  • 必须配合 ON_ERROR_STOP，这是官方文档明示的组合，理由见下一小节；
  • --single-transaction 无法与 CREATE INDEX CONCURRENTLY、CREATE DATABASE 这类「不能在事务块内执行」的语句共存，脚本包含它们时会直接报错。

ON_ERROR_STOP 与事务：安全脚本的双保险 ​

先看反例——不加 ON_ERROR_STOP 的 --single-transaction 脚本：

text
psql -1 -f bad_migrate.sql
├── CREATE TABLE t (...)      ✔ 执行（未提交）
├── INSERT INTO t ...         ✘ 语法错误 → 事务进入“已中止”状态
├── ALTER TABLE t ADD ...     ✘ current transaction is aborted, commands ignored…
└── COMMIT                    服务器实际执行 ROLLBACK —— 什么都没进库

最危险的地方在于：最后的服务器端 COMMIT 实际以 ROLLBACK 告终，psql 仍可能以退出码 0 结束。上层脚本若只看退出码，会得出「迁移成功」的错误结论。

加上 ON_ERROR_STOP=1 后行为完全不同：

bash
psql -v ON_ERROR_STOP=1 --single-transaction -f migrate.sql
├── CREATE TABLE t (...)      ✔ 执行（未提交）
├── INSERT INTO t ...         ✘ 出错 → 立即停止执行，退出码 3
└── 连接关闭 → 未提交事务被服务器回滚

于是形成一条铁律：凡是用 --single-transaction，必配 --set=ON_ERROR_STOP=1（或 -v ON_ERROR_STOP=1）。CI/CD 的迁移流水线再以退出码 3 为失败信号，实现「要么全部生效，要么库纹丝不动」的原子发布。

失败语句与事务状态机：25P02 的细节 ​

显式事务里一旦某条语句失败，事务不会立即回滚，而是进入「已中止（aborted）」状态——这是理解 psql 事务行为的关键状态机：

sql
BEGIN;
INSERT INTO t (id) VALUES (1);          -- 假设这里主键冲突，失败
SELECT * FROM t;                        -- 之后的所有语句都会被拒绝
text
ERROR:  duplicate key value violates unique constraint "t_pkey"
ERROR:  current transaction is aborted, commands ignored until end of transaction block
sql
ROLLBACK;   -- 唯一的出路：先回滚，事务才能恢复可用

这个状态机（SQLSTATE 25P02）决定了三件事：

  1. 失败后继续跑是无效功：已中止事务里的每条语句都白跑，psql 不加 ON_ERROR_STOP 时脚本的剩余部分全部浪费在「重复报错」上；
  2. 回滚是唯一的复位键：SAVEPOINT 是唯一的例外——ROLLBACK TO SAVEPOINT 能把事务从已中止状态拉回到保存点；
  3. 退出码会被污染：不加 ON_ERROR_STOP 时，脚本最后的 COMMIT 在已中止事务上实际执行 ROLLBACK，退出码可能仍是 0（前文详述的反例）。

ON_ERROR_ROLLBACK on 的实现原理正是围绕这个状态机：psql 在每条语句前自动打 SAVEPOINT、失败时自动 ROLLBACK TO，把「已中止」化解为「局部撤销」。

死锁与重试：事务脚本的并发课题 ​

事务脚本不是只跟自己打交道——只要并发存在，就有两个必须认识的 SQLSTATE：40P01（deadlock_detected，死锁）与 40001（serialization_failure / 可序列化冲突）。死锁的剧本：事务 A 锁了行 1 等行 2，事务 B 锁了行 2 等行 1，服务器在 deadlock_timeout（默认 1s）后主动检测并牺牲其中一个，被牺牲者收到 40P01，整个事务已回滚。

psql 脚本层面能做的不是「避免」死锁（那是设计问题），而是「接住」它：

sql
\set ON_ERROR_STOP on

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

脚本被死锁打断时，psql 以非零退出码结束，重试交给外层调度（带退避的再次执行整个脚本）。与驱动层「进程内捕获重试」不同，psql 的天然重试单元是整个脚本——这也再次说明：脚本要保持幂等（重跑安全），重试才有意义。

降低死锁概率的两条设计纪律，在写事务脚本时同样适用：

  1. 多事务修改多行时，按一致的顺序访问（例如都按 id 升序 UPDATE），打破「互相等待」的环形条件；
  2. 事务起点先锁热点行（SELECT ... FOR UPDATE），把「先干活后补锁」改成「先立桩后干活」，用短暂排队替代死锁回滚。

另外注意 ON_ERROR_ROLLBACK on 与重试的关系：它只做「语句级局部回滚」，不会替你重试死锁——事务在 40P01 之后已整体回滚，psql 会继续执行后续语句（随后每条都报错），因此脚本级死锁重试永远应该交给外层调度 + 幂等脚本。

长事务的代价与监控信号 ​

「事务边界按业务原子性划分」的另一面是：事务越小越好，除非原子性要求更大。长事务的四笔账：

代价机制后果
膨胀事务旧版本元组无法被 vacuum 回收表与索引虚胖，查询变慢
WAL 堆积事务提交前 WAL 不能被回收复制磁盘压力、备库延迟
锁持有事务越长，锁持有越久其他会话排队，超时雪崩
快照老化长事务持久的快照阻止 xmin 推进全库 vacuum 效率下降

监控长事务的标准查询：

sql
SELECT pid,
       now() - xact_start AS xact_age,
       state,
       wait_event_type,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_age DESC;

超过 5 分钟的活跃事务就该看一眼在干什么；超过 30 分钟通常意味着事故在酝酿。配合 pg_stat_activity 的 application_name（psql 里通过连接串设置）可以立即定位到人或服务。设置 idle_in_transaction_session_timeout（服务端参数）可以强制掐掉「开着事务发呆」的会话，是长事务问题的兜底防线。

批量脚本的安全模式：大事务还是分段提交？ ​

--single-transaction 不是万能的。一个把 5000 万行 DELETE 包进单个事务的脚本，本质是在服务器上构造一个巨型事务：WAL 大量堆积、表与索引膨胀来不及被 autovacuum 清理、复制延迟拉大、锁持有时间拉长、失败重试成本巨大（一行都不能少地重来）。批量脚本应该按「业务原子性」划界，而不是按「技术上的方便」：

场景推荐模式理由
结构迁移（几十条 DDL）--single-transaction + ON_ERROR_STOP数量小，原子性价值高
一次性小数据订正单事务包住改错了能整体回滚
千万行级 DELETE/UPDATE分批 + 每批一个事务控制事务大小与锁持有时长
数据迁移中的全量搬运\copy/COPY 分段提交后续 \copy 一页详述
含 CONCURRENTLY 操作的脚本绝不单事务包裹语法上就不允许

分段提交的骨架：

sql
\set ON_ERROR_STOP on
TRUNCATE batch_state;                      -- 记录批次状态的小表
INSERT INTO batch_state SELECT 1, false;

DO $$
DECLARE
  moved bigint;
BEGIN
  LOOP
    WITH victim AS (
      SELECT ctid FROM big_table WHERE archived = false LIMIT 100000
    ),
    moved_rows AS (
      DELETE FROM big_table USING victim WHERE big_table.ctid = victim.ctid
      RETURNING big_table.*
    )
    INSERT INTO archive_table SELECT * FROM moved_rows;

    GET DIAGNOSTICS moved = ROW_COUNT;
    RAISE NOTICE 'moved % rows', moved;
    EXIT WHEN moved = 0;
    COMMIT;   -- 每十万行提交一次（在 DO 内不可行时退化为外部脚本循环）
  END LOOP;
END $$;

上面这种「过程内循环提交」在 PostgreSQL 的 DO 块里并不可行（DO 本身运行在单个事务内），实际工程中请用外部脚本循环实现分批：bash 循环调用 psql、每轮跑一个 LIMIT 化的 DELETE，直到影响行数为 0。要点不在写法而在节奏——每个事务只装得下它该装的东西。

交互模式的 ON_ERROR_ROLLBACK ​

与 ON_ERROR_STOP 名字相近的 ON_ERROR_ROLLBACK 是另一回事：\set ON_ERROR_ROLLBACK on（或 interactive）让 psql 在交互模式下为每条语句自动打 SAVEPOINT，出错时自动回滚到上一语句之后而不是中止整个事务。它解决的是交互体验问题（一条手滑的 DELETE 不至于废掉整个事务），与脚本化的 ON_ERROR_STOP 互补：脚本要停，交互要活。

取值有三种：未设（关闭）、on（总是启用，脚本模式也会打 SAVEPOINT）、interactive（仅交互会话启用，脚本不受影响，默认推荐）。体验一下差别：

sql
\set AUTOCOMMIT off
\set ON_ERROR_ROLLBACK interactive

BEGIN;
DELETE FROM orders WHERE id = 999;   -- 不存在的行，DELETE 成功但影响 0 行（无感）
SELECT 1/0;                          -- 除零报错
SELECT 'still alive';                -- 事务仍然可用：自动 ROLLBACK TO SAVEPOINT 救了它
ROLLBACK;

代价要清楚：自动 SAVEPOINT 意味着每条语句前多一次轻量操作，且已中止前的部分效果需要靠你心里记账——自动回滚只回滚到「上一条语句之后」，之前已成功的语句仍在事务里等你最终 COMMIT 或 ROLLBACK。它不是「出错自动提交」也不是「部分提交」，别把语义记反。

输出控制配合：让事务脚本可审计 ​

事务脚本往往成百上千行，执行回显必须可控。三个常用组合：

sql
\t on        -- tuples_only：压掉表头与行数统计，日志更干净
\a           -- 切到 unaligned，长结果不换行
\echo === step 3: 初始化字典表 ===   -- 人工里程碑，日志里可搜索

配合 psql -v ECHO=queries（或 -a）能把输入的每条 SQL 也打出来，事后审计「到底执行过什么」时非常关键。生产迁移脚本的日志规范通常就是：ON_ERROR_STOP + ECHO=all + \o 落盘。

迁移工具视角：框架事务语义与 psql 的衔接 ​

团队里迁移脚本往往不直接由 psql 驱动，而是被 Flyway、Liquibase、自研迁移框架等工具托管。此时理解「工具的事务语义」与「psql 的事务选项」如何衔接至关重要：

  • 每个迁移文件一个事务是多数迁移工具的默认语义——对应到 psql 层就是「工具逐文件调用 psql，每次带 --single-transaction --set=ON_ERROR_STOP=1」；
  • 迁移历史表与业务变更必须同事务：工具记录「版本 0042 已应用」的 INSERT 与业务 DDL 落在同一个事务里，失败时两者一起回滚，历史表才不会「记录了成功但结构没变」；
  • psql 脚本内部不要再有 BEGIN/COMMIT：被工具包进事务后再写事务控制会破坏原子性假设（前文详述）；
  • CONCURRENTLY 类操作必须标记为「非事务迁移」：主流工具都有对应的「非事务执行」开关，这类迁移单独拆文件、单独调度。

用 psql 自制一个最小迁移执行器时，上述语义同样适用：

bash
#!/bin/bash
# migrate.sh —— 按序执行 pending/ 目录下的迁移，逐文件事务
set -euo pipefail
DB="$1"

for file in $(ls pending/*.sql | sort); do
  echo "== applying $file =="
  psql -X -d "$DB" -v ON_ERROR_STOP=1 --single-transaction -f "$file"

  # 成功后移入 applied/（与业务变更不在同一事务，属可接受折衷：
  # 更严谨的做法是把「记录历史」写进迁移文件最后一行）
  mv "$file" applied/
done

这个骨架没有「历史表原子性」，因此注释里指明了折衷方向——在迁移文件末尾追加一条 INSERT INTO schema_migrations ...，让它与业务变更同生共死。理解了 psql 的事务选项，无论工具多么花哨，事务语义都能还原成这几条基本组合。

实战示例 ​

示例一：交互体验 AUTOCOMMIT off ​

sql
\set AUTOCOMMIT off
\echo 自动提交已关闭

UPDATE products SET price = price * 0.9 WHERE category = 'clearance';
-- 提示：UPDATE 3（但未提交）

-- 另开一个会话验证：
-- SELECT * FROM products WHERE category='clearance';  -- 仍是旧价

COMMIT;                 -- 或 ROLLBACK;
\set AUTOCOMMIT on

体会三个动作：本会话看到新值、其他会话看不到、COMMIT 后全局可见。

示例二：标准迁移脚本包 ​

工程化的迁移脚本配套（bash 驱动 + SQL 正文）：

bash
#!/bin/bash
set -euo pipefail
DB="${1:?usage: $0 <dbname>}"
psql -X -d "$DB" -v ON_ERROR_STOP=1 --single-transaction -f 0042_add_tenant.sql
sql
-- 0042_add_tenant.sql
ALTER TABLE orders ADD COLUMN tenant_id bigint NOT NULL DEFAULT 0;
ALTER TABLE order_items ADD COLUMN tenant_id bigint NOT NULL DEFAULT 0;
CREATE INDEX idx_orders_tenant ON orders (tenant_id, created_at);
INSERT INTO schema_migrations(version, applied_at) VALUES ('0042', now());

任何一条失败 → 退出码 3 → set -e 让 bash 立即失败 → 服务器回滚全部。CI 里对迁移结果的判断只有「0 或非 0」一种语义。

示例三：\gexec + 事务批量授权 ​

sql
BEGIN;
SELECT format('GRANT SELECT ON %I.%I TO readonly_user;', schemaname, tablename)
FROM pg_tables
WHERE schemaname = 'public'
\gexec
COMMIT;
text
GRANT SELECT ON public.orders TO readonly_user
GRANT SELECT ON public.users TO readonly_user
…

数十张表一步到位且原子。若中途某条 GRANT 失败（例如目标角色不存在），ON_ERROR_STOP 会让 psql 立刻退出、事务整体回滚。

示例四：分段提交的大批量清理 ​

bash
#!/bin/bash
# 每轮删除 10 万行，直到删干净；每轮都是独立事务
TOTAL=0
while :; do
  MOVED=$(psql -X -d appdb -v ON_ERROR_STOP=1 -At -c \
    "WITH victim AS (
       SELECT ctid FROM audit_events WHERE event_time < '2024-01-01' LIMIT 100000
     ),
     del AS (DELETE FROM audit_events e USING victim WHERE e.ctid = victim.ctid RETURNING 1)
     SELECT count(*) FROM del;")
  TOTAL=$((TOTAL + MOVED))
  echo "batch moved: ${MOVED}, total: ${TOTAL}"
  [ "$MOVED" -eq 0 ] && break
  sleep 0.5
done
echo "done: ${TOTAL}"

每轮事务极小、锁持有短、复制延迟可感知、失败只需从断点继续——与「一个大事务包 2000 万行」的写法对比，代价差异是数量级的。-At 即 \pset tuples_only on + \pset format unaligned 的命令行等价形式，让 MOVED 只拿到纯数字。

示例五：CI 中的迁移流水线 ​

把本页所有要素串成一条 CI 流水线脚本（GitLab CI/GitHub Actions 的 step 通用）：

bash
#!/bin/bash
# ci_migrate.sh —— 迁移失败即阻断发布
set -euo pipefail

DB_URL="${DATABASE_URL:?DATABASE_URL is required}"
MIGRATION="migrations/${MIGRATION_FILE:?MIGRATION_FILE is required}"

echo "=== preflight：检查迁移文件与目标库连通性 ==="
[ -f "$MIGRATION" ] || { echo "migration not found: $MIGRATION"; exit 1; }

psql -X "$DB_URL" -v ON_ERROR_STOP=1 -At -c "SELECT 1" > /dev/null

echo "=== lint：检查脚本内是否误写了事务控制 ==="
if grep -niE '^\s*(BEGIN|COMMIT|ROLLBACK);' "$MIGRATION"; then
  echo "ERROR: migration script must not contain explicit transaction control"
  exit 1
fi

echo "=== applying（单事务原子执行）==="
set +e
psql -X "$DB_URL" \
     -v ON_ERROR_STOP=1 \
     --single-transaction \
     -v ECHO=queries \
     -f "$MIGRATION"
RC=$?
set -e

case $RC in
  0) echo "migration applied successfully" ;;
  3) echo "migration failed (SQL error, transaction rolled back)"; exit 1 ;;
  2) echo "connection lost during migration"; exit 1 ;;
  *) echo "psql failed with exit code $RC"; exit 1 ;;
esac

echo "=== postflight：连通性与版本确认 ==="
psql -X "$DB_URL" -At -c "SELECT version FROM schema_migrations ORDER BY applied_at DESC LIMIT 1;"

流水线设计的四个要点与本页知识一一对应：

  1. lint 阶段禁止脚本自带事务控制，保证 --single-transaction 的原子性假设成立；
  2. 退出码分支：只有 0 视为成功，3（SQL 错误已回滚）与 2（连接中断）分别给出可读的失败原因；
  3. ECHO=queries 留痕：CI 日志里能看到每条 SQL 与其结果，事后审计不缺料；
  4. postflight 确认版本落表：迁移历史与结构变更同事务时，这一步永远成功——它失败即说明历史表没进事务，流水线设计有缺陷。

注意事项 ​

--single-transaction 必须配 ON_ERROR_STOP。 不加时出错后脚本带着「已中止」状态跑完全程，最终以 ROLLBACK 收场，且退出码可能仍是 0——上层编排完全无法感知失败。这是本页最重要的一条注意事项。

--single-transaction 与脚本内事务控制互斥。 脚本里已有的 BEGIN/COMMIT 会与外层事务产生嵌套警告并破坏原子性假设；含 CREATE INDEX CONCURRENTLY、CREATE DATABASE 等语句的脚本会直接报错。要么改脚本，要么放弃单事务。

AUTOCOMMIT off 不是「安全模式」。 它只是延迟提交点。长会话里未提交的修改依然持有行锁、阻止 vacuum 推进、阻塞其他会话；忘了 COMMIT 反而制造排障事故。

切换 AUTOCOMMIT 不影响未决事务。 off → on 不会自动提交当前事务，on → off 也不会自动开启事务。收尾永远靠显式 COMMIT/ROLLBACK。

失败的事务必须先 ROLLBACK。 事务一旦出错进入已中止状态，后续所有语句都会被拒绝；交互模式可用 ON_ERROR_ROLLBACK 自动兜底，脚本模式则交给 ON_ERROR_STOP + 整体回滚。

大事务有物理代价。 单事务体积受 WAL、复制、vacuum 与锁持有时间共同约束；「业务原子性」才是事务边界的划分依据，别为了省事把千万行删除包进一个事务。

退出码语义要接入编排。 0/1/2/3 各有含义；CI 里只应把 0 视为成功。用 psql -v ON_ERROR_STOP=1 统一让任何 SQL 错误映射到非零退出码。

-c 的每段是独立事务。 psql -c A -c B 不等于 psql -c "A; B"，两者的事务边界不同（前者两段各自提交，后者一段一个事务）；需要原子时加 -1。

SAVEPOINT 是事务内的局部撤销，不是救命稻草。 它无法替代提交前的评审；RELEASE 与 ROLLBACK TO 的栈语义也要清楚，嵌套过深的保存点让脚本更难读。

工具托管的事务语义要写进交接文档。 迁移框架、调度系统、人工 psql 三种入口的事务边界各不相同；「这个脚本由谁在什么事务语义下执行」不写清楚，出事后无法还原现场。

死锁重试交给外层调度，别指望 psql 内部重试。 psql 没有语句级异常捕获，40P01 之后事务已整体回滚；让脚本保持幂等、由调度系统带退避重跑整个脚本，才是可维护的重试形态。

最佳实践 ​

  1. 脚本一律 psql -X -v ON_ERROR_STOP=1 起步。 隔离个人配置、错误即停，这两个参数应该成为所有自动化脚本的第一行肌肉记忆。
  2. 结构迁移用 --single-transaction --set=ON_ERROR_STOP=1。 DDL 数量少、原子价值高，是单事务模式最标准的适用场景。
  3. 交互保持 AUTOCOMMIT on，脚本才考虑 off。 交互里用显式 BEGIN 画边界比常开 off 模式安全得多。
  4. 事务边界按业务原子性划分，不按脚本文件划分。 「这组语句要么全有意义要么全无意义」才是 BEGIN/COMMIT 的判定问题。
  5. 千万行级 DML 分批提交。 每批 1~10 万行是常见量级，配合 LIMIT 游标化删除与轮间 sleep，把锁、WAL、复制的峰值都压平。
  6. 用 RETURNING + \gset 感知事务内的事实。 分支判断基于事务内视角的数据，而不是上一轮的陈旧快照。
  7. \gexec 批量 DDL 记得用事务包住。 生成式批量操作默认逐条提交，显式 BEGIN/COMMIT 让它原子化。
  8. 迁移日志规范：ECHO=all + \t/\a 收敛输出 + \o 落盘。 事后可审计「输入了什么、输出了什么、哪一步停的」。
  9. CI 只认退出码 0。 迁移流水线对 psql 的返回码做唯一真值判断，任何非 0 都阻断发布。
  10. 脚本内绝不混用显式 BEGIN 与 --single-transaction。 二选一：要么脚本自管事务，要么外层包装。
  11. 长事务期间盯 autovacuum 与复制延迟。 无法避免的大事务要安排在低峰，并监控 WAL 堆积与 standby 延迟。
  12. 把「失败重跑」设计进去。 分段提交的脚本天然支持断点续跑；单事务脚本失败即全回滚，重跑前先确认幂等性（IF NOT EXISTS、ON CONFLICT 等）。
  13. 为长事务设置 idle_in_transaction_session_timeout 兜底。 人为疏忽总会发生，让服务器自动掐掉「开着事务发呆」的会话，比事后救火便宜得多。
  14. 多对象更新按固定顺序，热点行先 FOR UPDATE。 死锁的重试成本永远比「多花两分钟设计访问顺序」高；脚本与在线服务共享表时尤其如此。
  15. 交互会话开 ON_ERROR_ROLLBACK=interactive，脚本会话保持 ON_ERROR_STOP。 两个开关的名字像、职责完全不同，各守各的场景。

下一节

继续学习：psql \copy 数据导入导出

参考链接 ​