Nicola Contu wrote: > we recently moved from postgres 9.6.6 to 10.4 > > We perform a pg_dump in production to restore daily in a preprod env. > This process used to work perfectly, but now we have a tiny problem. > > We first restore data, we perform a vacuum and then we restore matviews. > Restoring matviews now we have : > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 23090; 0 1912379424 MATERIALIZED VIEW DATA matview_vrs_request_sla postgres > pg_restore: [archiver (db)] could not execute query: ERROR: relation "all_days" does not exist > LINE 3: from all_days > ^ > QUERY: > select count(*)::numeric > from all_days > where (("date" between $2::date and $1::date) or ("date" between $1::date and $2::date)) > and dow not in (0,6) > > CONTEXT: SQL function "bdays" during inlining > Command was: REFRESH MATERIALIZED VIEW public.matview_vrs_request_sla; > > The relation is there, in fact if I go there when I get in to the office, the same command works. > > I'm not sure why it does not work here, this seems really strange to me.
I suspect that it has to do with the recent security fixes around the "public" schema.
Try to ALTER the materialized view so that it refers to "public.all_days" rather than "all_days".