Foreign data wrapper remote trigger cannot find a remote table

Question

Remote schema:

some_table
some_table_view
some_table_view_trigger (INSTEAD OF INSERT)
    -- tries to access some_table (select/update/insert)

Local schema:

some_table_view_fdw
    -- wraps some_table_view on remote

Now on local, when I run an INSERT on some_table_view_fdw, I get relation not found: some_table.

• I can select from some_table_view_fdw just fine (some_table_view just returns * from some_table).
• insert into some_table_view works just fine if run locally (on the remote). The trigger does what it should.
• Note that some_table_view_fdw does not reference some_table directly, so I guess the trigger must be running but then for some reason can't find it's own table?

I am using postgres 9.3

Answer 1

The problem stems from the fact that while querying a remote server search_path parameter is set to pg_catalog. References to tables in the schema public are therefore not automatically solved.

To resolve this problem use absolute table names in the trigger function, e.g. public.my_table instead of my_table. This also applies to all the functions and views used in triggers or views.

You can also set search_path in the trigger function, although I would not recommend this solution. When the trigger is fired locally, the quietly changed parameter will be in force until the end of the session, what could cause further confusions.

---

Treat it as a curiosity: how to check search_path on remote server with postgres_fdw?

Create the test table with the trigger on remote server (locally):

create table test_path (id int, val text);

create or replace function path_trigger()
returns trigger language plpgsql as $$
begin
    select setting into new.val
    from pg_settings where name = 'search_path';
    return new;
end $$;

create trigger path_trigger
before insert on test_path
for each row execute procedure path_trigger();

insert into test_path (id) values (1) returning *;

 id |      val     
----+----------------
  1 | "$user",public
(1 row)

On local server create foreign table and fire the remote trigger:

create foreign table test_path (id int, val text)
server backup_server
options (schema_name 'public', table_name 'test_path'); 

insert into test_path (id) values (2) returning *;

 id |    val     
----+------------
  2 | pg_catalog
(1 row)