Re: possible issue in postgres_fdw batching

On 8/19/24 01:40, Tom Lane wrote:
> Tomas Vondra <tomas@vondra.me> writes:
>> Consider this simplified example:
> 
>>   CREATE TABLE t (a INT);
>>   CREATE FOREIGN TABLE f (a INT) SERVER loopback
>>                                  OPTIONS (table_name 't');
>>   CREATE FUNCTION counter() RETURNS int LANGUAGE sql AS
>>   $$ SELECT COUNT(*) FROM f $$;
> 
>> And now do
> 
>>   INSERT INTO f SELECT counter() FROM generate_series(1,100);
> 
>> With batch_size=1 this produces a nice sequence, with batching we start
>> to get duplicate values - which is not surprising, as the function is
>> oblivious to the data still in the buffer.
> 
>> The first question is if this is a bug.
> 
> I'd say no; this query is unduly chummy with implementation details.
> Even with no foreign table in the picture at all, we would be fully
> within our rights to execute the SELECT to completion before any
> of the insertions became visible.  (Arguably, it's the current
> behavior that is surprising, not that one.)
> 

Thanks, that's a great point. It didn't occur to me we're entitled to
just run the SELECT to completion, before proceeding to the INSERT. That
indeed means this function is fundamentally unsafe.


regards

-- 
Tomas Vondra