CODEWITHSUNDEEP
postgresqltriggerssql-insertpostgresql-9.3foreign-data-wrapper
Jordan Morris
Jordan Morris

Reputation: 2301

Foreign data wrapper remote trigger cannot find a remote table

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 am using postgres 9.3

Upvotes: 4

Views: 1718

Answers (1)

klin
klin

Reputation: 121754

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)

Upvotes: 4

Related Questions