PostgreSQL 13.6存储过程Python调用时事务终止异常问题求助

PgAdmin与Python调用的核心差异 #

• PgAdmin默认自动提交：直接执行CALL语句时，PostgreSQL会自动为每个调用创建独立事务，存储过程内部的COMMIT可以正常终止该事务，不会产生冲突。即使从其他存储过程调用，PgAdmin的自动提交模式也会让整个调用链处于独立事务中，内部COMMIT合法。
• Python DBAPI默认非自动提交：比如psycopg2这类库，默认会开启一个事务上下文，所有execute操作都在这个未结束的外部事务里执行。存储过程内部的COMMIT试图终止这个外部事务，违反了PostgreSQL的事务规则，因此触发invalid transaction termination错误。

解决办法 #

方案1：开启Python连接的自动提交模式 #

修改Python代码，在执行CALL前将数据库连接设置为自动提交，让每个存储过程调用都处于独立事务中，和PgAdmin行为一致：

import psycopg2

# 建立连接
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pass host=your_host")
# 开启自动提交
conn.autocommit = True

cur = conn.cursor()
cur.execute("CALL stg.do_something(%s)", (your_parameters,))

方案2：改用自治事务记录日志 #

不需要修改Python代码，调整存储过程，将日志操作放到自治事务中（PostgreSQL 11及以上支持），让日志独立提交，不受主事务回滚影响，同时移除主过程中的COMMIT：

-- 先创建自治事务的日志函数
CREATE OR REPLACE FUNCTION stg.log_proc_start(proc_name text) RETURNS void AS $$
DECLARE
BEGIN
    INSERT INTO stg.proc_exec_logs (procedure_name, start_time, status)
    VALUES (proc_name, NOW(), 'STARTED');
    COMMIT; -- 仅提交自治事务的日志操作
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 修改原存储过程
CREATE OR REPLACE PROCEDURE stg.do_something(your_params type) AS $$
DECLARE
BEGIN
    -- 调用自治事务函数记录启动日志
    PERFORM stg.log_proc_start('stg.do_something');
    
    -- 原业务逻辑代码
    -- ...
    
    -- 记录结束日志（同样可以用自治事务函数）
    INSERT INTO stg.proc_exec_logs (procedure_name, end_time, status)
    VALUES ('stg.do_something', NOW(), 'SUCCESS');
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO stg.proc_exec_logs (procedure_name, end_time, status, error_msg)
        VALUES ('stg.do_something', NOW(), 'FAILED', SQLERRM);
        RAISE; -- 抛出错误让外部处理
END;
$$ LANGUAGE plpgsql;

这种方式下，无论外部事务是否回滚，启动日志都会被独立提交，同时避免了主过程中COMMIT引发的冲突。

方案3：显式拆分事务（不推荐） #

如果必须保持Python的非自动提交模式，可以在Python代码中先单独插入启动日志并提交，再调用存储过程，但这会把日志逻辑从存储过程移到外部，破坏原设计的封装性，因此不推荐。

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