On 8/19/24 01:40, Tom Lane wrote: > Tomas Vondra <to...@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
