Horton

RE: Fast COPY FROM based on batch insert

tsunakawa.takay@fujitsu.com <tsunakawa.takay@fujitsu.com>

View thread View on postgr.es
From: "tsunakawa.takay@fujitsu.com" <tsunakawa.takay@fujitsu.com>
To: 'Andrey Lepikhov' <a.lepikhov@postgrespro.ru>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Zhihong Yu <zyu@yugabyte.com>, Amit Langote <amitlangote09@gmail.com>, "tanghy.fnst@fujitsu.com" <tanghy.fnst@fujitsu.com>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, "houzj.fnst@fujitsu.com" <houzj.fnst@fujitsu.com>
Date: 2021-06-04T08:45:55Z
Lists: pgsql-hackers 
From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
> We still have slow 'COPY FROM' operation for foreign tables in current master.
> Now we have a foreign batch insert operation And I tried to rewrite the patch [1]
> with this machinery.

I haven't looked at the patch, but nice performance.

However, I see the following problems.  What do you think about them?

1)
No wonder why the user would think like "Why are INSERTs run on the remote server?  I ran COPY."


2)
Without the FDW API for COPY, other FDWs won't get a chance to optimize for bulk data loading.  For example, oracle_fdw might use conventional path insert for the FDW batch insert, and the direct path insert for the FDW COPY.


3)
INSERT and COPY in Postgres differs in whether the rule is invoked:

https://www.postgresql.org/docs/devel/sql-copy.html

"COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules."


Regards
Takayuki Tsunakawa

Commits

  1. Allow batch insertion during COPY into a foreign table.

  2. postgres_fdw: Disable batch insertion when there are WCO constraints.

  3. Enforce foreign key correctly during cross-partition updates

  4. Fix permission checks on constraint violation errors on partitions.

  5. Fix compilation of uuid-ossp