Re: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view - Mailing list pgsql-bugs
From | Greg Stark |
---|---|
Subject | Re: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view |
Date | |
Msg-id | BANLkTimaG0dD13SD-t5+MFPYkWAyB2OjSw@mail.gmail.com Whole thread Raw |
In response to | Re: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Re: BUG #6050: Dump and restore of view after a
schema change: can't restore the view
|
List | pgsql-bugs |
On Tue, Jun 7, 2011 at 3:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Stark <gsstark@gmail.com> writes: >> On Jun 3, 2011 4:20 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >>> I'm inclined to write this off as "so don't do that". =A0There's nothing >>> that pg_dump can do to make this work: it has to use the USING syntax >>> for the join, and that doesn't offer any way to qualify the column name >>> on just one side. > >> There's nothing stopping us from adding a nonstandard syntax to cover >> precisely the information needed to resolve this case when dumping. > >> For example we could support USING (a.a=3Db.a) or ON (a.a=3Db.a as a) > > 1. "Nonstandard syntax" is widely seen as "vendor lock-in". =A0I don't > think that people would appreciate such a fix, especially for an issue > so obscure that we've never seen it before. Well our dumps are already not going to be loadable on other SQL implementations. But yes, it's non-ideal which is why I was tempted to do it only when needed. > 2. I don't believe your proposal covers all cases. =A0For instance, there > are cases where there is no valid qualified name for a column, ie, it's > a merged column from an alias-less JOIN. =A0(The existence of such cases > is another reason why USING sucks, but I digress.) Hm, will have to think about that. >> We could use it only in this case where there's ambiguity too so it woul= dn't >> clutter people's dumps. > > No, because the problem case is where ambiguity gets added after the > fact. I think this is an interesting point. It seems there are two cases, one of which I think is much worse than the other. It sounds like you're concerned about someone dumping the view definition, then doing an alter table on one of the underlying tables, then trying to reload their old view definition. As you pointed out later there are lots of ways the alter table could cause the view to no longer work. Many of them involve USING which justifies your complaint that it's fragile but of course it could be as simple as the alter table dropping a needed column... The other case seems worse to me: someone creates the view, does the alter table, then dumps the database. They don't make any further database modifications, the dump is unloadable as it was dumped. That is, pg_dump produced an unloadable dump right off the bat. A lot of work has gone into making pg_dump/pg_restore guarantee that they'll always produce a copy of the database, even if you've done odd things like change the lower bounds of your arrays. A lot of this was from before the days of PITR when pg_dump/pg_restore was the *only* backup option and it was considered absolutely essential that they always work. But even today I think it's still a goal that pg_dump always dump a loadable database. Of course it won't always load in a different context but if you restore it in the right context or restore the whole database it ought to work. I had in mind for pg_dump to decide to use the non-standard syntax iff it was necessary at dump time. That doesn't protect against someone changing the table referenced after the dump but that's fine by me. At least when it was dumped the sql would have loaded to produce the same view as was dumped. --=20 greg
pgsql-bugs by date: