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:
================================================================
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 <nag...@sraoss.co.jp>
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 297b6ee715..c596bd80ea 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3241,6 +3241,11 @@ _copyDeleteStmt(const DeleteStmt *from)
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
+ COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(limitOffset);
+ COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
+
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index f537d3eb96..84f509b57b 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1050,6 +1050,11 @@ _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b)
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
+ COMPARE_NODE_FIELD(sortClause);
+ COMPARE_NODE_FIELD(limitOffset);
+ COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
+
return true;
}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index e276264882..8a201749bf 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -3668,6 +3668,12 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->withClause, context))
return true;
+ if (walker(stmt->sortClause, context))
+ return true;
+ if (walker(stmt->limitOffset, context))
+ return true;
+ if (walker(stmt->limitCount, context))
+ return true;
}
break;
case T_UpdateStmt:
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 146ee8dd1e..015e879f7a 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -471,6 +471,12 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ qry->sortClause = transformSortClause(pstate,
+ stmt->sortClause,
+ &qry->targetList,
+ EXPR_KIND_ORDER_BY,
+ false /* allow SQL92 rules */ );
+
qry->returningList = transformReturningList(pstate, stmt->returningList);
/* done building the range table and jointree */
@@ -482,6 +488,15 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qry->hasTargetSRFs = pstate->p_hasTargetSRFs;
qry->hasAggs = pstate->p_hasAggs;
+ /* transform LIMIT */
+ qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+ EXPR_KIND_OFFSET, "OFFSET",
+ stmt->limitOption);
+ qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+ EXPR_KIND_LIMIT, "LIMIT",
+ stmt->limitOption);
+ qry->limitOption = stmt->limitOption;
+
assign_query_collations(pstate, qry);
/* this must be done after collations, for reliable comparison of exprs */
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 86ce33bd97..0c6d11c23c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11207,6 +11207,7 @@ returning_clause:
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
using_clause where_or_current_clause returning_clause
+ opt_sort_clause opt_select_limit
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
@@ -11214,6 +11215,20 @@ DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
n->whereClause = $6;
n->returningList = $7;
n->withClause = $1;
+
+ n->sortClause = $8;
+ if ($9)
+ {
+ n->limitOffset = $9->limitOffset;
+ n->limitCount = $9->limitCount;
+ if (!n->sortClause &&
+ $9->limitOption == LIMIT_OPTION_WITH_TIES)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("WITH TIES cannot be specified without ORDER BY clause")));
+ n->limitOption = $9->limitOption;
+ }
+
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 067138e6b5..48cf65d032 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1610,6 +1610,11 @@ typedef struct DeleteStmt
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
+
+ List *sortClause; /* sort clause (a list of SortBy's) */
+ Node *limitOffset; /* # of result tuples to skip */
+ Node *limitCount; /* # of result tuples to return */
+ LimitOption limitOption; /* limit type */
} DeleteStmt;
/* ----------------------
