CODEWITHSUNDEEP
postgresqltriggersschemapostgres-fdw
L.Nelson
L.Nelson

Reputation: 21

Postgres fdw inserting record with foreign triggers

I have established a foreign data wrapper to a second Postgres database and imported the foreign schema. Now, when I attempt to insert a row to one of the foreign tables from my primary database it hits a function called on the insert trigger. This trigger is checking another table that exists on the foreign table but it says the "relation "the_other_table" does not exist. I can select from it from the primary database but while executing the trigger it cannot see it.

I tried with the two schemas being the same name then reimported the schema to a different name and still nothing. It is as though it is executing the trigger on my primary but not knowing where to look for the other table.

Any ideas?

Upvotes: 1

Views: 1079

Answers (1)

jjanes
jjanes

Reputation: 44285

I don't see this problem in general, so it must be something specifically about your set up.

cat fdw_trigger.sql:

create database fgn;
\c fgn
create table a (x int);
create table b (x int);
create or replace function foobar() returns trigger language plpgsql as $$ BEGIN insert into public.b values(NEW.x); return new; END $$;
create trigger lsadkjf before insert on a for each row execute function foobar() ;
\c postgres
create schema fgn;
create extension postgres_fdw ;
create server fgn foreign data wrapper postgres_fdw OPTIONS ( dbname 'fgn');
create user MAPPING FOR CURRENT_USER SERVER fgn;
import foreign schema public from server fgn into fgn;
insert into fgn.a  values (13);
\c fgn
select * from b ;
select * from a ;

psql postgres -f fdw_trigger.sql

works as expected.

Upvotes: 1

Related Questions