Re: postgres_fdw error - Mailing list pgsql-admin

From Adam FUCHS
Subject Re: postgres_fdw error
Date
Msg-id CAG=eVo14ghzQPH4SDCmHsDnAAumih8RWmdSqQ7o-taSkPG++pQ@mail.gmail.com
Whole thread Raw
In response to Re: postgres_fdw error  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: postgres_fdw error
List pgsql-admin
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@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@sss.pgh.pa.us> wrote:
Adam FUCHS <atman@berkeley.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

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: postgres_fdw error
Next
From: Tom Lane
Date:
Subject: Re: postgres_fdw error