Re: postgres_fdw error

[Adam FUCHS <atman@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxx> 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