agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH 3/3] Avoid creating scan nodes for partitioned tables 8+ messages / 2 participants [nested] [flat]
* [PATCH 3/3] Avoid creating scan nodes for partitioned tables @ 2017-02-06 08:26 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-02-06 08:26 UTC (permalink / raw) Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we need not create AppendRelInfo's for them in the planner prep phase. Further, the planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member (other than the parent itself which in normal cases is also a child member), which means the latter phases will not consider creating an Append plan and instead create a scan node. Avoid this if the RTE is a partitioned table, by noticing such cases in set_rel_size(). Per suggestion from Ashutosh Bapat. Next, since we do not add the RTE corresponding to the root partitioned table as the 1st child member of the inheritance set, inheritance_planner() must not assume the same when assigning nominalRelation to a ModifyTable node. --- src/backend/optimizer/path/allpaths.c | 9 +++ src/backend/optimizer/plan/planner.c | 16 ++++- src/backend/optimizer/prep/prepunion.c | 19 +++++- src/test/regress/expected/inherit.out | 116 +++++++++------------------------ src/test/regress/sql/inherit.sql | 4 ++ 5 files changed, 76 insertions(+), 88 deletions(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 633b5c1608..759e14d5fe 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -342,6 +342,15 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * An empty partitioned table, i.e., one without any leaf + * partitions, as signaled by rte->inh being set to false + * by the prep phase (see expand_inherited_rtentry). + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ca0ae7883e..9918d2ba8c 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; Assert(parse->commandType != CMD_INSERT); /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * We generate a modified instance of the original Query for each target * relation, plan that, and put all the plans into a list that will be * controlled by a single ModifyTable node. All the instances share the @@ -1060,7 +1070,6 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1214,6 +1223,11 @@ inheritance_planner(PlannerInfo *root) * will not be otherwise referenced in the plan, doing so would give * rise to confusing use of multiple aliases in EXPLAIN output for * what the user will think is the "same" table.) + * + * If the parent is a partitioned table, we do not have a separate RTE + * representing it as a member of the inheritance set, because we do + * not create a scan plan for it. As mentioned at the top of this + * function, we make the parent RTE itself the nominal relation. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..78f51c762b 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1450,6 +1450,18 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrelation = oldrelation; /* + * Ignore partitioned tables here, because we do not want them to + * be considered as child tables in the inheritance set. No scan + * plans will be created for them. + */ + if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + if (newrelation != oldrelation) + heap_close(newrelation, lockmode); + continue; + } + + /* * It is possible that the parent table has children that are temp * tables of other backends. We cannot safely access such tables * (because of buffering issues), and the best thing to do seems to be @@ -1548,9 +1560,12 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) * If all the children were temp tables, pretend it's a non-inheritance * situation. The duplicate RTE we added for the parent table is * harmless, so we don't bother to get rid of it; ditto for the useless - * PlanRowMark node. + * PlanRowMark node. Since we do not create appinfos for partitioned + * tables above, we may end up with just one appinfo corresponding to + * the only leaf partition, which is fine. */ - if (list_length(appinfos) < 2) + if ((rte->relkind != RELKIND_PARTITIONED_TABLE && + list_length(appinfos) < 2) || list_length(appinfos) < 1) { /* Clear flag before returning */ rte->inh = false; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a8c8b28a75..cd33a1a7ec 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1598,6 +1598,14 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -1605,71 +1613,60 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, @@ -1689,14 +1686,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1706,36 +1698,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1744,27 +1722,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1776,37 +1746,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1825,23 +1775,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted cascade; NOTICE: drop cascades to 3 other objects diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..34274746d3 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -570,6 +570,10 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); + +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); -- 2.11.0 --------------88CABA4F871D1159E4AB86FA Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------88CABA4F871D1159E4AB86FA-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 3/3] Avoid creating scan nodes for partitioned tables @ 2017-02-06 08:26 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-02-06 08:26 UTC (permalink / raw) Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we need not create AppendRelInfo's for them in the planner prep phase. Further, the planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member (other than the parent itself which in normal cases is also a child member), which means the latter phases will not consider creating an Append plan and instead create a scan node. Avoid this if the RTE is a partitioned table, by noticing such cases in set_rel_size(). Per suggestion from Ashutosh Bapat. Next, since we do not add the RTE corresponding to the root partitioned table as the 1st child member of the inheritance set, inheritance_planner() must not assume the same when assigning nominalRelation to a ModifyTable node. --- src/backend/optimizer/path/allpaths.c | 8 +++ src/backend/optimizer/plan/planner.c | 14 +++- src/backend/optimizer/prep/prepunion.c | 24 ++++++- src/test/regress/expected/inherit.out | 124 +++++++++++---------------------- src/test/regress/sql/inherit.sql | 8 +++ 5 files changed, 90 insertions(+), 88 deletions(-) diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 633b5c1608..f2c8d27193 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -342,6 +342,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ca0ae7883e..b5698f0ce7 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; Assert(parse->commandType != CMD_INSERT); /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * We generate a modified instance of the original Query for each target * relation, plan that, and put all the plans into a list that will be * controlled by a single ModifyTable node. All the instances share the @@ -1060,7 +1070,6 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1214,6 +1223,9 @@ inheritance_planner(PlannerInfo *root) * will not be otherwise referenced in the plan, doing so would give * rise to confusing use of multiple aliases in EXPLAIN output for * what the user will think is the "same" table.) + * + * If the parent is a partitioned table, we already set the nominal + * relation. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..e31daf737b 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1364,6 +1364,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) List *inhOIDs; List *appinfos; ListCell *l; + int min_child_rels; /* Does RT entry allow inheritance? */ if (!rte->inh) @@ -1450,6 +1451,22 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrelation = oldrelation; /* + * Ignore partitioned tables here, because we do not want them to + * be considered as child tables in the inheritance set. No scan + * plans will be created for them. + */ + if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * Don't lose the lock taken for us by find_all_inheritors() + * lest its partitions change under us. + */ + if (newrelation != oldrelation) + heap_close(newrelation, NoLock); + continue; + } + + /* * It is possible that the parent table has children that are temp * tables of other backends. We cannot safely access such tables * (because of buffering issues), and the best thing to do seems to be @@ -1548,9 +1565,12 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) * If all the children were temp tables, pretend it's a non-inheritance * situation. The duplicate RTE we added for the parent table is * harmless, so we don't bother to get rid of it; ditto for the useless - * PlanRowMark node. + * PlanRowMark node. Since we do not create appinfos for partitioned + * tables above, we may end up with just one appinfo corresponding to + * the only leaf partition, which is fine. */ - if (list_length(appinfos) < 2) + min_child_rels = (rte->relkind == RELKIND_PARTITIONED_TABLE ? 1 : 2); + if (list_length(appinfos) < min_child_rels) { /* Clear flag before returning */ rte->inh = false; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a8c8b28a75..55b147432f 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1598,6 +1598,14 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -1605,77 +1613,74 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); @@ -1689,14 +1694,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1706,36 +1706,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1744,27 +1730,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1776,37 +1754,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1825,23 +1783,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted cascade; NOTICE: drop cascades to 3 other objects diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..04cc4ffa23 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -570,6 +570,10 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); + +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -586,6 +590,10 @@ create table range_list_parted ( b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); + +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); -- 2.11.0 --------------6AAA587F9847CA9662C1C3C3 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------6AAA587F9847CA9662C1C3C3-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 2/3] Avoid creating scan nodes for partitioned tables @ 2017-02-06 08:26 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-02-06 08:26 UTC (permalink / raw) * Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we need not create AppendRelInfo's for them in the planner prep phase. * The planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member (other than the parent itself which in normal cases is also a child member), which means the latter phases will not consider creating an Append plan and instead create a scan node. Avoid this if the RTE is a partitioned table, by noticing such cases in set_rel_size(). Per suggestion from Ashutosh Bapat. * Since we do not add the RTE corresponding to the root partitioned table as the 1st child member of the inheritance set, inheritance_planner() must not assume the same when assigning nominalRelation to a ModifyTable node. * In ExecInitModifyTable(), use nominalRelation to initialize tuple routing information, instead of the first resultRelInfo. We set ModifyTable.nominalRelation to the root parent RTE in the partitioned table case (implicitly in the INSERT case, whereas explicitly in the UPDATE/DELETE cases). --- src/backend/executor/nodeModifyTable.c | 18 +++-- src/backend/optimizer/path/allpaths.c | 8 ++ src/backend/optimizer/plan/planner.c | 14 +++- src/backend/optimizer/prep/prepunion.c | 32 ++++++-- src/test/regress/expected/inherit.out | 124 ++++++++++-------------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 8 ++ 7 files changed, 108 insertions(+), 100 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 95e158970c..188ce8f8d4 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -45,6 +45,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/parsetree.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -1634,7 +1635,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) Plan *subplan; ListCell *l; int i; - Relation rel; + Relation nominalRel; + RangeTblEntry *nominalRTE; /* check for unsupported flags */ Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); @@ -1726,9 +1728,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = saved_resultRelInfo; /* Build state for INSERT tuple routing */ - rel = mtstate->resultRelInfo->ri_RelationDesc; + nominalRTE = rt_fetch(node->nominalRelation, estate->es_range_table); + nominalRel = heap_open(nominalRTE->relid, NoLock); if (operation == CMD_INSERT && - rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + nominalRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { PartitionDispatch *partition_dispatch_info; ResultRelInfo *partitions; @@ -1737,7 +1740,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) int num_parted, num_partitions; - ExecSetupPartitionTupleRouting(rel, + ExecSetupPartitionTupleRouting(nominalRel, &partition_dispatch_info, &partitions, &partition_tupconv_maps, @@ -1801,7 +1804,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* varno = node->nominalRelation */ mapped_wcoList = map_partition_varattnos(wcoList, node->nominalRelation, - partrel, rel); + partrel, nominalRel); foreach(ll, mapped_wcoList) { WithCheckOption *wco = (WithCheckOption *) lfirst(ll); @@ -1876,7 +1879,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* varno = node->nominalRelation */ rlist = map_partition_varattnos(returningList, node->nominalRelation, - partrel, rel); + partrel, nominalRel); rliststate = (List *) ExecInitExpr((Expr *) rlist, &mtstate->ps); resultRelInfo->ri_projectReturning = ExecBuildProjectionInfo(rliststate, econtext, slot, @@ -1897,6 +1900,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->ps.ps_ExprContext = NULL; } + /* We're done using it. */ + heap_close(nominalRel, NoLock); + /* * If needed, Initialize target list, projection and qual for ON CONFLICT * DO UPDATE. diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 633b5c1608..f2c8d27193 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -342,6 +342,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ca0ae7883e..b5698f0ce7 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; Assert(parse->commandType != CMD_INSERT); /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * We generate a modified instance of the original Query for each target * relation, plan that, and put all the plans into a list that will be * controlled by a single ModifyTable node. All the instances share the @@ -1060,7 +1070,6 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1214,6 +1223,9 @@ inheritance_planner(PlannerInfo *root) * will not be otherwise referenced in the plan, doing so would give * rise to confusing use of multiple aliases in EXPLAIN output for * what the user will think is the "same" table.) + * + * If the parent is a partitioned table, we already set the nominal + * relation. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..7bd4928945 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1364,6 +1364,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) List *inhOIDs; List *appinfos; ListCell *l; + bool has_child; /* Does RT entry allow inheritance? */ if (!rte->inh) @@ -1435,6 +1436,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) /* Scan the inheritance set and expand it */ appinfos = NIL; + has_child = false; foreach(l, inhOIDs) { Oid childOID = lfirst_oid(l); @@ -1450,6 +1452,22 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrelation = oldrelation; /* + * Since partitioned tables themselves do not contain data, later + * planning steps should not create scan nodes for them. So do not + * create the child RTE and AppendRelInfo. + */ + if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * Don't lose the lock taken for us by find_all_inheritors() + * lest its partitions change under us. + */ + if (newrelation != oldrelation) + heap_close(newrelation, NoLock); + continue; + } + + /* * It is possible that the parent table has children that are temp * tables of other backends. We cannot safely access such tables * (because of buffering issues), and the best thing to do seems to be @@ -1461,6 +1479,9 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) continue; } + /* We have a child table. */ + has_child = true; + /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID @@ -1545,12 +1566,13 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) heap_close(oldrelation, NoLock); /* - * If all the children were temp tables, pretend it's a non-inheritance - * situation. The duplicate RTE we added for the parent table is - * harmless, so we don't bother to get rid of it; ditto for the useless - * PlanRowMark node. + * If all the children were temp tables of other backends or if the parent + * is a partitioned table without any leaf partitions, pretend it's a + * non-inheritance situation. The duplicate RTE for the parent table we + * added in the non-partitioned table case is harmless, so we don't bother + * to get rid of it; ditto for the useless PlanRowMark node. */ - if (list_length(appinfos) < 2) + if (!has_child) { /* Clear flag before returning */ rte->inh = false; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a8c8b28a75..55b147432f 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1598,6 +1598,14 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -1605,77 +1613,74 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); @@ -1689,14 +1694,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1706,36 +1706,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1744,27 +1730,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1776,37 +1754,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1825,23 +1783,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted cascade; NOTICE: drop cascades to 3 other objects diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index b18e420e9b..d3794140fb 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -322,12 +322,10 @@ explain (costs off) QUERY PLAN ------------------------------------------- Append - -> Sample Scan on parted_sample - Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_1 Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_2 Sampling: bernoulli ('100'::real) -(7 rows) +(5 rows) drop table parted_sample, parted_sample_1, parted_sample_2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..04cc4ffa23 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -570,6 +570,10 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); + +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -586,6 +590,10 @@ create table range_list_parted ( b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); + +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); -- 2.11.0 --------------361CBC8BF740A795B0DA0383 Content-Type: text/x-diff; name="0003-Do-not-allocate-storage-for-partitioned-tables.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0003-Do-not-allocate-storage-for-partitioned-tables.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 2/3] Avoid creating scan nodes for partitioned tables @ 2017-02-06 08:26 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-02-06 08:26 UTC (permalink / raw) * Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we need not create AppendRelInfo's for them in the planner prep phase. * The planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member (other than the parent itself which in normal cases is also a child member), which means the latter phases will not consider creating an Append plan and instead create a scan node. Avoid this if the RTE is a partitioned table, by noticing such cases in set_rel_size(). Per suggestion from Ashutosh Bapat. * Since we do not add the RTE corresponding to the root partitioned table as the 1st child member of the inheritance set, inheritance_planner() must not assume the same when assigning nominalRelation to a ModifyTable node. * In ExecInitModifyTable(), use nominalRelation to initialize tuple routing information, instead of the first resultRelInfo. We set ModifyTable.nominalRelation to the root parent RTE in the partitioned table case (implicitly in the INSERT case, whereas explicitly in the UPDATE/DELETE cases). --- src/backend/executor/nodeModifyTable.c | 18 +++-- src/backend/optimizer/path/allpaths.c | 8 ++ src/backend/optimizer/plan/planner.c | 14 +++- src/backend/optimizer/prep/prepunion.c | 32 ++++++-- src/test/regress/expected/inherit.out | 124 ++++++++++-------------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 8 ++ 7 files changed, 108 insertions(+), 100 deletions(-) diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 95e158970c..188ce8f8d4 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -45,6 +45,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/parsetree.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -1634,7 +1635,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) Plan *subplan; ListCell *l; int i; - Relation rel; + Relation nominalRel; + RangeTblEntry *nominalRTE; /* check for unsupported flags */ Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); @@ -1726,9 +1728,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = saved_resultRelInfo; /* Build state for INSERT tuple routing */ - rel = mtstate->resultRelInfo->ri_RelationDesc; + nominalRTE = rt_fetch(node->nominalRelation, estate->es_range_table); + nominalRel = heap_open(nominalRTE->relid, NoLock); if (operation == CMD_INSERT && - rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + nominalRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { PartitionDispatch *partition_dispatch_info; ResultRelInfo *partitions; @@ -1737,7 +1740,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) int num_parted, num_partitions; - ExecSetupPartitionTupleRouting(rel, + ExecSetupPartitionTupleRouting(nominalRel, &partition_dispatch_info, &partitions, &partition_tupconv_maps, @@ -1801,7 +1804,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* varno = node->nominalRelation */ mapped_wcoList = map_partition_varattnos(wcoList, node->nominalRelation, - partrel, rel); + partrel, nominalRel); foreach(ll, mapped_wcoList) { WithCheckOption *wco = (WithCheckOption *) lfirst(ll); @@ -1876,7 +1879,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) /* varno = node->nominalRelation */ rlist = map_partition_varattnos(returningList, node->nominalRelation, - partrel, rel); + partrel, nominalRel); rliststate = (List *) ExecInitExpr((Expr *) rlist, &mtstate->ps); resultRelInfo->ri_projectReturning = ExecBuildProjectionInfo(rliststate, econtext, slot, @@ -1897,6 +1900,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->ps.ps_ExprContext = NULL; } + /* We're done using it. */ + heap_close(nominalRel, NoLock); + /* * If needed, Initialize target list, projection and qual for ON CONFLICT * DO UPDATE. diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 633b5c1608..f2c8d27193 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -342,6 +342,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index ca0ae7883e..b5698f0ce7 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; Assert(parse->commandType != CMD_INSERT); /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * We generate a modified instance of the original Query for each target * relation, plan that, and put all the plans into a list that will be * controlled by a single ModifyTable node. All the instances share the @@ -1060,7 +1070,6 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1214,6 +1223,9 @@ inheritance_planner(PlannerInfo *root) * will not be otherwise referenced in the plan, doing so would give * rise to confusing use of multiple aliases in EXPLAIN output for * what the user will think is the "same" table.) + * + * If the parent is a partitioned table, we already set the nominal + * relation. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..b4c65c4a90 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -1364,6 +1364,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) List *inhOIDs; List *appinfos; ListCell *l; + bool has_child; /* Does RT entry allow inheritance? */ if (!rte->inh) @@ -1435,6 +1436,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) /* Scan the inheritance set and expand it */ appinfos = NIL; + has_child = false; foreach(l, inhOIDs) { Oid childOID = lfirst_oid(l); @@ -1450,6 +1452,22 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrelation = oldrelation; /* + * Since partitioned tables themselves do not contain data, later + * planning steps should not create scan nodes for them. So do not + * create the child RTE and AppendRelInfo. + */ + if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * Don't lose the lock taken for us by find_all_inheritors() + * lest its partitions change under us. + */ + if (newrelation != oldrelation) + heap_close(newrelation, NoLock); + continue; + } + + /* * It is possible that the parent table has children that are temp * tables of other backends. We cannot safely access such tables * (because of buffering issues), and the best thing to do seems to be @@ -1461,6 +1479,9 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) continue; } + /* We have a child table. */ + has_child = true; + /* * Build an RTE for the child, and attach to query's rangetable list. * We copy most fields of the parent's RTE, but replace relation OID @@ -1545,12 +1566,13 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) heap_close(oldrelation, NoLock); /* - * If all the children were temp tables, pretend it's a non-inheritance - * situation. The duplicate RTE we added for the parent table is - * harmless, so we don't bother to get rid of it; ditto for the useless - * PlanRowMark node. + * If all the children were temp tables or if the parent is a partitioned + * table without any leaf partitions, pretend it's a non-inheritance + * situation. The duplicate RTE for the parent table we added in the + * non-partitioned table case is harmless, so we don't bother to get rid + * of it; ditto for the useless PlanRowMark node. */ - if (list_length(appinfos) < 2) + if (!has_child) { /* Clear flag before returning */ rte->inh = false; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index a8c8b28a75..55b147432f 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1598,6 +1598,14 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -1605,77 +1613,74 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); @@ -1689,14 +1694,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1706,36 +1706,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1744,27 +1730,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1776,37 +1754,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1825,23 +1783,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted cascade; NOTICE: drop cascades to 3 other objects diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index b18e420e9b..d3794140fb 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -322,12 +322,10 @@ explain (costs off) QUERY PLAN ------------------------------------------- Append - -> Sample Scan on parted_sample - Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_1 Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_2 Sampling: bernoulli ('100'::real) -(7 rows) +(5 rows) drop table parted_sample, parted_sample_1, parted_sample_2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index a8b7eb1c8d..04cc4ffa23 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -570,6 +570,10 @@ reset enable_bitmapscan; create table list_parted ( a varchar ) partition by list (a); + +-- test scanning partitioned table without any partitions +explain (costs off) select * from list_parted; + create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); create table part_null_xy partition of list_parted for values in (null, 'xy'); @@ -586,6 +590,10 @@ create table range_list_parted ( b char(2) ) partition by range (a); create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); + +-- test scanning partitioned table without any leaf partitions +explain (costs off) select * from range_list_parted; + create table part_1_10_ab partition of part_1_10 for values in ('ab'); create table part_1_10_cd partition of part_1_10 for values in ('cd'); create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); -- 2.11.0 --------------3902BA3B90B2AF54A9C44D74 Content-Type: text/x-diff; name="0003-Do-not-allocate-storage-for-partitioned-tables.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0003-Do-not-allocate-storage-for-partitioned-tables.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 1/2] Avoid creating scan nodes for partitioned tables @ 2017-03-10 04:48 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-03-10 04:48 UTC (permalink / raw) There are quite a few changes here: * Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So, it's not necessary to create AppendRelInfos for partitioned child tables in expand_inherited_rtentry(). Although, create the RTEs and record the RT indexes in a list. The list is associated with the parent_relid using the new PartitionedChildRelInfo nodes, which are kept in a global list called pcinfo_list in the root PlannerInfo, similar to append_rel_list. For a given parent RTE, add_paths_to_append_rel() and inheritance_planner() must copy the aforementioned list of partitioned child RT indexes to Append/MergeAppend and ModifyTable paths, respectively. The list is then carried over to a new field called partitioned_rels in Append, MergeAppend, and ModifyTable nodes. * For partitioned parent RTEs with inh=false, set_rel_size() must not try to do set_plain_rel_size(). Instead mark such rels as "dummy", because they are empty. Happens with partitioned tables without any leaf partitions. * In case of UPDATE/DELETE on a partitioned table, executor can already know the leaf result relations with PlannedStmt.resultRelations, but not the non-leaf relations. InitPlan must lock *all* the result relations before initializing the plan tree to avoid lock upgrade hazards, so we must include non-leaf result relations as well. For that, add a new field nonleafResultRelations to PlannedStmt, which is intitialized by copying ModifyTable.partitioned_rels. * PlanRowMarks for partitioned child tables are created with isParent set to true so that executor ignores them. That is, no ExecRowMarks are created for partitioned child tables. However those tables still must be locked with the appropriate lockmode; but InitPlan() does not, because it sees them as dummy entries. Instead the first Append or MergeAppend node that's initialized that refers to those table locks them appropriately. * In inheritance_planner(), set ModifyTable.nominalRelation to parent RT index in the partitioned inheritance parent case, so that EXPLAIN uses the root tables's original RT index to label a given ModifyTable node. Regression test outputs are adjusted to match the new plan shape. New tests are added for certain cases. --- src/backend/executor/execMain.c | 22 ++++- src/backend/executor/execParallel.c | 1 + src/backend/executor/execUtils.c | 56 ++++++++++++ src/backend/executor/nodeAppend.c | 6 ++ src/backend/executor/nodeMergeAppend.c | 6 ++ src/backend/executor/nodeModifyTable.c | 19 +++- src/backend/nodes/copyfuncs.c | 21 +++++ src/backend/nodes/equalfuncs.c | 12 +++ src/backend/nodes/outfuncs.c | 21 +++++ src/backend/nodes/readfuncs.c | 4 + src/backend/optimizer/path/allpaths.c | 43 +++++++-- src/backend/optimizer/path/joinrels.c | 2 +- src/backend/optimizer/plan/createplan.c | 14 ++- src/backend/optimizer/plan/planner.c | 89 +++++++++++++++--- src/backend/optimizer/plan/setrefs.c | 21 +++++ src/backend/optimizer/prep/prepunion.c | 103 ++++++++++++++------- src/backend/optimizer/util/pathnode.c | 10 +- src/backend/utils/cache/plancache.c | 3 +- src/include/executor/executor.h | 1 + src/include/nodes/nodes.h | 1 + src/include/nodes/plannodes.h | 20 +++- src/include/nodes/relation.h | 37 +++++++- src/include/optimizer/pathnode.h | 8 +- src/include/optimizer/planner.h | 2 + src/test/regress/expected/inherit.out | 147 +++++++++++++----------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 28 ++++++ 27 files changed, 537 insertions(+), 164 deletions(-) diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index f5cd65d8a0..3b6961d0a8 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -844,6 +844,22 @@ InitPlan(QueryDesc *queryDesc, int eflags) estate->es_num_result_relations = numResultRelations; /* es_result_relation_info is NULL except when within ModifyTable */ estate->es_result_relation_info = NULL; + + /* + * In the partitioned result relation case, lock the non-leaf result + * relations too. We don't however need ResultRelInfos for them. + */ + if (plannedstmt->nonleafResultRelations) + { + foreach(l, plannedstmt->nonleafResultRelations) + { + Index resultRelationIndex = lfirst_int(l); + Oid resultRelationOid; + + resultRelationOid = getrelid(resultRelationIndex, rangeTable); + LockRelationOid(resultRelationOid, RowExclusiveLock); + } + } } else { @@ -858,7 +874,11 @@ InitPlan(QueryDesc *queryDesc, int eflags) /* * Similarly, we have to lock relations selected FOR [KEY] UPDATE/SHARE * before we initialize the plan tree, else we'd be risking lock upgrades. - * While we are at it, build the ExecRowMark list. + * While we are at it, build the ExecRowMark list. Any partitioned child + * tables are ignored here (because, isParent=true) and will be locked by + * the first Append or MergeAppend node that references them. (Note that + * the RowMarks corresponding to partitioned child tables are present in + * the same list as the rest, i.e., plannedstmt->rowMarks.) */ estate->es_rowMarks = NIL; foreach(l, plannedstmt->rowMarks) diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c index a1289e5f12..86db73be43 100644 --- a/src/backend/executor/execParallel.c +++ b/src/backend/executor/execParallel.c @@ -161,6 +161,7 @@ ExecSerializePlan(Plan *plan, EState *estate) pstmt->planTree = plan; pstmt->rtable = estate->es_range_table; pstmt->resultRelations = NIL; + pstmt->nonleafResultRelations = NIL; pstmt->subplans = estate->es_plannedstmt->subplans; pstmt->rewindPlanIDs = NULL; pstmt->rowMarks = NIL; diff --git a/src/backend/executor/execUtils.c b/src/backend/executor/execUtils.c index 3d6a3801c0..a72cffeb6e 100644 --- a/src/backend/executor/execUtils.c +++ b/src/backend/executor/execUtils.c @@ -43,6 +43,7 @@ #include "executor/executor.h" #include "nodes/nodeFuncs.h" #include "parser/parsetree.h" +#include "storage/lmgr.h" #include "utils/memutils.h" #include "utils/rel.h" @@ -953,3 +954,58 @@ ShutdownExprContext(ExprContext *econtext, bool isCommit) MemoryContextSwitchTo(oldcontext); } + +/* + * ExecLockNonLeafAppendTables + * + * Locks, if necessary, the tables indicated by the RT indexes contained in + * the partitioned_rels list. These are the non-leaf tables in the partition + * tree controlled by a given Append or MergeAppend node. + */ +void +ExecLockNonLeafAppendTables(List *partitioned_rels, EState *estate) +{ + PlannedStmt *stmt = estate->es_plannedstmt; + ListCell *lc; + + foreach(lc, partitioned_rels) + { + ListCell *l; + Index rti = lfirst_int(lc); + bool is_result_rel = false; + Oid relid = getrelid(rti, estate->es_range_table); + + /* If this is a result relation, already locked in InitPlan */ + foreach(l, stmt->nonleafResultRelations) + { + if (rti == lfirst_int(l)) + { + is_result_rel = true; + break; + } + } + + /* + * Not a result relation; check if there is a RowMark that requires + * taking a RowShareLock on this rel. + */ + if (!is_result_rel) + { + PlanRowMark *rc = NULL; + + foreach(l, stmt->rowMarks) + { + if (((PlanRowMark *) lfirst(l))->rti == rti) + { + rc = lfirst(l); + break; + } + } + + if (rc && RowMarkRequiresRowShareLock(rc->markType)) + LockRelationOid(relid, RowShareLock); + else + LockRelationOid(relid, AccessShareLock); + } + } +} diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c index 6986caee6b..a107545b83 100644 --- a/src/backend/executor/nodeAppend.c +++ b/src/backend/executor/nodeAppend.c @@ -129,6 +129,12 @@ ExecInitAppend(Append *node, EState *estate, int eflags) Assert(!(eflags & EXEC_FLAG_MARK)); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node. It's a no-op for non-partitioned parent tables. + */ + ExecLockNonLeafAppendTables(node->partitioned_rels, estate); + + /* * Set up empty vector of subplan states */ nplans = list_length(node->appendplans); diff --git a/src/backend/executor/nodeMergeAppend.c b/src/backend/executor/nodeMergeAppend.c index 7a20bf07a4..8a2e78266b 100644 --- a/src/backend/executor/nodeMergeAppend.c +++ b/src/backend/executor/nodeMergeAppend.c @@ -72,6 +72,12 @@ ExecInitMergeAppend(MergeAppend *node, EState *estate, int eflags) Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node. It's a no-op for non-partitioned parent tables. + */ + ExecLockNonLeafAppendTables(node->partitioned_rels, estate); + + /* * Set up empty vector of subplan states */ nplans = list_length(node->mergeplans); diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 95e158970c..29c6a6e1d8 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -45,6 +45,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/parsetree.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -1725,8 +1726,20 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = saved_resultRelInfo; + /* The root table RT index is at the head of the partitioned_rels list */ + if (node->partitioned_rels) + { + Index root_rti; + Oid root_oid; + + root_rti = linitial_int(node->partitioned_rels); + root_oid = getrelid(root_rti, estate->es_range_table); + rel = heap_open(root_oid, NoLock); /* locked by InitPlan */ + } + else + rel = mtstate->resultRelInfo->ri_RelationDesc; + /* Build state for INSERT tuple routing */ - rel = mtstate->resultRelInfo->ri_RelationDesc; if (operation == CMD_INSERT && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { @@ -1897,6 +1910,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) mtstate->ps.ps_ExprContext = NULL; } + /* Close the root partitioned rel if we opened it above. */ + if (rel != mtstate->resultRelInfo->ri_RelationDesc) + heap_close(rel, NoLock); + /* * If needed, Initialize target list, projection and qual for ON CONFLICT * DO UPDATE. diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 25fd051d6e..7cf31b35fd 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -90,6 +90,7 @@ _copyPlannedStmt(const PlannedStmt *from) COPY_NODE_FIELD(planTree); COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(resultRelations); + COPY_NODE_FIELD(nonleafResultRelations); COPY_NODE_FIELD(subplans); COPY_BITMAPSET_FIELD(rewindPlanIDs); COPY_NODE_FIELD(rowMarks); @@ -200,6 +201,7 @@ _copyModifyTable(const ModifyTable *from) COPY_SCALAR_FIELD(operation); COPY_SCALAR_FIELD(canSetTag); COPY_SCALAR_FIELD(nominalRelation); + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(resultRelations); COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); @@ -235,6 +237,7 @@ _copyAppend(const Append *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(appendplans); return newnode; @@ -256,6 +259,7 @@ _copyMergeAppend(const MergeAppend *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(mergeplans); COPY_SCALAR_FIELD(numCols); COPY_POINTER_FIELD(sortColIdx, from->numCols * sizeof(AttrNumber)); @@ -2205,6 +2209,20 @@ _copyAppendRelInfo(const AppendRelInfo *from) } /* + * _copyPartitionedChildRelInfo + */ +static PartitionedChildRelInfo * +_copyPartitionedChildRelInfo(const PartitionedChildRelInfo *from) +{ + PartitionedChildRelInfo *newnode = makeNode(PartitionedChildRelInfo); + + COPY_SCALAR_FIELD(parent_relid); + COPY_NODE_FIELD(child_rels); + + return newnode; +} + +/* * _copyPlaceHolderInfo */ static PlaceHolderInfo * @@ -4892,6 +4910,9 @@ copyObject(const void *from) case T_AppendRelInfo: retval = _copyAppendRelInfo(from); break; + case T_PartitionedChildRelInfo: + retval = _copyPartitionedChildRelInfo(from); + break; case T_PlaceHolderInfo: retval = _copyPlaceHolderInfo(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 67529e3f86..d444222e38 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -896,6 +896,15 @@ _equalAppendRelInfo(const AppendRelInfo *a, const AppendRelInfo *b) } static bool +_equalPartitionedChildRelInfo(const PartitionedChildRelInfo *a, const PartitionedChildRelInfo *b) +{ + COMPARE_SCALAR_FIELD(parent_relid); + COMPARE_NODE_FIELD(child_rels); + + return true; +} + +static bool _equalPlaceHolderInfo(const PlaceHolderInfo *a, const PlaceHolderInfo *b) { COMPARE_SCALAR_FIELD(phid); @@ -3102,6 +3111,9 @@ equal(const void *a, const void *b) case T_AppendRelInfo: retval = _equalAppendRelInfo(a, b); break; + case T_PartitionedChildRelInfo: + retval = _equalPartitionedChildRelInfo(a, b); + break; case T_PlaceHolderInfo: retval = _equalPlaceHolderInfo(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 7418fbeded..1b9005fa53 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -252,6 +252,7 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node) WRITE_NODE_FIELD(planTree); WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(resultRelations); + WRITE_NODE_FIELD(nonleafResultRelations); WRITE_NODE_FIELD(subplans); WRITE_BITMAPSET_FIELD(rewindPlanIDs); WRITE_NODE_FIELD(rowMarks); @@ -344,6 +345,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); @@ -368,6 +370,7 @@ _outAppend(StringInfo str, const Append *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(appendplans); } @@ -380,6 +383,7 @@ _outMergeAppend(StringInfo str, const MergeAppend *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(mergeplans); WRITE_INT_FIELD(numCols); @@ -1808,6 +1812,7 @@ _outAppendPath(StringInfo str, const AppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); } @@ -1818,6 +1823,7 @@ _outMergeAppendPath(StringInfo str, const MergeAppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); WRITE_FLOAT_FIELD(limit_tuples, "%.0f"); } @@ -2023,6 +2029,7 @@ _outModifyTablePath(StringInfo str, const ModifyTablePath *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(subpaths); WRITE_NODE_FIELD(subroots); @@ -2099,6 +2106,7 @@ _outPlannerGlobal(StringInfo str, const PlannerGlobal *node) WRITE_NODE_FIELD(finalrtable); WRITE_NODE_FIELD(finalrowmarks); WRITE_NODE_FIELD(resultRelations); + WRITE_NODE_FIELD(nonleafResultRelations); WRITE_NODE_FIELD(relationOids); WRITE_NODE_FIELD(invalItems); WRITE_INT_FIELD(nParamExec); @@ -2137,6 +2145,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node) WRITE_NODE_FIELD(full_join_clauses); WRITE_NODE_FIELD(join_info_list); WRITE_NODE_FIELD(append_rel_list); + WRITE_NODE_FIELD(pcinfo_list); WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(placeholder_list); WRITE_NODE_FIELD(fkey_list); @@ -2420,6 +2429,15 @@ _outAppendRelInfo(StringInfo str, const AppendRelInfo *node) } static void +_outPartitionedChildRelInfo(StringInfo str, const PartitionedChildRelInfo *node) +{ + WRITE_NODE_TYPE("PARTITIONEDCHILDRELINFO"); + + WRITE_UINT_FIELD(parent_relid); + WRITE_NODE_FIELD(child_rels); +} + +static void _outPlaceHolderInfo(StringInfo str, const PlaceHolderInfo *node) { WRITE_NODE_TYPE("PLACEHOLDERINFO"); @@ -3906,6 +3924,9 @@ outNode(StringInfo str, const void *obj) case T_AppendRelInfo: _outAppendRelInfo(str, obj); break; + case T_PartitionedChildRelInfo: + _outPartitionedChildRelInfo(str, obj); + break; case T_PlaceHolderInfo: _outPlaceHolderInfo(str, obj); break; diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index d3bbc02f24..474f221a75 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1444,6 +1444,7 @@ _readPlannedStmt(void) READ_NODE_FIELD(planTree); READ_NODE_FIELD(rtable); READ_NODE_FIELD(resultRelations); + READ_NODE_FIELD(nonleafResultRelations); READ_NODE_FIELD(subplans); READ_BITMAPSET_FIELD(rewindPlanIDs); READ_NODE_FIELD(rowMarks); @@ -1535,6 +1536,7 @@ _readModifyTable(void) READ_ENUM_FIELD(operation, CmdType); READ_BOOL_FIELD(canSetTag); READ_UINT_FIELD(nominalRelation); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(resultRelations); READ_INT_FIELD(resultRelIndex); READ_NODE_FIELD(plans); @@ -1564,6 +1566,7 @@ _readAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(appendplans); READ_DONE(); @@ -1579,6 +1582,7 @@ _readMergeAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(mergeplans); READ_INT_FIELD(numCols); READ_ATTRNUMBER_ARRAY(sortColIdx, local_node->numCols); diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 43bfd23804..a1e1a87c29 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -95,7 +95,8 @@ static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte); static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys); + List *all_child_pathkeys, + List *partitioned_rels); static Path *get_cheapest_parameterized_child_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer); @@ -346,6 +347,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ @@ -1259,6 +1268,16 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, List *all_child_pathkeys = NIL; List *all_child_outers = NIL; ListCell *l; + List *partitioned_rels = NIL; + RangeTblEntry *rte; + + rte = planner_rt_fetch(rel->relid, root); + if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + partitioned_rels = get_partitioned_child_rels(root, rel->relid); + /* The root partitioned table is included as a child rel */ + Assert(list_length(partitioned_rels) >= 1); + } /* * For every non-dummy child, remember the cheapest path. Also, identify @@ -1359,7 +1378,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, * if we have zero or one live subpath due to constraint exclusion.) */ if (subpaths_valid) - add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0, + partitioned_rels)); /* * Consider an append of partial unordered, unparameterized partial paths. @@ -1386,7 +1406,7 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, /* Generate a partial append path. */ appendpath = create_append_path(rel, partial_subpaths, NULL, - parallel_workers); + parallel_workers, partitioned_rels); add_partial_path(rel, (Path *) appendpath); } @@ -1396,7 +1416,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, */ if (subpaths_valid) generate_mergeappend_paths(root, rel, live_childrels, - all_child_pathkeys); + all_child_pathkeys, + partitioned_rels); /* * Build Append paths for each parameterization seen among the child rels. @@ -1438,7 +1459,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, if (subpaths_valid) add_path(rel, (Path *) - create_append_path(rel, subpaths, required_outer, 0)); + create_append_path(rel, subpaths, required_outer, 0, + partitioned_rels)); } } @@ -1468,7 +1490,8 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel, static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys) + List *all_child_pathkeys, + List *partitioned_rels) { ListCell *lcp; @@ -1532,13 +1555,15 @@ generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, rel, startup_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); if (startup_neq_total) add_path(rel, (Path *) create_merge_append_path(root, rel, total_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); } } @@ -1671,7 +1696,7 @@ set_dummy_rel_pathlist(RelOptInfo *rel) rel->pathlist = NIL; rel->partial_pathlist = NIL; - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* * We set the cheapest path immediately, to ensure that IS_DUMMY_REL() diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 0551668976..6a0c67b9ab 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -1217,7 +1217,7 @@ mark_dummy_rel(RelOptInfo *rel) rel->partial_pathlist = NIL; /* Set up the dummy path */ - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* Set or update cheapest_total_path and related fields */ set_cheapest(rel); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 89e1946fc2..c80c9992c9 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -199,7 +199,7 @@ static CteScan *make_ctescan(List *qptlist, List *qpqual, Index scanrelid, int ctePlanId, int cteParam); static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual, Index scanrelid, int wtParam); -static Append *make_append(List *appendplans, List *tlist); +static Append *make_append(List *appendplans, List *tlist, List *partitioned_rels); static RecursiveUnion *make_recursive_union(List *tlist, Plan *lefttree, Plan *righttree, @@ -273,7 +273,7 @@ static Result *make_result(List *tlist, Node *resconstantqual, Plan *subplan); static ProjectSet *make_project_set(List *tlist, Plan *subplan); static ModifyTable *make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam); @@ -1026,7 +1026,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path) * parent-rel Vars it'll be asked to emit. */ - plan = make_append(subplans, tlist); + plan = make_append(subplans, tlist, best_path->partitioned_rels); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -1134,6 +1134,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path) subplans = lappend(subplans, subplan); } + node->partitioned_rels = best_path->partitioned_rels; node->mergeplans = subplans; return (Plan *) node; @@ -2314,6 +2315,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->operation, best_path->canSetTag, best_path->nominalRelation, + best_path->partitioned_rels, best_path->resultRelations, subplans, best_path->withCheckOptionLists, @@ -5161,7 +5163,7 @@ make_foreignscan(List *qptlist, } static Append * -make_append(List *appendplans, List *tlist) +make_append(List *appendplans, List *tlist, List *partitioned_rels) { Append *node = makeNode(Append); Plan *plan = &node->plan; @@ -5170,6 +5172,7 @@ make_append(List *appendplans, List *tlist) plan->qual = NIL; plan->lefttree = NULL; plan->righttree = NULL; + node->partitioned_rels = partitioned_rels; node->appendplans = appendplans; return node; @@ -6282,7 +6285,7 @@ make_project_set(List *tlist, static ModifyTable * make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam) @@ -6308,6 +6311,7 @@ make_modifytable(PlannerInfo *root, node->operation = operation; node->canSetTag = canSetTag; node->nominalRelation = nominalRelation; + node->partitioned_rels = partitioned_rels; node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 02286d9c52..cbdea1f537 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -212,6 +212,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) glob->finalrtable = NIL; glob->finalrowmarks = NIL; glob->resultRelations = NIL; + glob->nonleafResultRelations = NIL; glob->relationOids = NIL; glob->invalItems = NIL; glob->nParamExec = 0; @@ -380,6 +381,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) Assert(glob->finalrtable == NIL); Assert(glob->finalrowmarks == NIL); Assert(glob->resultRelations == NIL); + Assert(glob->nonleafResultRelations == NIL); top_plan = set_plan_references(root, top_plan); /* ... and the subplans (both regular subplans and initplans) */ Assert(list_length(glob->subplans) == list_length(glob->subroots)); @@ -405,6 +407,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) result->planTree = top_plan; result->rtable = glob->finalrtable; result->resultRelations = glob->resultRelations; + result->nonleafResultRelations = glob->nonleafResultRelations; result->subplans = glob->subplans; result->rewindPlanIDs = glob->rewindPlanIDs; result->rowMarks = glob->finalrowmarks; @@ -474,6 +477,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse, root->multiexpr_params = NIL; root->eq_classes = NIL; root->append_rel_list = NIL; + root->pcinfo_list = NIL; root->rowMarks = NIL; memset(root->upper_rels, 0, sizeof(root->upper_rels)); memset(root->upper_targets, 0, sizeof(root->upper_targets)); @@ -1007,6 +1011,8 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; + List *partitioned_rels = NIL; Assert(parse->commandType != CMD_INSERT); @@ -1065,13 +1071,24 @@ inheritance_planner(PlannerInfo *root) } /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because the RTEs added for partitioned tables + * (including the root parent) as child members of the inheritance set + * do not appear anywhere else in the plan. The situation is exactly + * the opposite in the case of non-partitioned inheritance parent as + * described below. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * And now we can get on with generating a plan for each child table. */ foreach(lc, root->append_rel_list) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1216,15 +1233,25 @@ inheritance_planner(PlannerInfo *root) grouping_planner(subroot, true, 0.0 /* retrieve all tuples */ ); /* - * We'll use the first child relation (even if it's excluded) as the - * nominal target relation of the ModifyTable node. Because of the - * way expand_inherited_rtentry works, this should always be the RTE - * representing the parent table in its role as a simple member of the - * inheritance set. (It would be logically cleaner to use the - * inheritance parent RTE as the nominal target; but since that RTE - * will not be otherwise referenced in the plan, doing so would give - * rise to confusing use of multiple aliases in EXPLAIN output for - * what the user will think is the "same" table.) + * Set the nomimal target relation of the ModifyTable node if not + * already done. We use the inheritance parent RTE as the nominal + * target relation if it's a partitioned table (see just above this + * loop). In the non-partitioned parent case, we'll use the first + * child relation (even if it's excluded) as the nominal target + * relation. Because of the way expand_inherited_rtentry works, the + * latter should be the RTE representing the parent table in its role + * as a simple member of the inheritance set. + * + * It would be logically cleaner to *always* use the inheritance + * parent RTE as the nominal relation; but that RTE is not otherwise + * referenced in the plan in the non-partitioned inheritance case. + * Instead the duplicate child RTE created by expand_inherited_rtentry + * is used elsewhere in the plan, so using the original parent RTE + * would give rise to confusing use of multiple aliases in EXPLAIN + * output for what the user will think is the "same" table. OTOH, + * it's not a problem in the partitioned inheritance case, because + * the duplicate child RTE added for the parent does not appear + * anywhere else in the plan tree. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; @@ -1298,6 +1325,13 @@ inheritance_planner(PlannerInfo *root) Assert(!parse->onConflict); } + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + { + partitioned_rels = get_partitioned_child_rels(root, parentRTindex); + /* The root partitioned table is included as a child rel */ + Assert(list_length(partitioned_rels) >= 1); + } + /* Result path must go into outer query's FINAL upperrel */ final_rel = fetch_upper_rel(root, UPPERREL_FINAL, NULL); @@ -1351,6 +1385,7 @@ inheritance_planner(PlannerInfo *root) parse->commandType, parse->canSetTag, nominalRelation, + partitioned_rels, resultRelations, subpaths, subroots, @@ -2046,6 +2081,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, parse->commandType, parse->canSetTag, parse->resultRelation, + NIL, list_make1_int(parse->resultRelation), list_make1(path), list_make1(root), @@ -3348,7 +3384,8 @@ create_grouping_paths(PlannerInfo *root, create_append_path(grouped_rel, paths, NULL, - 0); + 0, + NIL); path->pathtarget = target; } else @@ -5470,3 +5507,33 @@ plan_cluster_use_sort(Oid tableOid, Oid indexOid) return (seqScanAndSortPath.total_cost < indexScanPath->path.total_cost); } + +/* + * get_partitioned_child_rels + * Returns a list of the RT indexes of the partitioned child relations + * with rti as the root parent RT index. + * + * Note: Only call this function on RTEs known to be partitioned tables. + */ +List * +get_partitioned_child_rels(PlannerInfo *root, Index rti) +{ + List *result = NIL; + ListCell *l; + + foreach(l, root->pcinfo_list) + { + PartitionedChildRelInfo *pc = lfirst(l); + + if (pc->parent_relid == rti) + { + result = pc->child_rels; + break; + } + } + + /* The root partitioned table is included as a child rel */ + Assert(list_length(result) >= 1); + + return result; +} diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 5f3027e96f..5930747eba 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -835,6 +835,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) splan->nominalRelation += rtoffset; splan->exclRelRTI += rtoffset; + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->resultRelations) { lfirst_int(l) += rtoffset; @@ -863,6 +867,15 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) root->glob->resultRelations = list_concat(root->glob->resultRelations, list_copy(splan->resultRelations)); + + /* + * If the main target relation is a partitioned table, the + * following list contains the RT indexes of partitioned child + * relations, which are not included in the above list. + */ + root->glob->nonleafResultRelations = + list_concat(root->glob->nonleafResultRelations, + list_copy(splan->partitioned_rels)); } break; case T_Append: @@ -875,6 +888,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->appendplans) { lfirst(l) = set_plan_refs(root, @@ -893,6 +910,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->mergeplans) { lfirst(l) = set_plan_refs(root, diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..d88738ec7c 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -566,7 +566,7 @@ generate_union_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -678,7 +678,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -1364,6 +1364,9 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) List *inhOIDs; List *appinfos; ListCell *l; + bool need_append; + PartitionedChildRelInfo *pcinfo; + List *partitioned_child_rels = NIL; /* Does RT entry allow inheritance? */ if (!rte->inh) @@ -1435,6 +1438,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) /* Scan the inheritance set and expand it */ appinfos = NIL; + need_append = false; foreach(l, inhOIDs) { Oid childOID = lfirst_oid(l); @@ -1483,36 +1487,46 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) childRTindex = list_length(parse->rtable); /* - * Build an AppendRelInfo for this parent and child. + * Build an AppendRelInfo for this parent and child, unless the child + * is a partitioned table. */ - appinfo = makeNode(AppendRelInfo); - appinfo->parent_relid = rti; - appinfo->child_relid = childRTindex; - appinfo->parent_reltype = oldrelation->rd_rel->reltype; - appinfo->child_reltype = newrelation->rd_rel->reltype; - make_inh_translation_list(oldrelation, newrelation, childRTindex, - &appinfo->translated_vars); - appinfo->parent_reloid = parentOID; - appinfos = lappend(appinfos, appinfo); - - /* - * Translate the column permissions bitmaps to the child's attnums (we - * have to build the translated_vars list before we can do this). But - * if this is the parent table, leave copyObject's result alone. - * - * Note: we need to do this even though the executor won't run any - * permissions checks on the child RTE. The insertedCols/updatedCols - * bitmaps may be examined for trigger-firing purposes. - */ - if (childOID != parentOID) + if (childrte->relkind != RELKIND_PARTITIONED_TABLE) { - childrte->selectedCols = translate_col_privs(rte->selectedCols, + need_append = true; + appinfo = makeNode(AppendRelInfo); + appinfo->parent_relid = rti; + appinfo->child_relid = childRTindex; + appinfo->parent_reltype = oldrelation->rd_rel->reltype; + appinfo->child_reltype = newrelation->rd_rel->reltype; + make_inh_translation_list(oldrelation, newrelation, childRTindex, + &appinfo->translated_vars); + appinfo->parent_reloid = parentOID; + appinfos = lappend(appinfos, appinfo); + + /* + * Translate the column permissions bitmaps to the child's attnums + * (we have to build the translated_vars list before we can do + * this). But if this is the parent table, leave copyObject's + * result alone. + * + * Note: we need to do this even though the executor won't run any + * permissions checks on the child RTE. The + * insertedCols/updatedCols bitmaps may be examined for + * trigger-firing purposes. + */ + if (childOID != parentOID) + { + childrte->selectedCols = translate_col_privs(rte->selectedCols, appinfo->translated_vars); - childrte->insertedCols = translate_col_privs(rte->insertedCols, + childrte->insertedCols = translate_col_privs(rte->insertedCols, appinfo->translated_vars); - childrte->updatedCols = translate_col_privs(rte->updatedCols, + childrte->updatedCols = translate_col_privs(rte->updatedCols, appinfo->translated_vars); + } } + else + partitioned_child_rels = lappend_int(partitioned_child_rels, + childRTindex); /* * Build a PlanRowMark if parent is marked FOR UPDATE/SHARE. @@ -1529,7 +1543,13 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->allMarkTypes = (1 << newrc->markType); newrc->strength = oldrc->strength; newrc->waitPolicy = oldrc->waitPolicy; - newrc->isParent = false; + + /* + * We mark RowMarks for partitioned child tables as parent RowMarks + * so that the executor ignores them (except their existence means + * that the child tables be locked using appropriate mode). + */ + newrc->isParent = (childrte->relkind == RELKIND_PARTITIONED_TABLE); /* Include child's rowmark type in parent's allMarkTypes */ oldrc->allMarkTypes |= newrc->allMarkTypes; @@ -1545,18 +1565,37 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) heap_close(oldrelation, NoLock); /* - * If all the children were temp tables, pretend it's a non-inheritance - * situation. The duplicate RTE we added for the parent table is - * harmless, so we don't bother to get rid of it; ditto for the useless - * PlanRowMark node. + * If all the children were temp tables or a partitioned parent did not + * have any leaf partitions, pretend it's a non-inheritance situation; we + * don't need Append node in that case. The duplicate RTE we added for + * the parent table is harmless, so we don't bother to get rid of it; + * ditto for the useless PlanRowMark node. */ - if (list_length(appinfos) < 2) + if (!need_append) { /* Clear flag before returning */ rte->inh = false; return; } + /* + * We keep a list of objects in root, each of which maps a partitioned + * parent RT index to the list of RT indexes of its partitioned child + * tables. When creating an Append or a ModifyTable path for the parent, + * we copy the child RT index list verbatim to the path so that it could + * be carried over to the executor so that the latter could identify + * the partitioned child tables. + */ + if (partitioned_child_rels != NIL) + { + pcinfo = makeNode(PartitionedChildRelInfo); + + Assert(rte->relkind == RELKIND_PARTITIONED_TABLE); + pcinfo->parent_relid = rti; + pcinfo->child_rels = partitioned_child_rels; + root->pcinfo_list = lappend(root->pcinfo_list, pcinfo); + } + /* Otherwise, OK to add to root->append_rel_list */ root->append_rel_list = list_concat(root->append_rel_list, appinfos); } diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 8ce772d274..fca96eb001 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1201,7 +1201,7 @@ create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, */ AppendPath * create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, - int parallel_workers) + int parallel_workers, List *partitioned_rels) { AppendPath *pathnode = makeNode(AppendPath); ListCell *l; @@ -1216,6 +1216,7 @@ create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, pathnode->path.parallel_workers = parallel_workers; pathnode->path.pathkeys = NIL; /* result is always considered * unsorted */ + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -1258,7 +1259,8 @@ create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer) + Relids required_outer, + List *partitioned_rels) { MergeAppendPath *pathnode = makeNode(MergeAppendPath); Cost input_startup_cost; @@ -1274,6 +1276,7 @@ create_merge_append_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = pathkeys; + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -3105,7 +3108,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, ModifyTablePath * create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, @@ -3172,6 +3175,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->operation = operation; pathnode->canSetTag = canSetTag; pathnode->nominalRelation = nominalRelation; + pathnode->partitioned_rels = partitioned_rels; pathnode->resultRelations = resultRelations; pathnode->subpaths = subpaths; pathnode->subroots = subroots; diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index fa02374811..d284ab7d3d 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -1514,7 +1514,8 @@ AcquireExecutorLocks(List *stmt_list, bool acquire) * fail if it's been dropped entirely --- we'll just transiently * acquire a non-conflicting lock. */ - if (list_member_int(plannedstmt->resultRelations, rt_index)) + if (list_member_int(plannedstmt->resultRelations, rt_index) || + list_member_int(plannedstmt->nonleafResultRelations, rt_index)) lockmode = RowExclusiveLock; else if ((rc = get_plan_rowmark(plannedstmt->rowMarks, rt_index)) != NULL && RowMarkRequiresRowShareLock(rc->markType)) diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 02dbe7b228..e64d6fb93f 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -375,6 +375,7 @@ extern void RegisterExprContextCallback(ExprContext *econtext, extern void UnregisterExprContextCallback(ExprContext *econtext, ExprContextCallbackFunction function, Datum arg); +extern void ExecLockNonLeafAppendTables(List *partitioned_rels, EState *estate); /* * prototypes from functions in execIndexing.c diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 2bc7a5df11..2cbd6d77b8 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -275,6 +275,7 @@ typedef enum NodeTag T_PlaceHolderVar, T_SpecialJoinInfo, T_AppendRelInfo, + T_PartitionedChildRelInfo, T_PlaceHolderInfo, T_MinMaxAggInfo, T_PlannerParamItem, diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index b880dc16cf..4a95e16b69 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -65,6 +65,9 @@ typedef struct PlannedStmt /* rtable indexes of target relations for INSERT/UPDATE/DELETE */ List *resultRelations; /* integer list of RT indexes, or NIL */ + /* rtable indexes of non-leaf target relations for INSERT/UPDATE/DELETE */ + List *nonleafResultRelations; + List *subplans; /* Plan trees for SubPlan expressions */ Bitmapset *rewindPlanIDs; /* indices of subplans that require REWIND */ @@ -202,6 +205,8 @@ typedef struct ModifyTable CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *resultRelations; /* integer list of RT indexes */ int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ @@ -227,6 +232,8 @@ typedef struct ModifyTable typedef struct Append { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *appendplans; } Append; @@ -238,6 +245,8 @@ typedef struct Append typedef struct MergeAppend { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *mergeplans; /* remaining fields are just like the sort-key info in struct Sort */ int numCols; /* number of sort-key columns */ @@ -937,11 +946,12 @@ typedef enum RowMarkType * When the planner discovers that a relation is the root of an inheritance * tree, it sets isParent true, and adds an additional PlanRowMark to the * list for each child relation (including the target rel itself in its role - * as a child). The child entries have rti == child rel's RT index and - * prti == parent's RT index, and can therefore be recognized as children by - * the fact that prti != rti. The parent's allMarkTypes field gets the OR - * of (1<<markType) across all its children (this definition allows children - * to use different markTypes). + * as a child). isParent is also set to true for the partitioned child + * relations, which are not scanned just like the root parent. The child + * entries have rti == child rel's RT index and prti == parent's RT index, + * and can therefore be recognized as children by the fact that prti != rti. + * The parent's allMarkTypes field gets the OR of (1<<markType) across all + * its children (this definition allows children to use different markTypes). * * The planner also adds resjunk output columns to the plan that carry * information sufficient to identify the locked or fetched rows. When diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 05d6f07aea..1c88a79a21 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -107,6 +107,8 @@ typedef struct PlannerGlobal List *resultRelations; /* "flat" list of integer RT indexes */ + List *nonleafResultRelations; /* "flat" list of integer RT indexes */ + List *relationOids; /* OIDs of relations the plan depends on */ List *invalItems; /* other dependencies, as PlanInvalItems */ @@ -248,6 +250,8 @@ typedef struct PlannerInfo List *append_rel_list; /* list of AppendRelInfos */ + List *pcinfo_list; /* list of PartitionedChildRelInfos */ + List *rowMarks; /* list of PlanRowMarks */ List *placeholder_list; /* list of PlaceHolderInfos */ @@ -1116,6 +1120,8 @@ typedef struct CustomPath typedef struct AppendPath { Path path; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *subpaths; /* list of component Paths */ } AppendPath; @@ -1134,6 +1140,8 @@ typedef struct AppendPath typedef struct MergeAppendPath { Path path; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *subpaths; /* list of component Paths */ double limit_tuples; /* hard limit on output tuples, or -1 */ } MergeAppendPath; @@ -1482,6 +1490,8 @@ typedef struct ModifyTablePath CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *resultRelations; /* integer list of RT indexes */ List *subpaths; /* Path(s) producing source data */ List *subroots; /* per-target-table PlannerInfos */ @@ -1836,10 +1846,10 @@ typedef struct SpecialJoinInfo * * When we expand an inheritable table or a UNION-ALL subselect into an * "append relation" (essentially, a list of child RTEs), we build an - * AppendRelInfo for each child RTE. The list of AppendRelInfos indicates - * which child RTEs must be included when expanding the parent, and each - * node carries information needed to translate Vars referencing the parent - * into Vars referencing that child. + * AppendRelInfo for each non-partitioned child RTE. The list of + * AppendRelInfos indicates which child RTEs must be included when expanding + * the parent, and each node carries information needed to translate Vars + * referencing the parent into Vars referencing that child. * * These structs are kept in the PlannerInfo node's append_rel_list. * Note that we just throw all the structs into one list, and scan the @@ -1914,6 +1924,25 @@ typedef struct AppendRelInfo } AppendRelInfo; /* + * For a partitioned table, this maps its RT index to the list of RT indexes + * of the partitioned child tables in the partition tree. We need to + * separately store this information, because we do not create AppendRelInfos + * for the partitioned child tables of a parent table, since AppendRelInfos + * contain information that is unnecessary for the partitioned child tables. + * The child_rels list must contain at least one element, because the parent + * partitioned table is itself counted as a child. + * + * These structs are kept in the PlannerInfo node's pcinfo_list. + */ +typedef struct PartitionedChildRelInfo +{ + NodeTag type; + + Index parent_relid; + List *child_rels; +} PartitionedChildRelInfo; + +/* * For each distinct placeholder expression generated during planning, we * store a PlaceHolderInfo node in the PlannerInfo node's placeholder_list. * This stores info that is needed centrally rather than in each copy of the diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 373c7221a8..81640de7ab 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -64,12 +64,14 @@ extern BitmapOrPath *create_bitmap_or_path(PlannerInfo *root, extern TidPath *create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, Relids required_outer); extern AppendPath *create_append_path(RelOptInfo *rel, List *subpaths, - Relids required_outer, int parallel_workers); + Relids required_outer, int parallel_workers, + List *partitioned_rels); extern MergeAppendPath *create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer); + Relids required_outer, + List *partitioned_rels); extern ResultPath *create_result_path(PlannerInfo *root, RelOptInfo *rel, PathTarget *target, List *resconstantqual); extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath); @@ -232,7 +234,7 @@ extern LockRowsPath *create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h index 6922933e36..f3aaa23975 100644 --- a/src/include/optimizer/planner.h +++ b/src/include/optimizer/planner.h @@ -57,4 +57,6 @@ extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr); extern bool plan_cluster_use_sort(Oid tableOid, Oid indexOid); +extern List *get_partitioned_child_rels(PlannerInfo *root, Index rti); + #endif /* PLANNER_H */ diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6494b205c4..6163ed8117 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -588,6 +588,45 @@ select tableoid::regclass::text as relname, bar.* from bar order by 1,2; bar2 | 4 | 104 (8 rows) +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + relname | a | b +------------------+---+--- + parted_tab_part1 | 1 | b + parted_tab_part2 | 2 | b + parted_tab_part3 | 3 | a +(3 rows) + +truncate parted_tab; +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select 0 from parted_tab union all select 1 from parted_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + relname | a | b +------------------+---+--- + parted_tab_part1 | 1 | b + parted_tab_part2 | 2 | a + parted_tab_part3 | 3 | a +(3 rows) + +drop table parted_tab; +drop table some_tab cascade; +NOTICE: drop cascades to table some_tab_child /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); CREATE TABLE secondparent (tomorrow date default now() :: date + 1); @@ -1611,71 +1650,60 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, @@ -1695,14 +1723,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1712,36 +1735,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1750,27 +1759,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1782,37 +1783,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1831,23 +1812,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted; drop table range_list_parted; diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index b18e420e9b..d3794140fb 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -322,12 +322,10 @@ explain (costs off) QUERY PLAN ------------------------------------------- Append - -> Sample Scan on parted_sample - Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_1 Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_2 Sampling: bernoulli ('100'::real) -(7 rows) +(5 rows) drop table parted_sample, parted_sample_1, parted_sample_2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index e3e9e34895..d43b75c4a7 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -128,6 +128,34 @@ where bar.f1 = ss.f1; select tableoid::regclass::text as relname, bar.* from bar order by 1,2; +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); + +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + +truncate parted_tab; +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select 0 from parted_tab union all select 1 from parted_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + +drop table parted_tab; +drop table some_tab cascade; + /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); -- 2.11.0 --------------4ED6FA12B404EE36866223D4 Content-Type: text/x-diff; name="0002-Do-not-allocate-storage-for-partitioned-tables.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0002-Do-not-allocate-storage-for-partitioned-tables.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 1/2] Avoid creating scan nodes for partitioned tables @ 2017-03-10 04:48 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-03-10 04:48 UTC (permalink / raw) * Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we generate only a minimal AppendRelInfo for partitioned child tables. No need to generate translated_vars, for example. We need to hold on to the child_relid's, because we want to save it into the Append/ MergeAppend/ModifyTable node we will generate for the inheritance tree. * The planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member other than the parent itself which is also a child member. That means set_rel_size/pathlist will not create an Append node but a scan node for the parent RTE. We want to avoid that if the parent is a partitioned table, by noticing that in set_rel_size(). Per suggestion from Ashutosh Bapat. * Add a new partitioned_rels field to ModifyTable, Append, and MergeAppend nodes, which holds the RT indexes of all the non-leaf tables in a partition tree. Use the same to lock those tables during execution. The usual method employed by InitPlan to lock the tables does not work for partitioned tables, because they do not appear in the subplans list of either of those nodes. * In inheritance_planner, set ModifyTable.nominalRelation to parent RT index in the partitioned inheritance parent case, so that EXPLAIN uses the root tables's original RT index to label a given ModifyTable node. Regression test outputs are adjusted to match the new plan shape. --- src/backend/executor/nodeAppend.c | 39 +++++++++ src/backend/executor/nodeMergeAppend.c | 39 +++++++++ src/backend/executor/nodeModifyTable.c | 36 +++++++- src/backend/nodes/copyfuncs.c | 4 + src/backend/nodes/equalfuncs.c | 1 + src/backend/nodes/outfuncs.c | 7 ++ src/backend/nodes/readfuncs.c | 3 + src/backend/optimizer/path/allpaths.c | 49 +++++++++-- src/backend/optimizer/path/joinrels.c | 2 +- src/backend/optimizer/plan/createplan.c | 14 ++-- src/backend/optimizer/plan/initsplan.c | 6 ++ src/backend/optimizer/plan/planner.c | 61 +++++++++++--- src/backend/optimizer/plan/setrefs.c | 12 +++ src/backend/optimizer/prep/prepunion.c | 59 ++++++++----- src/backend/optimizer/util/pathnode.c | 10 ++- src/backend/utils/cache/plancache.c | 7 ++ src/include/nodes/plannodes.h | 6 ++ src/include/nodes/relation.h | 4 + src/include/optimizer/pathnode.h | 8 +- src/test/regress/expected/inherit.out | 134 +++++++++++------------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 22 +++++ 22 files changed, 386 insertions(+), 141 deletions(-) diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c index 6986caee6b..26be346199 100644 --- a/src/backend/executor/nodeAppend.c +++ b/src/backend/executor/nodeAppend.c @@ -59,6 +59,8 @@ #include "executor/execdebug.h" #include "executor/nodeAppend.h" +#include "parser/parsetree.h" +#include "storage/lmgr.h" static bool exec_append_initialize_next(AppendState *appendstate); @@ -129,6 +131,43 @@ ExecInitAppend(Append *node, EState *estate, int eflags) Assert(!(eflags & EXEC_FLAG_MARK)); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node; leaf tables will be locked below when initializing the node's + * subplans. Note that this occurs only if the root inheritance parent + * is a partitioned table. + */ + if (node->partitioned_rels) + { + foreach(lc, node->partitioned_rels) + { + Index rti = lfirst_int(lc); + PlanRowMark *rc = NULL; + ListCell *l; + Oid relid; + LOCKMODE lockmode; + + relid = getrelid(rti, estate->es_range_table); + + /* If there is a RowMark, we better adjust the lockmode */ + foreach(l, estate->es_plannedstmt->rowMarks) + { + rc = (PlanRowMark *) lfirst(l); + + if (rc->rti == rti) + break; + } + + Assert(rc == NULL || rc->isParent); + if (rc != NULL && RowMarkRequiresRowShareLock(rc->markType)) + lockmode = RowShareLock; + else + lockmode = AccessShareLock; + + LockRelationOid(relid, lockmode); + } + } + + /* * Set up empty vector of subplan states */ nplans = list_length(node->appendplans); diff --git a/src/backend/executor/nodeMergeAppend.c b/src/backend/executor/nodeMergeAppend.c index 7a20bf07a4..0b5f82cf20 100644 --- a/src/backend/executor/nodeMergeAppend.c +++ b/src/backend/executor/nodeMergeAppend.c @@ -42,6 +42,8 @@ #include "executor/nodeMergeAppend.h" #include "lib/binaryheap.h" +#include "parser/parsetree.h" +#include "storage/lmgr.h" /* * We have one slot for each item in the heap array. We use SlotNumber @@ -72,6 +74,43 @@ ExecInitMergeAppend(MergeAppend *node, EState *estate, int eflags) Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node; leaf tables will be locked below when initializing the node's + * subplans. Note that this occurs only if the root inheritance parent + * is a partitioned table. + */ + if (node->partitioned_rels) + { + foreach(lc, node->partitioned_rels) + { + Index rti = lfirst_int(lc); + PlanRowMark *rc = NULL; + ListCell *l; + Oid relid; + LOCKMODE lockmode; + + relid = getrelid(rti, estate->es_range_table); + + /* If there is a RowMark, we better adjust the lockmode */ + foreach(l, estate->es_plannedstmt->rowMarks) + { + rc = (PlanRowMark *) lfirst(l); + + if (rc->rti == rti) + break; + } + + Assert(rc == NULL || rc->isParent); + if (rc != NULL && RowMarkRequiresRowShareLock(rc->markType)) + lockmode = RowShareLock; + else + lockmode = AccessShareLock; + + LockRelationOid(relid, lockmode); + } + } + + /* * Set up empty vector of subplan states */ nplans = list_length(node->mergeplans); diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 95e158970c..9f548bdfac 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -45,6 +45,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/parsetree.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -1725,8 +1726,34 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = saved_resultRelInfo; + /* + * During UPDATE/DELETE of a partitioned table, node->partitioned_rels + * contains the RT indexes of non-leaf tables of the partition tree. + * Leaf tables have already been locked by InitPlan when setting up the + * ResultRelInfo's. + */ + if (node->partitioned_rels) + { + Index root_table_rti; + Oid root_table_oid; + + foreach(l, node->partitioned_rels) + { + Oid relid; + + relid = getrelid(lfirst_int(l), estate->es_range_table); + LockRelationOid(relid, RowExclusiveLock); + } + + /* We have the root table RT index at the head of the list */ + root_table_rti = linitial_int(node->partitioned_rels); + root_table_oid = getrelid(root_table_rti, estate->es_range_table); + rel = heap_open(root_table_oid, NoLock); /* locked just above */ + } + else + rel = mtstate->resultRelInfo->ri_RelationDesc; + /* Build state for INSERT tuple routing */ - rel = mtstate->resultRelInfo->ri_RelationDesc; if (operation == CMD_INSERT && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { @@ -1898,6 +1925,13 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } /* + * Close the root partitioned rel if we opened it above, but keep the + * lock. + */ + if (rel != mtstate->resultRelInfo->ri_RelationDesc) + heap_close(rel, NoLock); + + /* * If needed, Initialize target list, projection and qual for ON CONFLICT * DO UPDATE. */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bfc2ac1716..8bcac4f9dd 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -200,6 +200,7 @@ _copyModifyTable(const ModifyTable *from) COPY_SCALAR_FIELD(operation); COPY_SCALAR_FIELD(canSetTag); COPY_SCALAR_FIELD(nominalRelation); + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(resultRelations); COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); @@ -235,6 +236,7 @@ _copyAppend(const Append *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(appendplans); return newnode; @@ -256,6 +258,7 @@ _copyMergeAppend(const MergeAppend *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(mergeplans); COPY_SCALAR_FIELD(numCols); COPY_POINTER_FIELD(sortColIdx, from->numCols * sizeof(AttrNumber)); @@ -2198,6 +2201,7 @@ _copyAppendRelInfo(const AppendRelInfo *from) COPY_SCALAR_FIELD(child_relid); COPY_SCALAR_FIELD(parent_reltype); COPY_SCALAR_FIELD(child_reltype); + COPY_SCALAR_FIELD(child_relkind); COPY_NODE_FIELD(translated_vars); COPY_SCALAR_FIELD(parent_reloid); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 54e9c983a0..93a13952e5 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -889,6 +889,7 @@ _equalAppendRelInfo(const AppendRelInfo *a, const AppendRelInfo *b) COMPARE_SCALAR_FIELD(child_relid); COMPARE_SCALAR_FIELD(parent_reltype); COMPARE_SCALAR_FIELD(child_reltype); + COMPARE_SCALAR_FIELD(child_relkind); COMPARE_NODE_FIELD(translated_vars); COMPARE_SCALAR_FIELD(parent_reloid); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 7418fbeded..afa36a00ec 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -344,6 +344,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); @@ -368,6 +369,7 @@ _outAppend(StringInfo str, const Append *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(appendplans); } @@ -380,6 +382,7 @@ _outMergeAppend(StringInfo str, const MergeAppend *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(mergeplans); WRITE_INT_FIELD(numCols); @@ -1808,6 +1811,7 @@ _outAppendPath(StringInfo str, const AppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); } @@ -1818,6 +1822,7 @@ _outMergeAppendPath(StringInfo str, const MergeAppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); WRITE_FLOAT_FIELD(limit_tuples, "%.0f"); } @@ -2023,6 +2028,7 @@ _outModifyTablePath(StringInfo str, const ModifyTablePath *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(subpaths); WRITE_NODE_FIELD(subroots); @@ -2415,6 +2421,7 @@ _outAppendRelInfo(StringInfo str, const AppendRelInfo *node) WRITE_UINT_FIELD(child_relid); WRITE_OID_FIELD(parent_reltype); WRITE_OID_FIELD(child_reltype); + WRITE_CHAR_FIELD(child_relkind); WRITE_NODE_FIELD(translated_vars); WRITE_OID_FIELD(parent_reloid); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index d3bbc02f24..f2ec174216 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1535,6 +1535,7 @@ _readModifyTable(void) READ_ENUM_FIELD(operation, CmdType); READ_BOOL_FIELD(canSetTag); READ_UINT_FIELD(nominalRelation); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(resultRelations); READ_INT_FIELD(resultRelIndex); READ_NODE_FIELD(plans); @@ -1564,6 +1565,7 @@ _readAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(appendplans); READ_DONE(); @@ -1579,6 +1581,7 @@ _readMergeAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(mergeplans); READ_INT_FIELD(numCols); READ_ATTRNUMBER_ARRAY(sortColIdx, local_node->numCols); diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index b263359fde..566d1860aa 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -95,7 +95,8 @@ static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte); static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys); + List *all_child_pathkeys, + List *partitioned_rels); static Path *get_cheapest_parameterized_child_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer); @@ -344,6 +345,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ @@ -878,6 +887,10 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, if (appinfo->parent_relid != parentRTindex) continue; + /* Nothing to see here. */ + if (appinfo->child_relkind == RELKIND_PARTITIONED_TABLE) + continue; + childRTindex = appinfo->child_relid; childRTE = root->simple_rte_array[childRTindex]; @@ -1189,6 +1202,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, List *all_child_pathkeys = NIL; List *all_child_outers = NIL; ListCell *l; + List *partitioned_rels = NIL; /* * Generate access paths for each member relation, and remember the @@ -1208,6 +1222,17 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, if (appinfo->parent_relid != parentRTindex) continue; + /* + * Nothing to do here for partitioned child tables; just remember + * the RT index to put into the AppendPath. + */ + if (appinfo->child_relkind == RELKIND_PARTITIONED_TABLE) + { + partitioned_rels = lappend_int(partitioned_rels, + appinfo->child_relid); + continue; + } + /* Re-locate the child RTE and RelOptInfo */ childRTindex = appinfo->child_relid; childRTE = root->simple_rte_array[childRTindex]; @@ -1327,7 +1352,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, * if we have zero or one live subpath due to constraint exclusion.) */ if (subpaths_valid) - add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0, + partitioned_rels)); /* * Consider an append of partial unordered, unparameterized partial paths. @@ -1354,7 +1380,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, /* Generate a partial append path. */ appendpath = create_append_path(rel, partial_subpaths, NULL, - parallel_workers); + parallel_workers, partitioned_rels); add_partial_path(rel, (Path *) appendpath); } @@ -1364,7 +1390,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, */ if (subpaths_valid) generate_mergeappend_paths(root, rel, live_childrels, - all_child_pathkeys); + all_child_pathkeys, + partitioned_rels); /* * Build Append paths for each parameterization seen among the child rels. @@ -1406,7 +1433,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, if (subpaths_valid) add_path(rel, (Path *) - create_append_path(rel, subpaths, required_outer, 0)); + create_append_path(rel, subpaths, required_outer, 0, + partitioned_rels)); } } @@ -1436,7 +1464,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys) + List *all_child_pathkeys, + List *partitioned_rels) { ListCell *lcp; @@ -1500,13 +1529,15 @@ generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, rel, startup_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); if (startup_neq_total) add_path(rel, (Path *) create_merge_append_path(root, rel, total_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); } } @@ -1639,7 +1670,7 @@ set_dummy_rel_pathlist(RelOptInfo *rel) rel->pathlist = NIL; rel->partial_pathlist = NIL; - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* * We set the cheapest path immediately, to ensure that IS_DUMMY_REL() diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 0d0068360c..7dfbb7a970 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -1197,7 +1197,7 @@ mark_dummy_rel(RelOptInfo *rel) rel->partial_pathlist = NIL; /* Set up the dummy path */ - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* Set or update cheapest_total_path and related fields */ set_cheapest(rel); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index d002e6d567..6b29ccc0c4 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -199,7 +199,7 @@ static CteScan *make_ctescan(List *qptlist, List *qpqual, Index scanrelid, int ctePlanId, int cteParam); static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual, Index scanrelid, int wtParam); -static Append *make_append(List *appendplans, List *tlist); +static Append *make_append(List *appendplans, List *tlist, List *partitioned_rels); static RecursiveUnion *make_recursive_union(List *tlist, Plan *lefttree, Plan *righttree, @@ -273,7 +273,7 @@ static Result *make_result(List *tlist, Node *resconstantqual, Plan *subplan); static ProjectSet *make_project_set(List *tlist, Plan *subplan); static ModifyTable *make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam); @@ -1026,7 +1026,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path) * parent-rel Vars it'll be asked to emit. */ - plan = make_append(subplans, tlist); + plan = make_append(subplans, tlist, best_path->partitioned_rels); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -1134,6 +1134,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path) subplans = lappend(subplans, subplan); } + node->partitioned_rels = best_path->partitioned_rels; node->mergeplans = subplans; return (Plan *) node; @@ -2340,6 +2341,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->operation, best_path->canSetTag, best_path->nominalRelation, + best_path->partitioned_rels, best_path->resultRelations, subplans, best_path->withCheckOptionLists, @@ -5187,7 +5189,7 @@ make_foreignscan(List *qptlist, } static Append * -make_append(List *appendplans, List *tlist) +make_append(List *appendplans, List *tlist, List *partitioned_rels) { Append *node = makeNode(Append); Plan *plan = &node->plan; @@ -5196,6 +5198,7 @@ make_append(List *appendplans, List *tlist) plan->qual = NIL; plan->lefttree = NULL; plan->righttree = NULL; + node->partitioned_rels = partitioned_rels; node->appendplans = appendplans; return node; @@ -6308,7 +6311,7 @@ make_project_set(List *tlist, static ModifyTable * make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam) @@ -6334,6 +6337,7 @@ make_modifytable(PlannerInfo *root, node->operation = operation; node->canSetTag = canSetTag; node->nominalRelation = nominalRelation; + node->partitioned_rels = partitioned_rels; node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index b4ac224a7a..79792c406c 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -14,6 +14,7 @@ */ #include "postgres.h" +#include "catalog/pg_class.h" #include "catalog/pg_type.h" #include "nodes/nodeFuncs.h" #include "optimizer/clauses.h" @@ -642,6 +643,11 @@ create_lateral_join_info(PlannerInfo *root) if (appinfo->parent_relid != rti) continue; + + /* Need not consider these. */ + if (appinfo->child_relkind == RELKIND_PARTITIONED_TABLE) + continue; + childrel = root->simple_rel_array[appinfo->child_relid]; Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL); Assert(childrel->direct_lateral_relids == NULL); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 02286d9c52..4d43756ff7 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1007,6 +1007,8 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; + List *partitioned_rels = NIL; Assert(parse->commandType != CMD_INSERT); @@ -1055,6 +1057,13 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); + /* + * Need not consider these, because the latter planning steps + * simply ignore them. + */ + if (appinfo->child_relkind == RELKIND_PARTITIONED_TABLE) + continue; + if (bms_is_member(appinfo->parent_relid, subqueryRTindexes) || bms_is_member(appinfo->child_relid, subqueryRTindexes) || bms_overlap(pull_varnos((Node *) appinfo->translated_vars), @@ -1065,13 +1074,21 @@ inheritance_planner(PlannerInfo *root) } /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * And now we can get on with generating a plan for each child table. */ foreach(lc, root->append_rel_list) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1081,6 +1098,17 @@ inheritance_planner(PlannerInfo *root) continue; /* + * Nothing to do here for partitioned child tables; just remember + * the RT index to put into the ModifyTablePath. + */ + if (appinfo->child_relkind == RELKIND_PARTITIONED_TABLE) + { + partitioned_rels = lappend_int(partitioned_rels, + appinfo->child_relid); + continue; + } + + /* * We need a working copy of the PlannerInfo so that we can control * propagation of information back to the main copy. */ @@ -1216,15 +1244,23 @@ inheritance_planner(PlannerInfo *root) grouping_planner(subroot, true, 0.0 /* retrieve all tuples */ ); /* - * We'll use the first child relation (even if it's excluded) as the - * nominal target relation of the ModifyTable node. Because of the - * way expand_inherited_rtentry works, this should always be the RTE - * representing the parent table in its role as a simple member of the - * inheritance set. (It would be logically cleaner to use the - * inheritance parent RTE as the nominal target; but since that RTE - * will not be otherwise referenced in the plan, doing so would give - * rise to confusing use of multiple aliases in EXPLAIN output for - * what the user will think is the "same" table.) + * Set the nomimal target relation of the ModifyTable node if not + * already done. We use the inheritance parent RTE as the nominal + * target relation if it's a partitioned table (see just above this + * loop). In the non-partitioned parent case, we'll use the first + * child relation (even if it's excluded) as the nominal target + * relation. Because of the way expand_inherited_rtentry works, the + * latter should be the RTE representing the parent table in its role + * as a simple member of the inheritance set. + * + * It would be logically cleaner to *always* use the inheritance + * parent RTE as the nominal relation; but that RTE is not otherwise + * referenced in the plan in the non-partitioned inheritance case. + * Instead the duplicate RTE is used elsewhere in the plan, so using + * the original parent RTE would give rise to confusing use of multiple + * aliases in EXPLAIN output for what the user will think is the "same" + * table. It's not a problem in the partitioned inheritance case, + * because there is duplicate RTE for the parent in that case. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; @@ -1351,6 +1387,7 @@ inheritance_planner(PlannerInfo *root) parse->commandType, parse->canSetTag, nominalRelation, + partitioned_rels, resultRelations, subpaths, subroots, @@ -2046,6 +2083,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, parse->commandType, parse->canSetTag, parse->resultRelation, + NIL, list_make1_int(parse->resultRelation), list_make1(path), list_make1(root), @@ -3348,7 +3386,8 @@ create_grouping_paths(PlannerInfo *root, create_append_path(grouped_rel, paths, NULL, - 0); + 0, + NIL); path->pathtarget = target; } else diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 5f3027e96f..d148c2d37d 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -835,6 +835,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) splan->nominalRelation += rtoffset; splan->exclRelRTI += rtoffset; + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->resultRelations) { lfirst_int(l) += rtoffset; @@ -875,6 +879,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->appendplans) { lfirst(l) = set_plan_refs(root, @@ -893,6 +901,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->mergeplans) { lfirst(l) = set_plan_refs(root, diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..ece5fa7c7a 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -566,7 +566,7 @@ generate_union_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -678,7 +678,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -1352,6 +1352,12 @@ expand_inherited_tables(PlannerInfo *root) * * A childless table is never considered to be an inheritance set; therefore * a parent RTE must always have at least two associated AppendRelInfos. + * + * AppendRelInfo's created for each child table normally contains information + * necessary to create a scan plan for the table. However, for child tables + * that are partitioned (which contains no data), we need not create scan + * plans; so create a minimal AppendRelInfo in that case containing only the + * parent-child RT index pair. */ static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) @@ -1488,32 +1494,42 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) appinfo = makeNode(AppendRelInfo); appinfo->parent_relid = rti; appinfo->child_relid = childRTindex; - appinfo->parent_reltype = oldrelation->rd_rel->reltype; - appinfo->child_reltype = newrelation->rd_rel->reltype; - make_inh_translation_list(oldrelation, newrelation, childRTindex, - &appinfo->translated_vars); - appinfo->parent_reloid = parentOID; - appinfos = lappend(appinfos, appinfo); + appinfo->child_relkind = childrte->relkind; /* - * Translate the column permissions bitmaps to the child's attnums (we - * have to build the translated_vars list before we can do this). But - * if this is the parent table, leave copyObject's result alone. - * - * Note: we need to do this even though the executor won't run any - * permissions checks on the child RTE. The insertedCols/updatedCols - * bitmaps may be examined for trigger-firing purposes. + * No need to fill the following information for partitioned table + * appinfos, because we won't be needing it. */ - if (childOID != parentOID) + if (appinfo->child_relkind != RELKIND_PARTITIONED_TABLE) { - childrte->selectedCols = translate_col_privs(rte->selectedCols, + appinfo->parent_reltype = oldrelation->rd_rel->reltype; + appinfo->child_reltype = newrelation->rd_rel->reltype; + make_inh_translation_list(oldrelation, newrelation, childRTindex, + &appinfo->translated_vars); + appinfo->parent_reloid = parentOID; + + /* + * Translate the column permissions bitmaps to the child's attnums + * (we have to build the translated_vars list before we can do + * this). But if this is the parent table, leave copyObject's + * result alone. Note: we need to do this even though the executor + * won't run any permissions checks on the child RTE. The + * insertedCols/updatedCols bitmaps may be examined for + * trigger-firing purposes. + */ + if (childOID != parentOID) + { + childrte->selectedCols = translate_col_privs(rte->selectedCols, appinfo->translated_vars); - childrte->insertedCols = translate_col_privs(rte->insertedCols, + childrte->insertedCols = translate_col_privs(rte->insertedCols, appinfo->translated_vars); - childrte->updatedCols = translate_col_privs(rte->updatedCols, + childrte->updatedCols = translate_col_privs(rte->updatedCols, appinfo->translated_vars); + } } + appinfos = lappend(appinfos, appinfo); + /* * Build a PlanRowMark if parent is marked FOR UPDATE/SHARE. */ @@ -1529,7 +1545,10 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->allMarkTypes = (1 << newrc->markType); newrc->strength = oldrc->strength; newrc->waitPolicy = oldrc->waitPolicy; - newrc->isParent = false; + /* + * If child is a partitioned table, this is a "dummy" parent entry + */ + newrc->isParent = (rte->relkind == RELKIND_PARTITIONED_TABLE); /* Include child's rowmark type in parent's allMarkTypes */ oldrc->allMarkTypes |= newrc->allMarkTypes; diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 8ce772d274..fca96eb001 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1201,7 +1201,7 @@ create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, */ AppendPath * create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, - int parallel_workers) + int parallel_workers, List *partitioned_rels) { AppendPath *pathnode = makeNode(AppendPath); ListCell *l; @@ -1216,6 +1216,7 @@ create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, pathnode->path.parallel_workers = parallel_workers; pathnode->path.pathkeys = NIL; /* result is always considered * unsorted */ + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -1258,7 +1259,8 @@ create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer) + Relids required_outer, + List *partitioned_rels) { MergeAppendPath *pathnode = makeNode(MergeAppendPath); Cost input_startup_cost; @@ -1274,6 +1276,7 @@ create_merge_append_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = pathkeys; + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -3105,7 +3108,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, ModifyTablePath * create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, @@ -3172,6 +3175,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->operation = operation; pathnode->canSetTag = canSetTag; pathnode->nominalRelation = nominalRelation; + pathnode->partitioned_rels = partitioned_rels; pathnode->resultRelations = resultRelations; pathnode->subpaths = subpaths; pathnode->subroots = subroots; diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index dffc92762b..1db784784f 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -1509,6 +1509,13 @@ AcquireExecutorLocks(List *stmt_list, bool acquire) continue; /* + * We lock the partitioned tables in the respective control nodes + * such as Append, ModifyTable, etc. + */ + if (rte->relkind == RELKIND_PARTITIONED_TABLE) + continue; + + /* * Acquire the appropriate type of lock on each relation OID. Note * that we don't actually try to open the rel, and hence will not * fail if it's been dropped entirely --- we'll just transiently diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index b880dc16cf..8e8b8e5314 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -202,6 +202,8 @@ typedef struct ModifyTable CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *resultRelations; /* integer list of RT indexes */ int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ @@ -227,6 +229,8 @@ typedef struct ModifyTable typedef struct Append { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *appendplans; } Append; @@ -238,6 +242,8 @@ typedef struct Append typedef struct MergeAppend { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *mergeplans; /* remaining fields are just like the sort-key info in struct Sort */ int numCols; /* number of sort-key columns */ diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 05d6f07aea..4e9caf7e77 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -1116,6 +1116,7 @@ typedef struct CustomPath typedef struct AppendPath { Path path; + List *partitioned_rels; List *subpaths; /* list of component Paths */ } AppendPath; @@ -1134,6 +1135,7 @@ typedef struct AppendPath typedef struct MergeAppendPath { Path path; + List *partitioned_rels; List *subpaths; /* list of component Paths */ double limit_tuples; /* hard limit on output tuples, or -1 */ } MergeAppendPath; @@ -1482,6 +1484,7 @@ typedef struct ModifyTablePath CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + List *partitioned_rels; /* integer list of RT indexes */ List *resultRelations; /* integer list of RT indexes */ List *subpaths; /* Path(s) producing source data */ List *subroots; /* per-target-table PlannerInfos */ @@ -1886,6 +1889,7 @@ typedef struct AppendRelInfo */ Oid parent_reltype; /* OID of parent's composite type */ Oid child_reltype; /* OID of child's composite type */ + char child_relkind; /* relkind of append child rel */ /* * The N'th element of this list is a Var or expression representing the diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 373c7221a8..81640de7ab 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -64,12 +64,14 @@ extern BitmapOrPath *create_bitmap_or_path(PlannerInfo *root, extern TidPath *create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, Relids required_outer); extern AppendPath *create_append_path(RelOptInfo *rel, List *subpaths, - Relids required_outer, int parallel_workers); + Relids required_outer, int parallel_workers, + List *partitioned_rels); extern MergeAppendPath *create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer); + Relids required_outer, + List *partitioned_rels); extern ResultPath *create_result_path(PlannerInfo *root, RelOptInfo *rel, PathTarget *target, List *resconstantqual); extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath); @@ -232,7 +234,7 @@ extern LockRowsPath *create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6494b205c4..6737f925c3 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -588,6 +588,32 @@ select tableoid::regclass::text as relname, bar.* from bar order by 1,2; bar2 | 4 | 104 (8 rows) +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* +from parted_tab order by 1,2; + relname | a | b +------------------+---+--- + parted_tab_part1 | 1 | b + parted_tab_part2 | 2 | b + parted_tab_part3 | 3 | a +(3 rows) + +drop table parted_tab; +drop table some_tab cascade; +NOTICE: drop cascades to table some_tab_child /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); CREATE TABLE secondparent (tomorrow date default now() :: date + 1); @@ -1611,71 +1637,60 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, @@ -1695,14 +1710,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1712,36 +1722,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1750,27 +1746,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1782,37 +1770,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1831,23 +1799,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted; drop table range_list_parted; diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index b18e420e9b..d3794140fb 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -322,12 +322,10 @@ explain (costs off) QUERY PLAN ------------------------------------------- Append - -> Sample Scan on parted_sample - Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_1 Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_2 Sampling: bernoulli ('100'::real) -(7 rows) +(5 rows) drop table parted_sample, parted_sample_1, parted_sample_2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index e3e9e34895..ff94c4d4c3 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -128,6 +128,28 @@ where bar.f1 = ss.f1; select tableoid::regclass::text as relname, bar.* from bar order by 1,2; +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); + +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss +where parted_tab.a = ss.a; + +select tableoid::regclass::text as relname, parted_tab.* +from parted_tab order by 1,2; + +drop table parted_tab; +drop table some_tab cascade; + /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); -- 2.11.0 --------------BB5119F57CB7039CE032CF22 Content-Type: text/x-diff; name="0002-Do-not-allocate-storage-for-partitioned-tables.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0002-Do-not-allocate-storage-for-partitioned-tables.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH 1/2] Avoid creating scan nodes for partitioned tables @ 2017-03-10 04:48 amit <amitlangote09@gmail.com> 0 siblings, 0 replies; 8+ messages in thread From: amit @ 2017-03-10 04:48 UTC (permalink / raw) * Currently, we create scan nodes for inheritance parents in their role as an inheritance set member. Partitioned tables do not contain any data, so it's useless to create scan nodes for them. So we generate only a minimal AppendRelInfo for partitioned child tables. No need to generate translated_vars, for example. We need to hold on to the child_relid's, because we want to save it into the Append/ MergeAppend/ModifyTable node we will generate for the inheritance tree. Also mark such appinfos as with child_is_partitioned = true so that later steps know to process them appropriately. * The planner prep phase turns off inheritance on the parent RTE if there isn't at least one child member other than the parent itself which is also a child member. That means set_rel_size/pathlist will not create an Append node but a scan node for the parent RTE. We want to avoid that if the parent is a partitioned table, by noticing that in set_rel_size(). Per suggestion from Ashutosh Bapat. * Add a new partitioned_rels field to ModifyTable, Append, and MergeAppend nodes, which holds the RT indexes of all the non-leaf tables in a partition tree. Use the same to lock those tables during execution. The usual method employed by InitPlan to lock the tables does not work for partitioned tables, because they do not appear in the subplans list of either of those nodes. * In inheritance_planner, set ModifyTable.nominalRelation to parent RT index in the partitioned inheritance parent case, so that EXPLAIN uses the root tables's original RT index to label a given ModifyTable node. Regression test outputs are adjusted to match the new plan shape. --- src/backend/executor/nodeAppend.c | 39 ++++++++ src/backend/executor/nodeMergeAppend.c | 39 ++++++++ src/backend/executor/nodeModifyTable.c | 36 +++++++- src/backend/nodes/copyfuncs.c | 4 + src/backend/nodes/equalfuncs.c | 1 + src/backend/nodes/outfuncs.c | 7 ++ src/backend/nodes/readfuncs.c | 3 + src/backend/optimizer/path/allpaths.c | 49 ++++++++-- src/backend/optimizer/path/joinrels.c | 2 +- src/backend/optimizer/plan/createplan.c | 14 ++- src/backend/optimizer/plan/initsplan.c | 5 + src/backend/optimizer/plan/planner.c | 63 ++++++++++--- src/backend/optimizer/plan/setrefs.c | 12 +++ src/backend/optimizer/prep/prepjointree.c | 13 ++- src/backend/optimizer/prep/prepunion.c | 54 +++++++---- src/backend/optimizer/util/pathnode.c | 10 +- src/backend/optimizer/util/relnode.c | 7 ++ src/backend/utils/cache/plancache.c | 7 ++ src/include/nodes/plannodes.h | 6 ++ src/include/nodes/relation.h | 4 + src/include/optimizer/pathnode.h | 8 +- src/test/regress/expected/inherit.out | 147 +++++++++++++----------------- src/test/regress/expected/tablesample.out | 4 +- src/test/regress/sql/inherit.sql | 28 ++++++ 24 files changed, 420 insertions(+), 142 deletions(-) diff --git a/src/backend/executor/nodeAppend.c b/src/backend/executor/nodeAppend.c index 6986caee6b..26be346199 100644 --- a/src/backend/executor/nodeAppend.c +++ b/src/backend/executor/nodeAppend.c @@ -59,6 +59,8 @@ #include "executor/execdebug.h" #include "executor/nodeAppend.h" +#include "parser/parsetree.h" +#include "storage/lmgr.h" static bool exec_append_initialize_next(AppendState *appendstate); @@ -129,6 +131,43 @@ ExecInitAppend(Append *node, EState *estate, int eflags) Assert(!(eflags & EXEC_FLAG_MARK)); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node; leaf tables will be locked below when initializing the node's + * subplans. Note that this occurs only if the root inheritance parent + * is a partitioned table. + */ + if (node->partitioned_rels) + { + foreach(lc, node->partitioned_rels) + { + Index rti = lfirst_int(lc); + PlanRowMark *rc = NULL; + ListCell *l; + Oid relid; + LOCKMODE lockmode; + + relid = getrelid(rti, estate->es_range_table); + + /* If there is a RowMark, we better adjust the lockmode */ + foreach(l, estate->es_plannedstmt->rowMarks) + { + rc = (PlanRowMark *) lfirst(l); + + if (rc->rti == rti) + break; + } + + Assert(rc == NULL || rc->isParent); + if (rc != NULL && RowMarkRequiresRowShareLock(rc->markType)) + lockmode = RowShareLock; + else + lockmode = AccessShareLock; + + LockRelationOid(relid, lockmode); + } + } + + /* * Set up empty vector of subplan states */ nplans = list_length(node->appendplans); diff --git a/src/backend/executor/nodeMergeAppend.c b/src/backend/executor/nodeMergeAppend.c index 7a20bf07a4..0b5f82cf20 100644 --- a/src/backend/executor/nodeMergeAppend.c +++ b/src/backend/executor/nodeMergeAppend.c @@ -42,6 +42,8 @@ #include "executor/nodeMergeAppend.h" #include "lib/binaryheap.h" +#include "parser/parsetree.h" +#include "storage/lmgr.h" /* * We have one slot for each item in the heap array. We use SlotNumber @@ -72,6 +74,43 @@ ExecInitMergeAppend(MergeAppend *node, EState *estate, int eflags) Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK))); /* + * Lock the non-leaf tables in the partition tree controlled by this + * node; leaf tables will be locked below when initializing the node's + * subplans. Note that this occurs only if the root inheritance parent + * is a partitioned table. + */ + if (node->partitioned_rels) + { + foreach(lc, node->partitioned_rels) + { + Index rti = lfirst_int(lc); + PlanRowMark *rc = NULL; + ListCell *l; + Oid relid; + LOCKMODE lockmode; + + relid = getrelid(rti, estate->es_range_table); + + /* If there is a RowMark, we better adjust the lockmode */ + foreach(l, estate->es_plannedstmt->rowMarks) + { + rc = (PlanRowMark *) lfirst(l); + + if (rc->rti == rti) + break; + } + + Assert(rc == NULL || rc->isParent); + if (rc != NULL && RowMarkRequiresRowShareLock(rc->markType)) + lockmode = RowShareLock; + else + lockmode = AccessShareLock; + + LockRelationOid(relid, lockmode); + } + } + + /* * Set up empty vector of subplan states */ nplans = list_length(node->mergeplans); diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c index 95e158970c..9f548bdfac 100644 --- a/src/backend/executor/nodeModifyTable.c +++ b/src/backend/executor/nodeModifyTable.c @@ -45,6 +45,7 @@ #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/parsetree.h" #include "storage/bufmgr.h" #include "storage/lmgr.h" #include "utils/builtins.h" @@ -1725,8 +1726,34 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) estate->es_result_relation_info = saved_resultRelInfo; + /* + * During UPDATE/DELETE of a partitioned table, node->partitioned_rels + * contains the RT indexes of non-leaf tables of the partition tree. + * Leaf tables have already been locked by InitPlan when setting up the + * ResultRelInfo's. + */ + if (node->partitioned_rels) + { + Index root_table_rti; + Oid root_table_oid; + + foreach(l, node->partitioned_rels) + { + Oid relid; + + relid = getrelid(lfirst_int(l), estate->es_range_table); + LockRelationOid(relid, RowExclusiveLock); + } + + /* We have the root table RT index at the head of the list */ + root_table_rti = linitial_int(node->partitioned_rels); + root_table_oid = getrelid(root_table_rti, estate->es_range_table); + rel = heap_open(root_table_oid, NoLock); /* locked just above */ + } + else + rel = mtstate->resultRelInfo->ri_RelationDesc; + /* Build state for INSERT tuple routing */ - rel = mtstate->resultRelInfo->ri_RelationDesc; if (operation == CMD_INSERT && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) { @@ -1898,6 +1925,13 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags) } /* + * Close the root partitioned rel if we opened it above, but keep the + * lock. + */ + if (rel != mtstate->resultRelInfo->ri_RelationDesc) + heap_close(rel, NoLock); + + /* * If needed, Initialize target list, projection and qual for ON CONFLICT * DO UPDATE. */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index bfc2ac1716..59ced968b0 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -200,6 +200,7 @@ _copyModifyTable(const ModifyTable *from) COPY_SCALAR_FIELD(operation); COPY_SCALAR_FIELD(canSetTag); COPY_SCALAR_FIELD(nominalRelation); + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(resultRelations); COPY_SCALAR_FIELD(resultRelIndex); COPY_NODE_FIELD(plans); @@ -235,6 +236,7 @@ _copyAppend(const Append *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(appendplans); return newnode; @@ -256,6 +258,7 @@ _copyMergeAppend(const MergeAppend *from) /* * copy remainder of node */ + COPY_NODE_FIELD(partitioned_rels); COPY_NODE_FIELD(mergeplans); COPY_SCALAR_FIELD(numCols); COPY_POINTER_FIELD(sortColIdx, from->numCols * sizeof(AttrNumber)); @@ -2198,6 +2201,7 @@ _copyAppendRelInfo(const AppendRelInfo *from) COPY_SCALAR_FIELD(child_relid); COPY_SCALAR_FIELD(parent_reltype); COPY_SCALAR_FIELD(child_reltype); + COPY_SCALAR_FIELD(child_is_partitioned); COPY_NODE_FIELD(translated_vars); COPY_SCALAR_FIELD(parent_reloid); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 54e9c983a0..c2c3644d22 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -889,6 +889,7 @@ _equalAppendRelInfo(const AppendRelInfo *a, const AppendRelInfo *b) COMPARE_SCALAR_FIELD(child_relid); COMPARE_SCALAR_FIELD(parent_reltype); COMPARE_SCALAR_FIELD(child_reltype); + COMPARE_SCALAR_FIELD(child_is_partitioned); COMPARE_NODE_FIELD(translated_vars); COMPARE_SCALAR_FIELD(parent_reloid); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 7418fbeded..3b4ae0e2df 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -344,6 +344,7 @@ _outModifyTable(StringInfo str, const ModifyTable *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_INT_FIELD(resultRelIndex); WRITE_NODE_FIELD(plans); @@ -368,6 +369,7 @@ _outAppend(StringInfo str, const Append *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(appendplans); } @@ -380,6 +382,7 @@ _outMergeAppend(StringInfo str, const MergeAppend *node) _outPlanInfo(str, (const Plan *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(mergeplans); WRITE_INT_FIELD(numCols); @@ -1808,6 +1811,7 @@ _outAppendPath(StringInfo str, const AppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); } @@ -1818,6 +1822,7 @@ _outMergeAppendPath(StringInfo str, const MergeAppendPath *node) _outPathInfo(str, (const Path *) node); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(subpaths); WRITE_FLOAT_FIELD(limit_tuples, "%.0f"); } @@ -2023,6 +2028,7 @@ _outModifyTablePath(StringInfo str, const ModifyTablePath *node) WRITE_ENUM_FIELD(operation, CmdType); WRITE_BOOL_FIELD(canSetTag); WRITE_UINT_FIELD(nominalRelation); + WRITE_NODE_FIELD(partitioned_rels); WRITE_NODE_FIELD(resultRelations); WRITE_NODE_FIELD(subpaths); WRITE_NODE_FIELD(subroots); @@ -2415,6 +2421,7 @@ _outAppendRelInfo(StringInfo str, const AppendRelInfo *node) WRITE_UINT_FIELD(child_relid); WRITE_OID_FIELD(parent_reltype); WRITE_OID_FIELD(child_reltype); + WRITE_BOOL_FIELD(child_is_partitioned); WRITE_NODE_FIELD(translated_vars); WRITE_OID_FIELD(parent_reloid); } diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index d3bbc02f24..f2ec174216 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1535,6 +1535,7 @@ _readModifyTable(void) READ_ENUM_FIELD(operation, CmdType); READ_BOOL_FIELD(canSetTag); READ_UINT_FIELD(nominalRelation); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(resultRelations); READ_INT_FIELD(resultRelIndex); READ_NODE_FIELD(plans); @@ -1564,6 +1565,7 @@ _readAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(appendplans); READ_DONE(); @@ -1579,6 +1581,7 @@ _readMergeAppend(void) ReadCommonPlan(&local_node->plan); + READ_NODE_FIELD(partitioned_rels); READ_NODE_FIELD(mergeplans); READ_INT_FIELD(numCols); READ_ATTRNUMBER_ARRAY(sortColIdx, local_node->numCols); diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index b263359fde..dcae3c8531 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -95,7 +95,8 @@ static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, Index rti, RangeTblEntry *rte); static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys); + List *all_child_pathkeys, + List *partitioned_rels); static Path *get_cheapest_parameterized_child_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer); @@ -344,6 +345,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel, /* Foreign table */ set_foreign_size(root, rel, rte); } + else if (rte->relkind == RELKIND_PARTITIONED_TABLE) + { + /* + * A partitioned table without leaf partitions is marked + * as a dummy rel. + */ + set_dummy_rel_pathlist(rel); + } else if (rte->tablesample != NULL) { /* Sampled relation */ @@ -878,6 +887,10 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel, if (appinfo->parent_relid != parentRTindex) continue; + /* Nothing to see here. */ + if (appinfo->child_is_partitioned) + continue; + childRTindex = appinfo->child_relid; childRTE = root->simple_rte_array[childRTindex]; @@ -1189,6 +1202,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, List *all_child_pathkeys = NIL; List *all_child_outers = NIL; ListCell *l; + List *partitioned_rels = NIL; /* * Generate access paths for each member relation, and remember the @@ -1208,6 +1222,17 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, if (appinfo->parent_relid != parentRTindex) continue; + /* + * Nothing to do here for partitioned child tables; just remember + * the RT index to put into the AppendPath. + */ + if (appinfo->child_is_partitioned) + { + partitioned_rels = lappend_int(partitioned_rels, + appinfo->child_relid); + continue; + } + /* Re-locate the child RTE and RelOptInfo */ childRTindex = appinfo->child_relid; childRTE = root->simple_rte_array[childRTindex]; @@ -1327,7 +1352,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, * if we have zero or one live subpath due to constraint exclusion.) */ if (subpaths_valid) - add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, subpaths, NULL, 0, + partitioned_rels)); /* * Consider an append of partial unordered, unparameterized partial paths. @@ -1354,7 +1380,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, /* Generate a partial append path. */ appendpath = create_append_path(rel, partial_subpaths, NULL, - parallel_workers); + parallel_workers, partitioned_rels); add_partial_path(rel, (Path *) appendpath); } @@ -1364,7 +1390,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, */ if (subpaths_valid) generate_mergeappend_paths(root, rel, live_childrels, - all_child_pathkeys); + all_child_pathkeys, + partitioned_rels); /* * Build Append paths for each parameterization seen among the child rels. @@ -1406,7 +1433,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, if (subpaths_valid) add_path(rel, (Path *) - create_append_path(rel, subpaths, required_outer, 0)); + create_append_path(rel, subpaths, required_outer, 0, + partitioned_rels)); } } @@ -1436,7 +1464,8 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, List *live_childrels, - List *all_child_pathkeys) + List *all_child_pathkeys, + List *partitioned_rels) { ListCell *lcp; @@ -1500,13 +1529,15 @@ generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel, rel, startup_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); if (startup_neq_total) add_path(rel, (Path *) create_merge_append_path(root, rel, total_subpaths, pathkeys, - NULL)); + NULL, + partitioned_rels)); } } @@ -1639,7 +1670,7 @@ set_dummy_rel_pathlist(RelOptInfo *rel) rel->pathlist = NIL; rel->partial_pathlist = NIL; - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* * We set the cheapest path immediately, to ensure that IS_DUMMY_REL() diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.c index 0d0068360c..7dfbb7a970 100644 --- a/src/backend/optimizer/path/joinrels.c +++ b/src/backend/optimizer/path/joinrels.c @@ -1197,7 +1197,7 @@ mark_dummy_rel(RelOptInfo *rel) rel->partial_pathlist = NIL; /* Set up the dummy path */ - add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0)); + add_path(rel, (Path *) create_append_path(rel, NIL, NULL, 0, NIL)); /* Set or update cheapest_total_path and related fields */ set_cheapest(rel); diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index d002e6d567..6b29ccc0c4 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -199,7 +199,7 @@ static CteScan *make_ctescan(List *qptlist, List *qpqual, Index scanrelid, int ctePlanId, int cteParam); static WorkTableScan *make_worktablescan(List *qptlist, List *qpqual, Index scanrelid, int wtParam); -static Append *make_append(List *appendplans, List *tlist); +static Append *make_append(List *appendplans, List *tlist, List *partitioned_rels); static RecursiveUnion *make_recursive_union(List *tlist, Plan *lefttree, Plan *righttree, @@ -273,7 +273,7 @@ static Result *make_result(List *tlist, Node *resconstantqual, Plan *subplan); static ProjectSet *make_project_set(List *tlist, Plan *subplan); static ModifyTable *make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam); @@ -1026,7 +1026,7 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path) * parent-rel Vars it'll be asked to emit. */ - plan = make_append(subplans, tlist); + plan = make_append(subplans, tlist, best_path->partitioned_rels); copy_generic_path_info(&plan->plan, (Path *) best_path); @@ -1134,6 +1134,7 @@ create_merge_append_plan(PlannerInfo *root, MergeAppendPath *best_path) subplans = lappend(subplans, subplan); } + node->partitioned_rels = best_path->partitioned_rels; node->mergeplans = subplans; return (Plan *) node; @@ -2340,6 +2341,7 @@ create_modifytable_plan(PlannerInfo *root, ModifyTablePath *best_path) best_path->operation, best_path->canSetTag, best_path->nominalRelation, + best_path->partitioned_rels, best_path->resultRelations, subplans, best_path->withCheckOptionLists, @@ -5187,7 +5189,7 @@ make_foreignscan(List *qptlist, } static Append * -make_append(List *appendplans, List *tlist) +make_append(List *appendplans, List *tlist, List *partitioned_rels) { Append *node = makeNode(Append); Plan *plan = &node->plan; @@ -5196,6 +5198,7 @@ make_append(List *appendplans, List *tlist) plan->qual = NIL; plan->lefttree = NULL; plan->righttree = NULL; + node->partitioned_rels = partitioned_rels; node->appendplans = appendplans; return node; @@ -6308,7 +6311,7 @@ make_project_set(List *tlist, static ModifyTable * make_modifytable(PlannerInfo *root, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subplans, List *withCheckOptionLists, List *returningLists, List *rowMarks, OnConflictExpr *onconflict, int epqParam) @@ -6334,6 +6337,7 @@ make_modifytable(PlannerInfo *root, node->operation = operation; node->canSetTag = canSetTag; node->nominalRelation = nominalRelation; + node->partitioned_rels = partitioned_rels; node->resultRelations = resultRelations; node->resultRelIndex = -1; /* will be set correctly in setrefs.c */ node->plans = subplans; diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index b4ac224a7a..d55730fdce 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -642,6 +642,11 @@ create_lateral_join_info(PlannerInfo *root) if (appinfo->parent_relid != rti) continue; + + /* Nothing to do here for these. */ + if (appinfo->child_is_partitioned) + continue; + childrel = root->simple_rel_array[appinfo->child_relid]; Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL); Assert(childrel->direct_lateral_relids == NULL); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 02286d9c52..7a104fb78d 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1007,6 +1007,8 @@ inheritance_planner(PlannerInfo *root) RelOptInfo *final_rel; ListCell *lc; Index rti; + RangeTblEntry *parent_rte; + List *partitioned_rels = NIL; Assert(parse->commandType != CMD_INSERT); @@ -1055,6 +1057,13 @@ inheritance_planner(PlannerInfo *root) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); + /* + * Need not consider these, because we don't generate a plan + * for them below. + */ + if (appinfo->child_is_partitioned) + continue; + if (bms_is_member(appinfo->parent_relid, subqueryRTindexes) || bms_is_member(appinfo->child_relid, subqueryRTindexes) || bms_overlap(pull_varnos((Node *) appinfo->translated_vars), @@ -1065,13 +1074,21 @@ inheritance_planner(PlannerInfo *root) } /* + * If the parent RTE is a partitioned table, we should use that as the + * nominal relation, because we do not have the duplicate parent RTE, + * unlike in the case of a non-partitioned inheritance parent. + */ + parent_rte = rt_fetch(parentRTindex, root->parse->rtable); + if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE) + nominalRelation = parentRTindex; + + /* * And now we can get on with generating a plan for each child table. */ foreach(lc, root->append_rel_list) { AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc); PlannerInfo *subroot; - RangeTblEntry *parent_rte; RangeTblEntry *child_rte; RelOptInfo *sub_final_rel; Path *subpath; @@ -1081,6 +1098,17 @@ inheritance_planner(PlannerInfo *root) continue; /* + * Nothing to do here for partitioned child tables; just remember + * the RT index to put into the ModifyTablePath. + */ + if (appinfo->child_is_partitioned) + { + partitioned_rels = lappend_int(partitioned_rels, + appinfo->child_relid); + continue; + } + + /* * We need a working copy of the PlannerInfo so that we can control * propagation of information back to the main copy. */ @@ -1216,15 +1244,25 @@ inheritance_planner(PlannerInfo *root) grouping_planner(subroot, true, 0.0 /* retrieve all tuples */ ); /* - * We'll use the first child relation (even if it's excluded) as the - * nominal target relation of the ModifyTable node. Because of the - * way expand_inherited_rtentry works, this should always be the RTE - * representing the parent table in its role as a simple member of the - * inheritance set. (It would be logically cleaner to use the - * inheritance parent RTE as the nominal target; but since that RTE - * will not be otherwise referenced in the plan, doing so would give - * rise to confusing use of multiple aliases in EXPLAIN output for - * what the user will think is the "same" table.) + * Set the nomimal target relation of the ModifyTable node if not + * already done. We use the inheritance parent RTE as the nominal + * target relation if it's a partitioned table (see just above this + * loop). In the non-partitioned parent case, we'll use the first + * child relation (even if it's excluded) as the nominal target + * relation. Because of the way expand_inherited_rtentry works, the + * latter should be the RTE representing the parent table in its role + * as a simple member of the inheritance set. + * + * It would be logically cleaner to *always* use the inheritance + * parent RTE as the nominal relation; but that RTE is not otherwise + * referenced in the plan in the non-partitioned inheritance case. + * Instead the duplicate child RTE created by expand_inherited_rtentry + * is used elsewhere in the plan, so using the original parent RTE + * would give rise to confusing use of multiple aliases in EXPLAIN + * output for what the user will think is the "same" table. OTOH, + * it's not a problem in the partitioned inheritance case, because + * the duplicate child RTE added for the parent does not appear + * anywhere else in the plan tree. */ if (nominalRelation < 0) nominalRelation = appinfo->child_relid; @@ -1351,6 +1389,7 @@ inheritance_planner(PlannerInfo *root) parse->commandType, parse->canSetTag, nominalRelation, + partitioned_rels, resultRelations, subpaths, subroots, @@ -2046,6 +2085,7 @@ grouping_planner(PlannerInfo *root, bool inheritance_update, parse->commandType, parse->canSetTag, parse->resultRelation, + NIL, list_make1_int(parse->resultRelation), list_make1(path), list_make1(root), @@ -3348,7 +3388,8 @@ create_grouping_paths(PlannerInfo *root, create_append_path(grouped_rel, paths, NULL, - 0); + 0, + NIL); path->pathtarget = target; } else diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 5f3027e96f..d148c2d37d 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -835,6 +835,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) splan->nominalRelation += rtoffset; splan->exclRelRTI += rtoffset; + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->resultRelations) { lfirst_int(l) += rtoffset; @@ -875,6 +879,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->appendplans) { lfirst(l) = set_plan_refs(root, @@ -893,6 +901,10 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) */ set_dummy_tlist_references(plan, rtoffset); Assert(splan->plan.qual == NIL); + foreach(l, splan->partitioned_rels) + { + lfirst_int(l) += rtoffset; + } foreach(l, splan->mergeplans) { lfirst(l) = set_plan_refs(root, diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 048815d7b0..b89e340ef2 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1072,8 +1072,11 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, if (appinfo == containing_appendrel) rvcontext.need_phvs = false; - appinfo->translated_vars = (List *) - pullup_replace_vars((Node *) appinfo->translated_vars, &rvcontext); + + if (!appinfo->child_is_partitioned) + appinfo->translated_vars = (List *) + pullup_replace_vars((Node *) appinfo->translated_vars, + &rvcontext); rvcontext.need_phvs = save_need_phvs; } @@ -1327,6 +1330,7 @@ pull_up_union_leaf_queries(Node *setOp, PlannerInfo *root, int parentRTindex, appinfo->child_relid = childRTindex; appinfo->parent_reltype = InvalidOid; appinfo->child_reltype = InvalidOid; + appinfo->child_is_partitioned = false; make_setop_translation_list(setOpQuery, childRTindex, &appinfo->translated_vars); appinfo->parent_reloid = InvalidOid; @@ -2926,8 +2930,9 @@ fix_append_rel_relids(List *append_rel_list, int varno, Relids subrelids) } /* Also finish fixups for its translated vars */ - substitute_multiple_relids((Node *) appinfo->translated_vars, - varno, subrelids); + if (!appinfo->child_is_partitioned) + substitute_multiple_relids((Node *) appinfo->translated_vars, + varno, subrelids); } } diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 1389db18ba..8a4089bf97 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -566,7 +566,7 @@ generate_union_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -678,7 +678,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root, /* * Append the child results together. */ - path = (Path *) create_append_path(result_rel, pathlist, NULL, 0); + path = (Path *) create_append_path(result_rel, pathlist, NULL, 0, NIL); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); @@ -1352,6 +1352,12 @@ expand_inherited_tables(PlannerInfo *root) * * A childless table is never considered to be an inheritance set; therefore * a parent RTE must always have at least two associated AppendRelInfos. + * + * AppendRelInfo's created for each child table normally contains information + * necessary to create a scan plan for the table. However, for child tables + * that are partitioned (which contains no data), we need not create scan + * plans; so create a minimal AppendRelInfo in that case containing only the + * parent-child RT index pair. */ static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) @@ -1490,30 +1496,41 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) appinfo->child_relid = childRTindex; appinfo->parent_reltype = oldrelation->rd_rel->reltype; appinfo->child_reltype = newrelation->rd_rel->reltype; - make_inh_translation_list(oldrelation, newrelation, childRTindex, - &appinfo->translated_vars); + appinfo->child_is_partitioned = + (childrte->relkind == RELKIND_PARTITIONED_TABLE); appinfo->parent_reloid = parentOID; - appinfos = lappend(appinfos, appinfo); /* - * Translate the column permissions bitmaps to the child's attnums (we - * have to build the translated_vars list before we can do this). But - * if this is the parent table, leave copyObject's result alone. - * - * Note: we need to do this even though the executor won't run any - * permissions checks on the child RTE. The insertedCols/updatedCols - * bitmaps may be examined for trigger-firing purposes. + * No need to fill the following information for partitioned table + * appinfos, because we won't be needing it later. */ - if (childOID != parentOID) + if (!appinfo->child_is_partitioned) { - childrte->selectedCols = translate_col_privs(rte->selectedCols, + make_inh_translation_list(oldrelation, newrelation, childRTindex, + &appinfo->translated_vars); + + /* + * Translate the column permissions bitmaps to the child's attnums + * (we have to build the translated_vars list before we can do + * this). But if this is the parent table, leave copyObject's + * result alone. Note: we need to do this even though the executor + * won't run any permissions checks on the child RTE. The + * insertedCols/updatedCols bitmaps may be examined for + * trigger-firing purposes. + */ + if (childOID != parentOID) + { + childrte->selectedCols = translate_col_privs(rte->selectedCols, appinfo->translated_vars); - childrte->insertedCols = translate_col_privs(rte->insertedCols, + childrte->insertedCols = translate_col_privs(rte->insertedCols, appinfo->translated_vars); - childrte->updatedCols = translate_col_privs(rte->updatedCols, + childrte->updatedCols = translate_col_privs(rte->updatedCols, appinfo->translated_vars); + } } + appinfos = lappend(appinfos, appinfo); + /* * Build a PlanRowMark if parent is marked FOR UPDATE/SHARE. */ @@ -1529,7 +1546,10 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti) newrc->allMarkTypes = (1 << newrc->markType); newrc->strength = oldrc->strength; newrc->waitPolicy = oldrc->waitPolicy; - newrc->isParent = false; + /* + * If child is a partitioned table, this is a "dummy" parent entry + */ + newrc->isParent = (rte->relkind == RELKIND_PARTITIONED_TABLE); /* Include child's rowmark type in parent's allMarkTypes */ oldrc->allMarkTypes |= newrc->allMarkTypes; diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index 8ce772d274..fca96eb001 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1201,7 +1201,7 @@ create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, */ AppendPath * create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, - int parallel_workers) + int parallel_workers, List *partitioned_rels) { AppendPath *pathnode = makeNode(AppendPath); ListCell *l; @@ -1216,6 +1216,7 @@ create_append_path(RelOptInfo *rel, List *subpaths, Relids required_outer, pathnode->path.parallel_workers = parallel_workers; pathnode->path.pathkeys = NIL; /* result is always considered * unsorted */ + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -1258,7 +1259,8 @@ create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer) + Relids required_outer, + List *partitioned_rels) { MergeAppendPath *pathnode = makeNode(MergeAppendPath); Cost input_startup_cost; @@ -1274,6 +1276,7 @@ create_merge_append_path(PlannerInfo *root, pathnode->path.parallel_safe = rel->consider_parallel; pathnode->path.parallel_workers = 0; pathnode->path.pathkeys = pathkeys; + pathnode->partitioned_rels = partitioned_rels; pathnode->subpaths = subpaths; /* @@ -3105,7 +3108,7 @@ create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, ModifyTablePath * create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, @@ -3172,6 +3175,7 @@ create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, pathnode->operation = operation; pathnode->canSetTag = canSetTag; pathnode->nominalRelation = nominalRelation; + pathnode->partitioned_rels = partitioned_rels; pathnode->resultRelations = resultRelations; pathnode->subpaths = subpaths; pathnode->subroots = subroots; diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index caf8291e10..28b71b2c5d 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -204,6 +204,13 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind) if (appinfo->parent_relid != relid) continue; + /* + * Since we won't be creating any paths for these, don't need a + * RelOptInfo. + */ + if (appinfo->child_is_partitioned) + continue; + (void) build_simple_rel(root, appinfo->child_relid, RELOPT_OTHER_MEMBER_REL); } diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index dffc92762b..1db784784f 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -1509,6 +1509,13 @@ AcquireExecutorLocks(List *stmt_list, bool acquire) continue; /* + * We lock the partitioned tables in the respective control nodes + * such as Append, ModifyTable, etc. + */ + if (rte->relkind == RELKIND_PARTITIONED_TABLE) + continue; + + /* * Acquire the appropriate type of lock on each relation OID. Note * that we don't actually try to open the rel, and hence will not * fail if it's been dropped entirely --- we'll just transiently diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index b880dc16cf..8e8b8e5314 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -202,6 +202,8 @@ typedef struct ModifyTable CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *resultRelations; /* integer list of RT indexes */ int resultRelIndex; /* index of first resultRel in plan's list */ List *plans; /* plan(s) producing source data */ @@ -227,6 +229,8 @@ typedef struct ModifyTable typedef struct Append { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *appendplans; } Append; @@ -238,6 +242,8 @@ typedef struct Append typedef struct MergeAppend { Plan plan; + /* RT indexes of non-leaf tables in a partition tree */ + List *partitioned_rels; List *mergeplans; /* remaining fields are just like the sort-key info in struct Sort */ int numCols; /* number of sort-key columns */ diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h index 05d6f07aea..9ebdfb1d1f 100644 --- a/src/include/nodes/relation.h +++ b/src/include/nodes/relation.h @@ -1116,6 +1116,7 @@ typedef struct CustomPath typedef struct AppendPath { Path path; + List *partitioned_rels; List *subpaths; /* list of component Paths */ } AppendPath; @@ -1134,6 +1135,7 @@ typedef struct AppendPath typedef struct MergeAppendPath { Path path; + List *partitioned_rels; List *subpaths; /* list of component Paths */ double limit_tuples; /* hard limit on output tuples, or -1 */ } MergeAppendPath; @@ -1482,6 +1484,7 @@ typedef struct ModifyTablePath CmdType operation; /* INSERT, UPDATE, or DELETE */ bool canSetTag; /* do we set the command tag/es_processed? */ Index nominalRelation; /* Parent RT index for use of EXPLAIN */ + List *partitioned_rels; /* integer list of RT indexes */ List *resultRelations; /* integer list of RT indexes */ List *subpaths; /* Path(s) producing source data */ List *subroots; /* per-target-table PlannerInfos */ @@ -1886,6 +1889,7 @@ typedef struct AppendRelInfo */ Oid parent_reltype; /* OID of parent's composite type */ Oid child_reltype; /* OID of child's composite type */ + bool child_is_partitioned; /* is child a partitioned table? */ /* * The N'th element of this list is a Var or expression representing the diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h index 373c7221a8..81640de7ab 100644 --- a/src/include/optimizer/pathnode.h +++ b/src/include/optimizer/pathnode.h @@ -64,12 +64,14 @@ extern BitmapOrPath *create_bitmap_or_path(PlannerInfo *root, extern TidPath *create_tidscan_path(PlannerInfo *root, RelOptInfo *rel, List *tidquals, Relids required_outer); extern AppendPath *create_append_path(RelOptInfo *rel, List *subpaths, - Relids required_outer, int parallel_workers); + Relids required_outer, int parallel_workers, + List *partitioned_rels); extern MergeAppendPath *create_merge_append_path(PlannerInfo *root, RelOptInfo *rel, List *subpaths, List *pathkeys, - Relids required_outer); + Relids required_outer, + List *partitioned_rels); extern ResultPath *create_result_path(PlannerInfo *root, RelOptInfo *rel, PathTarget *target, List *resconstantqual); extern MaterialPath *create_material_path(RelOptInfo *rel, Path *subpath); @@ -232,7 +234,7 @@ extern LockRowsPath *create_lockrows_path(PlannerInfo *root, RelOptInfo *rel, extern ModifyTablePath *create_modifytable_path(PlannerInfo *root, RelOptInfo *rel, CmdType operation, bool canSetTag, - Index nominalRelation, + Index nominalRelation, List *partitioned_rels, List *resultRelations, List *subpaths, List *subroots, List *withCheckOptionLists, List *returningLists, diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 6494b205c4..6163ed8117 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -588,6 +588,45 @@ select tableoid::regclass::text as relname, bar.* from bar order by 1,2; bar2 | 4 | 104 (8 rows) +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + relname | a | b +------------------+---+--- + parted_tab_part1 | 1 | b + parted_tab_part2 | 2 | b + parted_tab_part3 | 3 | a +(3 rows) + +truncate parted_tab; +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select 0 from parted_tab union all select 1 from parted_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + relname | a | b +------------------+---+--- + parted_tab_part1 | 1 | b + parted_tab_part2 | 2 | a + parted_tab_part3 | 3 | a +(3 rows) + +drop table parted_tab; +drop table some_tab cascade; +NOTICE: drop cascades to table some_tab_child /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); CREATE TABLE secondparent (tomorrow date default now() :: date + 1); @@ -1611,71 +1650,60 @@ explain (costs off) select * from list_parted; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted -> Seq Scan on part_ab_cd -> Seq Scan on part_ef_gh -> Seq Scan on part_null_xy -(5 rows) +(4 rows) explain (costs off) select * from list_parted where a is null; QUERY PLAN -------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NULL) -> Seq Scan on part_null_xy Filter: (a IS NULL) -(5 rows) +(3 rows) explain (costs off) select * from list_parted where a is not null; QUERY PLAN --------------------------------- Append - -> Seq Scan on list_parted - Filter: (a IS NOT NULL) -> Seq Scan on part_ab_cd Filter: (a IS NOT NULL) -> Seq Scan on part_ef_gh Filter: (a IS NOT NULL) -> Seq Scan on part_null_xy Filter: (a IS NOT NULL) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); QUERY PLAN ---------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ab_cd Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -> Seq Scan on part_ef_gh Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) -(7 rows) +(5 rows) explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); QUERY PLAN --------------------------------------------------------------------------------------- Append - -> Seq Scan on list_parted - Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ab_cd Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_ef_gh Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -> Seq Scan on part_null_xy Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) -(9 rows) +(7 rows) explain (costs off) select * from list_parted where a = 'ab'; QUERY PLAN ------------------------------------------ Append - -> Seq Scan on list_parted - Filter: ((a)::text = 'ab'::text) -> Seq Scan on part_ab_cd Filter: ((a)::text = 'ab'::text) -(5 rows) +(3 rows) create table range_list_parted ( a int, @@ -1695,14 +1723,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab'); create table part_40_inf_cd partition of part_40_inf for values in ('cd'); create table part_40_inf_null partition of part_40_inf for values in (null); explain (costs off) select * from range_list_parted; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - -> Seq Scan on part_1_10 - -> Seq Scan on part_10_20 - -> Seq Scan on part_21_30 - -> Seq Scan on part_40_inf -> Seq Scan on part_1_10_ab -> Seq Scan on part_1_10_cd -> Seq Scan on part_10_20_ab @@ -1712,36 +1735,22 @@ explain (costs off) select * from range_list_parted; -> Seq Scan on part_40_inf_ab -> Seq Scan on part_40_inf_cd -> Seq Scan on part_40_inf_null -(15 rows) +(10 rows) explain (costs off) select * from range_list_parted where a = 5; - QUERY PLAN -------------------------------------- + QUERY PLAN +-------------------------------- Append - -> Seq Scan on range_list_parted - Filter: (a = 5) - -> Seq Scan on part_1_10 - Filter: (a = 5) -> Seq Scan on part_1_10_ab Filter: (a = 5) -> Seq Scan on part_1_10_cd Filter: (a = 5) -(9 rows) +(5 rows) explain (costs off) select * from range_list_parted where b = 'ab'; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_1_10 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_10_20 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_21_30 - Filter: (b = 'ab'::bpchar) - -> Seq Scan on part_40_inf - Filter: (b = 'ab'::bpchar) -> Seq Scan on part_1_10_ab Filter: (b = 'ab'::bpchar) -> Seq Scan on part_10_20_ab @@ -1750,27 +1759,19 @@ explain (costs off) select * from range_list_parted where b = 'ab'; Filter: (b = 'ab'::bpchar) -> Seq Scan on part_40_inf_ab Filter: (b = 'ab'::bpchar) -(19 rows) +(9 rows) explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); QUERY PLAN ----------------------------------------------------------------- Append - -> Seq Scan on range_list_parted - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_1_10 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_10_20 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) - -> Seq Scan on part_21_30 - Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_1_10_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_10_20_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -> Seq Scan on part_21_30_ab Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) -(15 rows) +(7 rows) /* Should select no rows because range partition key cannot be null */ explain (costs off) select * from range_list_parted where a is null; @@ -1782,37 +1783,17 @@ explain (costs off) select * from range_list_parted where a is null; /* Should only select rows from the null-accepting partition */ explain (costs off) select * from range_list_parted where b is null; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (b IS NULL) - -> Seq Scan on part_1_10 - Filter: (b IS NULL) - -> Seq Scan on part_10_20 - Filter: (b IS NULL) - -> Seq Scan on part_21_30 - Filter: (b IS NULL) - -> Seq Scan on part_40_inf - Filter: (b IS NULL) -> Seq Scan on part_40_inf_null Filter: (b IS NULL) -(13 rows) +(3 rows) explain (costs off) select * from range_list_parted where a is not null and a < 67; QUERY PLAN ------------------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_1_10 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_10_20 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_21_30 - Filter: ((a IS NOT NULL) AND (a < 67)) - -> Seq Scan on part_40_inf - Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_ab Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_1_10_cd @@ -1831,23 +1812,19 @@ explain (costs off) select * from range_list_parted where a is not null and a < Filter: ((a IS NOT NULL) AND (a < 67)) -> Seq Scan on part_40_inf_null Filter: ((a IS NOT NULL) AND (a < 67)) -(29 rows) +(19 rows) explain (costs off) select * from range_list_parted where a >= 30; - QUERY PLAN -------------------------------------- + QUERY PLAN +------------------------------------ Append - -> Seq Scan on range_list_parted - Filter: (a >= 30) - -> Seq Scan on part_40_inf - Filter: (a >= 30) -> Seq Scan on part_40_inf_ab Filter: (a >= 30) -> Seq Scan on part_40_inf_cd Filter: (a >= 30) -> Seq Scan on part_40_inf_null Filter: (a >= 30) -(11 rows) +(7 rows) drop table list_parted; drop table range_list_parted; diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out index b18e420e9b..d3794140fb 100644 --- a/src/test/regress/expected/tablesample.out +++ b/src/test/regress/expected/tablesample.out @@ -322,12 +322,10 @@ explain (costs off) QUERY PLAN ------------------------------------------- Append - -> Sample Scan on parted_sample - Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_1 Sampling: bernoulli ('100'::real) -> Sample Scan on parted_sample_2 Sampling: bernoulli ('100'::real) -(7 rows) +(5 rows) drop table parted_sample, parted_sample_1, parted_sample_2; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index e3e9e34895..d43b75c4a7 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -128,6 +128,34 @@ where bar.f1 = ss.f1; select tableoid::regclass::text as relname, bar.* from bar order by 1,2; +-- Check UPDATE with *partitioned* inherited target and an appendrel subquery +create table some_tab (a int); +insert into some_tab values (0); +create table some_tab_child () inherits (some_tab); +insert into some_tab_child values (1); +create table parted_tab (a int, b char) partition by list (a); +create table parted_tab_part1 partition of parted_tab for values in (1); +create table parted_tab_part2 partition of parted_tab for values in (2); +create table parted_tab_part3 partition of parted_tab for values in (3); +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); + +update parted_tab set b = 'b' +from + (select a from some_tab union all select a+1 from some_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + +truncate parted_tab; +insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); +update parted_tab set b = 'b' +from + (select 0 from parted_tab union all select 1 from parted_tab) ss (a) +where parted_tab.a = ss.a; +select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; + +drop table parted_tab; +drop table some_tab cascade; + /* Test multiple inheritance of column defaults */ CREATE TABLE firstparent (tomorrow date default now()::date + 1); -- 2.11.0 --------------620AFCADB28A0933C2CE0230 Content-Type: text/x-diff; name="0002-Do-not-allocate-storage-for-partitioned-tables.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename*0="0002-Do-not-allocate-storage-for-partitioned-tables.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v50 1/8] Make index_concurrently_create_copy more general @ 2026-03-24 18:02 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 8+ messages in thread From: Álvaro Herrera @ 2026-03-24 18:02 UTC (permalink / raw) Add a 'boolean concurrent' option, and make it work for both cases. Also rename it to index_create_copy. This allows it to be reused for other purposes -- specifically, for REPACK CONCURRENTLY. With the CONCURRENTLY option, REPACK cannot simply swap the heap file and rebuild its indexes. Instead, it needs to build a separate set of indexes (including system catalog entries) *before* the actual swap, to reduce the time AccessExclusiveLock needs to be held for. This approach is different from what CREATE INDEX CONCURRENTLY does. Per a suggestion from Mihail Nikalayeu. Author: Antonin Houska <ah@cybertec.at> Discussion: https://postgr.es/m/41104.1754922120@localhost --- src/backend/catalog/index.c | 41 ++++++++++++++++++++++---------- src/backend/commands/indexcmds.c | 9 +++---- src/include/catalog/index.h | 7 +++--- 3 files changed, 36 insertions(+), 21 deletions(-) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 1ccfa687f05..e418d67e8e4 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1289,17 +1289,17 @@ index_create(Relation heapRelation, } /* - * index_concurrently_create_copy + * index_create_copy * - * Create concurrently an index based on the definition of the one provided by - * caller. The index is inserted into catalogs and needs to be built later - * on. This is called during concurrent reindex processing. + * Create an index based on the definition of the one provided by caller. The + * index is inserted into catalogs. If 'concurrently' is TRUE, it needs to be + * built later on; otherwise it's built immediately. * * "tablespaceOid" is the tablespace to use for this index. */ Oid -index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, - Oid tablespaceOid, const char *newName) +index_create_copy(Relation heapRelation, bool concurrently, + Oid oldIndexId, Oid tablespaceOid, const char *newName) { Relation indexRelation; IndexInfo *oldInfo, @@ -1318,6 +1318,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, List *indexColNames = NIL; List *indexExprs = NIL; List *indexPreds = NIL; + int flags = 0; indexRelation = index_open(oldIndexId, RowExclusiveLock); @@ -1328,7 +1329,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, * Concurrent build of an index with exclusion constraints is not * supported. */ - if (oldInfo->ii_ExclusionOps != NULL) + if (oldInfo->ii_ExclusionOps != NULL && concurrently) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("concurrent index creation for exclusion constraints is not supported"))); @@ -1384,9 +1385,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, } /* - * Build the index information for the new index. Note that rebuild of - * indexes with exclusion constraints is not supported, hence there is no - * need to fill all the ii_Exclusion* fields. + * Build the index information for the new index. */ newInfo = makeIndexInfo(oldInfo->ii_NumIndexAttrs, oldInfo->ii_NumIndexKeyAttrs, @@ -1395,11 +1394,24 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indexPreds, oldInfo->ii_Unique, oldInfo->ii_NullsNotDistinct, - false, /* not ready for inserts */ - true, + !concurrently, /* isready */ + concurrently, /* concurrent */ indexRelation->rd_indam->amsummarizing, oldInfo->ii_WithoutOverlaps); + /* fetch exclusion constraint info if any */ + if (indexRelation->rd_index->indisexclusion) + { + /* + * XXX Beware: we're making newInfo point to oldInfo-owned memory. It + * would be more orthodox to palloc+memcpy, but we don't need that + * here at present. + */ + newInfo->ii_ExclusionOps = oldInfo->ii_ExclusionOps; + newInfo->ii_ExclusionProcs = oldInfo->ii_ExclusionProcs; + newInfo->ii_ExclusionStrats = oldInfo->ii_ExclusionStrats; + } + /* * Extract the list of column names and the column numbers for the new * index information. All this information will be used for the index @@ -1436,6 +1448,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, stattargets[i].isnull = isnull; } + if (concurrently) + flags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT; + /* * Now create the new index. * @@ -1459,7 +1474,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indcoloptions->values, stattargets, reloptionsDatum, - INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT, + flags, 0, true, /* allow table to be a system catalog? */ false, /* is_internal? */ diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 373e8234794..cba379810c7 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -3989,10 +3989,11 @@ ReindexRelationConcurrently(const ReindexStmt *stmt, Oid relationOid, const Rein tablespaceid = indexRel->rd_rel->reltablespace; /* Create new index definition based on given index */ - newIndexId = index_concurrently_create_copy(heapRel, - idx->indexId, - tablespaceid, - concurrentName); + newIndexId = index_create_copy(heapRel, + true, + idx->indexId, + tablespaceid, + concurrentName); /* * Now open the relation of the new index, a session-level lock is diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index a38e95bc0eb..ed9e4c37d27 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -101,10 +101,9 @@ extern Oid index_create(Relation heapRelation, #define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4) #define INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS (1 << 5) -extern Oid index_concurrently_create_copy(Relation heapRelation, - Oid oldIndexId, - Oid tablespaceOid, - const char *newName); +extern Oid index_create_copy(Relation heapRelation, bool concurrently, + Oid oldIndexId, Oid tablespaceOid, + const char *newName); extern void index_concurrently_build(Oid heapRelationId, Oid indexRelationId); -- 2.47.3 --brnevsqjnuzpyok4 Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v50-0002-Rename-cluster.c-h-repack.c-h.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-03-24 18:02 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-02-06 08:26 [PATCH 3/3] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-02-06 08:26 [PATCH 3/3] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-02-06 08:26 [PATCH 2/3] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-02-06 08:26 [PATCH 2/3] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-03-10 04:48 [PATCH 1/2] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-03-10 04:48 [PATCH 1/2] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2017-03-10 04:48 [PATCH 1/2] Avoid creating scan nodes for partitioned tables amit <amitlangote09@gmail.com> 2026-03-24 18:02 [PATCH v50 1/8] Make index_concurrently_create_copy more general Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox