postgresql - 选择更新返回零行

Question

---

更新:问题已解决!

那是 PostgreSQL 中的一个 bug。 Tom Lane 修复了它 commit .

---

为什么 SELECT FOR UPDATE 在下面的场景中返回 0 行？但是如果我只是从第二个事务执行 sql 查询，它总是返回 1 行。

交易 1:

BEGIN;
-- This query updates t1c1 to its current value, it doesn't change anything
UPDATE t1 SET t1c3 = 'string_value_1' WHERE t1c1 = 123456789;

-- Query returned successfully: one row affected, 51 msec execution time.

交易 2:

WITH 
    cte1 AS (
        SELECT t2c2 FROM t2 WHERE t2c1 = 'string_value_2'
    ),

    cte2 AS (
        SELECT * FROM t1
        WHERE 
             t1c1 = 123456789 
            AND t1c2 = (SELECT t2c2 FROM cte1)
        FOR UPDATE
    ) 

SELECT * FROM cte2

-- Waiting

交易 1:

COMMIT;

-- Query returned successfully with no result in 41 msec.

交易 2:

-- Returned 0 rows

示例:

CREATE TABLE t1 (_pk serial, t1c1 integer, t1c2 integer, t1c3 text);
CREATE TABLE t2 (_pk serial, t2c1 text, t2c2 integer);
insert into t1 (t1c1, t1c2, t1c3) values(123456789, 100, 'string_value_1');
insert into t2 (t2c1, t2c2) values('string_value_2', 100);

Answer 1

有趣的问题!使用 explain verbose analyze，我得到以下查询计划:

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 CTE Scan on cte2  (cost=51.13..51.15 rows=1 width=44) (actual time=4544.488..4544.488 rows=0 loops=1)
   Output: cte2._pk, cte2.t1c1, cte2.t1c2, cte2.t1c3
   CTE cte1
     ->  Seq Scan on public.t2  (cost=0.00..24.50 rows=6 width=4) (actual time=0.002..0.003 rows=1 loops=1)
           Output: t2.t2c2
           Filter: (t2.t2c1 = 'string_value_2'::text)
   CTE cte2
     ->  LockRows  (cost=0.12..26.63 rows=1 width=50) (actual time=4544.485..4544.485 rows=0 loops=1)
           Output: t1._pk, t1.t1c1, t1.t1c2, t1.t1c3, t1.ctid
           InitPlan 2 (returns $1)
             ->  CTE Scan on cte1  (cost=0.00..0.12 rows=6 width=4) (actual time=0.005..0.006 rows=1 loops=1)
                   Output: cte1.t2c2
           ->  Seq Scan on public.t1  (cost=0.00..26.50 rows=1 width=50) (actual time=0.018..0.019 rows=1 loops=1)
                 Output: t1._pk, t1.t1c1, t1.t1c2, t1.t1c3, t1.ctid
                 Filter: ((t1.t1c1 = 123456789) AND (t1.t1c2 = $1))
 Planning time: 0.116 ms
 Execution time: 4544.535 ms
(17 rows)

外部的“cte2 上的 CTE 扫描”似乎删除了在“LockRows”步骤中仍然存在的行。众所周知，Postgres 会在获取锁后重新评估 where 子句(请参阅 this example with work queues 。)也许查询计划在不可见的 ctid 上包含一个 where 子句> 行标识符，在任何 UPDATE 之后都会改变？

我已经在 this question 上询问 Postgres mailing list，看看其他人是否能够澄清这里发生的事情。