PostgreSQL存储过程执行COMMIT报invalid transaction termination错误如何解决

PostgreSQL的存储过程中，COMMIT/ROLLBACK仅能在无外层显式事务的上下文下执行。你将psql的AUTOCOMMIT设置为off后，执行任何SQL语句前psql都会自动开启一个显式事务块，相当于call语句外层已经包裹了一层BEGIN，此时存储过程内部执行事务终止语句就会触发invalid transaction termination报错，这是PostgreSQL和Oracle的事务模型差异导致的，Oracle允许存储过程内部嵌套提交/回滚，PostgreSQL不支持在外层事务未结束的情况下在存储过程内部操作事务。

• 方案1：将事务控制逻辑迁移到存储过程外部，存储过程内仅保留业务逻辑
  修改后的存储过程代码：

CREATE OR REPLACE PROCEDURE user.test_proc(IN param1 character varying)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
   i text;
BEGIN
   if param1 = '1' then
     insert into amian.test values ('1', '1');
   else
      insert into amian.test values('2','2');
   end if;
end;
$BODY$;

调用时手动控制事务即可：

-- 参数为2的场景
call user.test_proc('2');
commit;

-- 参数为1的场景
call user.test_proc('1');
rollback;

• 方案2：如果必须保留存储过程内部的事务控制逻辑，调用前先提交当前已开启的外层事务，保证call语句执行时无外层事务上下文：
  调用示例：

commit; -- 先结束psql自动开启的外层事务
call user.test_proc('2'); -- 此时存储过程内部的commit可以正常执行

注意：存储过程内部事务控制是PostgreSQL 11及以上版本才支持的特性，如果你使用的是更低版本，需要先升级数据库版本才能使用该能力。

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