Re: VIEW still referring to old name of field - Mailing list pgsql-general
| From | Robins Tharakan |
|---|---|
| Subject | Re: VIEW still referring to old name of field |
| Date | |
| Msg-id | CACk=U9NY3WCecCyKQsq9Oeti4fs0-tXiCTmxnaTg30Tpy8j2Rw@mail.gmail.com Whole thread Raw |
| In response to | Re: VIEW still referring to old name of field (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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@sss.pgh.pa.us> wrote:
Robins Tharakan <robins.tharakan@gmail.com> writes:Specifically, you mean that you had a column referenced by a USING
> 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).
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
pgsql-general by date: