Re: pg_dump search path issue

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Elijah Zupancic <elijah(at)zupancic(dot)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)