postgresql - 读取由 postgresql 函数创建的未提交行？

Question

我编写了一个函数，根据参数在表 mytable 中创建一行，并返回创建行的 ID。

不幸的是，如果我使用以下语句，SELECT 不返回任何行，就好像该函数的事务与 SELECT 的事务不同。

SELECT * FROM mytable WHERE id = createComplexRow(...);

我的理解是，我正在为选择和函数运行相同的事务，然后应该能够读取未提交的行。

我正在尝试使用 postgres 9.6

有什么线索可以让它正常工作吗？

Answer 1

让我们看看发生了什么。

CREATE TABLE mytable(
   id serial PRIMARY KEY,
   val timestamp with time zone NOT NULL
);

CREATE FUNCTION createcomplexrow() RETURNS integer
   LANGUAGE SQL AS
'INSERT INTO mytable (val) VALUES (current_timestamp) RETURNING id';

该函数是隐式的 VOLATILE，因为它必须如此，因为它修改了数据库。

让我们插入几行:

SELECT createcomplexrow();
SELECT createcomplexrow();

现在让我们试试你的陈述:

SELECT * FROM mytable WHERE id = createcomplexrow();

 id | val
----+-----
(0 rows)

确实，没有结果!

但是表中有新的值:

SELECT * FROM mytable;

 id |              val
----+-------------------------------
  1 | 2017-07-18 11:50:22.031922+02
  2 | 2017-07-18 11:50:23.640775+02
  3 | 2017-07-18 11:50:31.392773+02
  4 | 2017-07-18 11:50:31.392773+02
(4 rows)

要查看发生了什么，EXPLAIN 查询:

EXPLAIN (COSTS off)
   SELECT * FROM mytable WHERE id = createcomplexrow();

             QUERY PLAN
-------------------------------------
 Seq Scan on mytable
   Filter: (id = createcomplexrow())
(2 rows)

PostgreSQL 扫描表，对于找到的每一行，它调用函数并将结果与​​该行的 id 进行比较。

当它扫描 id = 1 的行时，该函数将返回 3(并插入一行)。所以 id = 1 的行被跳过。

类似地，id = 2 的行被跳过，并创建了 id = 4 的新行。

现在为什么执行停止在这里而不是继续扫描两个新创建的行？

这些 lines from the documentation稍微解释一下:

In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed.

(强调我的)

该语句看不到该函数的效果，因为该函数不是在以前的更新中执行的，而是在与 SELECT 相同的语句中执行的。

(同样发生在 data modifying WITH queries 中；您可能会发现阅读文档的那一部分很有启发性。)

实际上，您应该为以这种方式处理它而感到高兴，否则您将陷入不断向表中插入行的无限循环。