时间戳

首页 > 解决方案 > CTE 和 DML 语句的 PG 意外行为

postgresql - CTE 和 DML 语句的 PG 意外行为

问题描述

我偶然发现了这一点，为了让问题易于解释，我在下面放了一个示例代码

桌子：

CREATE TABLE "MyTab"
(
    "Id" integer NOT NULL DEFAULT nextval('"Anukram"."MyTab_Id_seq"'::regclass),
    "Text1" text COLLATE pg_catalog."default" NOT NULL,
    "Text2" text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "MyTab_pkey" PRIMARY KEY ("Id")
)

资料填写：

INSERT INTO "Anukram"."MyTab" ("Text1","Text2") values
('L','R'),('A','B'),('C','D'), ('L','R1'),('A','B1'),('C','D1')

问题陈述：

WITH "Temp" AS (
    UPDATE "MyTab"
    SET "Text2"="Text2" || "Text2"
    WHERE "Text1"='L'
    RETURNING *
)
DELETE 
FROM "MyTab"
USING "Temp"-- <--This is used in some more conditions in where clause not shown here
WHERE "MyTab"."Text1"='L'
RETURNING "MyTab".*

期望：

它应该在“Text1”列中删除 2 行带有“L”的行

实际：

它只删除一行（查看 RETURNING 语句）

观察：

如果我删除 Using 子句，一切都会按预期运行

那么我在这里缺少什么？或者这是PG使用的快照中的错误？

标签： postgresql

解决方案

这种情况下的具体结果令人惊讶，但根本问题已记录在案：

不支持在单个语句中尝试两次更新同一行。只进行了一项修改，但要可靠地预测哪一项并不容易（有时也不可能）。这也适用于删除已在同一语句中更新的行：仅执行更新。因此，您通常应避免尝试在单个语句中两次修改单个行。特别是避免编写WITH可能影响由主语句或兄弟子语句更改的相同行的子语句。这种声明的影响是不可预测的。

（强调我的。）

简而言之：不要试图UPDATE和DELETE同一行在同一个语句中，否则可能会发生奇怪的事情。

推荐阅读