Re: FDW INSERT batching can change behavior

On 8/9/24 05:07, git(at)jasonk(dot)me wrote:
> Hi,
>
> ...
>
> The first insert does not use batching, so it goes R W R W R W R W R W (R for
> executing the default function to generate a slot: nodeModifyTable.c
> ExecModifyTable context.planSlot = ExecProcNode(subplanstate); W for inserting
> into the table). This way, whenever the default function is called, it returns
> a new value.
>
> The second insert uses batching, so it goes R R R W W W R R W W. The function
> returns the same value within a batch, and in this case, it causes a conflict:
>
> ERROR: duplicate key value violates unique constraint "gloc1_pkey"
> DETAIL: Key (a)=(5) already exists.
> CONTEXT: remote SQL command: INSERT INTO public.gloc1(a, b) VALUES ($1, DEFAULT), ($2, DEFAULT), ($3, DEFAULT)
>
> Tested on 15.2 and 16.4 I compiled myself.
>

Yeah, we don't seem to check for this. I don't recall if it didn't occur
to me we could have DEFAULT on the foreign table.

We could/should disable batching, but I'm not quite sure what exactly to
check. AFAIK this can happen only when there are default expressions on
the foreign table, so maybe that? Or maybe only when the DEFAULT calls a
volatile function?

Thanks for the report.

--
Tomas Vondra