PostgreSQL如何避免迭代失效并支持遍历行时修改？底层实现探究

测试现象与疑问 #

先看实际测试的SQL代码及得到的现象：

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
    -- 遍历中插入新行：新行不会出现在当前循环里
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'ADD ROW..:%', row;
        INSERT INTO test (id, value) values (2, 'b');
    END LOOP;

    -- 遍历中更新行：循环仍能看到旧值，不受更新影响
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'UP ROW..:%', row;
        UPDATE test SET value = 'c' WHERE id = row.id;
    END LOOP;

    -- 更新后单独查询能看到新值
    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;

    -- 遍历中删除行：循环不受删除操作影响
    FOR row IN SELECT * FROM test
    LOOP
        RAISE NOTICE 'DEL ROW..:%', row;
        DELETE FROM test;
    END LOOP;
END
$function$;

最初猜测PostgreSQL是把全量数据加载到内存再遍历，类似以下Rust逻辑：

// 误以为的逻辑
fn main() {
    let mut table = vec![1, 2];
    
    for row in table {
        table.push(3);
        dbg!(row);
    }

    // 实际等效于克隆后遍历
    let query = table.clone();
    for row in query {
        table.push(3);
        dbg!(row);
    }    
}

但这种全量克隆的效率显然不现实，尤其对“更新后单独查询能看到新值”的现象，推测可能存在不同的游标或事务ID过滤逻辑。

底层核心实现：MVCC与快照隔离 #

PostgreSQL的核心是多版本并发控制（MVCC），并非预加载全量数据，而是通过事务快照和堆元组版本管理实现上述特性：

1. 事务快照的作用 #

当执行FOR row IN SELECT * FROM test时，PostgreSQL会为这个游标生成一个事务快照，快照记录了当前事务启动时所有已提交的事务ID（txid）。游标遍历过程中，只会返回快照生成前已存在且未被删除的元组版本，本事务后续的修改（插入、更新、删除）不会被该快照包含，因此循环内看不到新修改内容。

2. 堆元组的版本管理 #

PostgreSQL表数据以堆元组形式存储，每个元组包含三个关键事务字段：

• xmin：创建该元组的事务ID
• xmax：删除/替换该元组的事务ID
• ctid：元组在堆中的物理位置，更新操作会生成新元组，旧元组的xmax会标记为当前事务ID

以测试中的更新操作为例：

• 初始元组：xmin=tx1, xmax=0, data=(1,a)
• 执行UPDATE时，不会修改旧元组，而是生成新元组：xmin=tx2, xmax=0, data=(1,d)，同时将旧元组的xmax设为tx2

3. 不同查询的快照差异 #

• 循环内的游标使用快照生成时的视图，仅识别xmin <= 快照可见txid且xmax=0或xmax不在快照可见范围内的元组，因此看不到当前事务的新修改。
• 循环内部单独执行的SELECT，默认使用当前事务的最新状态，本事务的所有修改对自身可见，因此能匹配到新生成的元组。

为什么不是预加载全量数据？ #

PostgreSQL的游标是流式遍历，不会一次性把所有行加载到内存，而是根据查询计划（顺序扫描、索引扫描等）逐步从磁盘读取数据，再通过快照过滤出可见元组。仅当游标使用SCROLL CURSOR WITH HOLD或结果集极小时，才会缓存部分数据，但核心逻辑仍是基于MVCC快照，而非全量克隆。

各测试场景的具体解释 #

• 遍历中插入新行：游标快照生成时新行不存在，因此遍历过程不会返回新行，直到重新生成新快照（比如下一次循环的SELECT）。
• 遍历中更新当前行：游标看到的是旧元组（xmax被标记为当前事务ID，但快照认为该事务未提交，旧元组仍可见），新元组的xmin是当前事务ID，不在快照可见范围内，因此循环看不到新值。
• 更新后单独查询能看到新值：单独的SELECT会使用当前事务的最新状态，本事务的修改对自身可见，因此能匹配到新元组。
• 遍历中删除行：删除操作仅标记元组的xmax为当前事务ID，游标快照认为该事务未提交，旧元组仍可见，因此遍历不受影响。

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