PostgreSQL plpgsql存储过程异常捕获失败问题及异常处理最佳实践咨询

首先，咱们先拆解你代码里的几个关键问题——这些问题直接导致了异常捕获失效、日志没记录的情况：

1. 异常块的范围完全错误 #

你现在把EXCEPTION放在一个空的BEGIN...END块里，这个块里没有任何可能抛出异常的操作（比如INSERT、COMMIT都在这个块外面），所以自然捕获不到错误。PL/pgSQL的EXCEPTION只能捕获它所在的BEGIN...END块内的异常，必须把可能出错的核心逻辑放在包含EXCEPTION的块里。

2. 变量未声明 #

你用到了text_var1、text_var2、text_var3，但在DECLARE部分完全没定义这些变量，这会导致编译错误，只是可能你没注意到。

3. 循环逻辑混乱 #

• n_rec_cnt初始是NULL，第一次循环的EXIT WHEN n_rec_cnt = 0不会触发（因为NULL = 0的结果是NULL，不满足退出条件），但你在第一次GET DIAGNOSTICS之前没给它赋值，循环启动逻辑有问题。
• 你在循环里直接RAISE EXCEPTION 'Max retry count exceeded';，这会导致第一次循环就抛出异常，完全不符合重试的设计初衷——应该是重试次数耗尽后再抛出。

4. 语法错误 #

UPDATE job_log SET error_msg = text_var1 return;里的return是语法错误，要么去掉，要么如果需要返回更新的行，用RETURNING *;（但存储过程里如果不需要返回可以不用）。

修复后的示例代码 #

CREATE OR REPLACE PROCEDURE marketing_offers.stackover_overflow_ex_question(limit_size integer)
LANGUAGE plpgsql
AS $procedure$
DECLARE
    n_rec_cnt bigint := 1; -- 初始化非0值，确保循环能启动
    retry_count integer := 0;
    max_retries integer := 3; -- 自定义最大重试次数
    text_var1 text;
    text_var2 text;
    text_var3 text;
BEGIN
    -- 循环处理数据，直到无记录可处理或重试次数耗尽
    LOOP
        EXIT WHEN n_rec_cnt = 0 OR retry_count >= max_retries;

        BEGIN
            -- 把可能出错的操作包裹在这个块里，方便捕获异常
            WITH cte AS (
                SELECT * 
                FROM master_table mt 
                WHERE mt.created_date <= '1999-01-01'::date -- 这里你写的::time应该是笔误，改成::date更合理
                LIMIT limit_size
            )
            INSERT INTO some_archive_table 
            SELECT * FROM cte;
            
            -- 注意：pg_cron默认会在事务中执行任务，若不需要手动提交可去掉此句
            COMMIT;
            
            GET DIAGNOSTICS n_rec_cnt = row_count;
            retry_count := 0; -- 成功处理后重置重试计数
            
        EXCEPTION
            WHEN OTHERS THEN
                -- 捕获所有异常，获取完整错误信息
                GET STACKED DIAGNOSTICS 
                    text_var1 = message_text,
                    text_var2 = PG_EXCEPTION_DETAIL,
                    text_var3 = PG_EXCEPTION_HINT;
                
                -- 输出日志到PostgreSQL日志系统
                RAISE NOTICE 'Error occurred: %, Detail: %, Hint: %', text_var1, text_var2, text_var3;
                
                -- 更新任务日志表，补充必要元数据
                UPDATE job_log 
                SET error_msg = text_var1,
                    error_detail = text_var2,
                    error_hint = text_var3,
                    last_attempt = NOW(),
                    retry_count = retry_count + 1
                WHERE job_name = 'stackover_overflow_ex_question'; -- 假设job_log有标识任务的字段
                
                retry_count := retry_count + 1;
                n_rec_cnt := 1; -- 标记需要继续重试
                
                -- 可选：重试前等待一段时间，避免频繁重试压垮系统
                PERFORM pg_sleep(5);
        END;
    END LOOP;

    -- 若重试次数耗尽，抛出最终异常（根据业务需求决定是否保留）
    IF retry_count >= max_retries THEN
        RAISE EXCEPTION 'Max retry count (%) exceeded for data archiving task', max_retries;
    END IF;
END;
$procedure$;

PL/pgSQL异常处理最佳实践 #

1. 精准捕获异常，别滥用WHEN OTHERS #

虽然WHEN OTHERS能捕获所有异常，但实际场景中最好针对具体错误码捕获，比如：

• WHEN unique_violation THEN（唯一键冲突，错误码23505）：这类错误不需要重试，直接记录即可
• WHEN lock_not_available THEN（锁等待超时，错误码55P03）：这类错误适合重试
• WHEN no_data_found THEN（无数据返回，错误码P0002）：可以直接退出循环

精准捕获能让你针对不同错误做差异化处理，避免无效重试。

2. 合理划分BEGIN...EXCEPTION块 #

不要把整个存储过程塞进一个大的异常块里，应该把独立的风险逻辑（比如单次数据插入、外部服务调用）放在单独的小BEGIN...END块里，这样一个小错误不会导致整个过程的上下文丢失。

3. 详细记录异常上下文 #

除了message_text，一定要记录PG_EXCEPTION_DETAIL、PG_EXCEPTION_HINT、PG_EXCEPTION_CONTEXT（错误发生的代码位置），这些信息能帮你快速定位问题。另外，建议记录错误发生时间、重试次数、当前处理的数据量等元数据到日志表。

4. 事务处理要谨慎 #

• pg_cron默认会在事务中执行存储过程，如果你的存储过程里手动加了COMMIT，要确保pg_cron的任务配置没有冲突；如果不需要手动控制事务，直接去掉COMMIT即可。
• 在异常块里，如果事务已经处于不可用状态（比如严重错误），可以用ROLLBACK TO SAVEPOINT回滚到之前的保存点，或者直接ROLLBACK后再继续执行后续逻辑。

5. 重试逻辑要克制 #

• 必须设置最大重试次数，避免死循环。
• 重试前加入等待时间（比如pg_sleep），短时间内频繁重试只会加重系统负载。
• 只对可恢复的错误重试，比如网络波动、锁超时；像语法错误、数据约束冲突这类不可恢复的错误，直接记录并退出。

6. 主动测试异常场景 #

测试时要主动模拟异常（比如手动修改master_table结构导致INSERT失败、锁表），验证异常捕获和日志记录是否正常工作，别等线上出问题才发现逻辑漏洞。

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