Re: Re: BUG #6050: Dump and restore of view after a schema change: can't restore the view - Mailing list pgsql-bugs

I assume this should _not_ be added as a TODO.

---------------------------------------------------------------------------

Greg Stark wrote:
> 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". ?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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +