时间戳

首页 > 解决方案 > 为什么带有 DELETE 的 PostgreSQL CTE 不起作用？

sql - 为什么带有 DELETE 的 PostgreSQL CTE 不起作用？

问题描述

如果同一表中的更新导致使用两个 CTE 的数量为 0，我试图从我的 stock 表中删除一条记录。upserts 正在工作，但删除没有产生我期望的结果。库存表中的数量正在更改为零，但记录没有被删除。表结构：

CREATE TABLE IF NOT EXISTS stock_location (
    stock_location_id SERIAL
    , site_code VARCHAR(10) NOT NULL
    , location_code VARCHAR(50) NOT NULL
    , status CHAR(1) NOT NULL DEFAULT 'A'
    , CONSTRAINT pk_stock_location PRIMARY KEY (stock_location_id)
    , CONSTRAINT ui_stock_location__keys UNIQUE (site_code, location_code)
);

CREATE TABLE IF NOT EXISTS stock (
    stock_id SERIAL
    , stock_location_id INT NOT NULL
    , item_code VARCHAR(50) NOT NULL
    , quantity FLOAT NOT NULL
    , CONSTRAINT pk_stock PRIMARY KEY (stock_id)
    , CONSTRAINT ui_stock__keys UNIQUE (stock_location_id, item_code)
    , CONSTRAINT fk_stock__stock_location FOREIGN KEY (stock_location_id)
        REFERENCES stock_location (stock_location_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

这就是声明的样子：

WITH stock_location_upsert AS (
    INSERT INTO stock_location (
        site_code
        , location_code
        , status
    ) VALUES (
        inSiteCode
        , inLocationCode
        , inStatus
    )
    ON CONFLICT ON CONSTRAINT ui_stock_location__keys
        DO UPDATE SET
            status = inStatus
    RETURNING stock_location_id
)
, stock_upsert AS (
    INSERT INTO stock (
        stock_location_id
        , item_code
        , quantity
    )
    SELECT
        slo.stock_location_id
        , inItemCode
        , inQuantity
    FROM stock_location_upsert slo
    ON CONFLICT ON CONSTRAINT ui_stock__keys
        DO UPDATE SET
            quantity = stock.quantity + inQuantity
        RETURNING stock_id, quantity
)
DELETE FROM stock stk
USING stock_upsert stk2
WHERE stk.stock_id = stk2.stock_id
    AND stk.quantity = 0;

有谁知道发生了什么？

这是我正在尝试做的一个例子：

DROP TABLE IF EXISTS test1;

CREATE TABLE IF  NOT EXISTS test1 (
    id serial
    , code VARCHAR(10) NOT NULL
    , description VARCHAR(100) NOT NULL
    , quantity INT NOT NULL
    , CONSTRAINT pk_test1 PRIMARY KEY (id)
    , CONSTRAINT ui_test1 UNIQUE (code)
);

-- UPSERT
WITH test1_upsert AS (
    INSERT INTO test1 (
        code, description, quantity
    ) VALUES (
        '01', 'DESC 01', 1
    ) 
    ON CONFLICT ON CONSTRAINT ui_test1 
        DO UPDATE SET
            description = 'DESC 02'
            , quantity = 0
    RETURNING test1.id, test1.quantity
)
DELETE FROM test1 
USING test1_upsert
WHERE test1.id = test1_upsert.id
    AND test1_upsert.quantity = 0;

UPSERT 命令第二次运行时，一旦数量将更新为零，它应该从 test1 中删除记录。

说得通？

标签： sqlpostgresqlcommon-table-expressionsql-delete

解决方案

在这里，DELETE它按照设计的方式工作。答案实际上非常简单明了并且有文档记录。几年前我也经历过同样的行为。

您的删除实际上并未删除数据的原因是因为您的 where 条件与删除语句所见的表中存储的内容不匹配。

CTE(Common Table Expression) 中的所有子语句都使用相同的数据快照执行，因此它们看不到其他语句对目标表的影响。在这种情况下，当您运行UPDATEthen时DELETE，该DELETE语句会看到相同的数据UPDATE，而不会看到该UPDATE语句修改的更新数据。

你怎么能解决这个问题？您需要将 UPDATE & DELETE 分成两个独立的语句。

如果您需要传递有关删除内容的信息，例如（1）创建一个临时表并插入已更新的数据主键，以便您可以在后面的查询中加入该主键（DELETE 基于已更新）。(2)您可以通过简单地在更新的表中添加一列并更改其值以标记更新的行来获得相同的结果，或者(3)无论您喜欢它来完成工作。您应该通过上面的示例了解需要做什么。

引用手册来支持我的发现： 7.8.2。WITH 中的数据修改语句

WITH 中的子语句彼此同时执行，并与主查询同时执行。因此，当在 WITH 中使用数据修改语句时，指定更新实际发生的顺序是不可预测的。所有语句都使用同一个快照执行（参见第 13 章），因此它们无法“看到”彼此对目标表的影响。

(...) 这也适用于删除已在同一语句中更新的行：仅执行更新

推荐阅读