Thread: BUG #15282: Materialized view with transitive TYPE dependency failsrefresh using pg_restore and psql

The following bug has been logged on the website:

Bug reference:      15282
Logged by:          Keith Hickey
Email address:      kwhickey@gmail.com
PostgreSQL version: 10.4
Operating system:   RHEL7, Mac OSX
Description:

Scenario:
A materialized view runs a select query that uses a function on one of its
projected columns which internally uses an enum TYPE to map the column value
to an enum value.

Action and Resulting Error:
Trying to restore a database setup in the above way will fail with error:
ERROR:  type "<type_name>" does not exist

Reproduction:
This can be reproduced with a "plain" (.sql) dump of the database using
pg_dump and then restoring the schema and data by running psql against that
dump. It can also be reproduced when using a pg_dump --format=directory dump
of the database, and restoring that dump using pg_restore.

Failed Workarounds:
Lots of things, that didn't work.
(1) using --list to create a file to use with pg_restore and --use-list that
excluded MATERIALIZED VIEW DATA initially. Then doing this again to include
only MATERIALIZED VIEW DATA in a 2nd pg_restore call. It still resulted in
the same error.
(2) Doing the same as above but excluding MATERIALIZED VIEW (create) and
MATERIALIZED VIEW DATA statements initially, and then running only those in
a 2nd pg_restore call
(3) Doing the same as above, creating the database manually, then using
pg_restore --list / --use-list to create only TYPES, then calling pg_restore
to do everything but MATERIALIZED VIEW DATA, then finally using pg_restore
to do MATERIALIZED VIEW DATA only. Same result. Also separating out the
functions did not help either.
(4) Doing pg_restore in 3 phases using the --section flag: run once with
--section=pre-data, a second time with --section=data, a third time with
--section=post-data. Failed the 3rd time with the same error.
(5) Setting up default privileges in the database to GRANT ALL PRIVILEGES on
all objects (including TYPEs) before running pg_restore. 
(6) Restoring a plain sql dump of the database with psql. This failed so
long as the REFRESH MATERIALIZED VIEW call was in the same psql invocation
as the TYPE creation.

Successful Workaround:
Exclude the MATERIALIZED VIEW DATA entries from the pg_restore, do the
pg_restore, then follow that with a psql call to REFRESH each materialized
view

Example Database Reproducing this Error:
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.4
-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

CREATE TYPE public.customer_tier AS ENUM (
    'gold',
    'silver',
    'bronze'
);

CREATE FUNCTION public.get_customer_tier(total_purchase numeric) RETURNS
public.customer_tier
    LANGUAGE plpgsql
    AS $$ declare declare result text; begin if total_purchase < 10 then
result='bronze'; elsif total_purchase < 100 then result='silver'; else
result='gold'; end if; return result::customer_tier; end;$$;

SET default_tablespace = '';
SET default_with_oids = false;

