Re: postgres_fdw error

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
>