‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, March 11, 2021 9:42 AM, Amit Langote <amitlangot...@gmail.com>
wrote:
> Hi Georgios,
>
> On Wed, Mar 10, 2021 at 9:30 PM Georgios gkokola...@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
