Thread: pg_restore can't re-mat mat view that uses a function which depends on another mat view
pg_restore can't re-mat mat view that uses a function which depends on another mat view
From
Wells Oliver
Date:
Odd one here, but after restoring a database in parallel, I notice a few mat views are unmaterialized, e.g.
pg_restore: from TOC entry 21012; 0 1060827831 MATERIALIZED VIEW DATA vmwm_ytd_p postgres
pg_restore: error: could not execute query: ERROR: materialized view "vwm_info" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
CONTEXT: SQL function "age" statement 1
Command was: REFRESH MATERIALIZED VIEW stats.vwm_ytd_p;
pg_restore: error: could not execute query: ERROR: materialized view "vwm_info" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
CONTEXT: SQL function "age" statement 1
Command was: REFRESH MATERIALIZED VIEW stats.vwm_ytd_p;
The reason being is that vwm_ytd_p uses a function age() which queries from vwm_info, which has not yet been materialized. In fact, it is being materialized in parallel in another job, so not yet available.
Anything to do here? Weird sequence of dependencies, and I'm guessing pg_restore can't quite keep track of it since the dependency is buried in a function call.
Not a dreadful situation if I have to re-mat some views after the restore, but curious if I can do something different.
Re: pg_restore can't re-mat mat view that uses a function which depends on another mat view
From
Tom Lane
Date:
Wells Oliver <wells.oliver@gmail.com> writes:
> Odd one here, but after restoring a database in parallel, I notice a few
> mat views are unmaterialized, e.g.
> ...
> The reason being is that vwm_ytd_p uses a function age() which queries from
> vwm_info, which has not yet been materialized. In fact, it is being
> materialized in parallel in another job, so not yet available.
Right.
> Anything to do here?
Not much we can do today. User-defined functions are black boxes,
so pg_dump can't see what their innards might reference.
v14 will have the ability to declare SQL functions in something
closer to SQL-spec syntax, in which the body of the function will
be parsed at definition time. With that style of function, pg_dump
*does* see the dependencies so this sort of thing should work.
That approach is not without some downsides, but it sounds like it'd
improve your case here.
regards, tom lane