PostgreSQL 11中如何从函数调用包含commit事务控制的存储过程

错误原因说明 #

PostgreSQL 中自定义函数运行在调用方的事务上下文内，核心限制是不允许内部执行 COMMIT、ROLLBACK 这类事务终止语句，因此直接从函数中调用带事务控制的存储过程必然触发invalid transaction termination报错。

可行变通方案 #

方案1：将调用方改为存储过程（最推荐） #

如果业务允许不使用函数返回值的调用方式，直接将原调用函数改为存储过程即可，存储过程本身支持自主事务控制，可正常调用带COMMIT的存储过程。
示例代码：

-- 替换原函数f的存储过程
CREATE OR REPLACE PROCEDURE public.p_call()
 LANGUAGE plpgsql
AS $$
BEGIN
  raise notice 'Starting Procedure!!!';
  call public.p();
  raise notice 'Call success';
END;
$$;

-- 调用方式
call public.p_call();

方案2：使用dblink扩展实现独立会话调用（必须保留函数场景可用） #

如果业务逻辑必须保留函数的调用形式，可以通过dblink扩展建立独立的本地数据库连接，独立会话内的事务控制不受当前函数所在事务的限制。
操作步骤：

1. 安装dblink扩展（需要超级用户权限）

CREATE EXTENSION IF NOT EXISTS dblink;

2. 修改函数逻辑，通过dblink调用目标存储过程

CREATE OR REPLACE FUNCTION public.f()
 RETURNS text
 LANGUAGE plpgsql
AS $function$ 
DECLARE
  v_conn TEXT;
BEGIN
  raise notice 'Starting Function!!!';
  -- 替换为实际的本地数据库连接信息
  v_conn := 'dbname=你的库名 user=你的用户名 password=你的密码 host=127.0.0.1 port=5432';
  PERFORM dblink_connect(v_conn);
  PERFORM dblink_exec('call public.p()');
  PERFORM dblink_disconnect();
  return 'success';  
END;
$function$;

注意：dblink的执行逻辑是完全独立的事务，即使当前函数所在事务回滚，dblink中已提交的操作也不会回滚。

方案3：拆分存储过程逻辑 #

如果存储过程内部的COMMIT不是强制要求，可以拆分逻辑适配调用：

• 将存储过程中不需要事务控制的纯业务逻辑抽离为独立的函数/无事务控制的存储过程
• 原自定义函数直接调用抽离后的无事务逻辑单元
• 原有需要执行COMMIT的场景，直接调用原带事务控制的存储过程即可

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