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

On 2024-08-09T21:55:00+0200, Tomas Vondra wrote:
> 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?

I didn't bother checking, but if CHECK constraints can call volatile functions,
that is another avenue for differing behavior.

Here is a completely different example concerning the way batches to different
partitions are flushed per-partition resulting in out-of-order insertion:

    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 itrtest (a int) partition by range (a);
    create table loct1 (a int check (a % 100 != 3));
    create foreign table remp1 (a int check (a % 100 != 3)) server loopback options (table_name 'loct1');
    create table loct2 (a int check (a % 100 != 3));
    create foreign table remp2 (a int check (a % 100 != 3)) server loopback options (table_name 'loct2');
    alter table itrtest attach partition remp1 for values from (1) to (100);
    alter table itrtest attach partition remp2 for values from (101) to (200);

    insert into itrtest values (1), (2), (101), (103), (3);
    truncate itrtest;
    alter server loopback options (add batch_size '3');
    insert into itrtest values (1), (2), (101), (103), (3);

The first insert (non-batched) gives

    ERROR:  new row for relation "loct2" violates check constraint "loct2_a_check"
    DETAIL:  Failing row contains (103).
    CONTEXT:  remote SQL command: INSERT INTO public.loct2(a) VALUES ($1)

But the second insert (batched) gives

    ERROR:  new row for relation "loct1" violates check constraint "loct1_a_check"
    DETAIL:  Failing row contains (3).
    CONTEXT:  remote SQL command: INSERT INTO public.loct1(a) VALUES ($1), ($2), ($3)

This is because (103) is queued up in the batch and not actually inserted.
There might be a more severe example than this, but I did not think too much
about it.

Jason