为何单查询中写入后无法读取新插入的数据？

测试代码 #

DROP TABLE IF EXISTS A;
CREATE TABLE a (id int);

CREATE or replace FUNCTION insert_and_return(int) 
    RETURNS int AS $$
    BEGIN
        INSERT INTO a VALUES ($1);
        RETURN $1;
    END;
$$ LANGUAGE plpgsql;

SELECT * FROM insert_and_return(10),A AS y;

预期与实际结果 #

预期结果 #

预期FROM insert_and_return(10),A执行交叉连接时，函数插入的新行能被SELECT读取并返回结果。

实际结果 #

返回空结果集，必须再次执行SELECT * FROM a;才能看到插入的10。

核心问题解答 #

这是PostgreSQL查询执行的快照机制导致的：
PostgreSQL处理SELECT查询时，会先生成所有FROM子句涉及对象的快照，再执行FROM子句中的函数、CTE等操作。具体到这个案例：

1. 第一步：生成表A的快照（此时表A为空）
2. 第二步：执行insert_and_return(10)函数，向表A插入数据
3. 第三步：用第一步生成的空快照和函数返回的结果做交叉连接，最终得到空结果

数据确实已经插入到表中，但当前SELECT查询使用的是查询启动时的固定快照，无法看到查询执行过程中对表的修改。

相关疑问解答 #

• 是否与隔离级别有关？
  和隔离级别无关。隔离级别影响的是不同事务之间的可见性，而单查询内的快照一致性是PostgreSQL的基础行为——所有隔离级别下，单查询的快照都是查询启动时生成的固定快照，不会随查询执行中的修改变化。

• 为何CASE或SELECT price, price * 0.9这类动态生成数据的方式可行？
  这类操作是基于当前查询已读取到的数据做内存计算，没有修改底层表的数据，不需要生成新快照。它们是在查询执行阶段对已加载的数据做运算，自然能实时得到结果；而函数插入属于DML操作，修改的是表数据，不会影响当前查询已获取的快照。

• CTE方式的问题原因
  你尝试的CTE代码：

  WITH inserted_data AS (
      INSERT INTO a (id) VALUES (10)
      RETURNING id
  )
  SELECT * FROM inserted_data,A;
  

  遵循同样的规则：SELECT部分执行时先获取了表A的空快照，之后才执行CTE里的INSERT操作。虽然CTE返回了插入的id，但和表A的空快照做交叉连接，结果还是空。如果要在同一个查询里获取插入的数据，直接从CTE的RETURNING结果读取即可：

  WITH inserted_data AS (
      INSERT INTO a (id) VALUES (10)
      RETURNING id
  )
  SELECT * FROM inserted_data;
  

场景说明 #

这个场景不算边缘，它直接体现了PostgreSQL查询执行的核心规则——单查询的快照原子性，很多开发者会误以为单事务内的修改能被同事务的同查询读取，实际上PostgreSQL的单查询快照一旦生成就固定不变。

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