0012-Multi-level-partitioned-table-expansion.patch

From 389e0adf55085470f3fdbbfad8521f758cc45136 Mon Sep 17 00:00:00 2001
From: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Date: Fri, 10 Feb 2017 13:50:14 +0530
Subject: [PATCH 12/14] Multi-level partitioned table expansion.

Construct inheritance hierarchy of a partitioned table to reflect the partition
hierarchy. Propagate lateral join information down the partition hierarchy.
---
 src/backend/optimizer/plan/initsplan.c |   14 +++++-
 src/backend/optimizer/prep/prepunion.c |   78 +++++++++++++++++++++-----------
 src/test/regress/expected/inherit.out  |   38 ++++++++--------
 3 files changed, 83 insertions(+), 47 deletions(-)

diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index c170e96..e302f4f 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -628,7 +628,19 @@ create_lateral_join_info(PlannerInfo *root)
 	{
 		RelOptInfo *brel = root->simple_rel_array[rti];
 
-		if (brel == NULL || brel->reloptkind != RELOPT_BASEREL)
+		if (brel == NULL)
+			continue;
+
+		/*
+		 * If an "other rel" RTE is a "partitioned table", we must propagate
+		 * the lateral info inherited all the way from the root parent to its
+		 * children. That's because the children are not linked directly with
+		 * the root parent via AppendRelInfo's unlike in case of a regular
+		 * inheritance set (see expand_inherited_rtentry()).  Failing to
+		 * do this would result in those children not getting marked with the
+		 * appropriate lateral info.
+		 */
+		if (brel->reloptkind != RELOPT_BASEREL && !brel->part_scheme)
 			continue;
 
 		if (root->simple_rte_array[rti]->inh)
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index d459e95..63b45d6 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -98,7 +98,7 @@ static List *generate_append_tlist(List *colTypes, List *colCollations,
 					  List *refnames_tlist);
 static List *generate_setop_grouplist(SetOperationStmt *op, List *targetlist);
 static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte,
-						 Index rti);
+						 Index rti, LOCKMODE lockmode);
 static void make_inh_translation_list(Relation oldrelation,
 						  Relation newrelation,
 						  Index newvarno,
@@ -1319,19 +1319,44 @@ expand_inherited_tables(PlannerInfo *root)
 	Index		nrtes;
 	Index		rti;
 	ListCell   *rl;
+	Query	   *parse = root->parse;
 
 	/*
 	 * expand_inherited_rtentry may add RTEs to parse->rtable; there is no
 	 * need to scan them since they can't have inh=true.  So just scan as far
 	 * as the original end of the rtable list.
 	 */
-	nrtes = list_length(root->parse->rtable);
-	rl = list_head(root->parse->rtable);
+	nrtes = list_length(parse->rtable);
+	rl = list_head(parse->rtable);
 	for (rti = 1; rti <= nrtes; rti++)
 	{
 		RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl);
+		LOCKMODE	lockmode;
+		PlanRowMark	  *oldrc;
 
-		expand_inherited_rtentry(root, rte, rti);
+		/*
+		 * The rewriter should already have obtained an appropriate lock on
+		 * each relation named in the query.  However, for each child relation
+		 * we add to the query, we must obtain an appropriate lock, because
+		 * this will be the first use of those relations in the
+		 * parse/rewrite/plan pipeline.
+		 *
+		 * If the parent relation is the query's result relation, then we need
+		 * RowExclusiveLock.  Otherwise, if it's accessed FOR UPDATE/SHARE, we
+		 * need RowShareLock; otherwise AccessShareLock.  We can't just grab
+		 * AccessShareLock because then the executor would be trying to upgrade
+		 * the lock, leading to possible deadlocks.  (This code should match
+		 * the parser and rewriter.)
+		 */
+		oldrc = get_plan_rowmark(root->rowMarks, rti);
+		if (rti == parse->resultRelation)
+			lockmode = RowExclusiveLock;
+		else if (oldrc && RowMarkRequiresRowShareLock(oldrc->markType))
+			lockmode = RowShareLock;
+		else
+			lockmode = AccessShareLock;
+
+		expand_inherited_rtentry(root, rte, rti, lockmode);
 		rl = lnext(rl);
 	}
 }
@@ -1353,13 +1378,13 @@ expand_inherited_tables(PlannerInfo *root)
  * a parent RTE must always have at least two associated AppendRelInfos.
  */
 static void
-expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
+expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti,
+						 LOCKMODE lockmode)
 {
 	Query	   *parse = root->parse;
 	Oid			parentOID;
 	PlanRowMark *oldrc;
 	Relation	oldrelation;
-	LOCKMODE	lockmode;
 	List	   *inhOIDs;
 	List	   *appinfos;
 	ListCell   *l;
@@ -1383,28 +1408,18 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
 	}
 
 	/*
-	 * The rewriter should already have obtained an appropriate lock on each
-	 * relation named in the query.  However, for each child relation we add
-	 * to the query, we must obtain an appropriate lock, because this will be
-	 * the first use of those relations in the parse/rewrite/plan pipeline.
-	 *
-	 * If the parent relation is the query's result relation, then we need
-	 * RowExclusiveLock.  Otherwise, if it's accessed FOR UPDATE/SHARE, we
-	 * need RowShareLock; otherwise AccessShareLock.  We can't just grab
-	 * AccessShareLock because then the executor would be trying to upgrade
-	 * the lock, leading to possible deadlocks.  (This code should match the
-	 * parser and rewriter.)
+	 * Expand partitioned table level-wise to help optimizations like
+	 * partition-wise join which match partitions at every level. Otherwise,
+	 * scan for all members of inheritance set. Acquire needed locks
 	 */
-	oldrc = get_plan_rowmark(root->rowMarks, rti);
-	if (rti == parse->resultRelation)
-		lockmode = RowExclusiveLock;
-	else if (oldrc && RowMarkRequiresRowShareLock(oldrc->markType))
-		lockmode = RowShareLock;
+	if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+	{
+		inhOIDs = list_make1_oid(parentOID);
+		inhOIDs = list_concat(inhOIDs,
+							  find_inheritance_children(parentOID, lockmode));
+	}
 	else
-		lockmode = AccessShareLock;
-
-	/* Scan for all members of inheritance set, acquire needed locks */
-	inhOIDs = find_all_inheritors(parentOID, lockmode, NULL);
+		inhOIDs = find_all_inheritors(parentOID, lockmode, NULL);
 
 	/*
 	 * Check that there's at least one descendant, else treat as no-child
@@ -1418,6 +1433,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
 		return;
 	}
 
+	oldrc = get_plan_rowmark(root->rowMarks, rti);
 	/*
 	 * If parent relation is selected FOR UPDATE/SHARE, we need to mark its
 	 * PlanRowMark as isParent = true, and generate a new PlanRowMark for each
@@ -1475,7 +1491,12 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
 		childrte = copyObject(rte);
 		childrte->relid = childOID;
 		childrte->relkind = newrelation->rd_rel->relkind;
-		childrte->inh = false;
+		/* A partitioned child will need to be expanded further. */
+		if (childOID != parentOID &&
+			childrte->relkind == RELKIND_PARTITIONED_TABLE)
+			childrte->inh = true;
+		else
+			childrte->inh = false;
 		childrte->requiredPerms = 0;
 		childrte->securityQuals = NIL;
 		parse->rtable = lappend(parse->rtable, childrte);
@@ -1539,6 +1560,9 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
 		/* Close child relations, but keep locks */
 		if (childOID != parentOID)
 			heap_close(newrelation, NoLock);
+
+		/* Expand partitioned children recursively. */
+		expand_inherited_rtentry(root, childrte, childRTindex, lockmode);
 	}
 
 	heap_close(oldrelation, NoLock);
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index a8c8b28..6941045 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1694,15 +1694,15 @@ explain (costs off) select * from range_list_parted;
  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
    ->  Seq Scan on part_10_20_ab
    ->  Seq Scan on part_10_20_cd
+   ->  Seq Scan on part_21_30
    ->  Seq Scan on part_21_30_ab
    ->  Seq Scan on part_21_30_cd
+   ->  Seq Scan on part_40_inf
    ->  Seq Scan on part_40_inf_ab
    ->  Seq Scan on part_40_inf_cd
    ->  Seq Scan on part_40_inf_null
@@ -1730,18 +1730,18 @@ explain (costs off) select * from range_list_parted where b = 'ab';
          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
+         Filter: (b = 'ab'::bpchar)
    ->  Seq Scan on part_10_20_ab
          Filter: (b = 'ab'::bpchar)
+   ->  Seq Scan on part_21_30
+         Filter: (b = 'ab'::bpchar)
    ->  Seq Scan on part_21_30_ab
          Filter: (b = 'ab'::bpchar)
+   ->  Seq Scan on part_40_inf
+         Filter: (b = 'ab'::bpchar)
    ->  Seq Scan on part_40_inf_ab
          Filter: (b = 'ab'::bpchar)
 (19 rows)
@@ -1754,14 +1754,14 @@ explain (costs off) select * from range_list_parted where a between 3 and 23 and
          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
+         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
+         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)
@@ -1801,24 +1801,24 @@ 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_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
          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_10_20_ab
          Filter: ((a IS NOT NULL) AND (a < 67))
    ->  Seq Scan on part_10_20_cd
          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_21_30_ab
          Filter: ((a IS NOT NULL) AND (a < 67))
    ->  Seq Scan on part_21_30_cd
          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_40_inf_ab
          Filter: ((a IS NOT NULL) AND (a < 67))
    ->  Seq Scan on part_40_inf_cd
-- 
1.7.9.5