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

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

--
Andrew (irc:RhodiumToad)