Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZQKej-00056j-SP for pgsql-admin@arkaria.postgresql.org; Fri, 14 Aug 2015 19:28:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZQKej-0005IJ-6j for pgsql-admin@arkaria.postgresql.org; Fri, 14 Aug 2015 19:28:45 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZQKei-0005Gx-Fj for pgsql-admin@postgresql.org; Fri, 14 Aug 2015 19:28:44 +0000 Received: from mail-qg0-f42.google.com ([209.85.192.42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZQKee-0006fZ-Rq for pgsql-admin@postgresql.org; Fri, 14 Aug 2015 19:28:43 +0000 Received: by qged69 with SMTP id d69so57861193qge.0 for ; Fri, 14 Aug 2015 12:28:38 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:message-id:date:from:user-agent:mime-version:to :cc:subject:references:in-reply-to:content-type; bh=CMoAeg8v/3Qc8YV/YM0sKztq1dzN2kH+UeekGLDi/cA=; b=kf1R6xDVDHWkf+GixeFge4kCwLVrY4k4tJfDN9PnReTVzF6PqnANa0bsImH6I02OkG jdTL/5ezep6uCkmQ53WNotBvUr+ppJqIva2jwj9JliWMuJ/9ilRD4DmOp/V1SHtro/N9 jaMUvwzMPPw7I4ZrEltJLIx9f3OmoxLy2XNvNV8GN553Zo+FSqqhVSKr0D2xTyJGBgFU FRHYNwDEEzP/TR2BEBh2h+zRvudl0egg23+u9bMw5OcixrM29tGt6spmyQyBtE2iZbAl ktyokMMoJYSBeeccNOrA3af0Xeuv+WIMGvW17842y2duhcje6+WQ8qkGT8fXeK5tEOya uOYA== X-Gm-Message-State: ALoCoQmMTh6S/0bjJYjMVsFP+5JPzQhQzj/ilqg37ME+2TamWMeDhhgJYrRTVAHuicPQj/y8uqBa X-Received: by 10.140.130.2 with SMTP id 2mr87068653qhc.30.1439580518609; Fri, 14 Aug 2015 12:28:38 -0700 (PDT) Received: from MegaMac.local (va-76-4-65-131.sta.embarqhsd.net. [76.4.65.131]) by smtp.gmail.com with ESMTPSA id 67sm3551581qhs.8.2015.08.14.12.28.36 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 14 Aug 2015 12:28:36 -0700 (PDT) Message-ID: <55CE4142.9030204@enterprisedb.com> Date: Fri, 14 Aug 2015 15:28:02 -0400 From: Korry Douglas User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:17.0) Gecko/20130509 Thunderbird/17.0.6 MIME-Version: 1.0 To: Adam FUCHS CC: pgsql-admin@postgresql.org Subject: Re: postgres_fdw error References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020603030109030903070401" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------020603030109030903070401 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 > --------------020603030109030903070401 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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


--------------020603030109030903070401--