PostgreSQL中触发自定义异常时如何确保插入执行并抛出错误？

问题背景 #

需求明确：当VRecord.id大于5时手动触发异常，但要求插入操作必须生效，同时异常要正常抛出（不能仅转为通知输出）。

现有代码的问题 #

1. 第一段代码：
   触发异常后，PostgreSQL会回滚整个事务，导致所有已执行的插入操作被撤销，无法保留数据。

   DO
   $$
   DECLARE
       VRecord        RECORD;
   BEGIN
       DELETE FROM table_1;
   
       FOR VRecord IN 
           SELECT 
               GENERATE_SERIES(1, 10) id
       LOOP
           RAISE NOTICE 'VRecord.id [%]', VRecord.id;
   
           INSERT INTO table_1 VALUES (VRecord.id);
   
           IF (VRecord.id > 5) THEN
               RAISE EXCEPTION 'erro em VRecord.id [%]', VRecord.id;
           END IF;
       END LOOP;
   END
   $$
   

2. 第二段代码：
   通过外层EXCEPTION块捕获了异常并转为通知输出，没有向上抛出错误，不符合“抛出异常”的要求。

   DO $$
   DECLARE
       VRecord        RECORD;
   BEGIN
       DELETE FROM table_1;
   
       FOR VRecord IN 
           SELECT 
               GENERATE_SERIES(1, 10) id
       LOOP
           BEGIN
               RAISE NOTICE 'VRecord.id [%]', VRecord.id;
   
               BEGIN
                   INSERT INTO table_1 VALUES (VRecord.id);
               EXCEPTION
                   WHEN others THEN
                       RAISE NOTICE 'Erro na inserção para VRecord.id [%]: %', VRecord.id, SQLERRM;
               END;
   
               IF (VRecord.id > 5) THEN
                   RAISE EXCEPTION 'Erro em VRecord.id [%]', VRecord.id;
               END IF;
   
           EXCEPTION
               WHEN others THEN
                   RAISE NOTICE 'Erro capturado em VRecord.id [%]: %', VRecord.id, SQLERRM;
           END;
       END LOOP;
   END
   $$;
   

解决方案 #

核心思路是将插入操作放在自治事务中执行，确保插入提交后不受主事务异常回滚的影响，同时不捕获需要抛出的异常，让其自然向上传递。

实现代码 #

DO $$
DECLARE
    VRecord RECORD;
BEGIN
    DELETE FROM table_1;

    FOR VRecord IN 
        SELECT GENERATE_SERIES(1, 10) id
    LOOP
        RAISE NOTICE 'VRecord.id [%]', VRecord.id;

        -- 自治事务：独立执行插入并提交，不受主事务影响
        DECLARE
            PRAGMA AUTONOMOUS_TRANSACTION;
        BEGIN
            INSERT INTO table_1 VALUES (VRecord.id);
            COMMIT; -- 立即提交插入操作
        END;

        -- 触发异常，不捕获，让其向上抛出
        IF VRecord.id > 5 THEN
            RAISE EXCEPTION 'Erro em VRecord.id [%]', VRecord.id;
        END IF;
    END LOOP;
END
$$;

代码说明 #

• 自治事务：PRAGMA AUTONOMOUS_TRANSACTION会创建一个独立于主事务的子事务，插入操作执行后通过COMMIT立即生效，即使主事务后续抛出异常回滚，已提交的插入也不会被撤销。
• 异常抛出：当VRecord.id > 5时，直接抛出异常且不进行捕获，确保异常会正常传递到上层，满足“抛出错误”的需求。
• 执行效果：运行后table_1中会保留id=1到6的记录（id=6的插入已通过自治事务提交），同时控制台会输出对应的异常信息。

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