Re: Allow DELETE to use ORDER BY and LIMIT/OFFSET

On Thu, 16 Dec 2021 22:17:58 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> writes:
> > We cannot use ORDER BY or LIMIT/OFFSET in the current
> > DELETE statement syntax, so all the row matching the
> > WHERE condition are deleted. However, the tuple retrieving
> > process of DELETE is basically same as SELECT statement,
> > so I think that we can also allow DELETE to use ORDER BY
> > and LIMIT/OFFSET.
>
> Indeed, this is technically possible, but we've rejected the idea
> before and I'm not aware of any reason to change our minds.
> The problem is that a partial DELETE is not very deterministic
> about which rows are deleted, and that does not seem like a
> great property for a data-updating command. (The same applies
> to UPDATE, which is why we don't allow these options in that
> command either.) The core issues are:
>
> * If the sort order is underspecified, or you omit ORDER BY
> entirely, then it's not clear which rows will be operated on.
> The LIMIT might stop after just some of the rows in a peer
> group, and you can't predict which ones.
>
> * UPDATE/DELETE necessarily involve the equivalent of SELECT
> FOR UPDATE, which may cause the rows to be ordered more
> surprisingly than you expected, ie the sort happens *before*
> rows are replaced by their latest versions, which might have
> different sort keys.
>
> We live with this amount of indeterminism in SELECT, but that
> doesn't make it a brilliant idea to allow it in UPDATE/DELETE.

Thank you for your explaining it!
I'm glad to understand why this idea is not good and has been rejected.

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>