时间戳

首页 > 解决方案 > SELECT FOR UPDATE 返回零行 CTE

postgresql - SELECT FOR UPDATE 返回零行 CTE

问题描述

为什么 SELECT FOR UPDATE 在下面的场景中返回 0 行？（在交易中执行）

如果删除“FOR UPDATE”，则返回 1 行

环境：windows 10，PostgreSQL 13.3，Visual C++ build 1914编译，64位

也许相关的问题 - SELECT FOR UPDATE 返回零行

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (_pk serial primary key NOT NULL);
insert into t1 (_pk) values(1);

--EXPLAIN ANALYZE VERBOSE
WITH
    cte1 AS MATERIALIZED (
        UPDATE t1
        SET _pk = 1
        returning _pk
    ) , cte2 AS (
        SELECT * 
        FROM t1
        WHERE _pk = (SELECT _pk FROM cte1) FOR UPDATE
    )
SELECT _pk FROM cte2;

查询计划

"CTE Scan on cte2  (cost=94.68..94.70 rows=1 width=4) (actual time=0.064..0.065 rows=0 loops=1)"
"  Output: cte2._pk"
"  CTE cte1"
"    ->  Update on public.t1  (cost=0.00..35.50 rows=2550 width=10) (actual time=0.032..0.033 rows=1 loops=1)"
"          Output: t1._pk"
"          ->  Seq Scan on public.t1  (cost=0.00..35.50 rows=2550 width=10) (actual time=0.015..0.015 rows=1 loops=1)"
"                Output: 1, t1.ctid"
"  CTE cte2"
"    ->  LockRows  (cost=51.16..59.18 rows=1 width=10) (actual time=0.062..0.063 rows=0 loops=1)"
"          Output: t1_1._pk, t1_1.ctid"
"          InitPlan 2 (returns $2)"
"            ->  CTE Scan on cte1  (cost=0.00..51.00 rows=2550 width=4) (actual time=0.033..0.034 rows=1 loops=1)"
"                  Output: cte1._pk"
"          ->  Index Scan using t1_pkey on public.t1 t1_1  (cost=0.15..8.17 rows=1 width=10) (actual time=0.054..0.055 rows=1 loops=1)"
"                Output: t1_1._pk, t1_1.ctid"
"                Index Cond: (t1_1._pk = $2)"
"Planning Time: 0.170 ms"
"Execution Time: 0.107 ms"

标签： postgresqllockingcommon-table-expression

解决方案

推荐阅读