Thread: Confusing EXPLAIN output in case of inherited tables

Hi,
After running regression, I ran EXPLAIN on one of the queries in regression (test create_misc) and got following output
regression=# explain verbose select * into table ramp from road where name ~ '.*Ramp';
                                     QUERY PLAN                                    
------------------------------------------------------------------------------------
 Result  (cost=0.00..154.00 rows=841 width=67)
   Output: public.road.name, public.road.thepath
   ->  Append  (cost=0.00..154.00 rows=841 width=67)
         ->  Seq Scan on public.road  (cost=0.00..135.05 rows=418 width=67)
               Output: public.road.name, public.road.thepath
               Filter: (public.road.name ~ '.*Ramp'::text)
         ->  Seq Scan on public.ihighway road  (cost=0.00..14.99 rows=367 width=67)
                                                        ^^^^^
               Output: public.road.name, public.road.thepath
                           ^^^^^^^^^^,           ^^^^^^
               Filter: (public.road.name ~ '.*Ramp'::text)
                         ^^^^^^^^^^^
         ->  Seq Scan on public.shighway road  (cost=0.00..3.96 rows=56 width=67)
               Output: public.road.name, public.road.thepath
               Filter: (public.road.name ~ '.*Ramp'::text)
(12 rows)

regression=# \d+ road
                        Table "public.road"
 Column  | Type | Modifiers | Storage  | Stats target | Description
---------+------+-----------+----------+--------------+-------------
 name    | text |           | extended |              |
 thepath | path |           | extended |              |
Indexes:
    "rix" btree (name)
Child tables: ihighway,
              shighway
Has OIDs: no

Table "road" has children "ihighway" and "shighway" as seen in the \d+ output above. The EXPLAIN output of Seq Scan node on children has "public.road" as prefix for variables. "public.road" could imply the parent table "road" and thus can cause confusion, as to what's been referreed, the columns of parent table or child table. In the EXPLAIN output children tables have "road" as alias (as against "public.road"). The alias comes from RangeTblEntry->eref->aliasname. It might be better to have "road" as prefix in the variable names over "public.road".

