PostgreSQL匿名代码块实现操作回滚及失败记录返回的问题

问题核心 #

需要原子执行两项操作：刷新物化视图public.some_view、更新表public.some_table，任一操作失败则整体回滚，同时返回明确的失败记录。尝试过匿名代码块+临时表，但异常触发的回滚会连带删除临时表的创建与插入操作；PostgreSQL 12不支持函数内回滚，函数方案不可行。

解决方案思路 #

PostgreSQL的临时表属于会话级对象，只要不在事务内创建，就不会被事务回滚影响。因此可以先在匿名代码块外创建临时表，再在块内处理业务逻辑：

• 预先创建临时表存储结果，避免被业务操作的回滚删除
• 用嵌套块处理每个业务操作，失败时标记错误并抛出异常触发整体回滚
• 外层捕获异常后，将错误信息插入预先存在的临时表
• 操作全部成功时，也插入成功记录到临时表

修改后的代码 #

-- 预先创建会话级临时表，避免被事务回滚影响
DROP TABLE IF EXISTS temporary_tbl;
CREATE TEMPORARY TABLE temporary_tbl(result_code INT, message TEXT);

DO $$
    DECLARE
        result_code INT := 0;
        result_msg TEXT := '';
    BEGIN
        -- 嵌套块处理业务操作，失败则触发回滚
        BEGIN
            -- 刷新物化视图
            REFRESH MATERIALIZED VIEW public.some_view;
            RAISE NOTICE '0 - 成功刷新some_view.';

            -- 更新表
            UPDATE public.some_table
            SET some_date = NOW()
            WHERE some_date IS NULL;
            RAISE NOTICE '0 - some_table更新成功.';

            -- 全部成功时插入记录
            result_msg := '所有操作执行成功';
            INSERT INTO temporary_tbl VALUES (result_code, result_msg);
        EXCEPTION
            WHEN OTHERS THEN
                -- 根据错误来源设置对应编码
                IF SQLERRM LIKE '%some_view%' THEN
                    result_code := 1;
                    result_msg := '刷新物化视图失败: ' || SQLERRM;
                ELSE
                    result_code := 2;
                    result_msg := '更新some_table失败: ' || SQLERRM;
                END IF;
                RAISE NOTICE '% - %', result_code, result_msg;
                -- 插入错误记录到临时表（临时表已在块外创建，不会被回滚）
                INSERT INTO temporary_tbl VALUES (result_code, result_msg);
                -- 可选：抛出异常明确告知客户端操作失败
                -- RAISE EXCEPTION USING MESSAGE = result_msg, ERRCODE = result_code::text;
        END;
END$$;

-- 查询结果，临时表在当前会话持续存在
SELECT * FROM temporary_tbl;

关键说明 #

1. 临时表提前创建：将临时表的创建放在匿名代码块外，这样即使块内事务回滚，临时表依然存在，插入的结果不会丢失。
2. 嵌套块处理事务：内层块包裹所有业务操作，任一操作失败都会进入EXCEPTION块，此时内层块的所有业务操作会自动回滚，而临时表的插入操作属于会话级，不受影响。
3. 错误编码匹配：通过SQLERRM判断错误来源，设置对应的结果编码，也可以根据具体错误类型（如undefined_table）更精准匹配。
4. 成功记录插入：确保操作全部成功时也插入记录，避免临时表为空导致误解。

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