Postgres 如何避免“迭代失效”，您可以循环行并同时修改数据？

问：

我想知道 PG 如何在您同时迭代表时允许表中的更改。

特别是，我正在寻找如何在引擎的底层/内部完成这项工作，因为我希望复制这个想法。

DROP TABLE IF EXISTS test;

CREATE TABLE test (
    id      INTEGER PRIMARY KEY,
    value   TEXT NOT NULL
);

INSERT INTO test (id, value) values (1, 'a');

DO
$function$
DECLARE
  row RECORD;
BEGIN
    -- Can add while iterating?
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'ADD ROW..:%', row;
        INSERT INTO test (id, value) values (2, 'b');
    END LOOP;
    -- Yes, and the new row is not visible in the loop

    -- Can update while iterating?
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'UP ROW..:%', row;
        UPDATE test SET value = 'c' WHERE id = row.id;
    END LOOP;
    -- Yes, and the updated rows do not affect the iteration and you see the old value

    -- Can update and see the new row?
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'UP ROW..:%', row;
        UPDATE test SET value = 'd' WHERE id = row.id;
        
        SELECT * FROM test WHERE id = row.id AND value ='d' INTO row;
        IF row IS NOT NULL THEN
            RAISE NOTICE 'FOUND ROW..:%', row;
        END IF;
    END LOOP;
    -- Yes, we can see the change if run another query
    
    --Can remove while iterating?
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'DEL ROW..:%', row;
        DELETE FROM test;
    END LOOP;
    --Yes, and the deleted rows do not affect the iteration
END
$function$;

在我看来，它正在加载所有行，然后迭代内存，类似于这个 Rust 代码：PG

// instead of:
fn main() {
    let mut table = vec![1, 2];
    
    for row in table {
        table.push(3);
        dbg!(row);
    }

    // is doing
    let query = table.clone();
    for row in query {
        table.push(3);
        dbg!(row);
    }    
}

然而，这一定是非常低效的。特别值得一提的是，我发现有趣的是这是如何实现的：

   -- Can update and see the new row?
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'UP ROW..:%', row;
        UPDATE test SET value = 'd' WHERE id = row.id;
        
        SELECT * FROM test WHERE id = row.id AND value ='d' INTO row;
        IF row IS NOT NULL THEN
            RAISE NOTICE 'FOUND ROW..:%', row;
        END IF;
    END LOOP;
    -- Yes, we can see the change if run another query

因为这指向了 PG 有 2 个不同的 （？） 看到交易变化的不同部分的想法。cursors

所以，我想PG正在做这样的事情：

在循环中，它得到类似的东西，但是当你运行下一个查询时，它得到？SELECT * FROM data where tx_id <=1SELECT * FROM data where tx_id <=2