Hi,
We’re regularly having an issue when restoring dumps of our databases like this
[exec] CREATE DATABASE "testRestore";
[exec] pg_restore: [archiver (db)] Error while PROCESSING TOC:
[exec] pg_restore: [archiver (db)] Error from TOC entry 15728; 0 43798
MATERIALIZED VIEW DATA fact_tax dbowner@smile-DEV_2019-03-22T09-32-13.338
[exec] pg_restore: [archiver (db)] could not execute query: ERROR:
relation "basic" does not exist
[exec] LINE 1: SELECT chargegst from basic where uid = _account
[exec] ^
[exec] QUERY: SELECT chargegst from basic where uid = _account
[exec] CONTEXT: PL/pgSQL function ar.categorise_gst(integer,integer,date)
line 7 at IF
[exec] Command was: REFRESH MATERIALIZED VIEW cube02.fact_tax;
The issue is that there’s a mat view that refers to a plpgsql function that
refers to a table in the public schema, but it’s not qualified. When we create
the materialized view, and when we refresh it, the table is in the search path.
But when restoring from a dump, it’s not.
Is this the desired behaviour? This is an issue for us because
pg_dump/pg_restore is part of our disaster recovery process, so if we find this
problem during restore it will mean more downtime.
PG version 9.5.14. I’m attempting to find out now if it’s an issue in more
recent versions also.
TIA
David Wheeler
Software developer
[cid:2C4D0888-9F8B-463F-BD54-2B60A322210C]
E [email protected]<mailto:[email protected]>
D +61 3 9663 3554 W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.
