Re: postgres_fdw error

Thanks Tom, that seemed to get me farther. I issued this on the remote db:
ALTER FUNCTION utils.concat_artists(character varying) SET
search_path=public,utils;

And now my test errs on a different function, which probably needs the same:

piction(at)piction_transit> select * from piction.bampfa_test_fv ;
ERROR: relation "collectionobjects_common" does not exist
CONTEXT: Remote SQL command: SELECT objectcsid, idnumber, collections FROM
piction.bampfa_test_v
PL/pgSQL function utils.get_first_blobcsid(character varying) line 7 at SQL
statement

In case this breaks stuff, what is the default search_path for functions if
they are created without one set explicitly?
"$user"?

Adam

On Fri, Aug 14, 2015 at 2:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Adam FUCHS <atman(at)berkeley(dot)edu> writes:
> > Thanks Korry, how would I check the search_path that is being used by the
> > FDW user?
>
> postgres_fdw always does "SET search_path = pg_catalog" when opening the
> connection. Probably this needs to be documented, since it's user-visible
> if you try to attach a foreign table to a remote view.
>
> Anyway the short answer is that you should fully schema-qualify references
> in functions used by such a view, or else attach "SET search_path" options
> to the functions.
>
> regards, tom lane
>

--

Adam Fuchs

Database Administrator

UC Berkeley - Information Services & Technology

2195 Hearst Ave., Berkeley, CA 94120

510-664-4354