Re: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view

On Tue, Jun 7, 2011 at 3:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Stark <gsstark(at)gmail(dot)com> writes:
>> On Jun 3, 2011 4:20 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I'm inclined to write this off as "so don't do that".  There'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=b.a) or ON (a.a=b.a as a)
>
> 1. "Nonstandard syntax" is widely seen as "vendor lock-in".  I 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.  For 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.  (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 wouldn'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.

--
greg