Re: postgres_fdw: batch inserts vs. before row triggers

On 4/19/22 11:16, Etsuro Fujita wrote:
> 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.
>

+1, I think it's a bug to do batch insert in this case.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company