Re: Allow batched insert during cross-partition updates

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, March 11, 2021 9:42 AM, Amit Langote <amitlangote09@gmail.com> wrote:

> Hi Georgios,
>
> On Wed, Mar 10, 2021 at 9:30 PM Georgios gkokolatos@protonmail.com wrote:
>
> > I continued looking a bit at the patch, yet I am either failing to see fix or I am
> > looking at the wrong thing. Please find attached a small repro of what my expectetions
> > were.
> > As you can see in the repro, I would expect the
> > UPDATE local_root_remote_partitions SET a = 2;
> > to move the tuples to remote_partition_2 on the same transaction.
> > However this is not the case, with or without the patch.
> > Is my expectation of this patch wrong?
>
> I think yes. We currently don't have the feature you are looking for
> -- moving tuples from one remote partition to another remote
> partition. This patch is not for adding that feature.

Thank you for correcting me.

>
> What we do support however is moving rows from a local partition to a
> remote partition and that involves performing an INSERT on the latter.
> This patch is for teaching those INSERTs to use batched mode if
> allowed, which is currently prohibited. So with this patch, if an
> UPDATE moves 10 rows from a local partition to a remote partition,
> then they will be inserted with a single INSERT command containing all
> 10 rows, instead of 10 separate INSERT commands.

So, if I understand correctly then in my previously attached repro I
should have written instead:

    CREATE TABLE local_root_remote_partitions (a int) PARTITION BY LIST ( a );
    CREATE TABLE
        local_root_local_partition_1
    PARTITION OF
        local_root_remote_partitions FOR VALUES IN (1);

    CREATE FOREIGN TABLE
        local_root_remote_partition_2
    PARTITION OF
        local_root_remote_partitions FOR VALUES IN (2)
    SERVER
        remote_server
    OPTIONS (
        table_name 'remote_partition_2',
        batch_size '10'
    );

    INSERT INTO local_root_remote_partitions VALUES (1), (1);
    -- Everything should be on local_root_local_partition_1 and on the same transaction
    SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM local_root_remote_partitions;

    UPDATE local_root_remote_partitions SET a = 2;
    -- Everything should be on remote_partition_2 and on the same transaction
    SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM local_root_remote_partitions;


I am guessing that I am still wrong because the UPDATE operation above will
fail due to the restrictions imposed in postgresBeginForeignInsert regarding
UPDATES.

Would it be too much to ask for the addition of a test case that will
demonstrate the change of behaviour found in patch?

Cheers,
//Georgios

>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Amit Langote
> EDB: http://www.enterprisedb.com