Greetings,
Based on the feedback on my previous patch, I've separated only the
LIMIT part into its own feature. This version plays nicely with
inheritance. The intended use is splitting up big UPDATEs and DELETEs
into batches more easily and efficiently.
♜
*** a/doc/src/sgml/ref/delete.sgml
--- b/doc/src/sgml/ref/delete.sgml
***************
*** 25,30 **** PostgreSQL documentation
--- 25,31 ----
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable>
[ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
[ USING <replaceable class="PARAMETER">using_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE
CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
+ [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ RETURNING * | <replaceable
class="parameter">output_expression</replaceable> [ [ AS ] <replaceable
class="parameter">output_name</replaceable> ] [, ...] ]
</synopsis>
</refsynopsisdiv>
***************
*** 56,61 **** DELETE FROM [ ONLY ] <replaceable
class="PARAMETER">table_name</replaceable> [ *
--- 57,70 ----
</para>
<para>
+ If the <literal>LIMIT</> (or <literal>FETCH FIRST</>) clause
+ is present, processing will stop after the system has deleted the
+ specified amount of rows. Unlike in <literal>SELECT</>, the
+ <literal>OFFSET</literal> clause is not available in
+ <literal>DELETE</>.
+ </para>
+
+ <para>
The optional <literal>RETURNING</> clause causes <command>DELETE</>
to compute and return value(s) based on each row actually deleted.
Any expression using the table's columns, and/or columns of other
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 29,34 **** UPDATE [ ONLY ] <replaceable
class="PARAMETER">table_name</replaceable> [ * ] [
--- 29,35 ----
} [, ...]
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE
CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
+ [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ RETURNING * | <replaceable
class="parameter">output_expression</replaceable> [ [ AS ] <replaceable
class="parameter">output_name</replaceable> ] [, ...] ]
</synopsis>
</refsynopsisdiv>
***************
*** 51,56 **** UPDATE [ ONLY ] <replaceable
class="PARAMETER">table_name</replaceable> [ * ] [
--- 52,66 ----
circumstances.
</para>
+
+ <para>
+ If the <literal>LIMIT</> (or <literal>FETCH FIRST</>) clause
+ is present, processing will stop after the system has updated
+ the specified amount of rows. Unlike in <literal>SELECT</>, the
+ <literal>OFFSET</literal> clause is not available in
+ <literal>UPDATE</>.
+ </para>
+
<para>
The optional <literal>RETURNING</> clause causes <command>UPDATE</>
to compute and return value(s) based on each row actually updated.
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 323,329 **** ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
! bool canSetTag)
{
ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
--- 323,329 ----
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
! int64_t *processed)
{
ResultRelInfo *resultRelInfo;
Relation resultRelationDesc;
***************
*** 480,487 **** ldelete:;
*/
}
! if (canSetTag)
! (estate->es_processed)++;
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple);
--- 480,486 ----
*/
}
! (*processed)++;
/* AFTER ROW DELETE Triggers */
ExecARDeleteTriggers(estate, resultRelInfo, tupleid, oldtuple);
***************
*** 572,578 **** ExecUpdate(ItemPointer tupleid,
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
! bool canSetTag)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
--- 571,577 ----
TupleTableSlot *planSlot,
EPQState *epqstate,
EState *estate,
! int64_t *processed)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
***************
*** 771,778 **** lreplace:;
estate);
}
! if (canSetTag)
! (estate->es_processed)++;
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(estate, resultRelInfo, tupleid, oldtuple, tuple,
--- 770,776 ----
estate);
}
! (*processed)++;
/* AFTER ROW UPDATE Triggers */
ExecARUpdateTriggers(estate, resultRelInfo, tupleid, oldtuple, tuple,
***************
*** 885,896 **** ExecModifyTable(ModifyTableState *node)
return NULL;
/*
! * On first call, fire BEFORE STATEMENT triggers before proceeding.
*/
! if (node->fireBSTriggers)
{
fireBSTriggers(node);
! node->fireBSTriggers = false;
}
/* Preload local variables */
--- 883,919 ----
return NULL;
/*
! * On first call, evaluate the LIMIT expression if there is one, and
fire
! * BEFORE STATEMENT triggers before proceeding.
*/
! if (node->firstCall)
{
+ if (node->limitCount)
+ {
+ ExprContext *econtext = node->ps.ps_ExprContext;
+ Datum val;
+ bool isNull;
+
+ val = ExecEvalExprSwitchContext(node->limitCount,
+
econtext,
+
&isNull,
+
NULL);
+ if (isNull)
+ node->maxProcessed = -1;
+ else
+ {
+ node->maxProcessed = DatumGetInt64(val);
+ if (node->maxProcessed < 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
+ errmsg("LIMIT must not
be negative")));
+ }
+ }
+ else
+ node->maxProcessed = -1;
+
fireBSTriggers(node);
! node->firstCall = false;
}
/* Preload local variables */
***************
*** 916,921 **** ExecModifyTable(ModifyTableState *node)
--- 939,955 ----
for (;;)
{
/*
+ * Check that we haven't hit the LIMIT yet, if one was
specified. We
+ * must do this inside the loop in case a RETURNING clause was
not
+ * present.
+ */
+ if (node->processed == node->maxProcessed)
+ {
+ node->mt_done = true;
+ break;
+ }
+
+ /*
* Reset the per-output-tuple exprcontext. This is needed
because
* triggers expect to use that context as workspace. It's a
bit ugly
* to do this below the top level of the plan, however. We
might need
***************
*** 1023,1036 **** ExecModifyTable(ModifyTableState *node)
{
case CMD_INSERT:
slot = ExecInsert(slot, planSlot, estate,
node->canSetTag);
break;
case CMD_UPDATE:
slot = ExecUpdate(tupleid, oldtuple, slot,
planSlot,
!
&node->mt_epqstate, estate, node->canSetTag);
break;
case CMD_DELETE:
slot = ExecDelete(tupleid, oldtuple, planSlot,
!
&node->mt_epqstate, estate, node->canSetTag);
break;
default:
elog(ERROR, "unknown operation");
--- 1057,1077 ----
{
case CMD_INSERT:
slot = ExecInsert(slot, planSlot, estate,
node->canSetTag);
+ /* estate->es_processed already updated */
break;
case CMD_UPDATE:
slot = ExecUpdate(tupleid, oldtuple, slot,
planSlot,
!
&node->mt_epqstate, estate, &node->processed);
! /* keep EState up to date */
! if (node->canSetTag)
! estate->es_processed = node->processed;
break;
case CMD_DELETE:
slot = ExecDelete(tupleid, oldtuple, planSlot,
!
&node->mt_epqstate, estate, &node->processed);
! /* keep EState up to date */
! if (node->canSetTag)
! estate->es_processed = node->processed;
break;
default:
elog(ERROR, "unknown operation");
***************
*** 1091,1096 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int
eflags)
--- 1132,1138 ----
mtstate->operation = operation;
mtstate->canSetTag = node->canSetTag;
+ mtstate->processed = 0;
mtstate->mt_done = false;
mtstate->mt_plans = (PlanState **) palloc0(sizeof(PlanState *) *
nplans);
***************
*** 1100,1106 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int
eflags)
/* set up epqstate with dummy subplan data for the moment */
EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, NIL,
node->epqParam);
! mtstate->fireBSTriggers = true;
/*
* call ExecInitNode on each of the plans to be executed and save the
--- 1142,1148 ----
/* set up epqstate with dummy subplan data for the moment */
EvalPlanQualInit(&mtstate->mt_epqstate, estate, NULL, NIL,
node->epqParam);
! mtstate->firstCall = true;
/*
* call ExecInitNode on each of the plans to be executed and save the
***************
*** 1381,1386 **** ExecInitModifyTable(ModifyTable *node, EState *estate, int
eflags)
--- 1423,1441 ----
estate->es_trig_tuple_slot = ExecInitExtraTupleSlot(estate);
/*
+ * If we had a LIMIT, initialize it. We will evaluate it before the
first
+ * row is processed.
+ */
+ if (node->limitCount)
+ {
+ /* also create a context if RETURNING didn't already do that */
+ if (!mtstate->ps.ps_ExprContext)
+ mtstate->ps.ps_ExprContext = CreateExprContext(estate);
+ mtstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
+
(PlanState *) mtstate);
+ }
+
+ /*
* Lastly, if this is not the primary (canSetTag) ModifyTable node, add
it
* to estate->es_auxmodifytables so that it will be run to completion by
* ExecPostprocessPlan. (It'd actually work fine to add the primary
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***************
*** 183,188 **** _copyModifyTable(const ModifyTable *from)
--- 183,189 ----
COPY_NODE_FIELD(fdwPrivLists);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(epqParam);
+ COPY_NODE_FIELD(limitCount);
return newnode;
}
***************
*** 2532,2537 **** _copyDeleteStmt(const DeleteStmt *from)
--- 2533,2539 ----
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
+ COPY_NODE_FIELD(limitClause);
return newnode;
}
***************
*** 2547,2552 **** _copyUpdateStmt(const UpdateStmt *from)
--- 2549,2555 ----
COPY_NODE_FIELD(fromClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
+ COPY_NODE_FIELD(limitClause);
return newnode;
}
*** a/src/backend/nodes/equalfuncs.c
--- b/src/backend/nodes/equalfuncs.c
***************
*** 897,902 **** _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b)
--- 897,903 ----
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
+ COMPARE_NODE_FIELD(limitClause);
return true;
}
***************
*** 910,915 **** _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b)
--- 911,917 ----
COMPARE_NODE_FIELD(fromClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
+ COMPARE_NODE_FIELD(limitClause);
return true;
}
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 2988,2993 **** raw_expression_tree_walker(Node *node,
--- 2988,2995 ----
return true;
if (walker(stmt->withClause, context))
return true;
+ if (walker(stmt->limitClause, context))
+ return true;
}
break;
case T_UpdateStmt:
***************
*** 3006,3011 **** raw_expression_tree_walker(Node *node,
--- 3008,3015 ----
return true;
if (walker(stmt->withClause, context))
return true;
+ if (walker(stmt->limitClause, context))
+ return true;
}
break;
case T_SelectStmt:
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 337,342 **** _outModifyTable(StringInfo str, const ModifyTable *node)
--- 337,343 ----
WRITE_NODE_FIELD(fdwPrivLists);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(epqParam);
+ WRITE_NODE_FIELD(limitCount);
}
static void
*** a/src/backend/optimizer/plan/createplan.c
--- b/src/backend/optimizer/plan/createplan.c
***************
*** 4719,4725 **** make_modifytable(PlannerInfo *root,
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List
*returningLists,
! List *rowMarks, int epqParam)
{
ModifyTable *node = makeNode(ModifyTable);
Plan *plan = &node->plan;
--- 4719,4725 ----
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List
*returningLists,
! List *rowMarks, int epqParam, Node *limitCount)
{
ModifyTable *node = makeNode(ModifyTable);
Plan *plan = &node->plan;
***************
*** 4772,4777 **** make_modifytable(PlannerInfo *root,
--- 4772,4778 ----
node->returningLists = returningLists;
node->rowMarks = rowMarks;
node->epqParam = epqParam;
+ node->limitCount = limitCount;
/*
* For each result relation that is a foreign table, allow the FDW to
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 610,616 **** subquery_planner(PlannerGlobal *glob, Query *parse,
withCheckOptionLists,
returningLists,
rowMarks,
!
SS_assign_special_param(root));
}
}
--- 610,617 ----
withCheckOptionLists,
returningLists,
rowMarks,
!
SS_assign_special_param(root),
!
parse->limitCount);
}
}
***************
*** 1054,1060 **** inheritance_planner(PlannerInfo *root)
withCheckOptionLists,
returningLists,
rowMarks,
!
SS_assign_special_param(root));
}
/*--------------------
--- 1055,1062 ----
withCheckOptionLists,
returningLists,
rowMarks,
!
SS_assign_special_param(root),
!
parse->limitCount);
}
/*--------------------
***************
*** 2473,2478 **** limit_needed(Query *parse)
--- 2475,2485 ----
{
Node *node;
+ /* ModifyTable handles the LIMIT for us if this is an UPDATE or a
DELETE */
+ if (parse->commandType == CMD_UPDATE ||
+ parse->commandType == CMD_DELETE)
+ return false;
+
node = parse->limitCount;
if (node)
{
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 386,391 **** transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
--- 386,394 ----
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+
EXPR_KIND_LIMIT, "LIMIT");
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
/* done building the range table and jointree */
***************
*** 1947,1952 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
--- 1950,1958 ----
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+
EXPR_KIND_LIMIT, "LIMIT");
+
qry->returningList = transformReturningList(pstate,
stmt->returningList);
qry->rtable = pstate->p_rtable;
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 9163,9175 **** returning_clause:
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
! using_clause where_or_current_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
n->usingClause = $5;
n->whereClause = $6;
! n->returningList = $7;
n->withClause = $1;
$$ = (Node *)n;
}
--- 9163,9181 ----
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
! using_clause where_or_current_clause opt_select_limit
returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
n->usingClause = $5;
n->whereClause = $6;
! if (linitial($7) != NULL)
! ereport(ERROR,
!
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("OFFSET
is not supported with DELETE"),
!
parser_errposition(@7)));
! n->limitClause = lsecond($7);
! n->returningList = $8;
n->withClause = $1;
$$ = (Node *)n;
}
***************
*** 9229,9234 **** UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
--- 9235,9241 ----
SET set_clause_list
from_clause
where_or_current_clause
+ opt_select_limit
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
***************
*** 9236,9242 **** UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
n->targetList = $5;
n->fromClause = $6;
n->whereClause = $7;
! n->returningList = $8;
n->withClause = $1;
$$ = (Node *)n;
}
--- 9243,9255 ----
n->targetList = $5;
n->fromClause = $6;
n->whereClause = $7;
! if (linitial($8) != NULL)
! ereport(ERROR,
!
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("OFFSET
is not supported with UPDATE"),
!
parser_errposition(@8)));
! n->limitClause = lsecond($8);
! n->returningList = $9;
n->withClause = $1;
$$ = (Node *)n;
}
*** a/src/include/nodes/execnodes.h
--- b/src/include/nodes/execnodes.h
***************
*** 1088,1094 **** typedef struct ModifyTableState
ResultRelInfo *resultRelInfo; /* per-subplan target relations
*/
List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */
EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks
*/
! bool fireBSTriggers; /* do we need to fire stmt triggers? */
} ModifyTableState;
/* ----------------
--- 1088,1097 ----
ResultRelInfo *resultRelInfo; /* per-subplan target relations
*/
List **mt_arowmarks; /* per-subplan ExecAuxRowMark lists */
EPQState mt_epqstate; /* for evaluating EvalPlanQual rechecks
*/
! bool firstCall; /* is this our first time
through? */
! ExprState *limitCount; /* LIMIT expression, if any */
! int64_t maxProcessed; /* maximum number of rows we're allowed to
process */
! int64_t processed; /* number of rows we've processed so far */
} ModifyTableState;
/* ----------------
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 1060,1065 **** typedef struct DeleteStmt
--- 1060,1066 ----
Node *whereClause; /* qualifications */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
+ Node *limitClause; /* LIMIT clause */
} DeleteStmt;
/* ----------------------
***************
*** 1075,1080 **** typedef struct UpdateStmt
--- 1076,1082 ----
List *fromClause; /* optional from clause for more tables
*/
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
+ Node *limitClause; /* LIMIT clause */
} UpdateStmt;
/* ----------------------
*** a/src/include/nodes/plannodes.h
--- b/src/include/nodes/plannodes.h
***************
*** 177,182 **** typedef struct ModifyTable
--- 177,183 ----
List *fdwPrivLists; /* per-target-table FDW private data
lists */
List *rowMarks; /* PlanRowMarks (non-locking only) */
int epqParam; /* ID of Param for
EvalPlanQual re-eval */
+ Node *limitCount; /* maximum number of rows to process */
} ModifyTable;
/* ----------------
*** a/src/include/optimizer/planmain.h
--- b/src/include/optimizer/planmain.h
***************
*** 84,90 **** extern ModifyTable *make_modifytable(PlannerInfo *root,
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List
*returningLists,
! List *rowMarks, int epqParam);
extern bool is_projection_capable_plan(Plan *plan);
/*
--- 84,90 ----
CmdType operation, bool canSetTag,
List *resultRelations, List *subplans,
List *withCheckOptionLists, List
*returningLists,
! List *rowMarks, int epqParam, Node
*limitCount);
extern bool is_projection_capable_plan(Plan *plan);
/*
*** a/src/test/regress/expected/delete.out
--- b/src/test/regress/expected/delete.out
***************
*** 31,33 **** SELECT id, a, char_length(b) FROM delete_test;
--- 31,104 ----
(1 row)
DROP TABLE delete_test;
+ -- LIMIT
+ CREATE TABLE delete_test AS
+ SELECT i*10 AS a FROM generate_series(1, 10) i;
+ DELETE FROM delete_test LIMIT -1;
+ ERROR: LIMIT must not be negative
+ DELETE FROM delete_test OFFSET 0;
+ ERROR: OFFSET is not supported with DELETE
+ LINE 1: DELETE FROM delete_test OFFSET 0;
+ ^
+ DELETE FROM delete_test LIMIT 0;
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 10
+ (1 row)
+
+ DELETE FROM delete_test LIMIT 1;
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 9
+ (1 row)
+
+ DELETE FROM delete_test LIMIT (SELECT 1);
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 8
+ (1 row)
+
+ -- test against partitioned table
+ CREATE TABLE delete_test_child(a int) INHERITS (delete_test);
+ NOTICE: merging column "a" with inherited definition
+ INSERT INTO delete_test_child VALUES (5), (15), (25);
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 11
+ (1 row)
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 6
+ (1 row)
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 1
+ (1 row)
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+ count
+ -------
+ 0
+ (1 row)
+
+ EXPLAIN (COSTS OFF) DELETE FROM delete_test LIMIT 1;
+ QUERY PLAN
+ -------------------------------------
+ Delete on delete_test
+ -> Seq Scan on delete_test
+ -> Seq Scan on delete_test_child
+ (3 rows)
+
+ DROP TABLE delete_test CASCADE;
+ NOTICE: drop cascades to table delete_test_child
*** a/src/test/regress/expected/update.out
--- b/src/test/regress/expected/update.out
***************
*** 148,150 **** SELECT a, b, char_length(c) FROM update_test;
--- 148,221 ----
(4 rows)
DROP TABLE update_test;
+ -- LIMIT
+ CREATE TABLE update_test AS
+ SELECT i*10 AS a FROM generate_series(1, 10) i;
+ UPDATE update_test SET a = 0 LIMIT -1;
+ ERROR: LIMIT must not be negative
+ UPDATE update_test SET a = 0 OFFSET 0;
+ ERROR: OFFSET is not supported with UPDATE
+ LINE 1: UPDATE update_test SET a = 0 OFFSET 0;
+ ^
+ UPDATE update_test SET a = -1 LIMIT 0;
+ SELECT a FROM update_test WHERE a = -1;
+ a
+ ---
+ (0 rows)
+
+ UPDATE update_test SET a = -1 LIMIT 1;
+ SELECT a FROM update_test WHERE a = -1;
+ a
+ ----
+ -1
+ (1 row)
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT (SELECT 1);
+ SELECT a FROM update_test WHERE a = -1;
+ a
+ ----
+ -1
+ -1
+ (2 rows)
+
+ -- test against partitioned table
+ CREATE TABLE update_test_child(a int) INHERITS (update_test);
+ NOTICE: merging column "a" with inherited definition
+ INSERT INTO update_test_child VALUES (5), (15), (25);
+ SELECT count(*) FROM update_test WHERE a <> -1;
+ count
+ -------
+ 11
+ (1 row)
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+ count
+ -------
+ 6
+ (1 row)
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+ count
+ -------
+ 1
+ (1 row)
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+ count
+ -------
+ 0
+ (1 row)
+
+ EXPLAIN (COSTS OFF) UPDATE update_test SET a = 10 LIMIT 1;
+ QUERY PLAN
+ -------------------------------------
+ Update on update_test
+ -> Seq Scan on update_test
+ -> Seq Scan on update_test_child
+ (3 rows)
+
+ DROP TABLE update_test CASCADE;
+ NOTICE: drop cascades to table update_test_child
*** a/src/test/regress/sql/delete.sql
--- b/src/test/regress/sql/delete.sql
***************
*** 23,25 **** DELETE FROM delete_test WHERE a > 25;
--- 23,61 ----
SELECT id, a, char_length(b) FROM delete_test;
DROP TABLE delete_test;
+
+ -- LIMIT
+
+ CREATE TABLE delete_test AS
+ SELECT i*10 AS a FROM generate_series(1, 10) i;
+
+ DELETE FROM delete_test LIMIT -1;
+
+ DELETE FROM delete_test OFFSET 0;
+
+ DELETE FROM delete_test LIMIT 0;
+ SELECT count(*) FROM delete_test;
+
+ DELETE FROM delete_test LIMIT 1;
+ SELECT count(*) FROM delete_test;
+
+ DELETE FROM delete_test LIMIT (SELECT 1);
+ SELECT count(*) FROM delete_test;
+
+ -- test against partitioned table
+ CREATE TABLE delete_test_child(a int) INHERITS (delete_test);
+ INSERT INTO delete_test_child VALUES (5), (15), (25);
+ SELECT count(*) FROM delete_test;
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+
+ DELETE FROM delete_test LIMIT 5;
+ SELECT count(*) FROM delete_test;
+
+ EXPLAIN (COSTS OFF) DELETE FROM delete_test LIMIT 1;
+
+ DROP TABLE delete_test CASCADE;
*** a/src/test/regress/sql/update.sql
--- b/src/test/regress/sql/update.sql
***************
*** 75,77 **** UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
--- 75,113 ----
SELECT a, b, char_length(c) FROM update_test;
DROP TABLE update_test;
+
+ -- LIMIT
+
+ CREATE TABLE update_test AS
+ SELECT i*10 AS a FROM generate_series(1, 10) i;
+
+ UPDATE update_test SET a = 0 LIMIT -1;
+
+ UPDATE update_test SET a = 0 OFFSET 0;
+
+ UPDATE update_test SET a = -1 LIMIT 0;
+ SELECT a FROM update_test WHERE a = -1;
+
+ UPDATE update_test SET a = -1 LIMIT 1;
+ SELECT a FROM update_test WHERE a = -1;
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT (SELECT 1);
+ SELECT a FROM update_test WHERE a = -1;
+
+ -- test against partitioned table
+ CREATE TABLE update_test_child(a int) INHERITS (update_test);
+ INSERT INTO update_test_child VALUES (5), (15), (25);
+ SELECT count(*) FROM update_test WHERE a <> -1;
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+
+ UPDATE update_test SET a = -1 WHERE a <> -1 LIMIT 5;
+ SELECT count(*) FROM update_test WHERE a <> -1;
+
+ EXPLAIN (COSTS OFF) UPDATE update_test SET a = 10 LIMIT 1;
+
+ DROP TABLE update_test CASCADE;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
