这篇文章主要介绍“PostgreSQL查询语句分析”，在日常操作中，相信很多人在PostgreSQL查询语句分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL查询语句分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
子查询上拉在函数pull_up_subqueries中实现,该函数调用pull_up_subqueries_recurse函数递归实现子查询上拉.
pull_up_subqueries
/*
* pull_up_subqueries
* Look for subqueries in the rangetable that can be pulled up into
* the parent query. If the subquery has no special features like
* grouping/aggregation then we can merge it into the parent's jointree.
* Also, subqueries that are simple UNION ALL structures can be
* converted into "append relations".
*/
void
pull_up_subqueries(PlannerInfo *root)
{
/* Top level of jointree must always be a FromExpr */
Assert(IsA(root->parse->jointree, FromExpr));
/* Reset flag saying we need a deletion cleanup pass */
root->hasDeletedRTEs = false;
/* Recursion starts with no containing join nor appendrel */
root->parse->jointree = (FromExpr *)
pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,
NULL, NULL, NULL, false);
/* Apply cleanup phase if necessary */
if (root->hasDeletedRTEs)
root->parse->jointree = (FromExpr *)
pull_up_subqueries_cleanup((Node *) root->parse->jointree);
Assert(IsA(root->parse->jointree, FromExpr));
}pull_up_subqueries_recurse
/*
* pull_up_subqueries_recurse
* Recursive guts of pull_up_subqueries.
*
* This recursively processes the jointree and returns a modified jointree.
* Or, if it's valid to drop the current node from the jointree completely,
* it returns NULL.
*
* If this jointree node is within either side of an outer join, then
* lowest_outer_join references the lowest such JoinExpr node; otherwise
* it is NULL. We use this to constrain the effects of LATERAL subqueries.
*
* If this jointree node is within the nullable side of an outer join, then
* lowest_nulling_outer_join references the lowest such JoinExpr node;
* otherwise it is NULL. This forces use of the PlaceHolderVar mechanism for
* references to non-nullable targetlist items, but only for references above
* that join.
*
* If we are looking at a member subquery of an append relation,
* containing_appendrel describes that relation; else it is NULL.
* This forces use of the PlaceHolderVar mechanism for all non-Var targetlist
* items, and puts some additional restrictions on what can be pulled up.
*
* deletion_ok is true if the caller can cope with us returning NULL for a
* deletable leaf node (for example, a VALUES RTE that could be pulled up).
* If it's false, we'll avoid pullup in such cases.
*
* A tricky aspect of this code is that if we pull up a subquery we have
* to replace Vars that reference the subquery's outputs throughout the
* parent query, including quals attached to jointree nodes above the one
* we are currently processing! We handle this by being careful not to
* change the jointree structure while recursing: no nodes other than leaf
* RangeTblRef entries and entirely-empty FromExprs will be replaced or
* deleted. Also, we can't turn pullup_replace_vars loose on the whole
* jointree, because it'll return a mutated copy of the tree; we have to
* invoke it just on the quals, instead. This behavior is what makes it
* reasonable to pass lowest_outer_join and lowest_nulling_outer_join as
* pointers rather than some more-indirect way of identifying the lowest
* OJs. Likewise, we don't replace append_rel_list members but only their
* substructure, so the containing_appendrel reference is safe to use.
*
* Because of the rule that no jointree nodes with substructure can be
* replaced, we cannot fully handle the case of deleting nodes from the tree:
* when we delete one child of a JoinExpr, we need to replace the JoinExpr
* with a FromExpr, and that can't happen here. Instead, we set the
* root->hasDeletedRTEs flag, which tells pull_up_subqueries() that an
* additional pass over the tree is needed to clean up.
*/
/*
输入参数:
root-计划器相关信息
jtnode-需要处理的Node(jointree)
lowest_outer_join-如该节点位于外连接的任意一侧,则该指针指向此节点
lowest_nulling_outer_join-如该节点位于外连接的可空一侧,,则该指针指向此节点
containing_appendrel-Append操作中的Relation
deletion_ok-调用方可处理在可删除的叶子节点的情况下返回NULL,此值为true
输出参数:
*/
static Node *
pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
AppendRelInfo *containing_appendrel,
bool deletion_ok)
{
Assert(jtnode != NULL);
if (IsA(jtnode, RangeTblRef))//如为RTR
{
//获取该RTR相应的RTE
int varno = ((RangeTblRef *) jtnode)->rtindex;
RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);
/*
* Is this a subquery RTE, and if so, is the subquery simple enough to
* pull up?
*
* If we are looking at an append-relation member, we can't pull it up
* unless is_safe_append_member says so.
*/
if (rte->rtekind == RTE_SUBQUERY &&
is_simple_subquery(rte->subquery, rte,
lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL ||
is_safe_append_member(rte->subquery)))//简单子查询
return pull_up_simple_subquery(root, jtnode, rte,
lowest_outer_join,
lowest_nulling_outer_join,
containing_appendrel,
deletion_ok);
/*
* Alternatively, is it a simple UNION ALL subquery? If so, flatten
* into an "append relation".
*
* It's safe to do this regardless of whether this query is itself an
* appendrel member. (If you're thinking we should try to flatten the
* two levels of appendrel together, you're right; but we handle that
* in set_append_rel_pathlist, not here.)
*/
if (rte->rtekind == RTE_SUBQUERY &&
is_simple_union_all(rte->subquery))//UNION ALL子查询
return pull_up_simple_union_all(root, jtnode, rte);
/*
* Or perhaps it's a simple VALUES RTE?
*
* We don't allow VALUES pullup below an outer join nor into an
* appendrel (such cases are impossible anyway at the moment).
*/
if (rte->rtekind == RTE_VALUES &&
lowest_outer_join == NULL &&
containing_appendrel == NULL &&
is_simple_values(root, rte, deletion_ok))//VALUES子查询
return pull_up_simple_values(root, jtnode, rte);
/* Otherwise, do nothing at this node. */
}
else if (IsA(jtnode, FromExpr))//如为FromExpr
{
FromExpr *f = (FromExpr *) jtnode;
bool have_undeleted_child = false;
ListCell *l;
Assert(containing_appendrel == NULL);
/*
* If the FromExpr has quals, it's not deletable even if its parent
* would allow deletion.
*/
if (f->quals)
deletion_ok = false;
foreach(l, f->fromlist)
{
/*
* In a non-deletable FromExpr, we can allow deletion of child
* nodes so long as at least one child remains; so it's okay
* either if any previous child survives, or if there's more to
* come. If all children are deletable in themselves, we'll force
* the last one to remain unflattened.
*
* As a separate matter, we can allow deletion of all children of
* the top-level FromExpr in a query, since that's a special case
* anyway.
*/
bool sub_deletion_ok = (deletion_ok ||
have_undeleted_child ||
lnext(l) != NULL ||
f == root->parse->jointree);
lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
sub_deletion_ok);//递归调用
if (lfirst(l) != NULL)
have_undeleted_child = true;
}
if (deletion_ok && !have_undeleted_child)
{
/* OK to delete this FromExpr entirely */
root->hasDeletedRTEs = true; /* probably is set already */
return NULL;
}
}
else if (IsA(jtnode, JoinExpr))//如为JoinExpr
{
JoinExpr *j = (JoinExpr *) jtnode;
Assert(containing_appendrel == NULL);
/* Recurse, being careful to tell myself when inside outer join */
switch (j->jointype)
{
case JOIN_INNER:
/*
* INNER JOIN can allow deletion of either child node, but not
* both. So right child gets permission to delete only if
* left child didn't get removed.
*/
j->larg = pull_up_subqueries_recurse(root, j->larg,
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
true);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
lowest_outer_join,
lowest_nulling_outer_join,
NULL,
j->larg != NULL);
break;
case JOIN_LEFT:
case JOIN_SEMI:
case JOIN_ANTI:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
lowest_nulling_outer_join,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
NULL,
false);
break;
case JOIN_FULL:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
j,
NULL,
false);
break;
case JOIN_RIGHT:
j->larg = pull_up_subqueries_recurse(root, j->larg,
j,
j,
NULL,
false);
j->rarg = pull_up_subqueries_recurse(root, j->rarg,
j,
lowest_nulling_outer_join,
NULL,
false);
break;
default:
elog(ERROR, "unrecognized join type: %d",
(int) j->jointype);
break;
}
}
else
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(jtnode));
return jtnode;
}
/*
* pull_up_simple_subquery
* Attempt to pull up a single simple subquery.
*
* jtnode is a RangeTblRef that has been tentatively identified as a simple
* subquery by pull_up_subqueries. We return the replacement jointree node,
* or NULL if the subquery can be deleted entirely, or jtnode itself if we
* determine that the subquery can't be pulled up after all.
*
* rte is the RangeTblEntry referenced by jtnode. Remaining parameters are
* as for pull_up_subqueries_recurse.
*/
static Node *
pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
JoinExpr *lowest_nulling_outer_join,
AppendRelInfo *containing_appendrel,
bool deletion_ok)
{
Query *parse = root->parse;//查询树
int varno = ((RangeTblRef *) jtnode)->rtindex;//RTR中的index,指向rtable中的位置
Query *subquery;//子查询
PlannerInfo *subroot;//子root
int rtoffset;//rtable中的偏移
pullup_replace_vars_context rvcontext;//上下文
ListCell *lc;//临时变量
/*
* Need a modifiable copy of the subquery to hack on. Even if we didn't
* sometimes choose not to pull up below, we must do this to avoid
* problems if the same subquery is referenced from multiple jointree
* items (which can't happen normally, but might after rule rewriting).
*/
subquery = copyObject(rte->subquery);//子查询
/*
* Create a PlannerInfo data structure for this subquery.
*
* NOTE: the next few steps should match the first processing in
* subquery_planner(). Can we refactor to avoid code duplication, or
* would that just make things uglier?
*/
//为子查询构建PlannerInfo,尝试对此子查询进行上拉
subroot = makeNode(PlannerInfo);
subroot->parse = subquery;
subroot->glob = root->glob;
subroot->query_level = root->query_level;
subroot->parent_root = root->parent_root;
subroot->plan_params = NIL;
subroot->outer_params = NULL;
subroot->planner_cxt = CurrentMemoryContext;
subroot->init_plans = NIL;
subroot->cte_plan_ids = NIL;
subroot->multiexpr_params = NIL;
subroot->eq_classes = NIL;
subroot->append_rel_list = NIL;
subroot->rowMarks = NIL;
memset(subroot->upper_rels, 0, sizeof(subroot->upper_rels));
memset(subroot->upper_targets, 0, sizeof(subroot->upper_targets));
subroot->processed_tlist = NIL;
subroot->grouping_map = NULL;
subroot->minmax_aggs = NIL;
subroot->qual_security_level = 0;
subroot->inhTargetKind = INHKIND_NONE;
subroot->hasRecursion = false;
subroot->wt_param_id = -1;
subroot->non_recursive_path = NULL;
/* No CTEs to worry about */
Assert(subquery->cteList == NIL);
/*
* Pull up any SubLinks within the subquery's quals, so that we don't
* leave unoptimized SubLinks behind.
*/
if (subquery->hasSubLinks)//子链接?上拉子链接
pull_up_sublinks(subroot);
/*
* Similarly, inline any set-returning functions in its rangetable.
*/
inline_set_returning_functions(subroot);
/*
* Recursively pull up the subquery's subqueries, so that
* pull_up_subqueries' processing is complete for its jointree and
* rangetable.
*
* Note: it's okay that the subquery's recursion starts with NULL for
* containing-join info, even if we are within an outer join in the upper
* query; the lower query starts with a clean slate for outer-join
* semantics. Likewise, we needn't pass down appendrel state.
*/
pull_up_subqueries(subroot);//递归上拉子查询中的子查询
/*
* Now we must recheck whether the subquery is still simple enough to pull
* up. If not, abandon processing it.
*
* We don't really need to recheck all the conditions involved, but it's
* easier just to keep this "if" looking the same as the one in
* pull_up_subqueries_recurse.
*/
//子查询中子链接&子查询上拉后,再次检查,确保本次上拉没有问题
if (is_simple_subquery(subquery, rte,
lowest_outer_join, deletion_ok) &&
(containing_appendrel == NULL || is_safe_append_member(subquery)))
{
/* good to go */
}
else
{
/*
* Give up, return unmodified RangeTblRef.
*
* Note: The work we just did will be redone when the subquery gets
* planned on its own. Perhaps we could avoid that by storing the
* modified subquery back into the rangetable, but I'm not gonna risk
* it now.
*/
return jtnode;
}
/*
* We must flatten any join alias Vars in the subquery's targetlist,
* because pulling up the subquery's subqueries might have changed their
* expansions into arbitrary expressions, which could affect
* pullup_replace_vars' decisions about whether PlaceHolderVar wrappers
* are needed for tlist entries. (Likely it'd be better to do
* flatten_join_alias_vars on the whole query tree at some earlier stage,
* maybe even in the rewriter; but for now let's just fix this case here.)
*/
//子查询中的targetList扁平化处理
subquery->targetList = (List *)
flatten_join_alias_vars(subroot, (Node *) subquery->targetList);
/*
* Adjust level-0 varnos in subquery so that we can append its rangetable
* to upper query's. We have to fix the subquery's append_rel_list as
* well.
*/
//调整Var.varno
rtoffset = list_length(parse->rtable);
OffsetVarNodes((Node *) subquery, rtoffset, 0);
OffsetVarNodes((Node *) subroot->append_rel_list, rtoffset, 0);
/*
* Upper-level vars in subquery are now one level closer to their parent
* than before.
*/
//调整Var.varlevelsup
IncrementVarSublevelsUp((Node *) subquery, -1, 1);
IncrementVarSublevelsUp((Node *) subroot->append_rel_list, -1, 1);
/*
* The subquery's targetlist items are now in the appropriate form to
* insert into the top query, except that we may need to wrap them in
* PlaceHolderVars. Set up required context data for pullup_replace_vars.
*/
rvcontext.root = root;
rvcontext.targetlist = subquery->targetList;
rvcontext.target_rte = rte;
if (rte->lateral)
rvcontext.relids = get_relids_in_jointree((Node *) subquery->jointree,
true);
else /* won't need relids */
rvcontext.relids = NULL;
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
rvcontext.varno = varno;
/* these flags will be set below, if needed */
rvcontext.need_phvs = false;
rvcontext.wrap_non_vars = false;
/* initialize cache array with indexes 0 .. length(tlist) */
rvcontext.rv_cache = palloc0((list_length(subquery->targetList) + 1) *
sizeof(Node *));
/*
* If we are under an outer join then non-nullable items and lateral
* references may have to be turned into PlaceHolderVars.
*/
if (lowest_nulling_outer_join != NULL)
rvcontext.need_phvs = true;
/*
* If we are dealing with an appendrel member then anything that's not a
* simple Var has to be turned into a PlaceHolderVar. We force this to
* ensure that what we pull up doesn't get merged into a surrounding
* expression during later processing and then fail to match the
* expression actually available from the appendrel.
*/
if (containing_appendrel != NULL)
{
rvcontext.need_phvs = true;
rvcontext.wrap_non_vars = true;
}
/*
* If the parent query uses grouping sets, we need a PlaceHolderVar for
* anything that's not a simple Var. Again, this ensures that expressions
* retain their separate identity so that they will match grouping set
* columns when appropriate. (It'd be sufficient to wrap values used in
* grouping set columns, and do so only in non-aggregated portions of the
* tlist and havingQual, but that would require a lot of infrastructure
* that pullup_replace_vars hasn't currently got.)
*/
if (parse->groupingSets)
{
rvcontext.need_phvs = true;
rvcontext.wrap_non_vars = true;
}
/*
* Replace all of the top query's references to the subquery's outputs
* with copies of the adjusted subtlist items, being careful not to
* replace any of the jointree structure. (This'd be a lot cleaner if we
* could use query_tree_mutator.) We have to use PHVs in the targetList,
* returningList, and havingQual, since those are certainly above any
* outer join. replace_vars_in_jointree tracks its location in the
* jointree and uses PHVs or not appropriately.
*/
//处理投影
parse->targetList = (List *)
pullup_replace_vars((Node *) parse->targetList, &rvcontext);
parse->returningList = (List *)
pullup_replace_vars((Node *) parse->returningList, &rvcontext);
if (parse->onConflict)
{
parse->onConflict->onConflictSet = (List *)
pullup_replace_vars((Node *) parse->onConflict->onConflictSet,
&rvcontext);
parse->onConflict->onConflictWhere =
pullup_replace_vars(parse->onConflict->onConflictWhere,
&rvcontext);
/*
* We assume ON CONFLICT's arbiterElems, arbiterWhere, exclRelTlist
* can't contain any references to a subquery
*/
}
replace_vars_in_jointree((Node *) parse->jointree, &rvcontext,
lowest_nulling_outer_join);
Assert(parse->setOperations == NULL);
parse->havingQual = pullup_replace_vars(parse->havingQual, &rvcontext);
/*
* Replace references in the translated_vars lists of appendrels. When
* pulling up an appendrel member, we do not need PHVs in the list of the
* parent appendrel --- there isn't any outer join between. Elsewhere, use
* PHVs for safety. (This analysis could be made tighter but it seems
* unlikely to be worth much trouble.)
*/
//处理appendrels中的信息
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
bool save_need_phvs = rvcontext.need_phvs;
if (appinfo == containing_appendrel)
rvcontext.need_phvs = false;
appinfo->translated_vars = (List *)
pullup_replace_vars((Node *) appinfo->translated_vars, &rvcontext);
rvcontext.need_phvs = save_need_phvs;
}
/*
* Replace references in the joinaliasvars lists of join RTEs.
*
* You might think that we could avoid using PHVs for alias vars of joins
* below lowest_nulling_outer_join, but that doesn't work because the
* alias vars could be referenced above that join; we need the PHVs to be
* present in such references after the alias vars get flattened. (It
* might be worth trying to be smarter here, someday.)
*/
//处理RTE中类型为RTE_JOIN的节点
foreach(lc, parse->rtable)
{
RangeTblEntry *otherrte = (RangeTblEntry *) lfirst(lc);
if (otherrte->rtekind == RTE_JOIN)
otherrte->joinaliasvars = (List *)
pullup_replace_vars((Node *) otherrte->joinaliasvars,
&rvcontext);
}
/*
* If the subquery had a LATERAL marker, propagate that to any of its
* child RTEs that could possibly now contain lateral cross-references.
* The children might or might not contain any actual lateral
* cross-references, but we have to mark the pulled-up child RTEs so that
* later planner stages will check for such.
*/
//LATERAL支持
if (rte->lateral)
{
foreach(lc, subquery->rtable)
{
RangeTblEntry *child_rte = (RangeTblEntry *) lfirst(lc);
switch (child_rte->rtekind)
{
case RTE_RELATION:
if (child_rte->tablesample)
child_rte->lateral = true;
break;
case RTE_SUBQUERY:
case RTE_FUNCTION:
case RTE_VALUES:
case RTE_TABLEFUNC:
child_rte->lateral = true;
break;
case RTE_JOIN:
case RTE_CTE:
case RTE_NAMEDTUPLESTORE:
/* these can't contain any lateral references */
break;
}
}
}
/*
* Now append the adjusted rtable entries to upper query. (We hold off
* until after fixing the upper rtable entries; no point in running that
* code on the subquery ones too.)
*/
//子查询中的RTE填充至父查询中
parse->rtable = list_concat(parse->rtable, subquery->rtable);
/*
* Pull up any FOR UPDATE/SHARE markers, too. (OffsetVarNodes already
* adjusted the marker rtindexes, so just concat the lists.)
*/
parse->rowMarks = list_concat(parse->rowMarks, subquery->rowMarks);
/*
* We also have to fix the relid sets of any PlaceHolderVar nodes in the
* parent query. (This could perhaps be done by pullup_replace_vars(),
* but it seems cleaner to use two passes.) Note in particular that any
* PlaceHolderVar nodes just created by pullup_replace_vars() will be
* adjusted, so having created them with the subquery's varno is correct.
*
* Likewise, relids appearing in AppendRelInfo nodes have to be fixed. We
* already checked that this won't require introducing multiple subrelids
* into the single-slot AppendRelInfo structs.
*/
if (parse->hasSubLinks || root->glob->lastPHId != 0 ||
root->append_rel_list)
{
Relids subrelids;
subrelids = get_relids_in_jointree((Node *) subquery->jointree, false);
substitute_multiple_relids((Node *) parse, varno, subrelids);
fix_append_rel_relids(root->append_rel_list, varno, subrelids);
}
/*
* And now add subquery's AppendRelInfos to our list.
*/
root->append_rel_list = list_concat(root->append_rel_list,
subroot->append_rel_list);
/*
* We don't have to do the equivalent bookkeeping for outer-join info,
* because that hasn't been set up yet. placeholder_list likewise.
*/
Assert(root->join_info_list == NIL);
Assert(subroot->join_info_list == NIL);
Assert(root->placeholder_list == NIL);
Assert(subroot->placeholder_list == NIL);
/*
* Miscellaneous housekeeping.
*
* Although replace_rte_variables() faithfully updated parse->hasSubLinks
* if it copied any SubLinks out of the subquery's targetlist, we still
* could have SubLinks added to the query in the expressions of FUNCTION
* and VALUES RTEs copied up from the subquery. So it's necessary to copy
* subquery->hasSubLinks anyway. Perhaps this can be improved someday.
*/
parse->hasSubLinks |= subquery->hasSubLinks;
/* If subquery had any RLS conditions, now main query does too */
parse->hasRowSecurity |= subquery->hasRowSecurity;
/*
* subquery won't be pulled up if it hasAggs, hasWindowFuncs, or
* hasTargetSRFs, so no work needed on those flags
*/
/*
* Return the adjusted subquery jointree to replace the RangeTblRef entry
* in parent's jointree; or, if we're flattening a subquery with empty
* FROM list, return NULL to signal deletion of the subquery from the
* parent jointree (and set hasDeletedRTEs to ensure cleanup later).
*/
if (subquery->jointree->fromlist == NIL)
{
Assert(deletion_ok);
Assert(subquery->jointree->quals == NULL);
root->hasDeletedRTEs = true;
return NULL;
}
return (Node *) subquery->jointree;
}is_simple_subquery
/*
* is_simple_subquery
* Check a subquery in the range table to see if it's simple enough
* to pull up into the parent query.
*
* rte is the RTE_SUBQUERY RangeTblEntry that contained the subquery.
* (Note subquery is not necessarily equal to rte->subquery; it could be a
* processed copy of that.)
* lowest_outer_join is the lowest outer join above the subquery, or NULL.
* deletion_ok is true if it'd be okay to delete the subquery entirely.
*/
static bool
is_simple_subquery(Query *subquery, RangeTblEntry *rte,
JoinExpr *lowest_outer_join,
bool deletion_ok)
{
/*
* Let's just make sure it's a valid subselect ...
*/
if (!IsA(subquery, Query) ||
subquery->commandType != CMD_SELECT)
elog(ERROR, "subquery is bogus");
/*
* Can't currently pull up a query with setops (unless it's simple UNION
* ALL, which is handled by a different code path). Maybe after querytree
* redesign...
*/
if (subquery->setOperations)
return false;//存在集合操作
/*
* Can't pull up a subquery involving grouping, aggregation, SRFs,
* sorting, limiting, or WITH. (XXX WITH could possibly be allowed later)
*
* We also don't pull up a subquery that has explicit FOR UPDATE/SHARE
* clauses, because pullup would cause the locking to occur semantically
* higher than it should. Implicit FOR UPDATE/SHARE is okay because in
* that case the locking was originally declared in the upper query
* anyway.
*/
if (subquery->hasAggs ||
subquery->hasWindowFuncs ||
subquery->hasTargetSRFs ||
subquery->groupClause ||
subquery->groupingSets ||
subquery->havingQual ||
subquery->sortClause ||
subquery->distinctClause ||
subquery->limitOffset ||
subquery->limitCount ||
subquery->hasForUpdate ||
subquery->cteList)
return false;//存在聚合函数/窗口函数...
/*
* Don't pull up if the RTE represents a security-barrier view; we
* couldn't prevent information leakage once the RTE's Vars are scattered
* about in the upper query.
*/
if (rte->security_barrier)
return false;//
/*
* Don't pull up a subquery with an empty jointree, unless it has no quals
* and deletion_ok is true and we're not underneath an outer join.
*
* query_planner() will correctly generate a Result plan for a jointree
* that's totally empty, but we can't cope with an empty FromExpr
* appearing lower down in a jointree: we identify join rels via baserelid
* sets, so we couldn't distinguish a join containing such a FromExpr from
* one without it. We can only handle such cases if the place where the
* subquery is linked is a FromExpr or inner JOIN that would still be
* nonempty after removal of the subquery, so that it's still identifiable
* via its contained baserelids. Safe contexts are signaled by
* deletion_ok.
*
* But even in a safe context, we must keep the subquery if it has any
* quals, because it's unclear where to put them in the upper query.
*
* Also, we must forbid pullup if such a subquery is underneath an outer
* join, because then we might need to wrap its output columns with
* PlaceHolderVars, and the PHVs would then have empty relid sets meaning
* we couldn't tell where to evaluate them. (This test is separate from
* the deletion_ok flag for possible future expansion: deletion_ok tells
* whether the immediate parent site in the jointree could cope, not
* whether we'd have PHV issues. It's possible this restriction could be
* fixed by letting the PHVs use the relids of the parent jointree item,
* but that complication is for another day.)
*
* Note that deletion of a subquery is also dependent on the check below
* that its targetlist contains no set-returning functions. Deletion from
* a FROM list or inner JOIN is okay only if the subquery must return
* exactly one row.
*/
if (subquery->jointree->fromlist == NIL &&
(subquery->jointree->quals != NULL ||
!deletion_ok ||
lowest_outer_join != NULL))
return false;
/*
* If the subquery is LATERAL, check for pullup restrictions from that.
*/
if (rte->lateral)
{
bool restricted;
Relids safe_upper_varnos;
/*
* The subquery's WHERE and JOIN/ON quals mustn't contain any lateral
* references to rels outside a higher outer join (including the case
* where the outer join is within the subquery itself). In such a
* case, pulling up would result in a situation where we need to
* postpone quals from below an outer join to above it, which is
* probably completely wrong and in any case is a complication that
* doesn't seem worth addressing at the moment.
*/
if (lowest_outer_join != NULL)
{
restricted = true;
safe_upper_varnos = get_relids_in_jointree((Node *) lowest_outer_join,
true);
}
else
{
restricted = false;
safe_upper_varnos = NULL; /* doesn't matter */
}
if (jointree_contains_lateral_outer_refs((Node *) subquery->jointree,
restricted, safe_upper_varnos))
return false;
/*
* If there's an outer join above the LATERAL subquery, also disallow
* pullup if the subquery's targetlist has any references to rels
* outside the outer join, since these might get pulled into quals
* above the subquery (but in or below the outer join) and then lead
* to qual-postponement issues similar to the case checked for above.
* (We wouldn't need to prevent pullup if no such references appear in
* outer-query quals, but we don't have enough info here to check
* that. Also, maybe this restriction could be removed if we forced
* such refs to be wrapped in PlaceHolderVars, even when they're below
* the nearest outer join? But it's a pretty hokey usage, so not
* clear this is worth sweating over.)
*/
if (lowest_outer_join != NULL)
{
Relids lvarnos = pull_varnos_of_level((Node *) subquery->targetList, 1);
if (!bms_is_subset(lvarnos, safe_upper_varnos))
return false;
}
}
/*
* Don't pull up a subquery that has any volatile functions in its
* targetlist. Otherwise we might introduce multiple evaluations of these
* functions, if they get copied to multiple places in the upper query,
* leading to surprising results. (Note: the PlaceHolderVar mechanism
* doesn't quite guarantee single evaluation; else we could pull up anyway
* and just wrap such items in PlaceHolderVars ...)
*/
if (contain_volatile_functions((Node *) subquery->targetList))
return false;//存在易变函数
return true;
}pull_up_subqueries_cleanup
/*
* pull_up_subqueries_cleanup
* Recursively fix up jointree after deletion of some subqueries.
*
* The jointree now contains some NULL subtrees, which we need to get rid of.
* In a FromExpr, just rebuild the child-node list with null entries deleted.
* In an inner JOIN, replace the JoinExpr node with a one-child FromExpr.
*/
static Node *
pull_up_subqueries_cleanup(Node *jtnode)
{
Assert(jtnode != NULL);
if (IsA(jtnode, RangeTblRef))
{
/* Nothing to do at leaf nodes. */
}
else if (IsA(jtnode, FromExpr))
{
FromExpr *f = (FromExpr *) jtnode;
List *newfrom = NIL;
ListCell *l;
foreach(l, f->fromlist)
{
Node *child = (Node *) lfirst(l);
if (child == NULL)
continue;
child = pull_up_subqueries_cleanup(child);
newfrom = lappend(newfrom, child);
}
f->fromlist = newfrom;
}
else if (IsA(jtnode, JoinExpr))
{
JoinExpr *j = (JoinExpr *) jtnode;
if (j->larg)
j->larg = pull_up_subqueries_cleanup(j->larg);
if (j->rarg)
j->rarg = pull_up_subqueries_cleanup(j->rarg);
if (j->larg == NULL)
{
Assert(j->jointype == JOIN_INNER);
Assert(j->rarg != NULL);
return (Node *) makeFromExpr(list_make1(j->rarg), j->quals);
}
else if (j->rarg == NULL)
{
Assert(j->jointype == JOIN_INNER);
return (Node *) makeFromExpr(list_make1(j->larg), j->quals);
}
}
else
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(jtnode));
return jtnode;
}三、跟踪分析
gdb跟踪分析:
(gdb) b pull_up_subqueries
Breakpoint 1 at 0x77d63b: file prepjointree.c, line 612.
(gdb) c
Continuing.
Breakpoint 1, pull_up_subqueries (root=0x1d092d0) at prepjointree.c:612
612 root->hasDeletedRTEs = false;
(gdb)
#输入参数,root参见上拉子链接中的说明
#进入pull_up_subqueries_recurse
(gdb) step
615 pull_up_subqueries_recurse(root, (Node *) root->parse->jointree,
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1d092a0, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=false) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
(gdb)
#输入参数:
#1.root,同pull_up_subqueries
#2.jtnode,Query查询树
#3/4/5.lowest_outer_join/lowest_nulling_outer_join/containing_appendrel均为NULL
#6.deletion_ok,false
...
(gdb) p *jtnode
$2 = {type = T_FromExpr}
#FromExpr,进入相应的分支
...
#递归调用pull_up_subqueries_recurse
(gdb)
763 lfirst(l) = pull_up_subqueries_recurse(root, lfirst(l),
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1c73078, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
#注意:这时候的jtnode类型为RangeTblRef
(gdb) n
682 int varno = ((RangeTblRef *) jtnode)->rtindex;
(gdb)
683 RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);
(gdb)
692 if (rte->rtekind == RTE_SUBQUERY &&
(gdb) p varno
$4 = 1
#rtable中第1个RTE是父查询的Relation(即t_dwxx),不是子查询
(gdb) p *rte
$5 = {type = T_RangeTblEntry, rtekind = RTE_RELATION, relid = 16394, relkind = 114 'r', tablesample = 0x0, subquery = 0x0,
security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0, funcordinality = false,
tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false, coltypes = 0x0,
coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x1c4fd58, eref = 0x1c72c98,
lateral = false, inh = true, inFromCl = true, requiredPerms = 2, checkAsUser = 0, selectedCols = 0x1d07698,
insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}
(gdb) n
712 if (rte->rtekind == RTE_SUBQUERY &&
(gdb)
722 if (rte->rtekind == RTE_VALUES &&
(gdb)
852 return jtnode;
(gdb)
...
#rtable中的第2个元素,类型为RTE_SUBQUERY
(gdb) step
pull_up_subqueries_recurse (root=0x1d092d0, jtnode=0x1d07358, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:680
680 if (IsA(jtnode, RangeTblRef))
(gdb) n
682 int varno = ((RangeTblRef *) jtnode)->rtindex;
(gdb)
(gdb) p *rte
$7 = {type = T_RangeTblEntry, rtekind = RTE_SUBQUERY, relid = 0, relkind = 0 '\000', tablesample = 0x0,
subquery = 0x1c72968, security_barrier = false, jointype = JOIN_INNER, joinaliasvars = 0x0, functions = 0x0,
funcordinality = false, tablefunc = 0x0, values_lists = 0x0, ctename = 0x0, ctelevelsup = 0, self_reference = false,
coltypes = 0x0, coltypmods = 0x0, colcollations = 0x0, enrname = 0x0, enrtuples = 0, alias = 0x1c50548, eref = 0x1d071a0,
lateral = false, inh = false, inFromCl = true, requiredPerms = 0, checkAsUser = 0, selectedCols = 0x0,
insertedCols = 0x0, updatedCols = 0x0, securityQuals = 0x0}
...
#进入pull_up_simple_subquery
697 return pull_up_simple_subquery(root, jtnode, rte,
(gdb) step
pull_up_simple_subquery (root=0x1d092d0, jtnode=0x1d07358, rte=0x1c72a78, lowest_outer_join=0x0,
lowest_nulling_outer_join=0x0, containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:874
874 Query *parse = root->parse;
...
1247 return (Node *) subquery->jointree;
(gdb)
1248 }
(gdb)
pull_up_subqueries_recurse (root=0x1d09838, jtnode=0x1c736e0, lowest_outer_join=0x0, lowest_nulling_outer_join=0x0,
containing_appendrel=0x0, deletion_ok=true) at prepjointree.c:853
853 }
(gdb)到此，关于“PostgreSQL查询语句分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注天达云网站，小编会继续努力为大家带来更多实用的文章！