PostgreSQL未指定WITH HOLD的游标循环含Commit仍生效原因问询

首先明确PostgreSQL游标的基础规则：

• 未指定WITH HOLD的游标是事务级的，事务提交/回滚后会立即关闭，无法在后续事务中使用；
• WITH HOLD游标会在事务提交后保持打开，直到显式关闭或会话结束，其结果集基于创建游标时的事务快照。

你的示例能成功复制全部1000行，大概率是以下两种场景之一：

场景1：每次循环都重新创建游标（隐式/显式） #

比如你用的是类似下面的代码：

DO $$
DECLARE
    rec a%rowtype;
BEGIN
    LOOP
        -- 每次循环都重新查询表a中未插入到表b的行，隐式创建新游标
        SELECT * INTO rec FROM a WHERE id NOT IN (SELECT id FROM b) LIMIT 1;
        EXIT WHEN NOT FOUND;
        INSERT INTO b VALUES (rec.*);
        COMMIT;
    END LOOP;
END $$;

或者显式每次循环都打开新游标：

DO $$
DECLARE
    cur refcursor;
    rec a%rowtype;
BEGIN
    LOOP
        OPEN cur FOR SELECT * FROM a WHERE id NOT IN (SELECT id FROM b) LIMIT 1;
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        INSERT INTO b VALUES (rec.*);
        COMMIT;
        CLOSE cur;
    END LOOP;
END $$;

这种情况下：

• 每次COMMIT结束当前事务后，下一次循环会自动开启新事务，并创建、打开新的游标；
• 每个新游标基于当前事务的快照，能看到之前COMMIT后表b的最新数据，从而只处理表a中尚未插入的行；
• 这和WITH HOLD完全无关，因为你每次用的都是新游标，每个游标在自己的事务生命周期内有效。

场景2：用自治事务实现插入提交，主游标未被关闭 #

如果你的代码是把插入和COMMIT放在一个自治事务（通过dblink等方式实现独立事务）的函数中，主事务的游标始终保持打开：

-- 先创建自治事务函数（需要先安装dblink扩展）
CREATE OR REPLACE FUNCTION insert_and_commit(p_rec a%rowtype) RETURNS void AS $$
BEGIN
    PERFORM dblink_exec('dbname=' || current_database(),
        'INSERT INTO b VALUES (' || quote_literal(p_rec.id) || ', ''' || quote_literal(p_rec.name) || ''')');
END $$ LANGUAGE plpgsql;

DO $$
DECLARE
    cur CURSOR FOR SELECT * FROM a;
    rec a%rowtype;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        PERFORM insert_and_commit(rec);
    END LOOP;
    CLOSE cur;
    COMMIT;
END $$;

这种情况下：

• 主事务中的游标是WITHOUT HOLD，但主事务全程未提交，所以游标一直保持打开状态；
• 插入操作在独立的自治事务中提交，不影响主事务的生命周期，主游标基于主事务启动时的快照，能完整读取表a的1000行数据；
• 你看到的每次插入后COMMIT是自治事务的提交，主事务的游标不受影响。

对你几个疑问的明确回答 #

1. 是否等同于设置了WITH HOLD？
   不是。WITH HOLD是单个游标跨事务保留，而你的场景要么是每次用新游标，要么是主事务未提交、游标在单一事务内保持打开，和WITH HOLD的机制完全不同。

2. 是否保持匿名块启动时的数据视图？
   如果是场景2（自治事务），主游标会保持匿名块启动时的快照，看不到自己插入到表b的新数据；如果是场景1（每次重新创建游标），每个新游标会看到当前事务的最新数据（包括之前提交的插入）。

3. 是否能看到Commit后的新数据？
   场景1中每次重新打开的游标可以看到；场景2中主游标看不到，因为主事务的快照在启动时就固定了。

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