Thread: pg_dump search path issue

PostgreSQL Version: 9.3.5
Operating Systems Tested: Linux, SmartOS

1. Create extensions in the public schema.
2. Create additional schemas.
3. Create tables that use datatypes from the extensions that depend on
extension functions.
4. Run pg_dump or pg_dumpall.
5. Attempt to restore from the SQL dump.

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.

For now we have a work around that is very ugly. We just pipe the SQL
output to sed -e 's/^\(SET search_path =.*\);/\1, public;/' and
everything seems to work just fine.

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.

I would love to see a fix for this in future PostgreSQL versions, so
please let me know what I can do to help.

Thank you,
Elijah Zupancic
elijah@zupancic.name

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

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

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 > 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.

 Tom> All the cases I've seen of this involve user-defined functions
 Tom> that are broken, often dangerously so.

Just found (via a report on irc) a case that doesn't involve functions:

create extension hstore;
create schema foo
  create table t1 (a hstore, b integer)
  create table t2 (a hstore, c integer)
  create view v1 as select * from t1 join t2 using (a);

dump/restore gives:

ERROR:  operator does not exist: public.hstore = public.hstore
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT:  CREATE VIEW v1 AS
         SELECT t1.a,
            t1.b,
            t2.c
           FROM (t1
             JOIN t2 USING (a));

(the reporting user was actually using ip4r's ip4 type, which makes more
sense as a join column than hstore does, but the idea is the same)

--
Andrew (irc:RhodiumToad)

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> Just found (via a report on irc) a case that doesn't involve functions:

> create extension hstore;
> create schema foo
>   create table t1 (a hstore, b integer)
>   create table t2 (a hstore, c integer)
>   create view v1 as select * from t1 join t2 using (a);

> dump/restore gives:

> ERROR:  operator does not exist: public.hstore = public.hstore

Meh.  We've discussed this case before, though I'm having a hard time
finding it in the archives tonight.  I'm of the opinion that the real
problem here is that JOIN USING uses a name-based operator lookup at all.
Even disregarding the question of whether what you want is in the search
path, it's operating purely on faith that an operator named '=' will do
something appropriate.  What it ought to be doing (and likewise CASE,
IS DISTINCT FROM, and anything else that's implicitly assuming that '='
is what to use) is looking for an operator associated with an appropriate
default btree opclass.

In the last go-round, I remember worrying that cross-type operators might
be a problem to identify this way, but a look in the system catalogs
doesn't find anything that seems like an issue.  I'm envisioning a lookup
rule like this:

1. Find a default btree opclass that accepts the lefthand data type,
preferably exactly but if not that then binary-compatibly; fail if not
present or more than one binary-compatible candidate.  (This is the same
rule used to identify how to build a btree index by default.)

2. Likewise for the righthand datatype.

3. These opclasses must belong to the same operator family, and there
must be a suitable equality operator in that opfamily, else fail.

AFAICT, the operators that would be selected by the existing rule
but not by this rule are just these:

db1=# select o.oid::regoperator, oprcode, cl.opcfamily, cr.opcfamily from pg_operator o
left join pg_opclass cl
  on cl.opcintype=o.oprleft and cl.opcmethod = 403 and cl.opcdefault
left join pg_opclass cr
  on cr.opcintype=o.oprright and cr.opcmethod = 403 and cr.opcdefault
where
  oprname = '=' and
  (cl.opcfamily is null or
   cr.opcfamily is null or
   cl.opcfamily != cr.opcfamily);
           oid            |   oprcode    | opcfamily | opcfamily
--------------------------+--------------+-----------+-----------
 =(xid,xid)               | xideq        |           |
 =(xid,integer)           | xideqint4    |           |      1976
 =(cid,cid)               | cideq        |           |
 =(int2vector,int2vector) | int2vectoreq |           |
 =(aclitem,aclitem)       | aclitemeq    |           |
 =(box,box)               | box_eq       |           |
 =(path,path)             | path_n_eq    |           |
 =(circle,circle)         | circle_eq    |           |
 =(lseg,lseg)             | lseg_eq      |           |
 =(line,line)             | line_eq      |           |
(10 rows)

The first five of these are internal datatypes anyway; the other five are
fine examples of cases where you actively *don't want* the system silently
relying on them for JOIN/CASE/etc.  Three of those aren't equality at all
by any sane definition, and the other two are fuzzy.

So I think this would be a good change and we should just bite the bullet
and do it...

            regards, tom lane