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?

Answer 1

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