postgres_fdw: batch inserts vs. before row triggers - Mailing list pgsql-hackers

Hi,
(I added Tomas in CC:.)

One thing I noticed while reviewing the patch for fast copying into
foreign tables/partitions using batch insert [1] is that in
postgres_fdw we allow batch-inserting into foreign tables/partitions
with before row triggers, but such triggers might query the target
table/partition and act differently if the tuples that have already
been processed and prepared for batch-insertion are not there.  Here
is an example using HEAD:

create extension postgres_fdw;
create server loopback foreign data wrapper postgres_fdw options
(dbname 'postgres');
create user mapping for current_user server loopback;
create table t (a int);
create foreign table ft (a int) server loopback options (table_name 't');
create function ft_rowcount_tf() returns trigger as $$ begin raise
notice '%: rows = %', tg_name, (select count(*) from ft); return new;
end; $$ language plpgsql;
create trigger ft_rowcount before insert on ft for each row execute
function ft_rowcount_tf();

insert into ft select i from generate_series(1, 10) i;
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 1
NOTICE:  ft_rowcount: rows = 2
NOTICE:  ft_rowcount: rows = 3
NOTICE:  ft_rowcount: rows = 4
NOTICE:  ft_rowcount: rows = 5
NOTICE:  ft_rowcount: rows = 6
NOTICE:  ft_rowcount: rows = 7
NOTICE:  ft_rowcount: rows = 8
NOTICE:  ft_rowcount: rows = 9
INSERT 0 10

This looks good, but when batch insert is enabled, the trigger
produces incorrect results:

alter foreign table ft options (add batch_size '10');
delete from ft;

insert into ft select i from generate_series(1, 10) i;
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
NOTICE:  ft_rowcount: rows = 0
INSERT 0 10

So I think we should disable batch insert in such cases, just as we
disable multi insert when there are any before row triggers on the
target (local) tables/partitions in copyfrom.c.  Attached is a patch
for that.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/bc489202-9855-7550-d64c-ad2d83c24867%40postgrespro.ru