The reason why this happens is the code in get_variable()
3865     /* Exceptions occur only if the RTE is alias-less */
3866     if (rte->alias == NULL)
3867     {
3868         if (rte->rtekind == RTE_RELATION)
3869         {
3870             /*
3871              * It's possible that use of the bare refname would find another
3872              * more-closely-nested RTE, or be ambiguous, in which case we need
3873              * to specify the schemaname to avoid these errors.
3874              */
3875             if (find_rte_by_refname(rte->eref->aliasname, context) != rte)
3876                 schemaname = get_namespace_name(get_rel_namespace(rte->relid));
3877         }

If there is no alias, we find out the schema name and later add it to the prefix. In the inherited table case, we are actually creating a "kind of" alias for the children table and thus we should not find out the schema name and add it to the prefix. This case has been taken care of in get_from_clause_item(),
6505         else if (rte->rtekind == RTE_RELATION &&
6506             strcmp(rte->eref->aliasname, get_relation_name(rte->relid)) != 0)
6507         {
6508             /*
6509              * Apparently the rel has been renamed since the rule was made.
6510              * Emit a fake alias clause so that variable references will still
6511              * work.  This is not a 100% solution but should work in most
6512              * reasonable situations.
6513              */
6514             appendStringInfo(buf, " %s",
6515                              quote_identifier(rte->eref->aliasname));
6516             gavealias = true;
6517         }

I see similar code in ExplainTargetRel()
1778         if (objectname == NULL ||
1779             strcmp(rte->eref->aliasname, objectname) != 0)
1780             appendStringInfo(es->str, " %s",
1781                              quote_identifier(rte->eref->aliasname));

Based on this, here is patch to not add schemaname in the prefix for a variable.

I have run make check. All except inherit.sql passed. The expected output change is included in the patch.

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

A table can inherit from one or more parent table. So in that case, qualifying schema/table name
helps in finding out where the column is coming from.

Regards,
Chetan

--

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

On Wed, Jan 11, 2012 at 6:43 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> wrote:
> Hi,
> After running regression, I ran EXPLAIN on one of the queries in regression
> (test create_misc) and got following output
> regression=# explain verbose select * into table ramp from road where name ~
> '.*Ramp';
>                                      QUERY
> PLAN
> ------------------------------------------------------------------------------------
>  Result  (cost=0.00..154.00 rows=841 width=67)
>    Output: public.road.name, public.road.thepath
>    ->  Append  (cost=0.00..154.00 rows=841 width=67)
>          ->  Seq Scan on public.road  (cost=0.00..135.05 rows=418 width=67)
>                Output: public.road.name, public.road.thepath
>                Filter: (public.road.name ~ '.*Ramp'::text)
>          ->  Seq Scan on public.ihighway road  (cost=0.00..14.99 rows=367
> width=67)
>                                                         ^^^^^
>                Output: public.road.name, public.road.thepath
>                            ^^^^^^^^^^,           ^^^^^^
>                Filter: (public.road.name ~ '.*Ramp'::text)
>                          ^^^^^^^^^^^
>          ->  Seq Scan on public.shighway road  (cost=0.00..3.96 rows=56
> width=67)
>                Output: public.road.name, public.road.thepath
>                Filter: (public.road.name ~ '.*Ramp'::text)
> (12 rows)
>
> regression=# \d+ road
>                         Table "public.road"
>  Column  | Type | Modifiers | Storage  | Stats target | Description
> ---------+------+-----------+----------+--------------+-------------
>  name    | text |           | extended |              |
>  thepath | path |           | extended |              |
> Indexes:
>     "rix" btree (name)
> Child tables: ihighway,
>               shighway
> Has OIDs: no
>
> Table "road" has children "ihighway" and "shighway" as seen in the \d+
> output above. The EXPLAIN output of Seq Scan node on children has
> "public.road" as prefix for variables. "public.road" could imply the parent
> table "road" and thus can cause confusion, as to what's been referreed, the
> columns of parent table or child table. In the EXPLAIN output children
> tables have "road" as alias (as against "public.road"). The alias comes from
> RangeTblEntry->eref->aliasname. It might be better to have "road" as prefix
> in the variable names over "public.road".

It's a feature, not a bug, that we schema-qualify names when VERBOSE
is specified.  That was done on purpose for the benefit of external
tools that might need this information to disambiguate which object is
being referenced.

Table *aliases*, of course, should not be schema-qualified, but I
don't think that's what we're doing.  You could make it more clear by
including an alias in the query, like this:

explain verbose select * into table ramp from road hwy where name ~ '.*Ramp';

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> It's a feature, not a bug, that we schema-qualify names when VERBOSE
> is specified.  That was done on purpose for the benefit of external
> tools that might need this information to disambiguate which object is
> being referenced.

> Table *aliases*, of course, should not be schema-qualified, but I
> don't think that's what we're doing.  You could make it more clear by
> including an alias in the query, like this:

> explain verbose select * into table ramp from road hwy where name ~ '.*Ramp';

I think you are both focusing on the wrong thing.  There is a lot of
squishiness in what EXPLAIN prints out, since SQL notation is not always
well suited to what an execution plan actually does.  But this code has
a hard and fast requirement that it dump view definitions correctly,
else pg_dump doesn't work.  And after looking at this I think Ashutosh
has in fact found a bug.  Consider this example:

regression=# create schema s1;
CREATE SCHEMA
regression=# create schema s2;
CREATE SCHEMA
regression=# create table s1.t1 (f1 int);
CREATE TABLE
regression=# create table s2.t1 (f1 int);
CREATE TABLE
regression=# create view v1 as
regression-#   select * from s1.t1 where exists (
regression(#     select 1 from s2.t1 where s2.t1.f1 = s1.t1.f1
regression(#   );
CREATE VIEW
regression=# \d+ v1                  View "public.v1"Column |  Type   | Modifiers | Storage | Description 
--------+---------+-----------+---------+-------------f1     | integer |           | plain   | 
View definition:SELECT t1.f1  FROM s1.t1 WHERE (EXISTS ( SELECT 1          FROM s2.t1         WHERE t1.f1 =
s1.t1.f1));

regression=# alter table s2.t1 rename to tx;
ALTER TABLE
regression=# \d+ v1                  View "public.v1"Column |  Type   | Modifiers | Storage | Description 
--------+---------+-----------+---------+-------------f1     | integer |           | plain   | 
View definition:SELECT t1.f1  FROM s1.t1 WHERE (EXISTS ( SELECT 1          FROM s2.tx t1         WHERE t1.f1 =
s1.t1.f1));

Both of the above displays of the view are formally correct, in that the
variables will be taken to refer to the correct upper or lower RTE.
But let's change that back and rename the other table:

regression=# alter table s2.tx rename to t1;
ALTER TABLE
regression=# alter table s1.t1 rename to tx;
ALTER TABLE
regression=# \d+ v1                  View "public.v1"Column |  Type   | Modifiers | Storage | Description 
--------+---------+-----------+---------+-------------f1     | integer |           | plain   | 
View definition:SELECT t1.f1  FROM s1.tx t1 WHERE (EXISTS ( SELECT 1          FROM s2.t1         WHERE t1.f1 =
s1.t1.f1));

This is just plain wrong, as you'll see if you try to execute that
query:

regression=# SELECT t1.f1
regression-#    FROM s1.tx t1
regression-#   WHERE (EXISTS ( SELECT 1
regression(#            FROM s2.t1
regression(#           WHERE t1.f1 = s1.t1.f1));
ERROR:  invalid reference to FROM-clause entry for table "t1"
LINE 5:           WHERE t1.f1 = s1.t1.f1));                               ^
HINT:  There is an entry for table "t1", but it cannot be referenced
from this part of the query.

(The HINT is a bit confused here, but the query is certainly invalid.)

So what we have here is a potential failure to dump and reload view
definitions, which is a lot more critical in my book than whether
EXPLAIN's output is confusing.

If we stick with the existing rule for attaching a fake alias to renamed
RTEs, I think that Ashutosh's patch or something like it is probably
appropriate, because the variable-printing code ought to be in step with
the RTE-printing code.  Unfortunately, I think the hack to attach a fake
alias to renamed RTEs creates some issues of its own.  Consider
select * from s1.t1  where exists (select 1 from s2.t2 t1 where t1.f1 = s1.t1.f1);

If s1.t1 is now renamed to s1.tx, it is still possible to express
the same semantics:
select * from s1.tx  where exists (select 1 from s2.t2 t1 where t1.f1 = s1.tx.f1);

But when we attach a fake alias, it's broken:
select * from s1.tx t1  where exists (select 1 from s2.t2 t1 where t1.f1 = ?.f1);

There is no way to reference the outer RTE anymore from the subquery,
because the conflicting lower alias masks it.

We may be between a rock and a hard place though, because it's not that
hard to demonstrate cases where not adding a fake alias breaks it too:
select * from s1.t1 tx  where exists (select 1 from s2.t1 where s2.t1.f1 = tx.f1);

If s2.t1 is renamed to s2.tx, there's no longer any way to reference the
upper alias tx, unless you alias the lower RTE to some different name.
I think that when we put in the fake-alias behavior, we made a value
judgment that this type of situation was more common than the other,
but I'm not really sure why.

Maybe what we need to do instead is create totally-made-up, unique
aliases when something like this happens.
        regards, tom lane

Thanks Tom for giving a stronger case. I found the problem whille looking at inherited tables, and didn't think beyond
inheritedtables. Since inherited tables are expanded when subquery planner is invoked, I thought the problem will occur
onlyin Explain output as we won't generate queries, that can be used elsewhere after/during planning.<br /><br />So, as
Iunderstand we have two problems here<br />1. Prefixing schemaname to the fake alises if there is another RTE with same
name.There may not be a relation with that name (fake alias name given) in the schema chosen as prefix.<br /> 2. Fake
aliasesthemselves can be conflicting.<br /><br />If I understand correctly, if we solve the second problem, first
problemwill not occur. Is that correct?<br /><br /><div class="gmail_quote">On Sat, Jan 28, 2012 at 8:08 AM, Tom Lane
<spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0pt 0pt 0pt 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div
class="im">RobertHaas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> writes:<br /> > It's
afeature, not a bug, that we schema-qualify names when VERBOSE<br /> > is specified.  That was done on purpose for
thebenefit of external<br /> > tools that might need this information to disambiguate which object is<br /> >
beingreferenced.<br /><br /> > Table *aliases*, of course, should not be schema-qualified, but I<br /> > don't
thinkthat's what we're doing.  You could make it more clear by<br /> > including an alias in the query, like
this:<br/><br /> > explain verbose select * into table ramp from road hwy where name ~ '.*Ramp';<br /><br /></div>I
thinkyou are both focusing on the wrong thing.  There is a lot of<br /> squishiness in what EXPLAIN prints out, since
SQLnotation is not always<br /> well suited to what an execution plan actually does.  But this code has<br /> a hard
andfast requirement that it dump view definitions correctly,<br /> else pg_dump doesn't work.  And after looking at
thisI think Ashutosh<br /> has in fact found a bug.  Consider this example:<br /><br /> regression=# create schema
s1;<br/> CREATE SCHEMA<br /> regression=# create schema s2;<br /> CREATE SCHEMA<br /> regression=# create table s1.t1
(f1int);<br /> CREATE TABLE<br /> regression=# create table s2.t1 (f1 int);<br /> CREATE TABLE<br /> regression=#
createview v1 as<br /> regression-#   select * from s1.t1 where exists (<br /> regression(#     select 1 from s2.t1
wheres2.t1.f1 = s1.t1.f1<br /> regression(#   );<br /> CREATE VIEW<br /> regression=# \d+ v1<br />                  
View"public.v1"<br />  Column |  Type   | Modifiers | Storage | Description<br />
--------+---------+-----------+---------+-------------<br/>  f1     | integer |           | plain   |<br /> View
definition:<br/>  SELECT t1.f1<br />   FROM s1.t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM s2.t1<br />    
    WHERE t1.f1 = s1.t1.f1));<br /><br /> regression=# alter table s2.t1 rename to tx;<br /> ALTER TABLE<br />
regression=#\d+ v1<br />                   View "public.v1"<br />  Column |  Type   | Modifiers | Storage |
Description<br/> --------+---------+-----------+---------+-------------<br />  f1     | integer |           | plain  
|<br/> View definition:<br />  SELECT t1.f1<br />   FROM s1.t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM
s2.txt1<br />          WHERE t1.f1 = s1.t1.f1));<br /><br /> Both of the above displays of the view are formally
correct,in that the<br /> variables will be taken to refer to the correct upper or lower RTE.<br /> But let's change
thatback and rename the other table:<br /><br /> regression=# alter table s2.tx rename to t1;<br /> ALTER TABLE<br />
regression=#alter table s1.t1 rename to tx;<br /> ALTER TABLE<br /> regression=# \d+ v1<br />                   View
"public.v1"<br/>  Column |  Type   | Modifiers | Storage | Description<br />
--------+---------+-----------+---------+-------------<br/>  f1     | integer |           | plain   |<br /> View
definition:<br/>  SELECT t1.f1<br />   FROM s1.tx t1<br />  WHERE (EXISTS ( SELECT 1<br />           FROM s2.t1<br />  
      WHERE t1.f1 = s1.t1.f1));<br /><br /> This is just plain wrong, as you'll see if you try to execute that<br />
query:<br/><br /> regression=# SELECT t1.f1<br /> regression-#    FROM s1.tx t1<br /> regression-#   WHERE (EXISTS (
SELECT1<br /> regression(#            FROM s2.t1<br /> regression(#           WHERE t1.f1 = s1.t1.f1));<br /> ERROR:
 invalidreference to FROM-clause entry for table "t1"<br /> LINE 5:           WHERE t1.f1 = s1.t1.f1));<br />          
                    ^<br /> HINT:  There is an entry for table "t1", but it cannot be referenced<br /> from this part
ofthe query.<br /><br /> (The HINT is a bit confused here, but the query is certainly invalid.)<br /><br /> So what we
havehere is a potential failure to dump and reload view<br /> definitions, which is a lot more critical in my book than
whether<br/> EXPLAIN's output is confusing.<br /><br /> If we stick with the existing rule for attaching a fake alias
torenamed<br /> RTEs, I think that Ashutosh's patch or something like it is probably<br /> appropriate, because the
variable-printingcode ought to be in step with<br /> the RTE-printing code.  Unfortunately, I think the hack to attach
afake<br /> alias to renamed RTEs creates some issues of its own.  Consider<br /><br />        select * from s1.t1<br
/>         where exists (select 1 from s2.t2 t1 where t1.f1 = s1.t1.f1);<br /><br /> If s1.t1 is now renamed to s1.tx,
itis still possible to express<br /> the same semantics:<br /><br />        select * from s1.tx<br />          where
exists(select 1 from s2.t2 t1 where t1.f1 = s1.tx.f1);<br /><br /> But when we attach a fake alias, it's broken:<br
/><br/>        select * from s1.tx t1<br />          where exists (select 1 from s2.t2 t1 where t1.f1 = ?.f1);<br /><br
/>There is no way to reference the outer RTE anymore from the subquery,<br /> because the conflicting lower alias masks
it.<br/><br /> We may be between a rock and a hard place though, because it's not that<br /> hard to demonstrate cases
wherenot adding a fake alias breaks it too:<br /><br />        select * from s1.t1 tx<br />          where exists
(select1 from s2.t1 where s2.t1.f1 = tx.f1);<br /><br /> If s2.t1 is renamed to s2.tx, there's no longer any way to
referencethe<br /> upper alias tx, unless you alias the lower RTE to some different name.<br /> I think that when we
putin the fake-alias behavior, we made a value<br /> judgment that this type of situation was more common than the
other,<br/> but I'm not really sure why.<br /><br /> Maybe what we need to do instead is create totally-made-up,
unique<br/> aliases when something like this happens.<br /><br />                        regards, tom lane<br
/></blockquote></div><br/><br clear="all" /><br />-- <br />Best Wishes,<br />Ashutosh Bapat<br />EntepriseDB
Corporation<br/>The Enterprise Postgres Company<br /><br /> 

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> So, as I understand we have two problems here
> 1. Prefixing schemaname to the fake alises if there is another RTE with
> same name. There may not be a relation with that name (fake alias name
> given) in the schema chosen as prefix.
> 2. Fake aliases themselves can be conflicting.

