pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pg_restore can't re-mat mat view that uses a function which depends on another mat view 2+ messages / 2 participants [nested] [flat]
* pg_restore can't re-mat mat view that uses a function which depends on another mat view @ 2021-06-13 22:15 Wells Oliver <wells.oliver@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Wells Oliver @ 2021-06-13 22:15 UTC (permalink / raw) To: pgsql-admin 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; 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. -- Wells Oliver wells.oliver@gmail.com <wellsoliver@gmail.com> ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_restore can't re-mat mat view that uses a function which depends on another mat view @ 2021-06-14 06:00 Tom Lane <tgl@sss.pgh.pa.us> parent: Wells Oliver <wells.oliver@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2021-06-14 06:00 UTC (permalink / raw) To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-06-14 06:00 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-06-13 22:15 pg_restore can't re-mat mat view that uses a function which depends on another mat view Wells Oliver <wells.oliver@gmail.com> 2021-06-14 06:00 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox