Skip site navigation (1) Skip section navigation (2)

Re: Confusing EXPLAIN output in case of inherited tables

From: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Confusing EXPLAIN output in case of inherited tables
Date: 2012-02-02 01:44:22
Message-ID: CAFjFpRdxUXzq_C6TGV9g5yLAd7tmrVeWk9AZfV_yZo_YYTff_w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

pgsql-hackers by date

Next:From: Ashutosh BapatDate: 2012-02-02 01:56:18
Subject: Re: Confusing EXPLAIN output in case of inherited tables
Previous:From: Robert HaasDate: 2012-02-02 01:40:40
Subject: Re: Index-only scan performance regression

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group