Re: BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly

On Thu, Dec 10, 2009 at 10:48 AM, Andrew Gierth
<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>>>>>> "Robert" == Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>
>  > On Thu, Dec 10, 2009 at 1:46 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>  >>
>  >> My reading of the spec is that USING (and therefore NATURAL) is
>  >> defined to join identically named columns.  Therefore, renaming
>  >> one of the input columns as the OP did *should* indeed *must*
>  >> break the view.  The problem is not how to make it work, it's how
>  >> to give an error message that doesn't look like an internal
>  >> failure.
>
>  Robert> That seems ugly and unnecessary.  I think we might be able to
>  Robert> define ourselves out of this problem.  We don't guarantee
>  Robert> (and have never guaranteed) that selecting from a stored view
>  Robert> will produce the same results as re-executing the original
>  Robert> query.  For example, * refers the list of columns at
>  Robert> definition-time, not execution-time, and if a column is
>  Robert> renamed, the view still refers to the same column; it doesn't
>  Robert> start crashing, nor would we want it to.  Similarly, here,
>  Robert> the USING is internally converted to an equality join on the
>  Robert> two columns, and the ambiguous output column is, I think,
>  Robert> resolved in favor of one of them.  I think we can just say
>  Robert> that that conversion happens in toto at parse-time, just as
>  Robert> the *-to-column-list conversion and the
>  Robert> column-name-to-column-reference conversions do.  This seems
>  Robert> like a significantly more useful behavior and as a fringe
>  Robert> benefit it simplifies the code.
>
> There's another possible solution (albeit a somewhat nontrivial one)
> which came up when a bunch of us were talking about this one on IRC;
> which is to handle the problem in the view deparse: if a column used
> in a USING clause has been renamed, add an alias to the query that
> renames it back, e.g.
>  select ... from table1 as table1(v,a) join ... using (v)
>
> This would have to affect all the other references to that same column
> in the query, so you'd need to do something like this: before deparsing,
> walk the query looking for offending USING clauses, and make a list of
> renamings to apply to column names.
>
> I haven't tried actually implementing this, but I believe it is
> possible.

What advantage does this offer?

...Robert