pg_restore can't re-mat mat view that uses a function which depends on another mat view

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: pg_restore can't re-mat mat view that uses a function which depends on another mat view
Date: 2021-06-13 22:15:59
Message-ID: CAOC+FBU-2gHrbP+7QOWAbjTFP6=RGjckY7rTj80f=Rh9=+r67A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message pramod kg 2021-06-14 03:43:31 Re: PostgreSQL SSL params
Previous Message Tom Lane 2021-06-13 18:03:38 Re: pg_restore: warning: invalid creation date in header