Re: Confusing EXPLAIN output in case of inherited tables - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Re: Confusing EXPLAIN output in case of inherited tables |
| Date | |
| Msg-id | 3138.1348167308@sss.pgh.pa.us Whole thread Raw |
| In response to | Re: Confusing EXPLAIN output in case of inherited tables (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: Confusing EXPLAIN output in case of inherited tables
|
| List | pgsql-hackers |
I wrote: > I got interested in this problem again now that we have a user complaint > about it (bug #7553). > ... > As far as the relation-rename problem goes, I propose that what we > should do is have ruleutils.c invent nonconflicting fake aliases for > each RTE in the query tree. This would allow getting rid of some of the > dubious heuristics in get_variable: it should just print the chosen > alias and be done. (It still has to do something different for unnamed > joins, but we can leave that part alone I think.) Attached is a draft patch for this. It fixes the view-dumping problems that I exhibited in http://archives.postgresql.org/message-id/29791.1327718297@sss.pgh.pa.us as well as nicely cleaning up Ashutosh's original complaint at http://archives.postgresql.org/pgsql-hackers/2012-01/msg00505.php There are quite a few more changes in the regression-test plan printouts than was originally discussed, but they seem to be generally for the better IMO: for instance there is no longer any problem with different RTEs being printed with identical names in EXPLAIN. One thing I found while working on this is that some of the inconsistency is not really EXPLAIN's fault but the planner's: the planner does not take any trouble to avoid duplicate RTE aliases when it manufactures additional RTEs, which it does in at least two places (inheritance expansion and min/max aggregate optimization). In my first version of the patch I was getting EXPLAIN printouts like this for inheritance append-plans: Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append ! -> Index Scan using patest0i on patest0 patest0_1 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest2i on patest2 Index Cond: (id = int4_tbl.f1) That happened because the original inheritance-root RTE got the "patest0" alias, and then the inheritance-child RTE for the parent relation got stuck with "patest0_1". This isn't terribly desirable since the inheritance-root RTE isn't actually visible anywhere in the EXPLAIN printout, so giving it the preferred name isn't ideal. In the attached I've hacked around this by causing the planner to assign new aliases to RTEs that it replaces in this way (see planagg.c and prepunion.c diffs). This seems like a bit of a kluge, but it doesn't take much code. An alternative that I'm considering is to have EXPLAIN make a pre-pass over the plan tree to identify which RTEs will actually be referenced, and then consider only those RTEs while assigning aliases. This would be a great deal more code though, and code which would require maintenance every time we add plan node types etc. So I'm not sure it's really a better answer. Thoughts? regards, tom lane diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 1e8f618a3476b9e171658900f92532b87b3cde97..ab39c197c07f44c486104fbfe0f22c513413a716 100644 *** a/src/backend/commands/explain.c --- b/src/backend/commands/explain.c *************** ExplainPrintPlan(ExplainState *es, Query *** 542,547 **** --- 542,548 ---- Assert(queryDesc->plannedstmt != NULL); es->pstmt = queryDesc->plannedstmt; es->rtable = queryDesc->plannedstmt->rtable; + es->rtable_names = select_rtable_names_for_explain(es->rtable); ExplainNode(queryDesc->planstate, NIL, NULL, NULL, es); } *************** show_plan_tlist(PlanState *planstate, Li *** 1440,1446 **** /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable); useprefix = list_length(es->rtable) > 1; /* Deparse each result column (we now include resjunk ones) */ --- 1441,1448 ---- /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable, ! es->rtable_names); useprefix = list_length(es->rtable) > 1; /* Deparse each result column (we now include resjunk ones) */ *************** show_expression(Node *node, const char * *** 1471,1477 **** /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable); /* Deparse the expression */ exprstr = deparse_expression(node, context, useprefix, false); --- 1473,1480 ---- /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable, ! es->rtable_names); /* Deparse the expression */ exprstr = deparse_expression(node, context, useprefix, false); *************** show_sort_keys_common(PlanState *plansta *** 1573,1579 **** /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable); useprefix = (list_length(es->rtable) > 1 || es->verbose); for (keyno = 0; keyno < nkeys; keyno++) --- 1576,1583 ---- /* Set up deparsing context */ context = deparse_context_for_planstate((Node *) planstate, ancestors, ! es->rtable, ! es->rtable_names); useprefix = (list_length(es->rtable) > 1 || es->verbose); for (keyno = 0; keyno < nkeys; keyno++) *************** ExplainTargetRel(Plan *plan, Index rti, *** 1813,1820 **** --- 1817,1826 ---- char *namespace = NULL; const char *objecttag = NULL; RangeTblEntry *rte; + char *refname; rte = rt_fetch(rti, es->rtable); + refname = (char *) list_nth(es->rtable_names, rti - 1); switch (nodeTag(plan)) { *************** ExplainTargetRel(Plan *plan, Index rti, *** 1887,1896 **** quote_identifier(objectname)); else if (objectname != NULL) appendStringInfo(es->str, " %s", quote_identifier(objectname)); ! if (objectname == NULL || ! strcmp(rte->eref->aliasname, objectname) != 0) ! appendStringInfo(es->str, " %s", ! quote_identifier(rte->eref->aliasname)); } else { --- 1893,1901 ---- quote_identifier(objectname)); else if (objectname != NULL) appendStringInfo(es->str, " %s", quote_identifier(objectname)); ! if (refname != NULL && ! (objectname == NULL || strcmp(refname, objectname) != 0)) ! appendStringInfo(es->str, " %s", quote_identifier(refname)); } else { *************** ExplainTargetRel(Plan *plan, Index rti, *** 1898,1904 **** ExplainPropertyText(objecttag, objectname, es); if (namespace != NULL) ExplainPropertyText("Schema", namespace, es); ! ExplainPropertyText("Alias", rte->eref->aliasname, es); } } --- 1903,1910 ---- ExplainPropertyText(objecttag, objectname, es); if (namespace != NULL) ExplainPropertyText("Schema", namespace, es); ! if (refname != NULL) ! ExplainPropertyText("Alias", refname, es); } } diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c index 55a5ed7b4c6320886e1e0d36d7a6aa857efc25e2..61211d7c30001befc202f190f14944b97755c4e2 100644 *** a/src/backend/optimizer/plan/planagg.c --- b/src/backend/optimizer/plan/planagg.c *************** optimize_minmax_aggregates(PlannerInfo * *** 268,273 **** --- 268,291 ---- (void *) root); /* + * Modify all the original RTEs to have aliases different from those in + * the copied subplan(s). This allows EXPLAIN's heuristics for alias + * assignment to work nicely. (Otherwise, EXPLAIN would see the original + * RTE first and preferentially give it the base relation name, which we + * don't want since the original RTE doesn't have any actual role in the + * finished plan.) + */ + foreach(lc, parse->rtable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc); + char *aliasname; + + aliasname = (char *) palloc(strlen(rte->eref->aliasname) + 10); + sprintf(aliasname, "%s original", rte->eref->aliasname); + rte->alias = makeAlias(aliasname, NIL); + } + + /* * Generate the output plan --- basically just a Result */ plan = (Plan *) make_result(root, tlist, hqual, NULL); diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index 9e154e1120bed7cddd9e5827d5a5b566210c86af..0e1dad5e3a4451ab60fb5fe3fafcb75bc17780b3 100644 *** a/src/backend/optimizer/prep/prepunion.c --- b/src/backend/optimizer/prep/prepunion.c *************** expand_inherited_rtentry(PlannerInfo *ro *** 1234,1239 **** --- 1234,1240 ---- PlanRowMark *oldrc; Relation oldrelation; LOCKMODE lockmode; + char *aliasname; List *inhOIDs; List *appinfos; ListCell *l; *************** expand_inherited_rtentry(PlannerInfo *ro *** 1415,1420 **** --- 1416,1433 ---- /* Otherwise, OK to add to root->append_rel_list */ root->append_rel_list = list_concat(root->append_rel_list, appinfos); + + /* + * Force the parent RTE to have an alias that marks it as a parent. This + * is convenient for planner debugging purposes and also allows EXPLAIN's + * heuristics for alias assignment to work nicely. (Otherwise, EXPLAIN + * would see the parent RTE first and preferentially give it the base + * relation name, which we don't want since the parent RTE doesn't have + * any actual role in the finished plan.) + */ + aliasname = (char *) palloc(strlen(rte->eref->aliasname) + 10); + sprintf(aliasname, "%s parent", rte->eref->aliasname); + rte->alias = makeAlias(aliasname, NIL); } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 08396431384a8f8581492304c8c916c41e4b9516..aef2f5e4c23dcc134a52d4ab5aaa88b020746bee 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** typedef struct *** 104,110 **** * the current context's namespaces list. * * The rangetable is the list of actual RTEs from the query tree, and the ! * cte list is the list of actual CTEs. * * When deparsing plan trees, there is always just a single item in the * deparse_namespace list (since a plan tree never contains Vars with --- 104,112 ---- * the current context's namespaces list. * * The rangetable is the list of actual RTEs from the query tree, and the ! * cte list is the list of actual CTEs. rtable_names holds the alias name ! * to be used for each RTE (either a C string, or NULL for nameless RTEs ! * such as unnamed joins). * * When deparsing plan trees, there is always just a single item in the * deparse_namespace list (since a plan tree never contains Vars with *************** typedef struct *** 119,124 **** --- 121,127 ---- typedef struct { List *rtable; /* List of RangeTblEntry nodes */ + List *rtable_names; /* Parallel list of names for RTEs */ List *ctes; /* List of CommonTableExpr nodes */ /* Remaining fields are used only when deparsing a Plan tree: */ PlanState *planstate; /* immediate parent of current expression */ *************** static text *pg_get_expr_worker(text *ex *** 172,177 **** --- 175,184 ---- static int print_function_arguments(StringInfo buf, HeapTuple proctup, bool print_table_args, bool print_defaults); static void print_function_rettype(StringInfo buf, HeapTuple proctup); + static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces); + static bool refname_is_unique(char *refname, deparse_namespace *dpns, + List *parent_namespaces); + static char *get_rtable_name(int rtindex, deparse_context *context); static void set_deparse_planstate(deparse_namespace *dpns, PlanState *ps); static void push_child_plan(deparse_namespace *dpns, PlanState *ps, deparse_namespace *save_dpns); *************** static void get_rule_windowspec(WindowCl *** 212,219 **** deparse_context *context); static char *get_variable(Var *var, int levelsup, bool istoplevel, deparse_context *context); - static RangeTblEntry *find_rte_by_refname(const char *refname, - deparse_context *context); static Node *find_param_referent(Param *param, deparse_context *context, deparse_namespace **dpns_p, ListCell **ancestor_cell_p); static void get_parameter(Param *param, deparse_context *context); --- 219,224 ---- *************** pg_get_triggerdef_worker(Oid trigid, boo *** 676,682 **** oldrte->rtekind = RTE_RELATION; oldrte->relid = trigrec->tgrelid; oldrte->relkind = relkind; ! oldrte->eref = makeAlias("old", NIL); oldrte->lateral = false; oldrte->inh = false; oldrte->inFromCl = true; --- 681,688 ---- oldrte->rtekind = RTE_RELATION; oldrte->relid = trigrec->tgrelid; oldrte->relkind = relkind; ! oldrte->alias = makeAlias("old", NIL); ! oldrte->eref = oldrte->alias; oldrte->lateral = false; oldrte->inh = false; oldrte->inFromCl = true; *************** pg_get_triggerdef_worker(Oid trigid, boo *** 685,691 **** newrte->rtekind = RTE_RELATION; newrte->relid = trigrec->tgrelid; newrte->relkind = relkind; ! newrte->eref = makeAlias("new", NIL); newrte->lateral = false; newrte->inh = false; newrte->inFromCl = true; --- 691,698 ---- newrte->rtekind = RTE_RELATION; newrte->relid = trigrec->tgrelid; newrte->relkind = relkind; ! newrte->alias = makeAlias("new", NIL); ! newrte->eref = newrte->alias; newrte->lateral = false; newrte->inh = false; newrte->inFromCl = true; *************** pg_get_triggerdef_worker(Oid trigid, boo *** 694,699 **** --- 701,707 ---- memset(&dpns, 0, sizeof(dpns)); dpns.rtable = list_make2(oldrte, newrte); dpns.ctes = NIL; + set_rtable_names(&dpns, NIL); /* Set up context with one-deep namespace stack */ context.buf = &buf; *************** deparse_context_for(const char *aliasnam *** 2176,2182 **** rte->rtekind = RTE_RELATION; rte->relid = relid; rte->relkind = RELKIND_RELATION; /* no need for exactness here */ ! rte->eref = makeAlias(aliasname, NIL); rte->lateral = false; rte->inh = false; rte->inFromCl = true; --- 2184,2191 ---- rte->rtekind = RTE_RELATION; rte->relid = relid; rte->relkind = RELKIND_RELATION; /* no need for exactness here */ ! rte->alias = makeAlias(aliasname, NIL); ! rte->eref = rte->alias; rte->lateral = false; rte->inh = false; rte->inFromCl = true; *************** deparse_context_for(const char *aliasnam *** 2184,2189 **** --- 2193,2199 ---- /* Build one-element rtable */ dpns->rtable = list_make1(rte); dpns->ctes = NIL; + set_rtable_names(dpns, NIL); /* Return a one-deep namespace stack */ return list_make1(dpns); *************** deparse_context_for(const char *aliasnam *** 2209,2221 **** * most-closely-nested first. This is needed to resolve PARAM_EXEC Params. * Note we assume that all the PlanStates share the same rtable. * ! * The plan's rangetable list must also be passed. We actually prefer to use ! * the rangetable to resolve simple Vars, but the plan inputs are necessary ! * for Vars with special varnos. */ List * deparse_context_for_planstate(Node *planstate, List *ancestors, ! List *rtable) { deparse_namespace *dpns; --- 2219,2232 ---- * most-closely-nested first. This is needed to resolve PARAM_EXEC Params. * Note we assume that all the PlanStates share the same rtable. * ! * The plan's rangetable list must also be passed, along with the per-RTE ! * alias names assigned by a previous call to select_rtable_names_for_explain. ! * (We use the rangetable to resolve simple Vars, but the plan inputs are ! * necessary for Vars with special varnos.) */ List * deparse_context_for_planstate(Node *planstate, List *ancestors, ! List *rtable, List *rtable_names) { deparse_namespace *dpns; *************** deparse_context_for_planstate(Node *plan *** 2223,2228 **** --- 2234,2240 ---- /* Initialize fields that stay the same across the whole plan tree */ dpns->rtable = rtable; + dpns->rtable_names = rtable_names; dpns->ctes = NIL; /* Set our attention on the specific plan node passed in */ *************** deparse_context_for_planstate(Node *plan *** 2234,2239 **** --- 2246,2377 ---- } /* + * select_rtable_names_for_explain - Select RTE aliases for EXPLAIN + * + * Determine the aliases we'll use during an EXPLAIN operation. This is + * just a frontend to set_rtable_names. We have to expose this to EXPLAIN + * because EXPLAIN needs to know the right alias names to print. + */ + List * + select_rtable_names_for_explain(List *rtable) + { + deparse_namespace dpns; + + memset(&dpns, 0, sizeof(dpns)); + dpns.rtable = rtable; + dpns.ctes = NIL; + set_rtable_names(&dpns, NIL); + + return dpns.rtable_names; + } + + /* + * set_rtable_names: select RTE aliases to be used in printing variables + * + * We fill in dpns->rtable_names with a list of names that is one-for-one with + * the already-filled dpns->rtable list. Each RTE name is unique among those + * in the new namespace plus any ancestor namespaces listed in + * parent_namespaces. + */ + static void + set_rtable_names(deparse_namespace *dpns, List *parent_namespaces) + { + ListCell *lc; + + dpns->rtable_names = NIL; + foreach(lc, dpns->rtable) + { + RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc); + char *refname; + + if (rte->alias) + { + /* If RTE has a user-defined alias, prefer that */ + refname = rte->alias->aliasname; + } + else if (rte->rtekind == RTE_RELATION) + { + /* Use the current actual name of the relation */ + refname = get_rel_name(rte->relid); + } + else if (rte->rtekind == RTE_JOIN) + { + /* Unnamed join has no refname */ + refname = NULL; + } + else + { + /* Otherwise use whatever the parser assigned */ + refname = rte->eref->aliasname; + } + + /* + * If the selected name isn't unique, append digits to make it so + */ + if (refname && + !refname_is_unique(refname, dpns, parent_namespaces)) + { + char *modname = (char *) palloc(strlen(refname) + 32); + int i = 0; + + do + { + sprintf(modname, "%s_%d", refname, ++i); + } while (!refname_is_unique(modname, dpns, parent_namespaces)); + refname = modname; + } + + dpns->rtable_names = lappend(dpns->rtable_names, refname); + } + } + + /* + * refname_is_unique: is refname distinct from all already-chosen RTE names? + */ + static bool + refname_is_unique(char *refname, deparse_namespace *dpns, + List *parent_namespaces) + { + ListCell *lc; + + foreach(lc, dpns->rtable_names) + { + char *oldname = (char *) lfirst(lc); + + if (oldname && strcmp(oldname, refname) == 0) + return false; + } + foreach(lc, parent_namespaces) + { + deparse_namespace *olddpns = (deparse_namespace *) lfirst(lc); + ListCell *lc2; + + foreach(lc2, olddpns->rtable_names) + { + char *oldname = (char *) lfirst(lc2); + + if (oldname && strcmp(oldname, refname) == 0) + return false; + } + } + return true; + } + + /* + * get_rtable_name: convenience function to get a previously assigned RTE alias + * + * The RTE must belong to the topmost namespace level in "context". + */ + static char * + get_rtable_name(int rtindex, deparse_context *context) + { + deparse_namespace *dpns = (deparse_namespace *) linitial(context->namespaces); + + Assert(rtindex > 0 && rtindex <= list_length(dpns->rtable_names)); + return (char *) list_nth(dpns->rtable_names, rtindex - 1); + } + + /* * set_deparse_planstate: set up deparse_namespace to parse subexpressions * of a given PlanState node * *************** make_ruledef(StringInfo buf, HeapTuple r *** 2534,2539 **** --- 2672,2678 ---- memset(&dpns, 0, sizeof(dpns)); dpns.rtable = query->rtable; dpns.ctes = query->cteList; + set_rtable_names(&dpns, NIL); get_rule_expr(qual, &context, false); } *************** get_query_def(Query *query, StringInfo b *** 2680,2685 **** --- 2819,2825 ---- memset(&dpns, 0, sizeof(dpns)); dpns.rtable = query->rtable; dpns.ctes = query->cteList; + set_rtable_names(&dpns, parentnamespace); switch (query->commandType) { *************** get_select_query_def(Query *query, depar *** 2899,2905 **** foreach(l, query->rowMarks) { RowMarkClause *rc = (RowMarkClause *) lfirst(l); - RangeTblEntry *rte = rt_fetch(rc->rti, query->rtable); /* don't print implicit clauses */ if (rc->pushedDown) --- 3039,3044 ---- *************** get_select_query_def(Query *query, depar *** 2912,2918 **** appendContextKeyword(context, " FOR SHARE", -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); appendStringInfo(buf, " OF %s", ! quote_identifier(rte->eref->aliasname)); if (rc->noWait) appendStringInfo(buf, " NOWAIT"); } --- 3051,3058 ---- appendContextKeyword(context, " FOR SHARE", -PRETTYINDENT_STD, PRETTYINDENT_STD, 0); appendStringInfo(buf, " OF %s", ! quote_identifier(get_rtable_name(rc->rti, ! context))); if (rc->noWait) appendStringInfo(buf, " NOWAIT"); } *************** get_variable(Var *var, int levelsup, boo *** 3854,3860 **** AttrNumber attnum; int netlevelsup; deparse_namespace *dpns; - char *schemaname; char *refname; char *attname; --- 3994,3999 ---- *************** get_variable(Var *var, int levelsup, boo *** 3874,3879 **** --- 4013,4019 ---- if (var->varno >= 1 && var->varno <= list_length(dpns->rtable)) { rte = rt_fetch(var->varno, dpns->rtable); + refname = (char *) list_nth(dpns->rtable_names, var->varno - 1); attnum = var->varattno; } else if (var->varno == OUTER_VAR && dpns->outer_tlist) *************** get_variable(Var *var, int levelsup, boo *** 3993,4053 **** return NULL; } ! /* Identify names to use */ ! schemaname = NULL; /* default assumptions */ ! refname = rte->eref->aliasname; ! ! /* Exceptions occur only if the RTE is alias-less */ ! if (rte->alias == NULL) { ! if (rte->rtekind == RTE_RELATION) ! { ! /* ! * It's possible that use of the bare refname would find another ! * more-closely-nested RTE, or be ambiguous, in which case we need ! * to specify the schemaname to avoid these errors. ! */ ! if (find_rte_by_refname(rte->eref->aliasname, context) != rte) ! schemaname = get_namespace_name(get_rel_namespace(rte->relid)); ! } ! else if (rte->rtekind == RTE_JOIN) { ! /* ! * If it's an unnamed join, look at the expansion of the alias ! * variable. If it's a simple reference to one of the input vars ! * then recursively print the name of that var, instead. (This ! * allows correct decompiling of cases where there are identically ! * named columns on both sides of the join.) When it's not a ! * simple reference, we have to just print the unqualified ! * variable name (this can only happen with columns that were ! * merged by USING or NATURAL clauses). ! * ! * This wouldn't work in decompiling plan trees, because we don't ! * store joinaliasvars lists after planning; but a plan tree ! * should never contain a join alias variable. ! */ ! if (rte->joinaliasvars == NIL) ! elog(ERROR, "cannot decompile join alias var in plan tree"); ! if (attnum > 0) ! { ! Var *aliasvar; ! aliasvar = (Var *) list_nth(rte->joinaliasvars, attnum - 1); ! if (IsA(aliasvar, Var)) ! { ! return get_variable(aliasvar, var->varlevelsup + levelsup, ! istoplevel, context); ! } } - - /* - * Unnamed join has neither schemaname nor refname. (Note: since - * it's unnamed, there is no way the user could have referenced it - * to create a whole-row Var for it. So we don't have to cover - * that case below.) - */ - refname = NULL; } } if (attnum == InvalidAttrNumber) --- 4133,4173 ---- return NULL; } ! /* ! * If it's an unnamed join, look at the expansion of the alias variable. ! * If it's a simple reference to one of the input vars, then recursively ! * print the name of that var instead. (This allows correct decompiling ! * of cases where there are identically named columns on both sides of the ! * join.) When it's not a simple reference, we have to just print the ! * unqualified variable name (this can only happen with columns that were ! * merged by USING or NATURAL clauses). ! * ! * This wouldn't work in decompiling plan trees, because we don't store ! * joinaliasvars lists after planning; but a plan tree should never ! * contain a join alias variable. ! */ ! if (rte->rtekind == RTE_JOIN && rte->alias == NULL) { ! if (rte->joinaliasvars == NIL) ! elog(ERROR, "cannot decompile join alias var in plan tree"); ! if (attnum > 0) { ! Var *aliasvar; ! aliasvar = (Var *) list_nth(rte->joinaliasvars, attnum - 1); ! if (IsA(aliasvar, Var)) ! { ! return get_variable(aliasvar, var->varlevelsup + levelsup, ! istoplevel, context); } } + + /* + * Unnamed join has no refname. (Note: since it's unnamed, there is + * no way the user could have referenced it to create a whole-row Var + * for it. So we don't have to cover that case below.) + */ + Assert(refname == NULL); } if (attnum == InvalidAttrNumber) *************** get_variable(Var *var, int levelsup, boo *** 4057,4065 **** if (refname && (context->varprefix || attname == NULL)) { - if (schemaname) - appendStringInfo(buf, "%s.", - quote_identifier(schemaname)); appendStringInfoString(buf, quote_identifier(refname)); appendStringInfoChar(buf, '.'); } --- 4177,4182 ---- *************** get_name_for_var_field(Var *var, int fie *** 4289,4294 **** --- 4406,4412 ---- memset(&mydpns, 0, sizeof(mydpns)); mydpns.rtable = rte->subquery->rtable; mydpns.ctes = rte->subquery->cteList; + set_rtable_names(&mydpns, context->namespaces); context->namespaces = lcons(&mydpns, context->namespaces); *************** get_name_for_var_field(Var *var, int fie *** 4406,4411 **** --- 4524,4530 ---- memset(&mydpns, 0, sizeof(mydpns)); mydpns.rtable = ctequery->rtable; mydpns.ctes = ctequery->cteList; + set_rtable_names(&mydpns, context->namespaces); new_nslist = list_copy_tail(context->namespaces, ctelevelsup); *************** get_name_for_var_field(Var *var, int fie *** 4467,4513 **** return NameStr(tupleDesc->attrs[fieldno - 1]->attname); } - - /* - * find_rte_by_refname - look up an RTE by refname in a deparse context - * - * Returns NULL if there is no matching RTE or the refname is ambiguous. - * - * NOTE: this code is not really correct since it does not take account of - * the fact that not all the RTEs in a rangetable may be visible from the - * point where a Var reference appears. For the purposes we need, however, - * the only consequence of a false match is that we might stick a schema - * qualifier on a Var that doesn't really need it. So it seems close - * enough. - */ - static RangeTblEntry * - find_rte_by_refname(const char *refname, deparse_context *context) - { - RangeTblEntry *result = NULL; - ListCell *nslist; - - foreach(nslist, context->namespaces) - { - deparse_namespace *dpns = (deparse_namespace *) lfirst(nslist); - ListCell *rtlist; - - foreach(rtlist, dpns->rtable) - { - RangeTblEntry *rte = (RangeTblEntry *) lfirst(rtlist); - - if (strcmp(rte->eref->aliasname, refname) == 0) - { - if (result) - return NULL; /* it's ambiguous */ - result = rte; - } - } - if (result) - break; - } - return result; - } - /* * Try to find the referenced expression for a PARAM_EXEC Param that might * reference a parameter supplied by an upper NestLoop or SubPlan plan node. --- 4586,4591 ---- *************** get_from_clause_item(Node *jtnode, Query *** 6649,6654 **** --- 6727,6733 ---- { int varno = ((RangeTblRef *) jtnode)->rtindex; RangeTblEntry *rte = rt_fetch(varno, query->rtable); + char *refname = get_rtable_name(varno, context); bool gavealias = false; if (rte->lateral) *************** get_from_clause_item(Node *jtnode, Query *** 6688,6719 **** if (rte->alias != NULL) { ! appendStringInfo(buf, " %s", ! quote_identifier(rte->alias->aliasname)); gavealias = true; } ! else if (rte->rtekind == RTE_RELATION && ! strcmp(rte->eref->aliasname, get_relation_name(rte->relid)) != 0) { /* ! * Apparently the rel has been renamed since the rule was made. ! * Emit a fake alias clause so that variable references will still ! * work. This is not a 100% solution but should work in most ! * reasonable situations. */ ! appendStringInfo(buf, " %s", ! quote_identifier(rte->eref->aliasname)); ! gavealias = true; } else if (rte->rtekind == RTE_FUNCTION) { /* ! * For a function RTE, always give an alias. This covers possible * renaming of the function and/or instability of the * FigureColname rules for things that aren't simple functions. */ ! appendStringInfo(buf, " %s", ! quote_identifier(rte->eref->aliasname)); gavealias = true; } --- 6767,6797 ---- if (rte->alias != NULL) { ! /* Always print alias if user provided one */ ! appendStringInfo(buf, " %s", quote_identifier(refname)); gavealias = true; } ! else if (rte->rtekind == RTE_RELATION) { /* ! * No need to print alias if it's same as relation name (this ! * would normally be the case, but not if set_rtable_names had to ! * resolve a conflict). */ ! if (strcmp(refname, get_relation_name(rte->relid)) != 0) ! { ! appendStringInfo(buf, " %s", quote_identifier(refname)); ! gavealias = true; ! } } else if (rte->rtekind == RTE_FUNCTION) { /* ! * For a function RTE, always print alias. This covers possible * renaming of the function and/or instability of the * FigureColname rules for things that aren't simple functions. */ ! appendStringInfo(buf, " %s", quote_identifier(refname)); gavealias = true; } diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index c4215da1e329102df3289f9c88a732f51f90503e..4227f4e59c3d07cee8d0fc95660010705ce5e13b 100644 *** a/src/include/commands/explain.h --- b/src/include/commands/explain.h *************** typedef struct ExplainState *** 37,42 **** --- 37,43 ---- /* other states */ PlannedStmt *pstmt; /* top of plan */ List *rtable; /* range table */ + List *rtable_names; /* alias names for RTEs */ int indent; /* current indentation level */ List *grouping_stack; /* format-specific grouping state */ } ExplainState; diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index c9c665dae09c08a726c04ed58591d63e3b1b3347..2ff9460d0c7d58b0ef922490823cd1dd34fc192d 100644 *** a/src/include/utils/builtins.h --- b/src/include/utils/builtins.h *************** extern char *deparse_expression(Node *ex *** 654,660 **** bool forceprefix, bool showimplicit); extern List *deparse_context_for(const char *aliasname, Oid relid); extern List *deparse_context_for_planstate(Node *planstate, List *ancestors, ! List *rtable); extern const char *quote_identifier(const char *ident); extern char *quote_qualified_identifier(const char *qualifier, const char *ident); --- 654,661 ---- bool forceprefix, bool showimplicit); extern List *deparse_context_for(const char *aliasname, Oid relid); extern List *deparse_context_for_planstate(Node *planstate, List *ancestors, ! List *rtable, List *rtable_names); ! extern List *select_rtable_names_for_explain(List *rtable); extern const char *quote_identifier(const char *ident); extern char *quote_qualified_identifier(const char *qualifier, const char *ident); diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out index 6ca73a0ed73fc9c2fa18701f17fa34b7f12db131..7286f1aa446ffc0f3b0d0527daf2a5bbba34713e 100644 *** a/src/test/regress/expected/aggregates.out --- b/src/test/regress/expected/aggregates.out *************** insert into minmaxtest2 values(15), (16) *** 705,736 **** insert into minmaxtest3 values(17), (18); explain (costs off) select min(f1), max(f1) from minmaxtest; ! QUERY PLAN ! ------------------------------------------------------------------------------------------- Result InitPlan 1 (returns $0) -> Limit -> Merge Append ! Sort Key: public.minmaxtest.f1 -> Index Only Scan using minmaxtesti on minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest1i on minmaxtest1 minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest3i on minmaxtest3 minmaxtest Index Cond: (f1 IS NOT NULL) InitPlan 2 (returns $1) -> Limit -> Merge Append ! Sort Key: public.minmaxtest.f1 ! -> Index Only Scan Backward using minmaxtesti on minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest Index Cond: (f1 IS NOT NULL) (25 rows) --- 705,736 ---- insert into minmaxtest3 values(17), (18); explain (costs off) select min(f1), max(f1) from minmaxtest; ! QUERY PLAN ! ---------------------------------------------------------------------------------------------- Result InitPlan 1 (returns $0) -> Limit -> Merge Append ! Sort Key: minmaxtest.f1 -> Index Only Scan using minmaxtesti on minmaxtest Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest1i on minmaxtest1 Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest2i on minmaxtest2 Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest3i on minmaxtest3 Index Cond: (f1 IS NOT NULL) InitPlan 2 (returns $1) -> Limit -> Merge Append ! Sort Key: minmaxtest_1.f1 ! -> Index Only Scan Backward using minmaxtesti on minmaxtest minmaxtest_1 Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest1i on minmaxtest1 minmaxtest1_1 Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan using minmaxtest2i on minmaxtest2 minmaxtest2_1 Index Cond: (f1 IS NOT NULL) ! -> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1 Index Cond: (f1 IS NOT NULL) (25 rows) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 453a3894b202873b31dada5727e7e09ae3f2f142..c22d74c7b562e86d6e839b4ab3a87d3cf7a29149 100644 *** a/src/test/regress/expected/alter_table.out --- b/src/test/regress/expected/alter_table.out *************** explain (costs off) select * from nv_par *** 391,399 **** -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2010 nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2011 nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) (8 rows) --- 391,399 ---- -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2010 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2011 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) (8 rows) *************** explain (costs off) select * from nv_par *** 405,413 **** -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2010 nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2011 nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) (8 rows) --- 405,413 ---- -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2010 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) ! -> Seq Scan on nv_child_2011 Filter: ((d >= '08-01-2011'::date) AND (d <= '08-31-2011'::date)) (8 rows) *************** explain (costs off) select * from nv_par *** 418,428 **** -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2010 nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2011 nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2009 nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) (10 rows) --- 418,428 ---- -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2010 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2011 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2009 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) (10 rows) *************** explain (costs off) select * from nv_par *** 435,443 **** -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2010 nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2009 nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) (8 rows) --- 435,443 ---- -> Append -> Seq Scan on nv_parent Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2010 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) ! -> Seq Scan on nv_child_2009 Filter: ((d >= '08-01-2009'::date) AND (d <= '08-31-2009'::date)) (8 rows) diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 25adcd2346118853ef57943bd22d9b80667ca560..906a928b0c0a44ff1dcac5b3b9803cd508ec7533 100644 *** a/src/test/regress/expected/inherit.out --- b/src/test/regress/expected/inherit.out *************** analyze patest1; *** 1105,1121 **** analyze patest2; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! ---------------------------------------------------------- Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 patest0 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest2i on patest2 patest0 Index Cond: (id = int4_tbl.f1) (10 rows) --- 1105,1121 ---- analyze patest2; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! -------------------------------------------------- Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest2i on patest2 Index Cond: (id = int4_tbl.f1) (10 rows) *************** select * from patest0 join (select f1 fr *** 1130,1146 **** drop index patest2i; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! ---------------------------------------------------------- Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 patest0 Index Cond: (id = int4_tbl.f1) ! -> Seq Scan on patest2 patest0 Filter: (int4_tbl.f1 = id) (10 rows) --- 1130,1146 ---- drop index patest2i; explain (costs off) select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; ! QUERY PLAN ! -------------------------------------------------- Nested Loop -> Limit -> Seq Scan on int4_tbl -> Append -> Index Scan using patest0i on patest0 Index Cond: (id = int4_tbl.f1) ! -> Index Scan using patest1i on patest1 Index Cond: (id = int4_tbl.f1) ! -> Seq Scan on patest2 Filter: (int4_tbl.f1 = id) (10 rows) *************** insert into matest3 (name) values ('Test *** 1178,1199 **** insert into matest3 (name) values ('Test 6'); set enable_indexscan = off; -- force use of seqscan/sort, so no merge explain (verbose, costs off) select * from matest0 order by 1-id; ! QUERY PLAN ! --------------------------------------------------------------------------------- Sort ! Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) ! Sort Key: ((1 - public.matest0.id)) -> Result ! Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) -> Append -> Seq Scan on public.matest0 ! Output: public.matest0.id, public.matest0.name ! -> Seq Scan on public.matest1 matest0 ! Output: public.matest0.id, public.matest0.name ! -> Seq Scan on public.matest2 matest0 ! Output: public.matest0.id, public.matest0.name ! -> Seq Scan on public.matest3 matest0 ! Output: public.matest0.id, public.matest0.name (14 rows) select * from matest0 order by 1-id; --- 1178,1199 ---- insert into matest3 (name) values ('Test 6'); set enable_indexscan = off; -- force use of seqscan/sort, so no merge explain (verbose, costs off) select * from matest0 order by 1-id; ! QUERY PLAN ! ------------------------------------------------------------ Sort ! Output: matest0.id, matest0.name, ((1 - matest0.id)) ! Sort Key: ((1 - matest0.id)) -> Result ! Output: matest0.id, matest0.name, (1 - matest0.id) -> Append -> Seq Scan on public.matest0 ! Output: matest0.id, matest0.name ! -> Seq Scan on public.matest1 ! Output: matest1.id, matest1.name ! -> Seq Scan on public.matest2 ! Output: matest2.id, matest2.name ! -> Seq Scan on public.matest3 ! Output: matest3.id, matest3.name (14 rows) select * from matest0 order by 1-id; *************** select * from matest0 order by 1-id; *** 1210,1232 **** reset enable_indexscan; set enable_seqscan = off; -- plan with fewest seqscans should be merge explain (verbose, costs off) select * from matest0 order by 1-id; ! QUERY PLAN ! --------------------------------------------------------------------------------------------- Result ! Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) -> Merge Append ! Sort Key: ((1 - public.matest0.id)) -> Index Scan using matest0i on public.matest0 ! Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) ! -> Index Scan using matest1i on public.matest1 matest0 ! Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) -> Sort ! Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id)) ! Sort Key: ((1 - public.matest0.id)) ! -> Seq Scan on public.matest2 matest0 ! Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) ! -> Index Scan using matest3i on public.matest3 matest0 ! Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id) (15 rows) select * from matest0 order by 1-id; --- 1210,1232 ---- reset enable_indexscan; set enable_seqscan = off; -- plan with fewest seqscans should be merge explain (verbose, costs off) select * from matest0 order by 1-id; ! QUERY PLAN ! ------------------------------------------------------------------------ Result ! Output: matest0.id, matest0.name, ((1 - matest0.id)) -> Merge Append ! Sort Key: ((1 - matest0.id)) -> Index Scan using matest0i on public.matest0 ! Output: matest0.id, matest0.name, (1 - matest0.id) ! -> Index Scan using matest1i on public.matest1 ! Output: matest1.id, matest1.name, (1 - matest1.id) -> Sort ! Output: matest2.id, matest2.name, ((1 - matest2.id)) ! Sort Key: ((1 - matest2.id)) ! -> Seq Scan on public.matest2 ! Output: matest2.id, matest2.name, (1 - matest2.id) ! -> Index Scan using matest3i on public.matest3 ! Output: matest3.id, matest3.name, (1 - matest3.id) (15 rows) select * from matest0 order by 1-id; *************** SELECT thousand, tenthous FROM tenk1 *** 1258,1272 **** UNION ALL SELECT thousand, thousand FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ----------------------------------------------------------------------- Result -> Merge Append ! Sort Key: public.tenk1.thousand, public.tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort ! Sort Key: public.tenk1.thousand, public.tenk1.thousand ! -> Index Only Scan using tenk1_thous_tenthous on tenk1 (7 rows) explain (costs off) --- 1258,1272 ---- UNION ALL SELECT thousand, thousand FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ------------------------------------------------------------------------------- Result -> Merge Append ! Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort ! Sort Key: tenk1_1.thousand, tenk1_1.thousand ! -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 (7 rows) explain (costs off) *************** SELECT thousand, tenthous, thousand+tent *** 1274,1288 **** UNION ALL SELECT 42, 42, hundred FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ----------------------------------------------------------------- Result -> Merge Append ! Sort Key: public.tenk1.thousand, public.tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort Sort Key: (42), (42) ! -> Index Only Scan using tenk1_hundred on tenk1 (7 rows) explain (costs off) --- 1274,1288 ---- UNION ALL SELECT 42, 42, hundred FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ------------------------------------------------------------------------ Result -> Merge Append ! Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort Sort Key: (42), (42) ! -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1 (7 rows) explain (costs off) *************** SELECT thousand, tenthous FROM tenk1 *** 1290,1304 **** UNION ALL SELECT thousand, random()::integer FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ----------------------------------------------------------------------- Result -> Merge Append ! Sort Key: public.tenk1.thousand, public.tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort ! Sort Key: public.tenk1.thousand, ((random())::integer) ! -> Index Only Scan using tenk1_thous_tenthous on tenk1 (7 rows) -- Check min/max aggregate optimization --- 1290,1304 ---- UNION ALL SELECT thousand, random()::integer FROM tenk1 ORDER BY thousand, tenthous; ! QUERY PLAN ! ------------------------------------------------------------------------------- Result -> Merge Append ! Sort Key: tenk1.thousand, tenk1.tenthous -> Index Only Scan using tenk1_thous_tenthous on tenk1 -> Sort ! Sort Key: tenk1_1.thousand, ((random())::integer) ! -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 (7 rows) -- Check min/max aggregate optimization diff --git a/src/test/regress/expected/select_views.out b/src/test/regress/expected/select_views.out index e4eba1ae36c8715d2f414db413feb68e71c1c575..6a00c17d3f6a5f949b3fb88352cd970e2af0d380 100644 *** a/src/test/regress/expected/select_views.out --- b/src/test/regress/expected/select_views.out *************** EXPLAIN (COSTS OFF) SELECT * FROM my_cre *** 1421,1430 **** -> Subquery Scan on l Filter: f_leak(l.cnum) -> Hash Join ! Hash Cond: (r.cid = l.cid) ! -> Seq Scan on credit_card r -> Hash ! -> Seq Scan on customer l Filter: (name = ("current_user"())::text) (13 rows) --- 1421,1430 ---- -> Subquery Scan on l Filter: f_leak(l.cnum) -> Hash Join ! Hash Cond: (r_1.cid = l_1.cid) ! -> Seq Scan on credit_card r_1 -> Hash ! -> Seq Scan on customer l_1 Filter: (name = ("current_user"())::text) (13 rows) *************** EXPLAIN (COSTS OFF) SELECT * FROM my_cre *** 1447,1461 **** Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) -> Nested Loop ! Join Filter: (l.cid = r.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) -> Materialize -> Hash Join ! Hash Cond: (r.cid = l.cid) ! -> Seq Scan on credit_card r -> Hash ! -> Seq Scan on customer l Filter: (name = ("current_user"())::text) (13 rows) --- 1447,1461 ---- Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) -> Nested Loop ! Join Filter: (l_1.cid = r.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) -> Materialize -> Hash Join ! Hash Cond: (r_1.cid = l_1.cid) ! -> Seq Scan on credit_card r_1 -> Hash ! -> Seq Scan on customer l_1 Filter: (name = ("current_user"())::text) (13 rows) diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out index 94b439825c49b25651a7722f301d203f9d6c958f..0b509f4ee6ba9a58d125f223bdb85350976d3596 100644 *** a/src/test/regress/expected/select_views_1.out --- b/src/test/regress/expected/select_views_1.out *************** EXPLAIN (COSTS OFF) SELECT * FROM my_cre *** 1421,1430 **** -> Subquery Scan on l Filter: f_leak(l.cnum) -> Hash Join ! Hash Cond: (r.cid = l.cid) ! -> Seq Scan on credit_card r -> Hash ! -> Seq Scan on customer l Filter: (name = ("current_user"())::text) (13 rows) --- 1421,1430 ---- -> Subquery Scan on l Filter: f_leak(l.cnum) -> Hash Join ! Hash Cond: (r_1.cid = l_1.cid) ! -> Seq Scan on credit_card r_1 -> Hash ! -> Seq Scan on customer l_1 Filter: (name = ("current_user"())::text) (13 rows) *************** EXPLAIN (COSTS OFF) SELECT * FROM my_cre *** 1447,1461 **** Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) -> Nested Loop ! Join Filter: (l.cid = r.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) -> Materialize -> Hash Join ! Hash Cond: (r.cid = l.cid) ! -> Seq Scan on credit_card r -> Hash ! -> Seq Scan on customer l Filter: (name = ("current_user"())::text) (13 rows) --- 1447,1461 ---- Subquery Scan on my_credit_card_usage_secure Filter: f_leak(my_credit_card_usage_secure.cnum) -> Nested Loop ! Join Filter: (l_1.cid = r.cid) -> Seq Scan on credit_usage r Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date)) -> Materialize -> Hash Join ! Hash Cond: (r_1.cid = l_1.cid) ! -> Seq Scan on credit_card r_1 -> Hash ! -> Seq Scan on customer l_1 Filter: (name = ("current_user"())::text) (13 rows) diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 38cfb8c7276c9310a60375f0f70d56b8c14eb970..671f293b68de95617b6f953573a31fda3fde176f 100644 *** a/src/test/regress/expected/with.out --- b/src/test/regress/expected/with.out *************** SELECT * FROM parent; *** 2006,2013 **** EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) DELETE FROM a USING wcte WHERE aa = q2; ! QUERY PLAN ! -------------------------------------------------- Delete on public.a CTE wcte -> Insert on public.int8_tbl --- 2006,2013 ---- EXPLAIN (VERBOSE, COSTS OFF) WITH wcte AS ( INSERT INTO int8_tbl VALUES ( 42, 47 ) RETURNING q2 ) DELETE FROM a USING wcte WHERE aa = q2; ! QUERY PLAN ! ------------------------------------------------ Delete on public.a CTE wcte -> Insert on public.int8_tbl *************** DELETE FROM a USING wcte WHERE aa = q2; *** 2015,2045 **** -> Result Output: 42::bigint, 47::bigint -> Nested Loop ! Output: public.a.ctid, wcte.* ! Join Filter: (public.a.aa = wcte.q2) -> Seq Scan on public.a ! Output: public.a.ctid, public.a.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: public.a.ctid, wcte.* ! Join Filter: (public.a.aa = wcte.q2) ! -> Seq Scan on public.b a ! Output: public.a.ctid, public.a.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: public.a.ctid, wcte.* ! Join Filter: (public.a.aa = wcte.q2) ! -> Seq Scan on public.c a ! Output: public.a.ctid, public.a.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: public.a.ctid, wcte.* ! Join Filter: (public.a.aa = wcte.q2) ! -> Seq Scan on public.d a ! Output: public.a.ctid, public.a.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 (34 rows) --- 2015,2045 ---- -> Result Output: 42::bigint, 47::bigint -> Nested Loop ! Output: a.ctid, wcte.* ! Join Filter: (a.aa = wcte.q2) -> Seq Scan on public.a ! Output: a.ctid, a.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: b.ctid, wcte.* ! Join Filter: (b.aa = wcte.q2) ! -> Seq Scan on public.b ! Output: b.ctid, b.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: c.ctid, wcte.* ! Join Filter: (c.aa = wcte.q2) ! -> Seq Scan on public.c ! Output: c.ctid, c.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 -> Nested Loop ! Output: d.ctid, wcte.* ! Join Filter: (d.aa = wcte.q2) ! -> Seq Scan on public.d ! Output: d.ctid, d.aa -> CTE Scan on wcte Output: wcte.*, wcte.q2 (34 rows)
pgsql-hackers by date: