Re: BUG #15282: Materialized view with transitive TYPE dependency fails refresh using pg_restore and psql

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;

However --

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

When it finds an unqualified database object referenced, shouldn't it
search those schemas in that order, and eventually map the unqualified type
"customer_tier" to "public.customer_tier" when it finds it in the "public"
schema (after looking and not finding it in the "root" schema)?

Is there an underlying issue with the way the search path is searched?
Maybe just for types? Or maybe for types used in a type-cast?

Thanks,
Keith

On Tue, Jul 17, 2018 at 1:11 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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
>