Yugo NAGATA <nag...@sraoss.co.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