Re: VIEW still referring to old name of field

Thanks Tom.

I get it. Putting it in another way, if there was a function and a VIEW and
this field name were to be changed, then we'd have a broken function
anyway.

The only issue is that (before writing this mail) I expected that a VIEW
would either throw up errors or would work without fail regardless of a
dump / reload scenario (and yes likewise, I expected a function to not
exhibit that behaviour). So I actually did a search for this field in the
functions, and relied on PG to throw up errors for a VIEW. Probably I got
stumped there.

But that aside, the only question left here is that if a token is not
exposed by a VIEW, would not an automatic search / replace have done the
job ? Theoretically speaking, having a known case where a VIEW's definition
not working whereas the VIEW working is flawed (frankly worrying, now to
think of it).

Thanks nonetheless. Guess I need some daily dump/reload scripts for all
projects right away.
--
Robins Tharakan

On Thu, Feb 9, 2012 at 9:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robins Tharakan <robins(dot)tharakan(at)gmail(dot)com> writes:
> > This is a case where I changed the name of a field in a table that a VIEW
> > referred to, but the VIEW definition still points to the old name of the
> > field. The surprise is that the VIEW still works (with live data).
>
> Specifically, you mean that you had a column referenced by a USING
> clause, and then you renamed it, right? We've had discussions about
> that in the past, and concluded that the SQL spec is just fundamentally
> broken here. If you rename one of the input columns, there is no way
> to represent a view that (used to) use USING without changing the view's
> behavior -- in particular, the set of columns exposed by a join with
> USING is different from the set of columns exposed without that, so
> simply replacing the USING with an ON clause wouldn't get the job done.
> So our view-dumping code just doesn't bother to try. You'll get
> something that still says USING, but of course this won't work when the
> view definition is dumped and reloaded. This is not the fault of the
> view: if you'd not used a view but just issued the equivalent join
> directly as a SQL query, the rename would still have broken your
> application.
>
> Our internal representation doesn't depend on the name-matching aspect
> of USING, so the view continues to work as before, so long as you don't
> dump and reload. But it looks wrong if you dump the definition as SQL.
> That's basically because SQL lacks a way to represent the situation.
>
> The best idea I've heard for fixing it is to invent a non-standard
> syntax that could represent a USING clause matching two dissimilarly
> named columns, say USING (foo = bar AS baz), and then use that syntax
> when dumping a view if the column names don't match. Nobody's worked
> out the idea in full detail, though, let alone implemented it; it's not
> really clear it's worth the trouble.
>
> regards, tom lane
>