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

Ah. That'll do it.

I was going to ask: "Is this also the case for psql, where the default
search_path is not used" (... because using psql to restore a plain sql
dump also failed).

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);

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. Regardless, some mention of search_path in the pg_dump or
pg_restore docs would be helpful hint for this type of troubleshooting.

Thanks,
Keith

On Wed, Jul 18, 2018 at 11:17 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Keith Hickey <kwhickey(at)gmail(dot)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
>