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 | 25855.1348247529@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>) |
| List | pgsql-hackers |
I wrote:
> ... 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?
Attached is a second draft that does it like that. This adds about 130
lines to explain.c compared to the other way, but on reflection it's
probably a better solution compared to trying to kluge things in the
planner. The change in the select_views results shows that there's
at least one other case of duplicated RTE names that I'd not covered
with the two planner kluges.
I think the next question is whether we want to back-patch this.
Although the problem with incorrect view dumping is arguably a data
integrity issue (cf bug #7553), few enough people have hit it that
I'm not sure it's worth taking risks for. I'd feel better about
this code once it'd got through a beta test cycle. Comments?
regards, tom lane
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 1e8f618a3476b9e171658900f92532b87b3cde97..33252a8e205abc3726ffb32c5615ef6fa64e28a8 100644
*** a/src/backend/commands/explain.c
--- b/src/backend/commands/explain.c
*************** static void ExplainOneQuery(Query *query
*** 51,56 ****
--- 51,60 ----
static void report_triggers(ResultRelInfo *rInfo, bool show_relname,
ExplainState *es);
static double elapsed_time(instr_time *starttime);
+ static void ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used);
+ static void ExplainPreScanMemberNodes(List *plans, PlanState **planstates,
+ Bitmapset **rels_used);
+ static void ExplainPreScanSubPlans(List *plans, Bitmapset **rels_used);
static void ExplainNode(PlanState *planstate, List *ancestors,
const char *relationship, const char *plan_name,
ExplainState *es);
*************** ExplainOnePlan(PlannedStmt *plannedstmt,
*** 539,547 ****
--- 543,555 ----
void
ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc)
{
+ Bitmapset *rels_used = NULL;
+
Assert(queryDesc->plannedstmt != NULL);
es->pstmt = queryDesc->plannedstmt;
es->rtable = queryDesc->plannedstmt->rtable;
+ ExplainPreScanNode(queryDesc->planstate, &rels_used);
+ es->rtable_names = select_rtable_names_for_explain(es->rtable, rels_used);
ExplainNode(queryDesc->planstate, NIL, NULL, NULL, es);
}
*************** elapsed_time(instr_time *starttime)
*** 641,646 ****
--- 649,780 ----
}
/*
+ * ExplainPreScanNode -
+ * Prescan the planstate tree to identify which RTEs are referenced
+ *
+ * Adds the relid of each referenced RTE to *rels_used. The result controls
+ * which RTEs are assigned aliases by select_rtable_names_for_explain. This
+ * ensures that we don't confusingly assign un-suffixed aliases to RTEs that
+ * never appear in the EXPLAIN output (such as inheritance parents).
+ */
+ static void
+ ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used)
+ {
+ Plan *plan = planstate->plan;
+
+ switch (nodeTag(plan))
+ {
+ case T_SeqScan:
+ case T_IndexScan:
+ case T_IndexOnlyScan:
+ case T_BitmapHeapScan:
+ case T_TidScan:
+ case T_SubqueryScan:
+ case T_FunctionScan:
+ case T_ValuesScan:
+ case T_CteScan:
+ case T_WorkTableScan:
+ case T_ForeignScan:
+ *rels_used = bms_add_member(*rels_used,
+ ((Scan *) plan)->scanrelid);
+ break;
+ case T_ModifyTable:
+ /* cf ExplainModifyTarget */
+ *rels_used = bms_add_member(*rels_used,
+ linitial_int(((ModifyTable *) plan)->resultRelations));
+ break;
+ default:
+ break;
+ }
+
+ /* initPlan-s */
+ if (planstate->initPlan)
+ ExplainPreScanSubPlans(planstate->initPlan, rels_used);
+
+ /* lefttree */
+ if (outerPlanState(planstate))
+ ExplainPreScanNode(outerPlanState(planstate), rels_used);
+
+ /* righttree */
+ if (innerPlanState(planstate))
+ ExplainPreScanNode(innerPlanState(planstate), rels_used);
+
+ /* special child plans */
+ switch (nodeTag(plan))
+ {
+ case T_ModifyTable:
+ ExplainPreScanMemberNodes(((ModifyTable *) plan)->plans,
+ ((ModifyTableState *) planstate)->mt_plans,
+ rels_used);
+ break;
+ case T_Append:
+ ExplainPreScanMemberNodes(((Append *) plan)->appendplans,
+ ((AppendState *) planstate)->appendplans,
+ rels_used);
+ break;
+ case T_MergeAppend:
+ ExplainPreScanMemberNodes(((MergeAppend *) plan)->mergeplans,
+ ((MergeAppendState *) planstate)->mergeplans,
+ rels_used);
+ break;
+ case T_BitmapAnd:
+ ExplainPreScanMemberNodes(((BitmapAnd *) plan)->bitmapplans,
+ ((BitmapAndState *) planstate)->bitmapplans,
+ rels_used);
+ break;
+ case T_BitmapOr:
+ ExplainPreScanMemberNodes(((BitmapOr *) plan)->bitmapplans,
+ ((BitmapOrState *) planstate)->bitmapplans,
+ rels_used);
+ break;
+ case T_SubqueryScan:
+ ExplainPreScanNode(((SubqueryScanState *) planstate)->subplan,
+ rels_used);
+ break;
+ default:
+ break;
+ }
+
+ /* subPlan-s */
+ if (planstate->subPlan)
+ ExplainPreScanSubPlans(planstate->subPlan, rels_used);
+ }
+
+ /*
+ * Prescan the constituent plans of a ModifyTable, Append, MergeAppend,
+ * BitmapAnd, or BitmapOr node.
+ *
+ * Note: we don't actually need to examine the Plan list members, but
+ * we need the list in order to determine the length of the PlanState array.
+ */
+ static void
+ ExplainPreScanMemberNodes(List *plans, PlanState **planstates,
+ Bitmapset **rels_used)
+ {
+ int nplans = list_length(plans);
+ int j;
+
+ for (j = 0; j < nplans; j++)
+ ExplainPreScanNode(planstates[j], rels_used);
+ }
+
+ /*
+ * Prescan a list of SubPlans (or initPlans, which also use SubPlan nodes).
+ */
+ static void
+ ExplainPreScanSubPlans(List *plans, Bitmapset **rels_used)
+ {
+ ListCell *lst;
+
+ foreach(lst, plans)
+ {
+ SubPlanState *sps = (SubPlanState *) lfirst(lst);
+
+ ExplainPreScanNode(sps->planstate, rels_used);
+ }
+ }
+
+ /*
* ExplainNode -
* Appends a description of a plan tree to es->str
*
*************** 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) */
--- 1574,1581 ----
/* 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);
--- 1606,1613 ----
/* 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++)
--- 1709,1716 ----
/* 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 ****
--- 1950,1959 ----
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
{
--- 2026,2034 ----
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);
}
}
--- 2036,2043 ----
ExplainPropertyText(objecttag, objectname, es);
if (namespace != NULL)
ExplainPropertyText("Schema", namespace, es);
! if (refname != NULL)
! ExplainPropertyText("Alias", refname, es);
}
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 08396431384a8f8581492304c8c916c41e4b9516..c8d7d9c21b3a4398008160da844e83e8330e8ad4 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,185 ----
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,
+ Bitmapset *rels_used);
+ 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);
--- 220,225 ----
*************** 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;
--- 682,689 ----
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;
--- 692,699 ----
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 ****
--- 702,708 ----
memset(&dpns, 0, sizeof(dpns));
dpns.rtable = list_make2(oldrte, newrte);
dpns.ctes = NIL;
+ set_rtable_names(&dpns, NIL, NULL);
/* 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;
--- 2185,2192 ----
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 ****
--- 2194,2200 ----
/* Build one-element rtable */
dpns->rtable = list_make1(rte);
dpns->ctes = NIL;
+ set_rtable_names(dpns, NIL, NULL);
/* 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;
--- 2220,2233 ----
* 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 ****
--- 2235,2241 ----
/* 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 ****
--- 2247,2388 ----
}
/*
+ * 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 the aliases
+ * to EXPLAIN because EXPLAIN needs to know the right alias names to print.
+ */
+ List *
+ select_rtable_names_for_explain(List *rtable, Bitmapset *rels_used)
+ {
+ deparse_namespace dpns;
+
+ memset(&dpns, 0, sizeof(dpns));
+ dpns.rtable = rtable;
+ dpns.ctes = NIL;
+ set_rtable_names(&dpns, NIL, rels_used);
+
+ 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.
+ *
+ * If rels_used isn't NULL, only RTE indexes listed in it are given aliases.
+ */
+ static void
+ set_rtable_names(deparse_namespace *dpns, List *parent_namespaces,
+ Bitmapset *rels_used)
+ {
+ ListCell *lc;
+ int rtindex = 1;
+
+ dpns->rtable_names = NIL;
+ foreach(lc, dpns->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+ char *refname;
+
+ if (rels_used && !bms_is_member(rtindex, rels_used))
+ {
+ /* Ignore unreferenced RTE */
+ refname = NULL;
+ }
+ else 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);
+ rtindex++;
+ }
+ }
+
+ /*
+ * 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 ****
--- 2683,2689 ----
memset(&dpns, 0, sizeof(dpns));
dpns.rtable = query->rtable;
dpns.ctes = query->cteList;
+ set_rtable_names(&dpns, NIL, NULL);
get_rule_expr(qual, &context, false);
}
*************** get_query_def(Query *query, StringInfo b
*** 2680,2685 ****
--- 2830,2836 ----
memset(&dpns, 0, sizeof(dpns));
dpns.rtable = query->rtable;
dpns.ctes = query->cteList;
+ set_rtable_names(&dpns, parentnamespace, NULL);
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)
--- 3050,3055 ----
*************** 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");
}
--- 3062,3069 ----
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;
--- 4005,4010 ----
*************** get_variable(Var *var, int levelsup, boo
*** 3874,3879 ****
--- 4024,4030 ----
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)
--- 4144,4184 ----
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, '.');
}
--- 4188,4193 ----
*************** get_name_for_var_field(Var *var, int fie
*** 4289,4294 ****
--- 4417,4423 ----
memset(&mydpns, 0, sizeof(mydpns));
mydpns.rtable = rte->subquery->rtable;
mydpns.ctes = rte->subquery->cteList;
+ set_rtable_names(&mydpns, context->namespaces, NULL);
context->namespaces = lcons(&mydpns,
context->namespaces);
*************** get_name_for_var_field(Var *var, int fie
*** 4406,4411 ****
--- 4535,4541 ----
memset(&mydpns, 0, sizeof(mydpns));
mydpns.rtable = ctequery->rtable;
mydpns.ctes = ctequery->cteList;
+ set_rtable_names(&mydpns, context->namespaces, NULL);
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.
--- 4597,4602 ----
*************** get_from_clause_item(Node *jtnode, Query
*** 6649,6654 ****
--- 6738,6744 ----
{
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;
}
--- 6778,6808 ----
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..5bc3a75856d8efe53ed50064ae71d15711b2beaf 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,662 ----
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,
! Bitmapset *rels_used);
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..24bbff9d2e4727529d470454f012db0f9a774776 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
*** 1452,1459 ****
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)
--- 1452,1459 ----
Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
-> Materialize
-> Hash Join
! Hash Cond: (r_1.cid = l.cid)
! -> Seq Scan on credit_card r_1
-> Hash
-> Seq Scan on customer l
Filter: (name = ("current_user"())::text)
diff --git a/src/test/regress/expected/select_views_1.out b/src/test/regress/expected/select_views_1.out
index 94b439825c49b25651a7722f301d203f9d6c958f..ec6e938cb1d3a30d56bc8b0ceeafd0a6f8d6fe43 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
*** 1452,1459 ****
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)
--- 1452,1459 ----
Filter: ((ymd >= '10-01-2011'::date) AND (ymd < '11-01-2011'::date))
-> Materialize
-> Hash Join
! Hash Cond: (r_1.cid = l.cid)
! -> Seq Scan on credit_card r_1
-> Hash
-> Seq Scan on customer l
Filter: (name = ("current_user"())::text)
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: