On Thu, 16 Dec 2021 22:17:58 -0500 Tom Lane <t...@sss.pgh.pa.us> wrote:
> 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. 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 <nag...@sraoss.co.jp>