Re: Confusing EXPLAIN output in case of inherited tables

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