PostgreSQL中实现存储过程内每个_store事务独立提交的方案

问题描述 #

现有一个名为run_proc的PostgreSQL存储过程，其逻辑为调用三个子存储过程，其中一个存储过程的执行结果作为另一个的参数，代码如下：

CREATE OR REPLACE PROCEDURE run_proc() LANGUAGE plpgsql AS $$ DECLARE
    _store RECORD;
    _date RECORD; BEGIN
    CALL sp1();
    FOR _store IN SELECT * FROM temp_stores
    LOOP
        CALL sp2(_store.store_id);
        -- Check if temp_year_month has any data
        IF EXISTS (SELECT 1 FROM temp_year_month) THEN
            FOR _date IN SELECT * FROM temp_year_month
            LOOP
                CALL sp3(_store.store_id, _date.year_month);
            END LOOP;
        END IF;
        -- Drop dates table after use
        DROP TABLE temp_year_month;
    END LOOP;
    -- Don't forget to drop the tenants table when done
    DROP TABLE temp_stores; END; $$

当前该存储过程运行正常，但当temp_stores包含约1000条记录时，所有操作结果会一次性提交到数据库（sp3包含插入/更新语句）。由于PostgreSQL不直接支持自治事务，需要实现每个temp_stores中的_store值对应的操作单独提交。

解决方案 #

PostgreSQL虽无原生自治事务，但可通过dblink扩展调用独立会话的方式实现每个store操作的单独提交，具体步骤如下：

1. 安装dblink扩展（若未安装） #

dblink用于在当前会话中建立到数据库的新连接，每个连接对应独立事务：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 重构单store处理逻辑为独立存储过程 #

将原循环内的单个store处理逻辑抽离为单独的存储过程，便于独立调用：

CREATE OR REPLACE PROCEDURE process_single_store(p_store_id INT)
LANGUAGE plpgsql AS $$
DECLARE
    _date RECORD;
BEGIN
    CALL sp2(p_store_id);
    IF EXISTS (SELECT 1 FROM temp_year_month) THEN
        FOR _date IN SELECT * FROM temp_year_month
        LOOP
            CALL sp3(p_store_id, _date.year_month);
        END LOOP;
    END IF;
    DROP TABLE temp_year_month;
END;
$$;

3. 修改原run_proc，用dblink执行独立事务 #

在原存储过程中，通过dblink调用上述新存储过程，每个调用对应独立会话与事务，执行完成后自动提交：

CREATE OR REPLACE PROCEDURE run_proc()
LANGUAGE plpgsql AS $$
DECLARE
    _store RECORD;
    -- 构造当前数据库的连接字符串
    _conn_str TEXT := 'dbname=' || current_database();
BEGIN
    CALL sp1();
    FOR _store IN SELECT * FROM temp_stores
    LOOP
        -- 调用dblink执行单store处理逻辑，自动提交事务
        PERFORM dblink_exec(
            _conn_str,
            format('CALL process_single_store(%L);', _store.store_id)
        );
    END LOOP;
    DROP TABLE temp_stores;
END;
$$;

原理说明 #

• dblink的dblink_exec会创建一个独立的数据库会话，该会话内的所有操作（调用process_single_store、执行sp2/sp3、临时表操作）都会在单独的事务中执行，执行完毕后自动提交。
• 每个store的处理逻辑相互隔离，不会受主事务影响，实现了单store操作的单独提交。

注意事项 #

• 确保执行用户拥有dblink使用权限、目标存储过程调用权限，以及数据库连接权限。
• 临时表temp_year_month由sp2创建，因每个dblink会话独立，不会出现跨store的临时表冲突。

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