Materialized view breaks pg_restore - Mailing list pgsql-general
Hi,
E dwheeler@dgitsystems.com
D +61 3 9663 3554 W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.
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
Software developer
E dwheeler@dgitsystems.com
D +61 3 9663 3554 W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.
Attachment
pgsql-general by date: