Thread: Materialized view breaks pg_restore

Materialized view breaks pg_restore

From

David Wheeler

Date:

22 March 2019, 06:15:17

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

E dwheeler@dgitsystems.com
D +61 3 9663 3554 W http://dgitsystems.com
Level 4, 313 La Trobe St, Melbourne VIC 3000.

Attachment

image001.png

Re: Materialized view breaks pg_restore

From

Adrian Klaver

Date:

22 March 2019, 07:10:24

On 3/21/19 8:15 PM, David Wheeler wrote:
> 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.

Yes:

https://www.postgresql.org/about/news/1834/

The link in the above to the explanation is broken. The correct link is:

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d2aed664ee8271fd6c721ed0aa10168cda112ea

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5770172cb0c9df9e6ce27c507b449557e5b45124
> 
> 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
> *
> 
> 
> 
> 
> E dwheeler@dgitsystems.com <mailto:dwheeler@dgitsystems.com>
> D +61 3 9663 3554  W http://dgitsystems.com
> Level 4, 313 La Trobe St, Melbourne VIC 3000.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com