Re: possible issue in postgres_fdw batching

On 8/19/24 01:40, Tom Lane wrote:
> Tomas Vondra <tomas(at)vondra(dot)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