On Sun, Apr 17, 2022 at 6:20 PM Etsuro Fujita <etsuro.fuj...@gmail.com> wrote: > 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
Actually, the results are correct, as we do batch-insert here. But I just wanted to show that the trigger behaves *differently* when doing batch-insert. > 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. If there are no objections from Tomas or anyone else, I'll commit the patch. Best regards, Etsuro Fujita
