Обсуждение: BUG #12766: Dump/Load of Materialized View with inlined SQL function fails

The following bug has been logged on the website:

Bug reference:      12766
Logged by:          Felix Buenemann
Email address:      felix.buenemann@gmail.com
PostgreSQL version: 9.4.1
Operating system:   Mac OS X 10.10.2
Description:

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.

This leads to errors like this on restore:

psql:foo.sql:43: ERROR:  relation "foo" does not exist
LINE 1:  SELECT label FROM foo WHERE id = value;
                           ^
QUERY:   SELECT label FROM foo WHERE id = value;
CONTEXT:  SQL function "foo_label" during inlining

Problematic dump from error above:
https://gist.github.com/056f157a200e334dc5b0

Minimal testcase (thanks to RhodiumToad on IRC):
http://pgsql.privatepaste.com/cc80393e25

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