FDW INSERT batching can change behavior - Mailing list pgsql-bugs

Hi,

According to the code, foreign data wrapper INSERT ON CONFLICT batching has
several limitations such as no RETURNING clause, no row triggers(?).  I found
one case that is not disallowed and ends up causing a behavior difference
depending on whether batching is enabled and not.

This example is derived from the contrib/postgres_fdw pg_regress test:

    CREATE EXTENSION postgres_fdw;

    CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
    DO $d$
        BEGIN
            EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
                OPTIONS (dbname '$$||current_database()||$$',
                         port '$$||current_setting('port')||$$'
                )$$;
        END;
    $d$;

    CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

    create table gloc1 (
      a int PRIMARY KEY,
      b int generated always as (a * 2) stored);
    alter table gloc1 set (autovacuum_enabled = 'false');
    create foreign table grem1 (
      a int not null,
      b int generated always as (a * 2) stored)
      server loopback options(table_name 'gloc1');

    create function counter() returns int8 language sql as $$select count(*) from grem1$$;
    ALTER FOREIGN TABLE grem1 ALTER COLUMN a SET DEFAULT (counter());

    insert into grem1 (a) values (default), (default), (default), (default), (default);

    alter server loopback options (add batch_size '3');
    insert into grem1 (a) values (default), (default), (default), (default), (default);

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.

Jason