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

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

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: robertmhaas(at)gmail(dot)com (Robert Haas), Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Sergey Burladyan <eshkinkot(at)gmail(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly
Date: 2009-12-10 15:48:04
Message-ID: 877hsu7sd1.fsf@news-spur.riddles.org.uk (view raw or flat)
Thread:
Lists: pgsql-bugs
>>>>> "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)

In response to

Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 2009-12-10 16:00:12
Subject: Re: BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly
Previous:From: Robert HaasDate: 2009-12-10 15:36:10
Subject: Re: BUG #5234: ALTER TABLE ... RENAME COLUMN change view definition incorrectly

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