Well, the issue is more that a fake alias might unintentionally collide
with a regular alias elsewhere in the query.  There's no guard against
that in the current behavior, and ISTM there needs to be.

The one possibly-simplifying thing about this whole issue is that we
needn't cater for references that couldn't have been made in the
original query.  For instance, if the inner and outer queries both have
explicit aliases "tx", it's impossible for the inner query to have
referred to any columns of the outer "tx" --- so we don't have to try to
make it possible in the dumped form.

> If I understand correctly, if we solve the second problem, first problem
> will not occur. Is that correct?

I don't believe that the problem has anything to do with the names of
other tables that might happen to exist in the database.  It's a matter
of what RTE names/aliases are exposed for variable references in
different parts of the query.
        regards, tom lane

I don't believe that the problem has anything to do with the names of



--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

Looking at the code, it seems that the fake aliases (eref) for relations (may be views as well) are not generated per
say,but they do not get changed when the relation name changes OR in case of inherited tables, they do not get changed
whenthe inheritance is expanded (expand_inherited_rtentry). So, there is not question of generating them so as to not
collidewith other aliases in the query. However I did not find answers to these questions<br /> 1. What is the use of
erefin case of relation when the relation name itself can be provided by the reloid?<br />2. Can we use schema
qualifiedrelation name in get_from_clause_item() and get_variable() instead of use eref->aliasname. I have noticed
thatthe logic in get_rte_attribute_name() gives preference to the column names in catalog tables over
eref->colnames.<br/><br />Anyone?<br /><br /><div class="gmail_quote">On Mon, Jan 30, 2012 at 10:26 PM, Tom Lane
<spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">Ashutosh Bapat
<<ahref="mailto:ashutosh.bapat@enterprisedb.com">ashutosh.bapat@enterprisedb.com</a>> writes:<br /> > So, as I
understandwe have two problems here<br /> > 1. Prefixing schemaname to the fake alises if there is another RTE
with<br/> > same name. There may not be a relation with that name (fake alias name<br /> > given) in the schema
chosenas prefix.<br /> > 2. Fake aliases themselves can be conflicting.<br /><br /></div>Well, the issue is more
thata fake alias might unintentionally collide<br /> with a regular alias elsewhere in the query.  There's no guard
against<br/> that in the current behavior, and ISTM there needs to be.<br /><br /> The one possibly-simplifying thing
aboutthis whole issue is that we<br /> needn't cater for references that couldn't have been made in the<br /> original
query. For instance, if the inner and outer queries both have<br /> explicit aliases "tx", it's impossible for the
innerquery to have<br /> referred to any columns of the outer "tx" --- so we don't have to try to<br /> make it
possiblein the dumped form.<br /><div class="im"><br /> > If I understand correctly, if we solve the second problem,
firstproblem<br /> > will not occur. Is that correct?<br /><br /></div>I don't believe that the problem has anything
todo with the names of<br /> other tables that might happen to exist in the database.  It's a matter<br /> of what RTE
names/aliasesare exposed for variable references in<br /> different parts of the query.<br /><br />                    
  regards, tom lane<br /></blockquote></div><br /><br clear="all" /><br />-- <br />Best Wishes,<br />Ashutosh Bapat<br
/>EntepriseDBCorporation<br />The Enterprise Postgres Company<br /><br /> 

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> Looking at the code, it seems that the fake aliases (eref) for relations
> (may be views as well) are not generated per say, but they do not get
> changed when the relation name changes OR in case of inherited tables, they
> do not get changed when the inheritance is expanded
> (expand_inherited_rtentry). So, there is not question of generating them so
> as to not collide with other aliases in the query.

Well, what I was considering was exactly generating new aliases that
don't collide with anything else in the query.  The fact that the code
doesn't do that now doesn't mean we can't make it do that.

> However I did not find answers to these questions
> 1. What is the use of eref in case of relation when the relation name
> itself can be provided by the reloid?

eref is stored mainly so that parsing code doesn't have to repeatedly
look up what the effective RTE name is.  The alias field is meant to
represent whether there was an AS clause or not, and if so exactly what
it said.  So eref is a derived result whereas alias is essentially raw
grammar output.  Because of the possibility that the relation gets
renamed, it's probably best if we don't rely on eref anymore after
initial parsing of a query, ie ruleutils.c probably shouldn't use it.
(Too lazy to go check right now if that's already true, but it seems
like a good goal to pursue if we're going to change this code.)

> 2. Can we use schema qualified relation name in get_from_clause_item() and
> get_variable() instead of use eref->aliasname.

No.  If there is an alias, it is flat wrong to use the relation name
instead, with or without schema name.  You might want to go study the
SQL spec a bit in this area.

> I have noticed that the
> logic in get_rte_attribute_name() gives preference to the column names in
> catalog tables over eref->colnames.

Hm.  What it should probably do is look at alias first, and if the alias
field doesn't specify a column name, then go to the catalogs to get the
current name.


Thinking about this some more, it seems like there are ways for a user
to shoot himself in the foot pretty much irretrievably.  Consider

CREATE TABLE t (x int);
CREATE VIEW v AS SELECT y FROM t t(y);
ALTER TABLE t ADD COLUMN y int;

On dump and reload, we'll have

CREATE TABLE t (x int, y int);
CREATE VIEW v AS SELECT y FROM t t(y);

and now the CREATE VIEW will fail, complaining (correctly) that the
column reference "y" is ambiguous.  Should ruleutils be expected to take
it upon itself to prevent that?  We could conceive of "fixing" it by
inventing column aliases out of whole cloth:

CREATE VIEW v AS SELECT y FROM t t(y, the_other_y);

but that seems a little much, not to mention that such a view definition
would be horribly confusing to work with.  On the other hand it isn't
all that far beyond what I had in mind of inventing relation aliases
to cure relation-name conflicts.  Should we take the existence of such
cases as evidence that we shouldn't try hard in this area?  It seems
reasonable to me to try to handle relation renames but draw the line
at disambiguating column names.  But others might find that distinction
artificial.
        regards, tom lane

On Wed, Feb 1, 2012 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> On the other hand it isn't
> all that far beyond what I had in mind of inventing relation aliases
> to cure relation-name conflicts.  Should we take the existence of such
> cases as evidence that we shouldn't try hard in this area?  It seems
> reasonable to me to try to handle relation renames but draw the line
> at disambiguating column names.  But others might find that distinction
> artificial.

I sure do.

I mean, in Oracle, if you rename a table or column involved in a view,
then the view breaks.  Blammo!  The reference is by object name, not
by some internal identifier a la OID.  If you put back an object with
the correct name (either the original one or a different one), you can
re-enable the view.

We've decide that we don't want that behavior: instead, our references
are to the object itself rather than to the name of the object.
Renaming the object doesn't change what the reference points to.  But
given that position, it seems to me that we ought to be willing to
work pretty hard to make sure that when we dump-and-reload the
database, things stay sane.  Otherwise, we're sort of in this
unsatisfying in-between place where references are *mostly* by
internal identifier but everyone once in a while it falls apart and
name collisions can break everything.  Yech!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

I got interested in this problem again now that we have a user complaint
about it (bug #7553).

Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> On Wed, Feb 1, 2012 at 11:01 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Feb 1, 2012 at 12:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> ... It seems
>>> reasonable to me to try to handle relation renames but draw the line
>>> at disambiguating column names.  But others might find that distinction
>>> artificial.

>> I sure do.

> For me the relation names problem and column aliases problems are two
> independent problems.

I think they are independent problems, and I also think that people are
far less likely to trip over column-name problems in practice.  Columns
of a table are not independent objects and so people aren't so likely
to think they can just rename them freely.  Moreover, if you rename
columns that are used in views, you can get breakage of things like
USING or NATURAL joins, and that is something we *cannot* provide a
workaround for --- it's a failure inherent in the language definition.

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.)

We can do this as follows:

1. If there's a user-assigned alias, use that.  (It's possible this is
not unique within the query, but that's okay because any actual
variable reference must be to the most closely nested such RTE.)

2. Otherwise, if the relation's current name doesn't conflict with
any previously-assigned alias, use that.

3. Otherwise, append something (underscore and some digits probably)
to the relation's current name to construct a string not matching any
previously-assigned alias.

This might result in printouts that are a bit uglier than the old way
in such cases, but anybody who's offended can select their own aliases.
        regards, tom lane

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)

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)