Hi everybody,
Yesterday I noticed a strange behaviour, I wonder if it is a bug, a non-documented feature or just me. If this is the expected behaviour the documentation should be updated accordingly. This happens on Posgresql 9.4.
Consider the following SQL script, where I insert 4 rows into a table using a single insert statement and at the end a per-statement trigger is executed:
create table dbpkg.user_role
CREATE FUNCTION dbpkg.dummy_fn() RETURNS trigger AS $$
CREATE TRIGGER insert_trigger
AFTER INSERT ON dbpkg.user_role
FOR STATEMENT EXECUTE PROCEDURE dbpkg.dummy_fn();
-- put some values into the temporary table tmp
insert into dbpkg.tmp(user_uuid, role_uuid)
values (uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4());
-- insert all the values from tmp into user_role
insert into dbpkg.user_role(user_uuid, role_uuid)
select user_uuid, role_uuid from dbpkg.tmp;
drop trigger insert_trigger on dbpkg.user_role;
drop function dbpkg.dummy_fn();
drop table dbpkg.user_role;
When I execute this locally (i.e. without any foreign table) everything works great. The per-statement trigger dbpkg.dummy_fn is executed only once.
If otherwise I do the same using a FDW (i.e the dbpkg.user_role table is declared in a second DB using CREATE FOREIGN TABLE ... SERVER ... OPTIONS ...), the per-statement trigger is executed 4 times, once for every row inserted.
I don't know the FDW internals, but it looks like the insert select statement in this case generates more than one insert.
Can someone please shed some light on this?
Thanks in advance
Andrea