felix.buenemann@gmail.com writes:
> pg_dump dumps materialized views in the wrong order, if the view is
> alphabetically sorted before a table that it references indirectly through a
> stored procedure in sql language.
> The reason seems to be that pg_dump does not know about the indirect
> dependency of the matview, so it doesn't reorder the tables/matviews
> properly.
It's mathematically impossible to guarantee that pg_dump could handle such
things --- maybe it could do simple cases, given enormously more knowledge
about PL functions than it actually possesses, but a full solution would
be equivalent to solving the halting problem. So we're not going to try
to fix this in pg_dump.
Having said that, I wonder why CREATE MATERIALIZED VIEW seems to insist on
running the planner and even executing the query when told WITH NO DATA.
If it were satisfied to store the view definition and quit, we'd not
be seeing a failure here.
> Problematic dump from error above:
> https://gist.github.com/056f157a200e334dc5b0
> Minimal testcase (thanks to RhodiumToad on IRC):
> http://pgsql.privatepaste.com/cc80393e25
BTW, links to pages that will probably not be there next month are
entirely inadequate documentation for bug reports. For the sake
of the archives, here's the test case:
create table t1 (a integer);
create function f1() returns integer language sql stable as
$f$ select a from t1 limit 1; $f$;
create table t2 (b integer);
create materialized view m1 as select f1(), b from t2;
Dumping and restoring this causes the reported failure, since
m1 is restored before t1.
regards, tom lane