在可重复读隔离级别下，如何在WITH CTE中查询刚插入的行？为何结果不同？

先看表结构：

CREATE TABLE IF NOT EXISTS foo (
    name TEXT NOT NULL
);

正常场景：单独INSERT后能查到新行 #

执行以下事务时，第二次SELECT可以获取到新插入的bar行：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

SELECT * FROM foo;

INSERT INTO foo(name) VALUES('bar');

SELECT * FROM foo;

COMMIT;

异常场景：CTE插入后主查询查不到新行 #

但改用CTE形式时，主查询里的foo表无法查到刚插入的行（只有CTE的ins能返回该行）：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;

SELECT * FROM foo;

WITH ins AS (
  INSERT INTO foo(name) VALUES('bar') RETURNING *
)
SELECT * FROM foo, ins;

COMMIT;

原因解析 #

这是PostgreSQL在REPEATABLE READ隔离级别下的快照机制和CTE执行逻辑共同导致的：

• REPEATABLE READ隔离级别下，事务第一次读取操作会生成一个数据快照，后续普通SELECT默认基于这个快照读取。但PostgreSQL有个特殊处理：如果是在INSERT/UPDATE/DELETE之后单独执行SELECT，会绕过快照，直接读取当前事务内的修改，保证事务能看到自己的操作结果。
• 但在CTE+主查询的同一条语句中，整个主查询（包括对foo的引用）是基于事务初始快照执行的。CTE中的INSERT虽然会写入数据，也能通过RETURNING返回结果，但主查询里对foo的查询不会感知到同语句内CTE的插入操作，仍然沿用初始快照。

简单说：单独的INSERT后SELECT是跨语句操作，PostgreSQL会特殊处理让你看到自己的修改；而CTE和主查询属于同一条语句，主查询的快照在语句执行前就确定了，看不到同语句内的插入。

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