Why PostgreSQL CTE with DELETE is not working?

Question

I was trying to delete a record from my stock table if the update in the same table results in quantity 0 using two CTEs. The upserts are working, but the delete is not generating the result I was expecting. the quantity in stock table is changing to zero but the record is not being deleted. Table structure:

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
);

This is how the statement looks like:

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;

Does anyone know what's going on?

This is an example of what I'm trying to do:

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;

The second time the UPSERT command runs, it should delete the record from test1 once the quantity will be updated to zero.

Makes sense?

What is not working? Add some example of data and data structure pls. — Grzegorz Grabek
– Grzegorz Grabek, Commented Oct 21, 2018 at 20:51
This is not needed to answer the question, but this is invalid statement (there's no source for the data): INSERT INTO stock_location (site_code, location_code, status) VALUES (inSiteCode, inLocationCode, inStatus) I imagine this is passed by procedure's arguments, but that should also be included in your question or changed to static values. — Kamil Gosciminski
– Kamil Gosciminski, Commented Oct 21, 2018 at 22:24

Kamil Gosciminski · Accepted Answer · 2018-10-22 06:12:05Z

6

Here, DELETE is working in the way it was designed to work. The answer is actually pretty straightforward and documented. I've experienced the same behaviour years ago.

The reason your delete is not actually removing the data is because your where condition doesn't match with what's stored inside the table as far as what the delete statement sees.

All sub-statements within CTE (Common Table Expression) are executed with the same snapshot of data, so they can't see other statement effect on target table. In this case, when you run UPDATE and then DELETE, the DELETE statement sees the same data that UPDATE did, and doesn't see the updated data that UPDATE statement modified.

How can you work around that? You need to separate UPDATE & DELETE into two independent statements.

In case you need to pass the information about what to delete you could for example (1) create a temporary table and insert the data primary key that has been updated so that you can join to that in your latter query (DELETE based on data that was UPDATEd). (2) You could achieve the same result by simply adding a column within the updated table and changing its value to mark updated rows or (3) however you like it to get the job done. You should get the feeling of what needs to be done by above examples.

Quoting the manual to support my findings: 7.8.2. Data-Modifying Statements in WITH

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables.

(...) This also applies to deleting a row that was already updated in the same statement: only the update is performed

edited Oct 22, 2018 at 6:12

answered Oct 21, 2018 at 22:13

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SABER Over a year ago

if that is the case , then what happen when we use the output of one cte in another cte, does it wait till the execution of previous cte, so that it can use the output of previous cte?

Kamil Gosciminski Over a year ago

@SABER-FICTIONALCHARACTER in cases where you're using RETURNING clause and then calling one CTE from within another it has to be performed before that one since if you're building up a result-set which is later being used in let's say an update statement, it has to know what to run it on. In it's nature, though, CTEs are meant to be executed concurrently.

Felipe Pilon Over a year ago

Many thanks @KamilGosciminski. Actually I saw this documentation but I didn't understand very well. Now it's more clear for me. I changed my function now to verify and delete it in another command after the CTE.

Alexi Theodore · Accepted Answer · 2021-05-11 23:20:25Z

Adding to the helpful explanation above... Whenever possible it is absolutely best to break out modifying procedures into their own statements.

However, when the CTE has multiple modifying procedures that reference the same subquery and temporary tables are unideal (such as in stored procedures) then you just need a good solution.

In that case if you'd like a simple trick about how to go about ensuring a bit of order, consider this example:

WITH
    to_insert AS
(
SELECT
    *
FROM new_values
)
,   first AS
(
DELETE FROM some_table
WHERE
    id in (SELECT id FROM to_insert)
RETURNING *
)
INSERT INTO some_other_table
SELECT * FROM new_values
WHERE
    exists (SELECT count(*) FROM first)
;

The trick here is the exists (SELECT count(*) FROM first) part which must be executed first before the insert can happen. This is a way (which I wouldn't consider too hacky) to enforce an order while keeping everything within one CTE.

But this is just the concept - there are more optimal ways of doing the same thing for a given context.

Collectives™ on Stack Overflow

Why PostgreSQL CTE with DELETE is not working?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related