Re: postgres_fdw error

Thanks Korry, how would I check the search_path that is being used by the
FDW user?

On the remote( source ):
piction_ro(at)bampfa_domain_bampfa> show search_path;
search_path
----------------
"$user",public

on the target:

piction(at)piction_transit> show search_path
piction_transit-> ;
search_path
----------------
"$user",public
(1 row)

I was able to create a test view that selects from the table in the error
message ( collectionobjects_common )
and that works with no explict setting of search path. I suspect it has
something to do with the function that is called in the original view.

On source ( remote ):
create or replace view piction.test_v as
SELECT
c.id,
c.physicaldescription
from
collectionobjects_common c
limit 10
;
alter view piction.test_v owner to piction;

grant select on piction.test_v to piction_ro;

On target:

\c piction_transit
create foreign table piction.test_fv
(
id character varying(36),
physicaldescription text
)
server bampfa_dev_server
options ( schema_name 'piction', table_name 'test_v');
alter foreign table piction.test_fv owner to piction;

piction(at)piction_transit> select * from test_fv limit 4;
id |
physicaldescriptio
n
--------------------------------------+-----------------------------------------
------------------------
2d40e6eb-a4f7-402b-ab06-f3fb57fb6e5d | Pencil and watercolor on paper
439480f4-7cc6-4be8-b339-722e64c0cb2f | Handwritten text on brown paper. 16
page
s. Photocopy. 16 pages.
4757f5c8-2175-47cb-b0f4-6c2bbd4454ed | etching
6983dbc4-5a67-41c0-8e46-f1f08f12557b | gelatin silver print
(4 rows)

On Fri, Aug 14, 2015 at 12:28 PM, Korry Douglas <
korry(dot)douglas(at)enterprisedb(dot)com> wrote:

> Are all references schema-qualified?
>
> If not, have you checked the search_path used when the FDW connects to the
> remote server?
>
>
> -- Korry
>
>
> I am struggling with a FDW between two 9.4.3 postgres databases.
> I am refering to the remote db as the source and the db with the fdw as
> the target.
>
> The connected user has the ability to query the source table on the source
> db successfully so I am assuming all the required grants are there.
>
> When querying from the target system, I get a "relation does not exist"
> error.
> The hitch is that the source table is actually a view that calls other
> views, which in turn contain functions. When I used a basic table on the
> target the fdw worked without error.
>
>
> config:
>
> source:
> select current_user;
> current_user
> --------------
> piction_ro
> (1 row)
>
> piction_ro(at)bampfa_domain_bampfa> select count(*) from
> piction.bampfa_metadata_v;
> count
> -------
> 21309
> (1 row)
>
> target:
> List of foreign servers
> -[ RECORD 1
> ]--------+-------------------------------------------------------------------------
> Name | bampfa_dev_server
> Owner | postgres51
> Foreign-data wrapper | postgres_fdw
> Access privileges |
> Type |
> Version |
> FDW Options | (host 'source_host', port 'XXXX', dbname
> 'bampfa_domain_bampfa')
> Description |
>
> List of user mappings
> -[ RECORD 1 ]--------------------------------------------
> Server | bampfa_dev_server
> User name | piction
> FDW Options | ("user" 'piction_ro', password '***')
>
> List of foreign tables
> -[ RECORD 1 ]--------------------------------------------------------
> Schema | piction
> Table | bampfa_metadata_fv
> Server | bampfa_dev_server
> FDW Options | (schema_name 'piction', table_name 'bampfa_metadata_v')
> Description |
>
> piction(at)piction_transit> select * from piction.bampfa_metadata_fv limit 4;
> ERROR: relation "collectionobjects_common" does not exist
> CONTEXT: Remote SQL command: SELECT objectcsid, idnumber,
> sortobjectnumber, artistcalc, artistorigin, title, datemade, site,
> itemclass, materials, measurement, fullbampfacreditline, copyrightcredit,
> photocredit, subjects, collections, periodstyles, artistdates, caption,
> tags, permissiontoreproduce, acquisitionsource, legalstatus, updatedat FROM
> piction.bampfa_metadata_v
> PL/pgSQL function utils.concat_artists(character varying) line 7 at SQL
> statement
>
> But if we are on the source as the mapped user, we can select from the
> table in question:
>
> piction_ro(at)bampfa_domain_bampfa> select * from collectionobjects_common
> limit 2;
> -[ RECORD 1
> ]------------------+-------------------------------------------------
>
> --------------------------------------------------------------------------------
> id | 2d40e6eb-a4f7-402b-ab06-f3fb57fb6e5d
> assoceventnametype |
> ownershipcategory |
> fieldcollectionnote |
> ownershipplace |
> ...
>
> We can also execute the function in the source view:
>
> piction_ro(at)bampfa_domain_bampfa> select utils.concat_artists('foo');
> -[ RECORD 1 ]--+-
> concat_artists |
>
>
> \df+ utils.concat_artists
> List of functions
> -[ RECORD 1
> ]-------+----------------------------------------------------------------------------------------
> Schema | utils
> Name | concat_artists
> Result data type | character varying
> Argument data types | csid character varying
> Type | normal
> Security | invoker
> Volatility | immutable
> Owner | nuxeo_bampfa
> Language | plpgsql
>
>
> Any help would be much appreciated, I am thinking it could have something
> to do with the complexity of the base view, or perhaps fdw gets confused if
> the source is a view and not an actual table, but I would think the query
> should act just as the mapped user on the source system.
>
> Adam
>
>
>
> --
>
> Adam Fuchs
>
> Database Administrator
>
> UC Berkeley - Information Services & Technology
>
> 2195 Hearst Ave., Berkeley, CA 94120
>
> 510-664-4354
>
>
>

--

Adam Fuchs

Database Administrator

UC Berkeley - Information Services & Technology

2195 Hearst Ave., Berkeley, CA 94120

510-664-4354