Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit568b4e1

Browse files
author
Amit Kapila
committed
Don't allow LIMIT/OFFSET clause within sub-selects to be pushed to workers.
Allowing sub-select containing LIMIT/OFFSET in workers can lead toinconsistent results at the top-level as there is no guarantee that therow order will be fully deterministic. The fix is to prohibit pushingLIMIT/OFFSET within sub-selects to workers.Reported-by: Andrew FletcherBug: 15324Author: Amit KapilaReviewed-by: Dilip KumarBackpatch-through: 9.6Discussion:https://postgr.es/m/153417684333.10284.11356259990921828616@wrigleys.postgresql.org
1 parent271b678 commit568b4e1

File tree

5 files changed

+40
-2
lines changed

5 files changed

+40
-2
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -585,7 +585,20 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
585585
* the SubqueryScanPath as not parallel-safe. (Note that
586586
* set_subquery_pathlist() might push some of these quals down
587587
* into the subquery itself, but that doesn't change anything.)
588+
*
589+
* We can't push sub-select containing LIMIT/OFFSET to workers as
590+
* there is no guarantee that the row order will be fully
591+
* deterministic, and applying LIMIT/OFFSET will lead to
592+
* inconsistent results at the top-level. (In some cases, where
593+
* the result is ordered, we could relax this restriction. But it
594+
* doesn't currently seem worth expending extra effort to do so.)
588595
*/
596+
{
597+
Query*subquery=castNode(Query,rte->subquery);
598+
599+
if (limit_needed(subquery))
600+
return;
601+
}
589602
break;
590603

591604
caseRTE_JOIN:

‎src/backend/optimizer/plan/planner.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,6 @@ static void preprocess_rowmarks(PlannerInfo *root);
9898
staticdoublepreprocess_limit(PlannerInfo*root,
9999
doubletuple_fraction,
100100
int64*offset_est,int64*count_est);
101-
staticboollimit_needed(Query*parse);
102101
staticvoidremove_useless_groupby_columns(PlannerInfo*root);
103102
staticList*preprocess_groupclause(PlannerInfo*root,List*force);
104103
staticList*extract_rollup_sets(List*groupingSets);
@@ -2492,7 +2491,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
24922491
* a key distinction: here we need hard constants in OFFSET/LIMIT, whereas
24932492
* in preprocess_limit it's good enough to consider estimated values.
24942493
*/
2495-
staticbool
2494+
bool
24962495
limit_needed(Query*parse)
24972496
{
24982497
Node*node;

‎src/include/optimizer/planner.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,8 @@ extern bool is_dummy_plan(Plan *plan);
4646
externRowMarkTypeselect_rowmark_type(RangeTblEntry*rte,
4747
LockClauseStrengthstrength);
4848

49+
externboollimit_needed(Query*parse);
50+
4951
externvoidmark_partial_aggref(Aggref*agg,AggSplitaggsplit);
5052

5153
externPath*get_cheapest_fractional_path(RelOptInfo*rel,

‎src/test/regress/expected/select_parallel.out

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -205,6 +205,25 @@ explain (costs off, verbose)
205205
Output: b.unique1
206206
(15 rows)
207207

208+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
209+
explain (costs off)
210+
select * from tenk1 a where two in
211+
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
212+
QUERY PLAN
213+
---------------------------------------------------------------
214+
Hash Semi Join
215+
Hash Cond: (a.two = b.two)
216+
-> Gather
217+
Workers Planned: 4
218+
-> Parallel Seq Scan on tenk1 a
219+
-> Hash
220+
-> Limit
221+
-> Gather
222+
Workers Planned: 4
223+
-> Parallel Seq Scan on tenk1 b
224+
Filter: (stringu1 ~~ '%AAAA'::text)
225+
(11 rows)
226+
208227
explain (costs off)
209228
select stringu1::int2 from tenk1 where unique1 = 1;
210229
QUERY PLAN

‎src/test/regress/sql/select_parallel.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,11 @@ explain (costs off, verbose)
8888
selectcount(*)from tenk1 awhere (unique1, two)in
8989
(select unique1, row_number() over()from tenk1 b);
9090

91+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
92+
explain (costs off)
93+
select*from tenk1 awhere twoin
94+
(select twofrom tenk1 bwhere stringu1like'%AAAA'limit3);
95+
9196
explain (costs off)
9297
select stringu1::int2from tenk1where unique1=1;
9398

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp