Re: Confusing EXPLAIN output in case of inherited tables

On Wed, Feb 1, 2012 at 10:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)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.
>

To clarify matters a bit, item 2 is in conjunction with item 1. Aliases, if
provided, are output irrespective of whether we get the relation name from
eref or catalogs. ruleutils should just ignore eref (for RTE_RELATION
only) and get the relation name from given OID.

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

It does give preference to aliases today. I compared preferences of
colnames in eref and that obtained from catalogs.

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

I agree. The example of the colnames was only to show that the preference
alias > relation information from catalogs > eref exists somewhere in the
code.

>
> regards, tom lane
>

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