Trigger Not Working Through Foreign Table in Postgres

Question

I have two Postgres Databases DB1 and DB2. I have a table Tab1 on DB1 that holds some records. There is another table Tab2 on the same DB (DB1) that is managed by a trigger on Tab1 ( basically a count table that holds aggregated count of different attributes of Tab1).

The trigger to update Tab2 in the case of any Insert / Update / Delete on Tab1 is working fine when I am doing the operation directly on DB1.

But I have another database, DB2, where I have a Foreign Table created for Tab1 of DB1. When I am deleting the record from Tab1 using this Foreign Table, the trigger defined on Tab1 in DB1 is not getting fired.

Here is a sample code that I created to test the scenario

On DB1

create table test_trg_tbd( a integer , b varchar(10) );

create table test_trg_tbd2( a integer , b varchar(10) );

CREATE OR REPLACE FUNCTION public.fn_test_trg_tbd()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
BEGIN

    if TG_OP = 'INSERT' then

    elsif TG_OP = 'UPDATE' then

    elsif TG_OP = 'DELETE' then
        insert into test_trg_tbd2 values( old.a, old.b );  
    end if;

    RETURN NEW;

EXCEPTION
        when others then
                RETURN NEW;
END;
$function$;

create or replace trigger trg_test_trg_tbd AFTER INSERT OR DELETE OR UPDATE ON test_trg_tbd FOR EACH ROW EXECUTE FUNCTION fn_test_trg_tbd();

insert into test_trg_tbd values(1, 'Matasya');
insert into test_trg_tbd values(2, 'Kurma');
insert into test_trg_tbd values(3, 'Varaha');

select * from test_trg_tbd 
 a |  b
---+------
 1 | Matasya
 2 | Kurma
 3 | Varaha
(3 rows)

enq=# select * from test_trg_tbd2;
 a | b
---+---
(0 rows)

delete from test_trg_tbd where a = 1;

select * from test_trg_tbd 
 a |  b
---+------
 2 | Kurma
 3 | Varaha
(2 rows)

enq=# select * from test_trg_tbd2;
 a | b
---+---
 1 | Matasya
(1 row)


On DB2 

create foreign table test_trg_tbd_ft( a integer , b varchar(10) ) SERVER db1_ft
OPTIONS (schema_name 'public', table_name 'test_trg_tbd');

select * from test_trg_tbd_ft 
 a |  b
---+------
 2 | Kurma
 3 | Varaha
(2 rows)

delete from test_trg_tbd_ft where a = 2;


Now on DB1

select * from test_trg_tbd 
 a |  b
---+------
 3 | Varaha
(2 rows)

enq=# select * from test_trg_tbd2;
 a | b
---+---
 1 | Matasya
(1 row)

What mistake I might be making?

@LaurenzAlbe, CREATE SERVER db1_ft FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'db1', host 'db1.mycompany.com', port '5439') — Anuj Sharma
– Anuj Sharma, Commented Oct 23, 2023 at 12:35
Thanks. Changing "insert into test_trg_tbd2 values( old.a, old.b ); " into "insert into public.test_trg_tbd2 values( old.a, old.b ); " in the trigger function did the trick. — Anuj Sharma
– Anuj Sharma, Commented Oct 25, 2023 at 8:11

Laurenz Albe · Accepted Answer · 2023-10-25 08:23:08Z

0

postgres_fdw sets search_path to an empty string in its remote connection for security reasons. So if you perform a DELETE on a foreign table and that causes a trigger to execute on the remote database, that trigger function will run with an empty search_path.

Now your trigger function references the table test_trg_tbd2 without a schema, so PostgreSQL won't find the table on the search_path.

There are two possible solutions:

reference the table with the schema name, like public.test_trg_tbd2

set a search_path on the function:

ALTER FUNCTION public.fn_test_trg_tbd() SET search_path = public;

The second solution is normally the better one. I hope you have REVOKEd the CREATE privilege for PUBLIC on that schema (if you are running an older PostgreSQL version).

answered Oct 25, 2023 at 8:23

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger Not Working Through Foreign Table in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related