DELETE deletes more than one rows when LIMIT is used in the USING clause

From: Onur Tirtir <Onur(dot)Tirtir(at)microsoft(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: DELETE deletes more than one rows when LIMIT is used in the USING clause
Date: 2022-06-13 14:13:16
Message-ID: VI1PR83MB0398B72C2EDE2768892C5C11E9AB9@VI1PR83MB0398.EURPRD83.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello everyone,

Let me first share some information regarding my setup:
Platform: Ubuntu 20.04 - x86_64 (on wsl2)
Kernel version: 5.10.102.1-microsoft-standard-WSL2
GCC version: gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0
PostgreSQL version: 14.3: compiled from the source using following flags:
--enable-debug
--enable-depend
--enable-cassert
CFLAGS=-ggdb -O0 -g -fno-omit-frame-pointer
--with-openssl
--with-libxml
--with-icu
--with-libxslt

With the following table schema, DELETE deletes more than one rows unexpectedly when LIMIT is specified in the USING clause of the DELETE statement.
Surprisingly, this happens only when there is a primary key based on some columns used for filtering in DELETE statement:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (int_col_1 int, int_col_2 int, int_col_3 int);
INSERT INTO my_table VALUES (1, 1, 100), (1, 2, 101);

BEGIN;
-- deletes 1 tuple, correct
DELETE FROM my_table d
USING (
SELECT int_col_3
FROM my_table
WHERE my_table.int_col_1 = 1
LIMIT 1 FOR UPDATE OF my_table
) s
WHERE d.int_col_3 = s.int_col_3 AND d.int_col_1 = 1;
ROLLBACK;

-- now define the pkey and let's see what happens with the same DELETE statement
ALTER TABLE my_table ADD CONSTRAINT my_table_pkey PRIMARY KEY (int_col_1, int_col_2);

BEGIN;
-- deletes 2 tuples, incorrect
DELETE FROM my_table d
USING (
SELECT int_col_3
FROM my_table
WHERE my_table.int_col_1 = 1
LIMIT 1 FOR UPDATE OF my_table
) s
WHERE d.int_col_3 = s.int_col_3 AND d.int_col_1 = 1;
ROLLBACK;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-06-13 14:52:16 Re: BUG #17504: psql --single-transaction -vON_ERROR_STOP=1 still commits after client-side error
Previous Message Álvaro Herrera 2022-06-13 11:09:48 Re: Using PQexecQuery in pipeline mode produces unexpected Close messages