BUG #12766: Dump/Load of Materialized View with inlined SQL function fails

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs