PostgreSQL plpgsql存储过程事务控制与IF条件失效问题咨询

全表更新问题成因 #

你第一个版本的MaJ存储过程出现全表更新，不是PostgreSQL忽略了IF r.ID % 2 = 0判断，而是你触发判断后的UPDATE语句没有加WHERE过滤条件：

-- 错误写法：无WHERE条件，执行一次就会更新usr表所有行
UPDATE usr SET lname = 'KONAN';

只要任意一行满足ID为偶数的条件触发了这条UPDATE，就会直接修改表中全部数据。你后续补充的CRDM过程里已经修正了这个问题，新增了WHERE USR.ID = R.ID限制，只会更新当前循环匹配的行，不会再出现全表更新的问题。

PL/pgSQL事务机制核心要点 #

在PostgreSQL中使用事务控制，需要先明确几个基础规则：

1. 只有**存储过程（PROCEDURE）**支持内部执行COMMIT/ROLLBACK，普通函数（FUNCTION）不允许修改事务状态，必须运行在调用者的事务上下文中。
2. 存储过程内部可以执行事务操作的前提是：调用链的顶层没有开启显式事务块。如果外层先执行了BEGIN;启动显式事务，再调用存储过程，内部的COMMIT/ROLLBACK会直接报错。
3. 嵌套调用存储过程时，子过程内部执行的COMMIT/ROLLBACK会直接作用于整个顶层事务，所有上层未提交的修改都会被一并提交或回滚。

入参控制事务的最优实现方案 #

你当前基于入参判断提交/回滚的逻辑本身是可行的，只需要补充以下优化点即可：

1. 事务控制尽量上移到调用链顶层：避免多层嵌套的存储过程都包含事务操作，大幅降低事务状态异常的排查成本。如果必须在子过程做事务控制，一定要在代码注释中明确标注该过程会修改事务状态，避免上层调用者不知情引发问题。
2. 补充入参校验：对控制事务的入参做合法性校验，避免非法入参导致事务未按预期处理，例如：

IF Crtrn NOT IN (0,1) THEN
  RAISE EXCEPTION '非法入参Crtrn：%，仅支持0（提交）、1（回滚）', Crtrn;
END IF;

3. 补充异常捕获逻辑：如果更新逻辑运行时报错，事务会进入 aborted 状态，后续的事务操作会直接失败，建议新增EXCEPTION块统一处理异常：

CREATE OR REPLACE PROCEDURE CRDM(Crtrn INOUT INT)
AS
$CRDM$
DECLARE
R RECORD;
BEGIN
  FOR R IN SELECT * FROM usr
  LOOP
    IF R.ID % 2 = 0 THEN
      UPDATE USR SET lname = 'MAGNI' WHERE USR.ID = R.ID;
    END IF;
  END LOOP;
  -- 入参校验
  IF Crtrn NOT IN (0,1) THEN
    RAISE EXCEPTION '非法入参Crtrn：%', Crtrn;
  END IF;
  IF Crtrn = 0 THEN
    COMMIT;
  ELSE
    ROLLBACK;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 异常统一回滚，返回错误码
    ROLLBACK;
    Crtrn := -1;
    RAISE NOTICE '执行报错：%', SQLERRM;
END;
$CRDM$ LANGUAGE plpgsql;

现有代码遗漏点 #

• 第一个版本的MaJ过程重复声明了r变量，属于冗余代码，会覆盖前一次的声明。
• 你当前的示例代码中rtrn默认值为0，每次调用都会默认提交，如果要测试回滚逻辑，需要将AMI过程中的rtrn初始值改为1。
• 没有显式设置事务的隔离级别，如果对数据一致性有要求，建议在事务启动前明确指定隔离级别。

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