Re: [POC] Fast COPY FROM command for the table with foreign partitions - Mailing list pgsql-hackers

From Andrey Lepikhov
Subject Re: [POC] Fast COPY FROM command for the table with foreign partitions
Date
Msg-id ae25e79e-6122-720e-1349-fb832c6021df@postgrespro.ru
Whole thread Raw
In response to Re: [POC] Fast COPY FROM command for the table with foreign partitions  (Ashutosh Bapat <ashutosh.bapat@2ndquadrant.com>)
Responses Re: [POC] Fast COPY FROM command for the table with foreign partitions
List pgsql-hackers

22.06.2020 17:11, Ashutosh Bapat пишет:
> 
> 
> On Wed, 17 Jun 2020 at 11:54, Andrey V. Lepikhov 
> <a.lepikhov@postgrespro.ru <mailto:a.lepikhov@postgrespro.ru>> wrote:
> 
>     On 6/15/20 10:26 AM, Ashutosh Bapat wrote:
>      > Thanks Andrey for the patch. I am glad that the patch has taken care
>      > of some corner cases already but there exist still more.
>      >
>      > COPY command constructed doesn't take care of dropped columns. There
>      > is code in deparseAnalyzeSql which constructs list of columns for a
>      > given foreign relation. 0002 patch attached here, moves that code
>     to a
>      > separate function and reuses it for COPY. If you find that code
>     change
>      > useful please include it in the main patch.
> 
>     Thanks, i included it.
> 
>      > 2. In the same case, if the foreign table declared locally didn't
>     have
>      > any non-dropped columns but the relation that it referred to on the
>      > foreign server had some non-dropped columns, COPY command fails. I
>      > added a test case for this in 0002 but haven't fixed it.
> 
>     I fixed it.
>     This is very special corner case. The problem was that COPY FROM does
>     not support semantics like the "INSERT INTO .. DEFAULT VALUES". To
>     simplify the solution, i switched off bulk copying for this case.
> 
>       > I think this work is useful. Please add it to the next commitfest so
>       > that it's tracked.
>     Ok.
> 
> 
> It looks like we call BeginForeignInsert and EndForeignInsert even 
> though actual copy is performed using BeginForeignCopy, ExecForeignCopy 
> and EndForeignCopy. BeginForeignInsert constructs the INSERT query which 
> looks unnecessary. Also some of the other PgFdwModifyState members are 
> initialized unnecessarily. It also gives an impression that we are using 
> INSERT underneath the copy. Instead a better way would be to 
> call BeginForeignCopy instead of BeginForeignInsert and EndForeignCopy 
> instead of EndForeignInsert, if we are going to use COPY protocol to 
> copy data to the foreign server. Corresponding postgres_fdw 
> implementations need to change in order to do that.
Fixed.
I replaced names of CopyIn FDW API. Also the partition routing 
initializer calls BeginForeignInsert or BeginForeignCopyIn routines in 
accordance with value of ResultRelInfo::UseBulkModifying.
I introduced this parameter because foreign partitions can be placed at 
foreign servers with different types of foreign wrapper. Not all 
wrappers can support CopyIn API.
Also I ran the Tomas Vondra benchmark. At my laptop we have results:
* regular: 5000 ms.
* Tomas buffering patch: 11000 ms.
* This CopyIn patch: 8000 ms.

-- 
regards,
Andrey Lepikhov
Postgres Professional

Attachment

pgsql-hackers by date:

Previous
From: Dilip Kumar
Date:
Subject: Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions
Next
From: Daniel Gustafsson
Date:
Subject: Re: explain HashAggregate to report bucket and memory stats