CREATE TABLE public.customer (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.product (
    id integer NOT NULL,
    name text,
    price numeric DEFAULT 0.00
);

CREATE TABLE public.purchase (
    id integer NOT NULL,
    product_id integer NOT NULL,
    customer_id integer NOT NULL,
    quantity integer DEFAULT 1
);

CREATE MATERIALIZED VIEW public.customer_rewards AS
 SELECT c.id AS customer_id,
    sum(((x.quantity)::numeric * p.price)) AS total_purchase,
    public.get_customer_tier(sum(((x.quantity)::numeric * p.price))) AS
customer_tier
   FROM ((public.purchase x
     JOIN public.customer c ON ((c.id = x.customer_id)))
     JOIN public.product p ON ((p.id = x.product_id)))
  GROUP BY c.id
  WITH NO DATA;

COPY public.customer (id, name) FROM stdin;
1    c1
2    c2
\.

COPY public.product (id, name, price) FROM stdin;
1    p1    1.00
2    p2    2.00
3    p3    3.00
\.

COPY public.purchase (id, product_id, customer_id, quantity) FROM stdin;
1    1    1    1
2    2    2    2
3    3    1    3
4    2    1    2
\.

REFRESH MATERIALIZED VIEW public.customer_rewards;

--
-- PostgreSQL database dump complete
--

pg_dump Commands:
〉pg_dump --dbname
postgresql://root:password@127.0.0.1:5432/restore_matview_bug --jobs 16
--compress=6 --format=directory --file restore_matview_bug_dump
--no-privileges --no-owner --verbose
〉pg_dump --dbname
postgresql://root:password@127.0.0.1:5432/restore_matview_bug --file
restore_matview_bug_dump.sql --no-privileges --no-owner --verbose

Failed Restore Commands and Errors:
(1) pg_restore
〉pg_restore --jobs 16 --dbname
postgresql://root:password@127.0.0.1:5432/restore_matview_bug_test --verbose
--exit-on-error restore_matview_bug_dump/
...
pg_restore: launching item 2433 MATERIALIZED VIEW DATA customer_rewards
pg_restore: creating MATERIALIZED VIEW DATA "public.customer_rewards"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2433; 0 16411 MATERIALIZED
VIEW DATA customer_rewards root
pg_restore: [archiver (db)] could not execute query: ERROR:  type
"customer_tier" does not exist
LINE 1: SELECT result::customer_tier
                       ^
QUERY:  SELECT result::customer_tier
CONTEXT:  PL/pgSQL function public.get_customer_tier(numeric) line 1 at
RETURN
    Command was: REFRESH MATERIALIZED VIEW public.customer_rewards;



pg_restore: [parallel archiver] a worker process died unexpectedly

(2) psql
〉psql --dbname
postgresql://root:password@127.0.0.1:5432/restore_matview_bug_test --file
restore_matview_bug_dump.sql 
psql:restore_matview_bug_dump.sql:161: ERROR:  type "customer_tier" does not
exist
LINE 1: SELECT result::customer_tier
                       ^
QUERY:  SELECT result::customer_tier
CONTEXT:  PL/pgSQL function public.get_customer_tier(numeric) line 1 at
RETURN

Possibly Similar Issues:
May be related to Bug #13870 and #13907:  

https://www.postgresql.org/message-id/flat/20160115175546.2968.6033%40wrigleys.postgresql.org#20160115175546.2968.6033@wrigleys.postgresql.org

"But the issue is that CREATE MATERIALIZED VIEW ... WITH NO DATA
tries to plan and execute the query, not just store it, which entirely
defeats pg_dump's attempt to avoid hidden dependencies.  We need a less
chintzy solution to handling this variant of CREATE MATERIALIZED VIEW."

And here:
http://www.postgresql-archive.org/BUG-13907-Restore-materialized-view-throw-permission-denied-td5885472.html

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> A materialized view runs a select query that uses a function on one of its
> projected columns which internally uses an enum TYPE to map the column value
> to an enum value.

> Action and Resulting Error:
> Trying to restore a database setup in the above way will fail with error:
> ERROR:  type "<type_name>" does not exist

If this started happening recently, the problem is likely that the type
is not in the restrictive search_path that dump/restore now uses.  You
could possibly fix it just by schema-qualifying the type name in the
function body.  However, that may only let you get as far as the next
failure of the same kind.  A better fix is to add a "SET search_path"
clause to the function definition so that it works independently of
what the caller's search path is.

            regards, tom lane

Keith Hickey <kwhickey@gmail.com> writes:
> Ok, that worked. Such a simple change.
> i.e. in the function definition, changed the return statement from
> return result::customer_tier;
> to
> return result::public.customer_tier;

Check.

> However --
> When running the restore as user "root", with the default search path
> (which was the case): "$user", public

pg_restore doesn't use the default search path anymore.

            regards, tom lane

Keith Hickey <kwhickey@gmail.com> writes:
> But then I saw this config setting embedded at the top of the plain sql
> dump file:

> SELECT pg_catalog.set_config('search_path', '', false);

Right, that's what implements the change in behavior.

> So it looks like pg_dump somehow embeds clearing the search_path into its
> dumps. There doesn't seem to be a way in pg_dump to tell it to do this or
> not to do this.

I argued at the time that there should be an option to let it run with
some other search path, with the security implications being on the user's
head to worry about.  I lost that argument, but I still think that we're
going to have to provide such a feature.  Lately we've been seeing about
one complaint a week about this, as uptake of the locked-down versions
increases.

            regards, tom lane