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

On Thu, Dec 10, 2009 at 10:48 AM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
>>>>>> "Robert" =3D=3D Robert Haas <robertmhaas@gmail.com> writes:
>
> =A0> On Thu, Dec 10, 2009 at 1:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =A0>>
> =A0>> My reading of the spec is that USING (and therefore NATURAL) is
> =A0>> defined to join identically named columns. =A0Therefore, renaming
> =A0>> one of the input columns as the OP did *should* indeed *must*
> =A0>> break the view. =A0The problem is not how to make it work, it's how
> =A0>> to give an error message that doesn't look like an internal
> =A0>> failure.
>
> =A0Robert> That seems ugly and unnecessary. =A0I think we might be able to
> =A0Robert> define ourselves out of this problem. =A0We don't guarantee
> =A0Robert> (and have never guaranteed) that selecting from a stored view
> =A0Robert> will produce the same results as re-executing the original
> =A0Robert> query. =A0For example, * refers the list of columns at
> =A0Robert> definition-time, not execution-time, and if a column is
> =A0Robert> renamed, the view still refers to the same column; it doesn't
> =A0Robert> start crashing, nor would we want it to. =A0Similarly, here,
> =A0Robert> the USING is internally converted to an equality join on the
> =A0Robert> two columns, and the ambiguous output column is, I think,
> =A0Robert> resolved in favor of one of them. =A0I think we can just say
> =A0Robert> that that conversion happens in toto at parse-time, just as
> =A0Robert> the *-to-column-list conversion and the
> =A0Robert> column-name-to-column-reference conversions do. =A0This seems
> =A0Robert> like a significantly more useful behavior and as a fringe
> =A0Robert> 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.
> =A0select ... 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