pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres_fdw error
7+ messages / 3 participants
[nested] [flat]
* postgres_fdw error
@ 2015-08-14 19:17 Adam FUCHS <atman@berkeley.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Adam FUCHS @ 2015-08-14 19:17 UTC (permalink / raw)
To: pgsql-admin; +Cc: Adam FUCHS <atman@berkeley.edu>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-14 19:28 Korry Douglas <korry.douglas@enterprisedb.com>
parent: Adam FUCHS <atman@berkeley.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Korry Douglas @ 2015-08-14 19:28 UTC (permalink / raw)
To: Adam FUCHS <atman@berkeley.edu>; +Cc: pgsql-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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-14 21:19 Adam FUCHS <atman@berkeley.edu>
parent: Korry Douglas <korry.douglas@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adam FUCHS @ 2015-08-14 21:19 UTC (permalink / raw)
To: Korry Douglas <korry.douglas@enterprisedb.com>; +Cc: pgsql-admin
Thanks Korry, how would I check the search_path that is being used by the
FDW user?
On the remote( source ):
piction_ro@bampfa_domain_bampfa> show search_path;
search_path
----------------
"$user",public
on the target:
piction@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@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.douglas@enterprisedb.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@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
>
>
>
--
Adam Fuchs
Database Administrator
UC Berkeley - Information Services & Technology
2195 Hearst Ave., Berkeley, CA 94120
510-664-4354
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-14 21:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam FUCHS <atman@berkeley.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2015-08-14 21:38 UTC (permalink / raw)
To: Adam FUCHS <atman@berkeley.edu>; +Cc: Korry Douglas <korry.douglas@enterprisedb.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-14 22:39 Adam FUCHS <atman@berkeley.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Adam FUCHS @ 2015-08-14 22:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Korry Douglas <korry.douglas@enterprisedb.com>; 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-15 18:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam FUCHS <atman@berkeley.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2015-08-15 18:34 UTC (permalink / raw)
To: Adam FUCHS <atman@berkeley.edu>; +Cc: Korry Douglas <korry.douglas@enterprisedb.com>; pgsql-admin
Adam FUCHS <atman@berkeley.edu> writes:
> In case this breaks stuff, what is the default search_path for functions if
> they are created without one set explicitly?
Ordinarily a function just runs with whatever search_path is active
when it's called.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: postgres_fdw error
@ 2015-08-18 23:50 Adam FUCHS <atman@berkeley.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Adam FUCHS @ 2015-08-18 23:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Korry Douglas <korry.douglas@enterprisedb.com>; pgsql-admin
I just wanted to update folks that indeed running this for each function
called in the view fixed my permissions issues:
ALTER FUNCTION <func> SET search_path=public;
I also had to grant select to the mapped user for each table used in the
function call.
Thank you for everyone's help!
Adam
On Sat, Aug 15, 2015 at 11:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Adam FUCHS <atman@berkeley.edu> writes:
> > In case this breaks stuff, what is the default search_path for functions
> if
> > they are created without one set explicitly?
>
> Ordinarily a function just runs with whatever search_path is active
> when it's called.
>
> regards, tom lane
>
--
Adam Fuchs
Database Administrator
UC Berkeley - Information Services & Technology
2195 Hearst Ave., Berkeley, CA 94120
510-664-4354
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2015-08-18 23:50 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-14 19:17 postgres_fdw error Adam FUCHS <atman@berkeley.edu>
2015-08-14 19:28 ` Korry Douglas <korry.douglas@enterprisedb.com>
2015-08-14 21:19 ` Adam FUCHS <atman@berkeley.edu>
2015-08-14 21:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-14 22:39 ` Adam FUCHS <atman@berkeley.edu>
2015-08-15 18:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-08-18 23:50 ` Adam FUCHS <atman@berkeley.edu>
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