PG unexpected behaviour with CTE and DML statements

user_13298819 0
Viewed 46

I have stumbled upon this, for keeping the problem easy to explain i have put a sample code below

Table:

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

Data Fill:

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

Statement in Question:

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".*

Expectation:

It should delete 2 rows with 'L' in "Text1" col

Actual:

It deletes just one row(looking at RETURNING statement)

Observation:

If i remove Using clause everything behaves as expected

So what am i missing here? or is this a bug in snapshots used by PG?

postgresql
1 Answers

The specific outcome in this case is surprising, but the underlying problem is documented:

Trying to update the same row twice in a single statement is not supported. Only one of the modifications takes place, but it is not easy (and sometimes not possible) to reliably predict which one. This also applies to deleting a row that was already updated in the same statement: only the update is performed. Therefore you should generally avoid trying to modify a single row twice in a single statement. In particular avoid writing WITH sub-statements that could affect the same rows changed by the main statement or a sibling sub-statement. The effects of such a statement will not be predictable.

(Emphasis mine.)

In short: don't try to UPDATE and DELETE the same row in the same statement, else strange things may happen.

user_64643080
Related