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 <t...@sss.pgh.pa.us> wrote: > Robins Tharakan <robins.thara...@gmail.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 >
