Fwd: [BUGS] pg_dump search path issue - Mailing list pgsql-general

I posted this to pgbugs a little while ago and I couldn't get much
traction. I'm hoping that someone in the general list may be able to
help me with this. Namely, this question:

For contrib functions - is there even a way for embedded queries in
functions to be auto-coded to the correct schema when you run CREATE
EXTENSION? I know the command (CREATE EXTENSION) takes a schema name,
but how does that get added to embedded queries? Is there a best
practice for this?

---------- Forwarded message ----------
From: Elijah Zupancic <elijah@zupancic.name>
Date: Wed, Feb 4, 2015 at 6:01 PM
Subject: Re: [BUGS] pg_dump search path issue
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@postgresql.org


Hi Tom,

Thanks for your reply. The functions in question are user defined
inasmuch as they are from the contrib package.

Here is a sample of one of the errors:

psql:./prod-db-2015-02-04.sql:1688: ERROR:  function
cube_distance(public.earth, public.earth) does not exist
LINE 1: SELECT sec_to_gc(cube_distance($1, $2))
                         ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY:  SELECT sec_to_gc(cube_distance($1, $2))
CONTEXT:  SQL function "earth_distance" during inlining
SELECT 0

This happens when pg_dump tries to recreate the definition of a
materialized view.

When I look at the dump, I see it sets a search path like: SET
search_path = aggregator, pg_catalog;

Then it goes a ways and creates a bunch of tables. Then it gets to the
materialized view. Upon closer inspection, the materialized view is
then calling the public.earth_distance function properly. However, the
earth distance function is calling an unqualified (missing the schema
specifier) function like so:

SELECT sec_to_gc(cube_distance($1, $2))

So, I'm with you - this is a problem with how the functions were
created. They should specify the schema so that they work correctly.
However, for contrib functions - is there even a way for them to be
auto-coded to the correct schema when you run CREATE EXTENSION? I know
the command takes a schema name, but how does that get added to
embedded queries? If there, is a best practice for this, I can take a
stab at patching earthdistance.

Thanks,
Elijah Zupancic

On Wed, Feb 4, 2015 at 5:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Elijah Zupancic <elijah@zupancic.name> writes:
>> In the SQL dump, you will notice that the SET search_path = xxx values
>> will often not include the public schema which holds the functions
>> needed to properly recreate tables that depend on extensions.
>
> All the cases I've seen of this involve user-defined functions that are
> broken, often dangerously so.  A function should not assume that it's
> being called with any particular search_path; if it's intended for use in
> a multi-schema database, good practice is to either explicitly qualify
> names or use a SET clause to force the search_path to be what it expects.
>
>> It seems like the code that generates the SET search_path should check
>> to see if any of the objects it is dumping depend on functions that
>> use the public schema.
>
> If that didn't involve solving the halting problem, we might try to do
> it.  But for better or worse, functions in Postgres are mostly black boxes
> so far as callers are concerned.  It's not possible for pg_dump to know
> that some function has an expectation of being invoked with a particular
> search path.
>
>                         regards, tom lane



--
-Elijah


--
-Elijah