Обсуждение: BUG #12766: Dump/Load of Materialized View with inlined SQL function fails
BUG #12766: Dump/Load of Materialized View with inlined SQL function fails
От
felix.buenemann@gmail.com
Дата:
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