Re: Is there a way around function search_path killing SQL function inlining?

On Thu, Mar 10, 2016 at 3:21 AM, Regina Obe <lr(at)pcorp(dot)us> wrote:
> When you back up the database, it would create a backup with this line:
>
> SET search_path = public, pg_catalog;
> --your create materialized view here
>
> When you restore even if your database has search_paths set, your materialized view will not come back and will error out with:
>
> ERROR: function _helper(box, box) does not exist
> LINE 2: SELECT $1 && $2 AND _helper($1,$2) = 0;
> ^
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY:
> SELECT $1 && $2 AND _helper($1,$2) = 0;

Hmm. The meaning of funcs.inline depends on the search_path, not just
during dump restoration but all the time. So anything uses it under a
different search_path setting than the normal one will have this kind
of problem; not just dump/restore.

I don't have a very good idea what to do about that.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company