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

On Fri, 17 Dec 2021 09:47:18 +0900
Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> Hello hackers,
>
> 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.
>
> Attached is the concept patch. This enables the following
> operations:

After post this, I noticed that there are several similar
proposals in past:

https://www.postgresql.org/message-id/flat/AANLkTi%3D6fBZh9yZT7f7kKh%2BzmQngAyHgZWBPM3eiEMj1%40mail.gmail.com
https://www.postgresql.org/message-id/flat/1393112801.59251.YahooMailNeo%40web163006.mail.bf1.yahoo.com
https://www.postgresql.org/message-id/flat/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm%3Dmg%40mail.gmail.com
https://www.postgresql.org/message-id/flat/CALAY4q9fcrscybax7fg_uojFwjw_Wg0UMuSrf-FvN68SeSAPAA%40mail.gmail.com

Anyway, I'll review these threads before progressing it.

>
> ================================================================
> postgres=# select * from t order by i;
> i
> ----
> 1
> 2
> 2
> 2
> 2
> 5
> 10
> 20
> 33
> 35
> 53
> (11 rows)
>
> postgres=# delete from t where i = 2 limit 2;
> DELETE 2
> postgres=# select * from t order by i;
> i
> ----
> 1
> 2
> 2
> 5
> 10
> 20
> 33
> 35
> 53
> (9 rows)
>
> postgres=# delete from t order by i offset 3 limit 3;
> DELETE 3
> postgres=# select * from t order by i;
> i
> ----
> 1
> 2
> 2
> 33
> 35
> 53
> (6 rows)
> ================================================================
>
> Although we can do the similar operations using ctid and a subquery
> such as
>
> DELETE FROM t WHERE ctid IN (SELECT ctid FROM t WHERE ... ORDER BY ... LIMIT ...),
>
> it is more user friendly and intuitive to allow it in the DELETE syntax
> because ctid is a system column and most users may not be familiar with it.
>
> Although this is not allowed in the SQL standard, it is supported
> in MySQL[1]. DB2 also supports it although the syntax is somewhat
> strange.[2]
>
> Also, here seem to be some use cases. For example,
> - when you want to delete the specified number of rows from a table
> that doesn't have a primary key and contains tuple duplicated.
> - when you want to delete the bottom 10 items with bad scores
> (without using rank() window function).
> - when you want to delete only some of rows because it takes time
> to delete all of them.
>
> [1] https://dev.mysql.com/doc/refman/8.0/en/delete.html
> [2] https://www.dba-db2.com/2015/04/delete-first-1000-rows-in-a-db2-table-using-fetch-first.html
>
> How do you think it?
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>

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