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;