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

Commit830d756

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 parenta2afcda commit830d756

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
@@ -620,7 +620,20 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
620620
* the SubqueryScanPath as not parallel-safe. (Note that
621621
* set_subquery_pathlist() might push some of these quals down
622622
* into the subquery itself, but that doesn't change anything.)
623+
*
624+
* We can't push sub-select containing LIMIT/OFFSET to workers as
625+
* there is no guarantee that the row order will be fully
626+
* deterministic, and applying LIMIT/OFFSET will lead to
627+
* inconsistent results at the top-level. (In some cases, where
628+
* the result is ordered, we could relax this restriction. But it
629+
* doesn't currently seem worth expending extra effort to do so.)
623630
*/
631+
{
632+
Query*subquery=castNode(Query,rte->subquery);
633+
634+
if (limit_needed(subquery))
635+
return;
636+
}
624637
break;
625638

626639
caseRTE_JOIN:

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

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -123,7 +123,6 @@ static void preprocess_rowmarks(PlannerInfo *root);
123123
staticdoublepreprocess_limit(PlannerInfo*root,
124124
doubletuple_fraction,
125125
int64*offset_est,int64*count_est);
126-
staticboollimit_needed(Query*parse);
127126
staticvoidremove_useless_groupby_columns(PlannerInfo*root);
128127
staticList*preprocess_groupclause(PlannerInfo*root,List*force);
129128
staticList*extract_rollup_sets(List*groupingSets);
@@ -2871,7 +2870,7 @@ preprocess_limit(PlannerInfo *root, double tuple_fraction,
28712870
* a key distinction: here we need hard constants in OFFSET/LIMIT, whereas
28722871
* in preprocess_limit it's good enough to consider estimated values.
28732872
*/
2874-
staticbool
2873+
bool
28752874
limit_needed(Query*parse)
28762875
{
28772876
Node*node;

‎src/include/optimizer/planner.h‎

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

50+
externboollimit_needed(Query*parse);
51+
5052
externvoidmark_partial_aggref(Aggref*agg,AggSplitaggsplit);
5153

5254
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
@@ -984,6 +984,25 @@ explain (costs off, verbose)
984984
Output: b.unique1
985985
(18 rows)
986986

987+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
988+
explain (costs off)
989+
select * from tenk1 a where two in
990+
(select two from tenk1 b where stringu1 like '%AAAA' limit 3);
991+
QUERY PLAN
992+
---------------------------------------------------------------
993+
Hash Semi Join
994+
Hash Cond: (a.two = b.two)
995+
-> Gather
996+
Workers Planned: 4
997+
-> Parallel Seq Scan on tenk1 a
998+
-> Hash
999+
-> Limit
1000+
-> Gather
1001+
Workers Planned: 4
1002+
-> Parallel Seq Scan on tenk1 b
1003+
Filter: (stringu1 ~~ '%AAAA'::text)
1004+
(11 rows)
1005+
9871006
-- to increase the parallel query test coverage
9881007
SAVEPOINT settings;
9891008
SET LOCAL force_parallel_mode = 1;

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -368,6 +368,11 @@ explain (costs off, verbose)
368368
(select unique1, row_number() over()from tenk1 b);
369369

370370

371+
-- LIMIT/OFFSET within sub-selects can't be pushed to workers.
372+
explain (costs off)
373+
select*from tenk1 awhere twoin
374+
(select twofrom tenk1 bwhere stringu1like'%AAAA'limit3);
375+
371376
-- to increase the parallel query test coverage
372377
SAVEPOINT settings;
373378
SET LOCAL force_parallel_mode=1;

0 commit comments

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