From: Tom Lane <tgl@sss.pgh.pa.us>
To: Onur Tirtir <Onur.Tirtir@microsoft.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: DELETE deletes more than one rows when LIMIT is used in the USING
 clause
In-reply-to: 
 <VI1PR83MB0398B72C2EDE2768892C5C11E9AB9@VI1PR83MB0398.EURPRD83.prod.outlook.com>
References: 
 <VI1PR83MB0398B72C2EDE2768892C5C11E9AB9@VI1PR83MB0398.EURPRD83.prod.outlook.com>
Comments: In-reply-to Onur Tirtir <Onur.Tirtir@microsoft.com>
	message dated "Mon, 13 Jun 2022 14:13:16 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3798785.1655133396.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 13 Jun 2022 11:16:36 -0400
Message-ID: <3798786.1655133396@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3798786.1655133396%40sss.pgh.pa.us>
Precedence: bulk

Onur Tirtir <Onur.Tirtir@microsoft.com> writes:
> With the following table schema, DELETE deletes more than one rows unexp=
ectedly when LIMIT is specified in the USING clause of the DELETE statemen=
t.

I do not see a bug here.  Your query is fundamentally unstable:

>     DELETE FROM my_table d
>     USING (
>         SELECT int_col_3
>         FROM my_table
>         WHERE my_table.int_col_1 =3D 1
>         LIMIT 1 FOR UPDATE OF my_table
>     ) s
>     WHERE d.int_col_3 =3D s.int_col_3 AND d.int_col_1 =3D 1;

The SELECT ... LIMIT query is underspecified, in that there are multiple
rows it could choose to return.  Worse, because it's FOR UPDATE, its
results are affected by whatever the DELETE may have already done: it
won't return an already-deleted row.  Thus, the query's behavior changes
depending on whether the planner happens to put the sub-select on the
inside or the outside of the nestloop join with "d".  I think the CREATE
INDEX command is just incidentally causing a statistics update that
switches the preferred plan shape.

Perhaps there's an argument that the planner should understand that the
sub-select's results are volatile and avoid executing it more than once.
But we haven't felt a need for such a restriction in the past, and this
example does nothing to convince me that one is needed now.  It looks
like a mighty ugly, brute-force hack; surely there's a simpler and better
solution to whatever your actual need is.

If you really do want to build a query that works this way, the approved
solution for ensuring the sub-select executes just once is to put it
in WITH ... AS MATERIALIZED.

			regards, tom lane