PostgreSQL中PL/pgSQL异常处理导致提交失效问题咨询

这是个很典型的PL/pgSQL事务机制坑，我来帮你拆解清楚三个案例背后的原因：

核心本质：EXCEPTION块绑定了隐式保存点 #

当你在存储过程里定义EXCEPTION块时，PostgreSQL会自动给整个BEGIN...EXCEPTION...END代码块创建一个保存点（savepoint）——这个保存点是用来在异常发生时回滚块内所有操作的，但它会直接干扰你在块内的显式COMMIT操作。

逐个分析你的案例 #

Case 1：有异常+EXCEPTION块

1. 进入BEGIN块，PostgreSQL自动创建保存点S1。
2. 执行INSERT，数据写入事务日志但未真正持久化。
3. 执行COMMIT：表面上提交了事务，但此时保存点S1还存在，PostgreSQL会把这次提交标记为"保存点内操作"。
4. 触发除零异常：PL/pgSQL会回滚到保存点S1，这直接撤销了之前的INSERT和COMMIT操作，所以最终数据没插入。

Case 2：无异常但有EXCEPTION块

1. 同样进入BEGIN块，创建保存点S1。
2. 执行INSERT和COMMIT，但COMMIT依然被标记为保存点内操作。
3. 块正常结束，PL/pgSQL尝试释放保存点S1——但之前的COMMIT已经销毁了保存点，这个矛盾会触发PL/pgSQL的隐性回滚，直接把之前的INSERT操作撤销了，所以数据还是没插入。

Case 3：无EXCEPTION块

没有异常块的情况下，PL/pgSQL不会创建任何隐式保存点。你的INSERT和COMMIT是直接在顶级事务中执行的，COMMIT成功提交了事务，数据自然就持久化到表中了。

解决方法 #

如果你既需要异常处理，又要确保COMMIT生效，有两种常见方式：

1. 把COMMIT移出EXCEPTION块范围：

   CREATE OR REPLACE PROCEDURE ins () AS $$
   DECLARE i INT;
   BEGIN
     -- 把需要异常保护的逻辑单独嵌套一个小BEGIN块
     BEGIN
       insert into scott.emp (empno) values (9000);
       i:=1/0; -- 这里的异常会被内层EXCEPTION捕获
     EXCEPTION WHEN OTHERS THEN
       -- 处理异常逻辑
       RAISE NOTICE 'Error occurred: %', SQLERRM;
       RETURN; -- 异常时直接退出，不执行后续COMMIT
     END;
     -- COMMIT放在外层，不受内层保存点影响
     commit;
   END $$ LANGUAGE plpgsql;
   

2. 移除不需要的EXCEPTION块：如果你的异常处理逻辑是空的（比如案例里只是注释掉的ROLLBACK），直接删掉EXCEPTION块即可，避免保存点干扰事务。

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