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

Onur Tirtir <Onur(dot)Tirtir(at)microsoft(dot)com> writes:
> With the following table schema, DELETE deletes more than one rows unexpectedly when LIMIT is specified in the USING clause of the DELETE statement.

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 = 1
> LIMIT 1 FOR UPDATE OF my_table
> ) s
> WHERE d.int_col_3 = s.int_col_3 AND d.int_col_1 = 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