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
postgres_fdw-disable-batching.patch
Description: Binary data
