PostgreSQL 16.x存储过程事务处理及提交回滚问题咨询

一、事务控制：PostgreSQL vs SQL Server的核心差异 #

从SQL Server转过来最容易踩的坑就是事务逻辑：

• SQL Server存储过程里可以随意写COMMIT/ROLLBACK，不管外层有没有事务；但PostgreSQL中，只有用PROCEDURE创建的存储过程（注意不是FUNCTION，函数不支持显式事务），且处于独立事务上下文时，才能用显式事务控制语句。
• 在pgAdmin手动调用时，默认处于未提交事务中（pgAdmin默认开启事务，除非勾选"自动提交"），此时存储过程里的COMMIT会触发"cannot commit while a subtransaction is active"错误——因为当前会话已有外层事务，存储过程操作属于子事务，PostgreSQL不允许子事务内提交。

最佳实践：如果要同时支持pgAdmin手动执行和Web应用调用，建议不在存储过程内部写COMMIT/ROLLBACK，把事务控制权交给调用方：

• pgAdmin手动执行：调用后可手动提交/回滚，或开启自动提交模式（查询工具顶部有"自动提交"按钮）；
• Web应用调用：由应用层（如JDBC、psycopg2）管理事务提交回滚，更灵活。

二、解决"cannot commit while a subtransaction is active"报错 #

这个错误本质是子事务存在时尝试提交外层事务，常见场景是存储过程用了EXCEPTION块（PostgreSQL的EXCEPTION会隐式创建子事务），之后又写了COMMIT。

错误示例：

CREATE OR REPLACE PROCEDURE delete_batch()
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM table1 WHERE id < 100;
    DELETE FROM table2 WHERE ref_id < 100;
    COMMIT; -- 错误：调用时外层有事务会触发子事务报错
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE EXCEPTION '删除失败：%', SQLERRM;
END;
$$;

修正写法（去掉内部事务控制，交给调用方）：

CREATE OR REPLACE PROCEDURE delete_batch()
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM table1 WHERE id < 100;
    DELETE FROM table2 WHERE ref_id < 100;
EXCEPTION
    WHEN OTHERS THEN
        -- 抛出异常会自动触发事务回滚，无需手动ROLLBACK
        RAISE EXCEPTION '删除失败：%', SQLERRM;
END;
$$;

如果一定要在存储过程内管理事务（确保每次调用是独立事务），可以判断当前事务状态：

CREATE OR REPLACE PROCEDURE delete_batch()
LANGUAGE plpgsql
AS $$
BEGIN
    -- 仅当无外层事务时开启新事务
    IF NOT IN_TRANSACTION THEN
        BEGIN;
    END IF;

    DELETE FROM table1 WHERE id < 100;
    DELETE FROM table2 WHERE ref_id < 100;

    IF NOT IN_TRANSACTION THEN
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF NOT IN_TRANSACTION THEN
            ROLLBACK;
        END IF;
        RAISE EXCEPTION '删除失败：%', SQLERRM;
END;
$$;

不过这种写法兼容性稍差，更推荐交给调用方控制事务。

三、RAISE EXCEPTION vs RAISE NOTICE：该用哪个？ #

结合你"所有操作整体提交或回滚"的需求，两者区别明确：

• RAISE EXCEPTION：立即中断执行，触发整个事务回滚——这正是你需要的，只要任何一步DELETE出错，所有操作都回滚，符合原子性要求。
• RAISE NOTICE：仅输出提示消息，不中断执行、不影响事务——适合输出成功提示（比如RAISE NOTICE '已删除%条记录', GET DIAGNOSTICS row_count = ROW_COUNT;），但绝对不能用来处理错误，否则错误后操作仍会执行，破坏原子性。

四、最终可用的存储过程示例 #

结合多个DELETE、原子性、多场景调用的需求，最终写法如下：

CREATE OR REPLACE PROCEDURE delete_old_records()
LANGUAGE plpgsql
AS $$
DECLARE
    table1_rows INT;
    table2_rows INT;
BEGIN
    -- 执行第一个DELETE并统计行数
    DELETE FROM table1 WHERE created_at < NOW() - INTERVAL '30 days';
    GET DIAGNOSTICS table1_rows = ROW_COUNT;

    -- 执行关联DELETE并统计行数
    DELETE FROM table2 WHERE ref_id IN (SELECT id FROM table1 WHERE created_at < NOW() - INTERVAL '30 days');
    GET DIAGNOSTICS table2_rows = ROW_COUNT;

    -- 输出成功提示（可选）
    RAISE NOTICE '删除完成：table1删除%条，table2删除%条', table1_rows, table2_rows;
EXCEPTION
    WHEN OTHERS THEN
        -- 抛出异常触发回滚，附带错误信息
        RAISE EXCEPTION '批量删除失败：错误信息：%，错误代码：%', SQLERRM, SQLSTATE;
END;
$$;

调用方式： #

1. pgAdmin手动执行：
   • 开启自动提交：直接执行CALL delete_old_records();，操作自动提交；
   • 未开启自动提交：执行CALL delete_old_records();后，手动点击"提交"或执行COMMIT;，需回滚则点击"回滚"或执行ROLLBACK;。
2. Web应用调用：
   • 在应用代码中开启事务，调用存储过程，根据是否抛出异常决定提交或回滚（如psycopg2、JDBC都有对应事务API）。

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