PostgreSQL中FOR循环内更新源表未遍历新插入行如何解决

PL/pgSQL中FOR 变量 IN SELECT查询的循环写法，会在循环启动时就把SELECT查询返回的所有结果一次性物化到内存中，生成一份静态的迭代集合。后续循环体内对作为数据源的表做的任何增删改操作，都不会修改这份已经生成的静态集合，所以示例中初始表只有3条数据，循环就只会执行3次，后续插入的4、5不会被纳入迭代范围。

针对迭代过程中动态生成新数据、需要将新数据纳入后续迭代的场景，有两种稳定的实现方式：

方案1：使用敏感显式游标 #

不要用预物化结果的FOR IN SELECT写法，改用支持感知同事务内数据变更的显式游标逐行取数，示例代码：

DO $$
DECLARE p_Id int;
    data_cur refcursor;
BEGIN
    DROP TABLE IF EXISTS TempTbl;
    CREATE TEMPORARY TABLE TempTbl (Id int);
    INSERT INTO TempTbl VALUES (1);
    INSERT INTO TempTbl VALUES (2);
    INSERT INTO TempTbl VALUES (3);    
        
    -- 打开默认敏感游标（不加INSENSITIVE参数即可），不会预物化全量结果
    OPEN data_cur FOR SELECT Id FROM TempTbl;
    LOOP
        FETCH NEXT FROM data_cur INTO p_Id;
        -- 无更多数据时退出循环
        EXIT WHEN NOT FOUND;
        
        RAISE NOTICE 'Value of p_Id: %', p_Id;

        INSERT INTO TempTbl
        SELECT 4
        WHERE 4 NOT IN (SELECT Id FROM TempTbl);
        
        INSERT INTO TempTbl
        SELECT 5
        WHERE 5 NOT IN (SELECT Id FROM TempTbl);      
    END LOOP;
    CLOSE data_cur;
END $$;

注意：定义游标时不要加INSENSITIVE关键字，加了之后游标会自动物化全量结果，会回到原有问题。

方案2：加处理状态标记（鲁棒性更强，复杂场景推荐） #

给临时表增加处理状态字段，每次循环查询未处理的单行数据处理，处理完标记状态，新插入的数据默认是未处理状态，自然会被后续循环捕获，示例代码：

DO $$
DECLARE p_Id int;
BEGIN
    DROP TABLE IF EXISTS TempTbl;
    -- 增加processed字段标记是否已处理，默认未处理
    CREATE TEMPORARY TABLE TempTbl (Id int, processed boolean DEFAULT false);
    INSERT INTO TempTbl VALUES (1, false);
    INSERT INTO TempTbl VALUES (2, false);
    INSERT INTO TempTbl VALUES (3, false);    
        
    LOOP
        -- 每次取1条未处理的数据，加行锁避免重复处理
        SELECT Id INTO p_Id 
        FROM TempTbl 
        WHERE processed = false 
        LIMIT 1 
        FOR UPDATE SKIP LOCKED;
        -- 没有未处理数据时退出
        EXIT WHEN NOT FOUND;
        
        RAISE NOTICE 'Value of p_Id: %', p_Id;

        -- 插入新数据，默认未处理
        INSERT INTO TempTbl
        SELECT 4, false
        WHERE 4 NOT IN (SELECT Id FROM TempTbl);
        
        INSERT INTO TempTbl
        SELECT 5, false
        WHERE 5 NOT IN (SELECT Id FROM TempTbl);
        
        -- 标记当前行已处理
        UPDATE TempTbl SET processed = true WHERE Id = p_Id;
    END LOOP;
END $$;

两种方案执行后都会输出预期的5条NOTICE结果。

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