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

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.

regards, tom lane