PostgreSQL错误时事务回滚方法及脚本语法问题求助

兄弟，我太懂你这种被PostgreSQL事务语法绕晕的感觉了！核心问题就是你把普通SQL的事务控制语句和PL/pgSQL的过程化控制块搞混了，咱们一步步拆解你的问题，再给你靠谱的解决方案。

先说说你的尝试为啥都失败了 #

1. 直接写BEGIN...EXCEPTION...END报错 #

PostgreSQL的普通SQL里根本没有这种语法！BEGIN...EXCEPTION...END是PL/pgSQL（PostgreSQL的过程化脚本语言）里的错误处理结构，不能直接在普通SQL脚本里用。你直接写的话，数据库把开头的BEGIN当成事务启动命令，后面的EXCEPTION就成了莫名其妙的语法，不报错才怪。

2. 事务出错后为啥不自动回滚？ #

你的观察是对的：PostgreSQL事务出错后会进入「中止状态」，但不会自动帮你回滚——它会等着你明确发ROLLBACK或者COMMIT命令。这里要注意：如果事务已经中止，执行COMMIT的效果其实和ROLLBACK一样，都会结束事务并丢弃所有修改。

你举的那个测试例子，可能是执行方式的问题：如果你在pgAdmin里逐行跑脚本，跑到INSERT报错后，没执行最后的COMMIT就去查表，这时候事务还挂在中止状态，自然会提示你先结束事务。但如果你把整个脚本一次性执行完（包括最后的COMMIT），那Dummy表肯定是不会被创建出来的，因为中止状态的事务被COMMIT回滚了。

3. DO块里用ROLLBACK为啥报错？ #

DO块是PL/pgSQL的匿名函数，而PL/pgSQL是运行在事务内部的——它根本不允许你显式执行BEGIN/COMMIT/ROLLBACK这类事务控制命令。提示里说的「用带EXCEPTION的BEGIN块代替」，是指用PL/pgSQL的错误处理块来捕获块内错误，自动回滚块里的修改，而不是让你用事务命令。

正确实现原子脚本的两种方法 #

方法1：用psql参数一键搞定（最简单） #

如果你是用psql命令行或者pgAdmin的查询工具执行脚本，直接在开头加个psql内置参数，就能让脚本在出错时自动回滚：

-- 开启错误自动回滚
\set ON_ERROR_ROLLBACK on

BEGIN;
-- 1. 你的正常操作
CREATE TABLE "Dummy" (
"Id" INT GENERATED ALWAYS AS IDENTITY,
"ParentId" INT NULL,
CONSTRAINT "PK_Dummy" PRIMARY KEY ("Id"),
CONSTRAINT "FK_Dummy_Dummy" FOREIGN KEY ("ParentId") REFERENCES "Dummy" ("Id")
);
-- 2. 会出错的操作
INSERT INTO "Dummy" ("ParentId") VALUES (99);
COMMIT;

ON_ERROR_ROLLBACK=on会让psql在遇到错误时立刻回滚当前事务，不用你手动处理。如果脚本全程没出错，就正常执行COMMIT提交。

方法2：用PL/pgSQL的DO块封装（适合复杂逻辑） #

如果你的脚本有更复杂的逻辑，比如需要捕获特定错误做处理，可以用DO块的错误处理来实现原子性：

BEGIN;
DO $$
BEGIN
  -- 执行你的操作
  CREATE TABLE "Dummy" (
  "Id" INT GENERATED ALWAYS AS IDENTITY,
  "ParentId" INT NULL,
  CONSTRAINT "PK_Dummy" PRIMARY KEY ("Id"),
  CONSTRAINT "FK_Dummy_Dummy" FOREIGN KEY ("ParentId") REFERENCES "Dummy" ("Id")
  );
  INSERT INTO "Dummy" ("ParentId") VALUES (99);
  
EXCEPTION
  WHEN OTHERS THEN
    -- PL/pgSQL会自动回滚DO块内的所有修改
    -- 这里可以加自定义错误提示，或者直接重新抛出错误让外层事务中止
    RAISE NOTICE '执行出错，已回滚块内操作';
    RAISE; -- 重新抛错，让外层事务进入中止状态
END $$;
COMMIT;

当DO块里出错时，EXCEPTION块会自动回滚块内的所有更改，然后我们重新抛出错误，让外层事务进入中止状态，最后执行COMMIT就会自动回滚整个事务。

方法3：Shell脚本封装（适合批量部署） #

如果是在服务器上批量执行脚本，可以用Shell脚本捕获错误并手动回滚：

#!/bin/bash
# 连接数据库执行脚本，遇到错误立刻停止
psql -d 你的数据库名 -v ON_ERROR_STOP=1 << EOF
BEGIN;
CREATE TABLE "Dummy" (
"Id" INT GENERATED ALWAYS AS IDENTITY,
"ParentId" INT NULL,
CONSTRAINT "PK_Dummy" PRIMARY KEY ("Id"),
CONSTRAINT "FK_Dummy_Dummy" FOREIGN KEY ("ParentId") REFERENCES "Dummy" ("Id")
);
INSERT INTO "Dummy" ("ParentId") VALUES (99);
COMMIT;
EOF

# 如果psql执行失败，手动回滚事务
if [ $? -ne 0 ]; then
  psql -d 你的数据库名 -c "ROLLBACK;"
  echo "脚本执行失败，已回滚事务"
fi

最后再划几个重点 #

1. PostgreSQL事务是「显式控制」的：出错后不会自动回滚，必须用ROLLBACK或COMMIT结束（中止状态的COMMIT等于ROLLBACK）。
2. PL/pgSQL的BEGIN...EXCEPTION是「块级错误处理」，只能回滚当前块的修改，管不了整个事务。
3. psql的\set ON_ERROR_ROLLBACK on是最省心的方式，适合大多数脚本场景。

内容的提问来源于stack exchange，提问作者Marchyello