1

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?

L.Nelson
  • 21
  • 3
  • Can you show an example? Are you fully qualifying the table name with a schema name? – jjanes Apr 08 '20 at 00:12
  • Databases: CONV (where I wish to write my scripts and where it imported the foreign schema to, and INTERIM (the original database where I wish to insert into. I am attempting to insert into a table on INTERIM executing from CONV (where I want all my conversion scripts to reside). Using an INSERT INTO (on CONV) it hits a trigger on the INTERIM table which calls a function. This function checks a table for a value and then dies with "relation "s_the_other_table" does not exist" while running the function – L.Nelson Apr 09 '20 at 18:45
  • Yes, I am using the full schema.table_name. Running the same script on the INTERIM database works. Running it from CONV using the foreign_schema.table_name does not. – L.Nelson Apr 09 '20 at 18:48

1 Answers1

1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Thanks for the pointers. The problem is that the trigger and function are on INTERIM and I am inserting from CONV. It starts the insert on INTERIM, sees the trigger and fires it calling a function - but the function should check another table on INTERIM but cannot see it even though it does exist on INTERIM. Its as though the function is executing against CONV (where the table does not exist) rather than against INTERIM. – L.Nelson Apr 13 '20 at 14:14
  • You description seems to match what I did, and it worked for me. Please create your own reproducible test case, or point out where our cases diverge. – jjanes Apr 13 '20 at 14:22
  • Thanks for continuing to follow-up Appreciate it. Perhaps add a trigger on fgn table a which calls a function that checks for something on table b and then try the insert from postgres. This is what I'm facing, – L.Nelson Apr 14 '20 at 15:55