On Wed, Jan 9, 2019 at 8:18 PM Tomas Vondra <tomas.von...@2ndquadrant.com>
wrote:
>
> See the attached patch, which recomputes the count regularly. I don't
> claim the patch is committable or that it has no other bugs, but
> hopefully it shows what I meant.
>
>
I got only one issue it is not work well with cursor
postgres=# START TRANSACTION;
START TRANSACTION
postgres=# create table t as select i from generate_series(1,1000) s(i);
SELECT 1000
postgres=# declare c cursor for select * from t fetch first 5 percent rows
only;
DECLARE CURSOR
postgres=# fetch all in c;
ERROR: trying to store a minimal tuple into wrong type of slot
I am looking at it .
meanwhile i fix row estimation and cost and make create_ordered_paths
creation with no LIMIT consideration in PERCENTAGE case
regards
Surafel
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 4db8142afa..8491b7831a 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -44,7 +44,7 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
[ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
[ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
- [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
+ [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
<phrase>where <replaceable class="parameter">from_item</replaceable> can be one of:</phrase>
@@ -1397,7 +1397,7 @@ OFFSET <replaceable class="parameter">start</replaceable>
which <productname>PostgreSQL</productname> also supports. It is:
<synopsis>
OFFSET <replaceable class="parameter">start</replaceable> { ROW | ROWS }
-FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY
+FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] [ PERCENT ] { ROW | ROWS } ONLY
</synopsis>
In this syntax, the <replaceable class="parameter">start</replaceable>
or <replaceable class="parameter">count</replaceable> value is required by
@@ -1407,7 +1407,8 @@ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] {
ambiguity.
If <replaceable class="parameter">count</replaceable> is
omitted in a <literal>FETCH</literal> clause, it defaults to 1.
- <literal>ROW</literal>
+ with <literal>PERCENT</literal> count specifies the maximum number of rows to return
+ in percentage.<literal>ROW</literal>
and <literal>ROWS</literal> as well as <literal>FIRST</literal>
and <literal>NEXT</literal> are noise words that don't influence
the effects of these clauses.
diff --git a/src/backend/executor/nodeLimit.c b/src/backend/executor/nodeLimit.c
index baa669abe8..8fc70b1b5f 100644
--- a/src/backend/executor/nodeLimit.c
+++ b/src/backend/executor/nodeLimit.c
@@ -21,6 +21,8 @@
#include "postgres.h"
+#include <math.h>
+
#include "executor/executor.h"
#include "executor/nodeLimit.h"
#include "miscadmin.h"
@@ -44,6 +46,7 @@ ExecLimit(PlanState *pstate)
ScanDirection direction;
TupleTableSlot *slot;
PlanState *outerPlan;
+ slot = node->subSlot;
CHECK_FOR_INTERRUPTS();
@@ -81,7 +84,15 @@ ExecLimit(PlanState *pstate)
/*
* Check for empty window; if so, treat like empty subplan.
*/
- if (node->count <= 0 && !node->noCount)
+ if (node->limitOption == PERCENTAGE)
+ {
+ if (node->percent == 0.0)
+ {
+ node->lstate = LIMIT_EMPTY;
+ return NULL;
+ }
+ }
+ else if (node->count <= 0 && !node->noCount)
{
node->lstate = LIMIT_EMPTY;
return NULL;
@@ -122,6 +133,7 @@ ExecLimit(PlanState *pstate)
return NULL;
case LIMIT_INWINDOW:
+
if (ScanDirectionIsForward(direction))
{
/*
@@ -130,6 +142,32 @@ ExecLimit(PlanState *pstate)
* advancing the subplan or the position variable; but change
* the state machine state to record having done so.
*/
+
+ /*
+ * When in percentage mode, we need to see if we can get any
+ * additional rows from the subplan (enough to increase the
+ * node->count value).
+ */
+ if (node->limitOption == PERCENTAGE)
+ {
+ /* loop until the node->count increments */
+ while (node->position - node->offset >= node->count)
+ {
+ int64 cnt;
+
+ slot = ExecProcNode(outerPlan);
+ if (TupIsNull(slot))
+ break;
+
+ tuplestore_puttupleslot(node->totalTuple, slot);
+
+ /* plus 1, because the first tuple is returned from LIMIT_RESCAN */
+ cnt = 1 + tuplestore_tuple_count(node->totalTuple);
+
+ node->count = ceil(node->percent * cnt / 100.0);
+ }
+ }
+
if (!node->noCount &&
node->position - node->offset >= node->count)
{
@@ -145,6 +183,20 @@ ExecLimit(PlanState *pstate)
return NULL;
}
+ if (node->limitOption == PERCENTAGE)
+ {
+ while (node->position - node->offset < node->count)
+ {
+ if (tuplestore_gettupleslot(node->totalTuple, true, true, slot))
+ {
+ node->subSlot = slot;
+ node->position++;
+ }
+ }
+ }
+ else if (node->limitOption == EXACT_NUMBER)
+ {
+
/*
* Get next tuple from subplan, if any.
*/
@@ -156,6 +208,7 @@ ExecLimit(PlanState *pstate)
}
node->subSlot = slot;
node->position++;
+ }
}
else
{
@@ -278,17 +331,29 @@ recompute_limits(LimitState *node)
/* Interpret NULL count as no count (LIMIT ALL) */
if (isNull)
{
- node->count = 0;
+ node->count = 1;
node->noCount = true;
}
else
{
- node->count = DatumGetInt64(val);
- if (node->count < 0)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
- errmsg("LIMIT must not be negative")));
- node->noCount = false;
+ if (node->limitOption == PERCENTAGE)
+ {
+ /*
+ * We expect to return at least one row (unless there
+ * are no rows in the subplan), and we'll update this
+ * count later as we go.
+ */
+ node->count = 0;
+ node->percent = DatumGetFloat8(val);
+ }
+ else
+ {
+ node->count = DatumGetInt64(val);
+ if (node->count < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_ROW_COUNT_IN_LIMIT_CLAUSE),
+ errmsg("LIMIT must not be negative")));
+ }
}
}
else
@@ -311,7 +376,8 @@ recompute_limits(LimitState *node)
* must update the child node anyway, in case this is a rescan and the
* previous time we got a different result.
*/
- ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
+ if (node->limitOption == EXACT_NUMBER)
+ ExecSetTupleBound(compute_tuples_needed(node), outerPlanState(node));
}
/*
@@ -374,6 +440,9 @@ ExecInitLimit(Limit *node, EState *estate, int eflags)
(PlanState *) limitstate);
limitstate->limitCount = ExecInitExpr((Expr *) node->limitCount,
(PlanState *) limitstate);
+ limitstate->limitOption = node->limitOption;
+ if (node->limitOption == PERCENTAGE)
+ limitstate->totalTuple= tuplestore_begin_heap(true, false, work_mem);
/*
* Initialize result type.
@@ -405,6 +474,8 @@ ExecEndLimit(LimitState *node)
{
ExecFreeExprContext(&node->ps);
ExecEndNode(outerPlanState(node));
+ if (node->totalTuple!= NULL)
+ tuplestore_end(node->totalTuple);
}
@@ -424,4 +495,6 @@ ExecReScanLimit(LimitState *node)
*/
if (node->ps.lefttree->chgParam == NULL)
ExecReScan(node->ps.lefttree);
+ if (node->totalTuple!= NULL)
+ tuplestore_rescan(node->totalTuple);
}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 3eb7e95d64..16afcac059 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1136,6 +1136,7 @@ _copyLimit(const Limit *from)
*/
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
return newnode;
}
@@ -3021,6 +3022,7 @@ _copyQuery(const Query *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
COPY_NODE_FIELD(constraintDeps);
@@ -3105,6 +3107,7 @@ _copySelectStmt(const SelectStmt *from)
COPY_NODE_FIELD(sortClause);
COPY_NODE_FIELD(limitOffset);
COPY_NODE_FIELD(limitCount);
+ COPY_SCALAR_FIELD(limitOption);
COPY_NODE_FIELD(lockingClause);
COPY_NODE_FIELD(withClause);
COPY_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5c4fa7d077..617fd846d6 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -974,6 +974,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
COMPARE_NODE_FIELD(constraintDeps);
@@ -1048,6 +1049,7 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
COMPARE_NODE_FIELD(sortClause);
COMPARE_NODE_FIELD(limitOffset);
COMPARE_NODE_FIELD(limitCount);
+ COMPARE_SCALAR_FIELD(limitOption);
COMPARE_NODE_FIELD(lockingClause);
COMPARE_NODE_FIELD(withClause);
COMPARE_SCALAR_FIELD(op);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 0fde876c77..1a53068fe2 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -900,6 +900,7 @@ _outLimit(StringInfo str, const Limit *node)
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
}
static void
@@ -2099,6 +2100,7 @@ _outLimitPath(StringInfo str, const LimitPath *node)
WRITE_NODE_FIELD(subpath);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
}
static void
@@ -2680,6 +2682,7 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(lockingClause);
WRITE_NODE_FIELD(withClause);
WRITE_ENUM_FIELD(op, SetOperation);
@@ -2889,6 +2892,7 @@ _outQuery(StringInfo str, const Query *node)
WRITE_NODE_FIELD(sortClause);
WRITE_NODE_FIELD(limitOffset);
WRITE_NODE_FIELD(limitCount);
+ WRITE_ENUM_FIELD(limitOption, LimitOption);
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
WRITE_NODE_FIELD(constraintDeps);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index ec6f2569ab..1efd54f900 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -278,6 +278,7 @@ _readQuery(void)
READ_NODE_FIELD(sortClause);
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
READ_NODE_FIELD(constraintDeps);
@@ -2320,6 +2321,7 @@ _readLimit(void)
READ_NODE_FIELD(limitOffset);
READ_NODE_FIELD(limitCount);
+ READ_ENUM_FIELD(limitOption, LimitOption);
READ_DONE();
}
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 97d0c28132..0e5eed5807 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2155,7 +2155,8 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
plan = (Plan *) make_limit(plan,
subparse->limitOffset,
- subparse->limitCount);
+ subparse->limitCount,
+ subparse->limitOption);
/* Must apply correct cost/width data to Limit node */
plan->startup_cost = mminfo->path->startup_cost;
@@ -2460,7 +2461,8 @@ create_limit_plan(PlannerInfo *root, LimitPath *best_path, int flags)
plan = make_limit(subplan,
best_path->limitOffset,
- best_path->limitCount);
+ best_path->limitCount,
+ best_path->limitOption);
copy_generic_path_info(&plan->plan, (Path *) best_path);
@@ -6325,7 +6327,7 @@ make_lockrows(Plan *lefttree, List *rowMarks, int epqParam)
* Build a Limit plan node
*/
Limit *
-make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
+make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption)
{
Limit *node = makeNode(Limit);
Plan *plan = &node->plan;
@@ -6337,6 +6339,7 @@ make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount)
node->limitOffset = limitOffset;
node->limitCount = limitCount;
+ node->limitOption = limitOption;
return node;
}
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 4465f002c8..146eb7ca28 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2077,12 +2077,20 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
*/
if (parse->sortClause)
{
- current_rel = create_ordered_paths(root,
- current_rel,
- final_target,
- final_target_parallel_safe,
- have_postponed_srfs ? -1.0 :
- limit_tuples);
+ if (parse->limitOption == PERCENTAGE)
+ current_rel = create_ordered_paths(root,
+ current_rel,
+ final_target,
+ final_target_parallel_safe,
+ have_postponed_srfs ? -1.0 :
+ -1.0);
+ else
+ current_rel = create_ordered_paths(root,
+ current_rel,
+ final_target,
+ final_target_parallel_safe,
+ have_postponed_srfs ? -1.0 :
+ limit_tuples);
/* Fix things up if final_target contains SRFs */
if (parse->hasTargetSRFs)
adjust_paths_for_srfs(root, current_rel,
@@ -2145,6 +2153,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
path = (Path *) create_limit_path(root, final_rel, path,
parse->limitOffset,
parse->limitCount,
+ parse->limitOption,
offset_est, count_est);
}
diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c
index b2637d0e89..5cb2c115ed 100644
--- a/src/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pathnode.c
@@ -3411,6 +3411,7 @@ LimitPath *
create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est)
{
LimitPath *pathnode = makeNode(LimitPath);
@@ -3432,6 +3433,7 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel,
pathnode->subpath = subpath;
pathnode->limitOffset = limitOffset;
pathnode->limitCount = limitCount;
+ pathnode->limitOption = limitOption;
/*
* Adjust the output rows count and costs according to the offset/limit.
@@ -3473,9 +3475,21 @@ create_limit_path(PlannerInfo *root, RelOptInfo *rel,
count_rows = (double) count_est;
else
count_rows = clamp_row_est(subpath->rows * 0.10);
+ if (limitOption == PERCENTAGE)
+ {
+ double per_count = DatumGetFloat8(count_est);
+ count_rows = clamp_row_est((subpath->rows * per_count) / 100);
+ if (subpath->rows > 0)
+ {
+ pathnode->path.startup_cost = (count_rows *
+ subpath->total_cost) / subpath->rows;
+ pathnode->path.total_cost = subpath->total_cost +
+ (count_rows * 0.1);
+ }
+ }
if (count_rows > pathnode->path.rows)
count_rows = pathnode->path.rows;
- if (subpath->rows > 0)
+ if (subpath->rows > 0 && limitOption == EXACT_NUMBER)
pathnode->path.total_cost = pathnode->path.startup_cost +
(subpath->total_cost - subpath->startup_cost)
* count_rows / subpath->rows;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 8f96558b3d..0e47758eb3 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1300,10 +1300,11 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
}
/* transform LIMIT */
- qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+ qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
/* transform window clauses after we have seen all window functions */
qry->windowClause = transformWindowDefinitions(pstate,
@@ -1548,10 +1549,11 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt)
EXPR_KIND_ORDER_BY,
false /* allow SQL92 rules */ );
- qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, stmt->limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, stmt->limitCount,
+ qry->limitCount = transformLimitClause(pstate, stmt->limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
if (stmt->lockingClause)
ereport(ERROR,
@@ -1783,10 +1785,11 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
parser_errposition(pstate,
exprLocation(list_nth(qry->targetList, tllen)))));
- qry->limitOffset = transformLimitClause(pstate, limitOffset,
+ qry->limitOffset = transformLimitClause(pstate, limitOffset, stmt->limitOption,
EXPR_KIND_OFFSET, "OFFSET");
- qry->limitCount = transformLimitClause(pstate, limitCount,
+ qry->limitCount = transformLimitClause(pstate, limitCount, stmt->limitOption,
EXPR_KIND_LIMIT, "LIMIT");
+ qry->limitOption = stmt->limitOption;
qry->rtable = pstate->p_rtable;
qry->jointree = makeFromExpr(pstate->p_joinlist, NULL);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d8a3c2d4cc..1abe076fa4 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -164,6 +164,7 @@ static List *makeOrderedSetArgs(List *directargs, List *orderedargs,
static void insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner);
static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg);
@@ -387,7 +388,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
target_list opt_target_list insert_column_list set_target_list
set_clause_list set_clause
def_list operator_def_list indirection opt_indirection
- reloption_list group_clause TriggerFuncArgs select_limit
+ reloption_list group_clause TriggerFuncArgs select_limit limit_clause
opt_select_limit opclass_item_list opclass_drop_list
opclass_purpose opt_opfamily transaction_mode_list_or_empty
OptTableFuncElementList TableFuncElementList opt_type_modifiers
@@ -449,7 +450,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
comment_type_any_name comment_type_name
security_label_type_any_name security_label_type_name
-%type <node> fetch_args limit_clause select_limit_value
+%type <node> fetch_args select_limit_value
offset_clause select_offset_value
select_fetch_first_value I_or_F_const
%type <ival> row_or_rows first_or_next
@@ -662,7 +663,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
ORDER ORDINALITY OTHERS OUT_P OUTER_P
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
- PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
+ PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PERCENT PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
@@ -11191,7 +11192,7 @@ select_no_parens:
| select_clause sort_clause
{
insertSelectOptions((SelectStmt *) $1, $2, NIL,
- NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL,
yyscanner);
$$ = $1;
}
@@ -11199,6 +11200,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $3,
list_nth($4, 0), list_nth($4, 1),
+ (list_nth($4, 2)),
NULL,
yyscanner);
$$ = $1;
@@ -11207,6 +11209,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $1, $2, $4,
list_nth($3, 0), list_nth($3, 1),
+ list_nth($3, 2),
NULL,
yyscanner);
$$ = $1;
@@ -11215,7 +11218,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, NULL, NIL,
NULL, NULL,
- $1,
+ NULL, $1,
yyscanner);
$$ = $2;
}
@@ -11223,7 +11226,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, NIL,
NULL, NULL,
- $1,
+ NULL, $1,
yyscanner);
$$ = $2;
}
@@ -11231,6 +11234,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $4,
list_nth($5, 0), list_nth($5, 1),
+ list_nth($5, 2),
$1,
yyscanner);
$$ = $2;
@@ -11239,6 +11243,7 @@ select_no_parens:
{
insertSelectOptions((SelectStmt *) $2, $3, $5,
list_nth($4, 0), list_nth($4, 1),
+ list_nth($4, 2),
$1,
yyscanner);
$$ = $2;
@@ -11525,20 +11530,20 @@ sortby: a_expr USING qual_all_Op opt_nulls_order
select_limit:
- limit_clause offset_clause { $$ = list_make2($2, $1); }
- | offset_clause limit_clause { $$ = list_make2($1, $2); }
- | limit_clause { $$ = list_make2(NULL, $1); }
- | offset_clause { $$ = list_make2($1, NULL); }
+ limit_clause offset_clause { $$ = list_make3($2, list_nth($1, 0), list_nth($1, 1)); }
+ | offset_clause limit_clause { $$ = list_make3($1, list_nth($2, 0), list_nth($2, 1)); }
+ | limit_clause { $$ = list_make3(NULL, list_nth($1, 0), list_nth($1, 1)); }
+ | offset_clause { $$ = list_make3($1, NULL, NULL); }
;
opt_select_limit:
select_limit { $$ = $1; }
- | /* EMPTY */ { $$ = list_make2(NULL,NULL); }
+ | /* EMPTY */ { $$ = list_make3(NULL, NULL, NULL); }
;
limit_clause:
LIMIT select_limit_value
- { $$ = $2; }
+ { $$ = list_make2($2, NULL); }
| LIMIT select_limit_value ',' select_offset_value
{
/* Disabled because it was too confusing, bjm 2002-02-18 */
@@ -11556,9 +11561,11 @@ limit_clause:
* we can see the ONLY token in the lookahead slot.
*/
| FETCH first_or_next select_fetch_first_value row_or_rows ONLY
- { $$ = $3; }
+ { $$ = list_make2($3, makeString("EXACT_NUMBER")); }
+ | FETCH first_or_next select_fetch_first_value PERCENT row_or_rows ONLY
+ { $$ = list_make2($3, makeString("PERCENTAGE")); }
| FETCH first_or_next row_or_rows ONLY
- { $$ = makeIntConst(1, -1); }
+ { $$ = list_make2(makeIntConst(1, -1), NULL); }
;
offset_clause:
@@ -15425,6 +15432,7 @@ reserved_keyword:
| ONLY
| OR
| ORDER
+ | PERCENT
| PLACING
| PRIMARY
| REFERENCES
@@ -15808,6 +15816,7 @@ static void
insertSelectOptions(SelectStmt *stmt,
List *sortClause, List *lockingClause,
Node *limitOffset, Node *limitCount,
+ void *limitOption,
WithClause *withClause,
core_yyscan_t yyscanner)
{
@@ -15846,6 +15855,17 @@ insertSelectOptions(SelectStmt *stmt,
parser_errposition(exprLocation(limitCount))));
stmt->limitCount = limitCount;
}
+ if (limitOption)
+ {
+ if (stmt->limitOption)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple LIMIT options not allowed")));
+ if (strcmp(strVal(limitOption), "PERCENTAGE") == 0)
+ stmt->limitOption = PERCENTAGE;
+ else
+ stmt->limitOption = EXACT_NUMBER;
+ }
if (withClause)
{
if (stmt->withClause)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 8805543da7..5876d092b5 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1708,7 +1708,7 @@ transformWhereClause(ParseState *pstate, Node *clause,
* constructName does not affect the semantics, but is used in error messages
*/
Node *
-transformLimitClause(ParseState *pstate, Node *clause,
+transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption,
ParseExprKind exprKind, const char *constructName)
{
Node *qual;
@@ -1717,8 +1717,10 @@ transformLimitClause(ParseState *pstate, Node *clause,
return NULL;
qual = transformExpr(pstate, clause, exprKind);
-
- qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName);
+ if (limitOption == PERCENTAGE && (strcmp(constructName, "LIMIT") == 0))
+ qual = coerce_to_specific_type(pstate, qual, FLOAT8OID, constructName);
+ else
+ qual = coerce_to_specific_type(pstate, qual, INT8OID, constructName);
/* LIMIT can't refer to any variables of the current query */
checkExprIsVarFree(pstate, qual, constructName);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 7cae085177..911f6c4021 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2287,8 +2287,11 @@ typedef struct LimitState
PlanState ps; /* its first field is NodeTag */
ExprState *limitOffset; /* OFFSET parameter, or NULL if none */
ExprState *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* limit specification type */
int64 offset; /* current OFFSET value */
int64 count; /* current COUNT, if any */
+ float8 percent; /* percentage */
+ Tuplestorestate *totalTuple; /* total number of row outer node return */
bool noCount; /* if true, ignore count */
LimitStateCond lstate; /* state machine status, as above */
int64 position; /* 1-based index of last tuple returned */
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 10dac60cd3..7065771adc 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -814,4 +814,16 @@ typedef enum OnConflictAction
ONCONFLICT_UPDATE /* ON CONFLICT ... DO UPDATE */
} OnConflictAction;
+/*
+ * LimitOption -
+ * LIMIT option of query
+ *
+ * This is needed in both parsenodes.h and plannodes.h, so put it here...
+ */
+typedef enum LimitOption
+{
+ EXACT_NUMBER, /* LIMIT in exact number of rows */
+ PERCENTAGE /* LIMIT in percentage */
+} LimitOption;
+
#endif /* NODES_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index addc2c2ec7..f861bc4da4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -159,6 +159,7 @@ typedef struct Query
Node *limitOffset; /* # of result tuples to skip (int8 expr) */
Node *limitCount; /* # of result tuples to return (int8 expr) */
+ LimitOption limitOption; /* limit type */
List *rowMarks; /* a list of RowMarkClause's */
@@ -1572,6 +1573,7 @@ typedef struct SelectStmt
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 */
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
WithClause *withClause; /* WITH clause */
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 6d087c268f..1cdfa706df 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -946,6 +946,7 @@ typedef struct Limit
Plan plan;
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* LIMIT in percentage or exact number */
} Limit;
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 3430061361..f789ee4b79 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1737,6 +1737,7 @@ typedef struct LimitPath
Path *subpath; /* path representing input source */
Node *limitOffset; /* OFFSET parameter, or NULL if none */
Node *limitCount; /* COUNT parameter, or NULL if none */
+ LimitOption limitOption; /* LIMIT in percentage or exact number */
} LimitPath;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index bd905d3328..303501a14c 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -248,6 +248,7 @@ extern ModifyTablePath *create_modifytable_path(PlannerInfo *root,
extern LimitPath *create_limit_path(PlannerInfo *root, RelOptInfo *rel,
Path *subpath,
Node *limitOffset, Node *limitCount,
+ LimitOption limitOption,
int64 offset_est, int64 count_est);
extern Path *reparameterize_path(PlannerInfo *root, Path *path,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index bec0c38617..1932df1517 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -66,7 +66,7 @@ extern Agg *make_agg(List *tlist, List *qual,
int numGroupCols, AttrNumber *grpColIdx, Oid *grpOperators,
List *groupingSets, List *chain,
double dNumGroups, Plan *lefttree);
-extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount);
+extern Limit *make_limit(Plan *lefttree, Node *limitOffset, Node *limitCount, LimitOption limitOption);
/*
* prototypes for plan/initsplan.c
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index adeb834ce8..ab816083b7 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -299,6 +299,7 @@ PG_KEYWORD("partial", PARTIAL, UNRESERVED_KEYWORD)
PG_KEYWORD("partition", PARTITION, UNRESERVED_KEYWORD)
PG_KEYWORD("passing", PASSING, UNRESERVED_KEYWORD)
PG_KEYWORD("password", PASSWORD, UNRESERVED_KEYWORD)
+PG_KEYWORD("percent", PERCENT, RESERVED_KEYWORD)
PG_KEYWORD("placing", PLACING, RESERVED_KEYWORD)
PG_KEYWORD("plans", PLANS, UNRESERVED_KEYWORD)
PG_KEYWORD("policy", POLICY, UNRESERVED_KEYWORD)
diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h
index 179f3ab3c3..39a74c32d2 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -22,7 +22,7 @@ extern int setTargetTable(ParseState *pstate, RangeVar *relation,
extern Node *transformWhereClause(ParseState *pstate, Node *clause,
ParseExprKind exprKind, const char *constructName);
-extern Node *transformLimitClause(ParseState *pstate, Node *clause,
+extern Node *transformLimitClause(ParseState *pstate, Node *clause, LimitOption limitOption,
ParseExprKind exprKind, const char *constructName);
extern List *transformGroupClause(ParseState *pstate, List *grouplist,
List **groupingSets,
diff --git a/src/test/modules/test_ddl_deparse/expected/create_table.out b/src/test/modules/test_ddl_deparse/expected/create_table.out
index 2d7dfd533e..eb04e36aac 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_table.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_table.out
@@ -105,7 +105,7 @@ CREATE TABLE student (
) INHERITS (person);
NOTICE: DDL test: type simple, tag CREATE TABLE
CREATE TABLE stud_emp (
- percent int4
+ "percent" int4
) INHERITS (emp, student);
NOTICE: merging multiple inherited definitions of column "id"
NOTICE: merging multiple inherited definitions of column "name"
diff --git a/src/test/modules/test_ddl_deparse/sql/create_table.sql b/src/test/modules/test_ddl_deparse/sql/create_table.sql
index dd3a908638..f158dd4296 100644
--- a/src/test/modules/test_ddl_deparse/sql/create_table.sql
+++ b/src/test/modules/test_ddl_deparse/sql/create_table.sql
@@ -94,7 +94,7 @@ CREATE TABLE student (
) INHERITS (person);
CREATE TABLE stud_emp (
- percent int4
+ "percent" int4
) INHERITS (emp, student);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 7e52c27e3f..a30bfa919c 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -79,7 +79,7 @@ CREATE TABLE student (
gpa float8
) INHERITS (person);
CREATE TABLE stud_emp (
- percent int4
+ "percent" int4
) INHERITS (emp, student);
NOTICE: merging multiple inherited definitions of column "name"
NOTICE: merging multiple inherited definitions of column "age"
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index a2cae9663c..ed94b78978 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -92,7 +92,7 @@ CREATE TABLE student (
CREATE TABLE stud_emp (
- percent int4
+ "percent" int4
) INHERITS (emp, student);
