From: Korry Douglas <korry.douglas@enterprisedb.com>
To: Adam FUCHS <atman@berkeley.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: postgres_fdw error
Date: Fri, 14 Aug 2015 15:28:02 -0400
Message-ID: <55CE4142.9030204@enterprisedb.com> (raw)
In-Reply-To: <CAG=eVo2-1vnSEC+n1wmCi4744i=kurn=3J7fZPFH5zWd5NqXmQ@mail.gmail.com>
References: <CAG=eVo2-1vnSEC+n1wmCi4744i=kurn=3J7fZPFH5zWd5NqXmQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@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@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@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@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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: korry.douglas@enterprisedb.com, atman@berkeley.edu
Subject: Re: postgres_fdw error
In-Reply-To: <55CE4142.9030204@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox