Re: pg_dump search path issue

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