Re: postgres_fdw: batch inserts vs. before row triggers

On Sun, Apr 17, 2022 at 6:20 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)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