From: Andrey V. Lepikhov <a.lepikhov@postgrespro.ru>
To: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Zhihong Yu <zyu@yugabyte.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: tanghy.fnst@fujitsu.com, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Cc: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: houzj.fnst@fujitsu.com
Subject: Re: Fast COPY FROM based on batch insert
Date: Thu, 24 Mar 2022 11:43:37 +0500
Message-ID: <45ad1b94-339b-a841-8885-67ebe6fc5bc1@postgrespro.ru> (raw)
In-Reply-To: <CAPmGK15Z1kvZE0oULa+JD_CKzc4NwWEZYAfJ26VkoVYg1md63g@mail.gmail.com>
References: <TYAPR01MB2990B8451B4C725242E270F7FE979@TYAPR01MB2990.jpnprd01.prod.outlook.com>
<CALNJ-vTdEpeF0nzMhTPYJAhcZVdtwFN+YEkz4tn6p2gDG7j5sA@mail.gmail.com>
<TYAPR01MB299090411AC25A6B56103869FE979@TYAPR01MB2990.jpnprd01.prod.outlook.com>
<20210304203949.GQ29832@telsasoft.com>
<TYAPR01MB29900C08C2C17747612A28B5FE969@TYAPR01MB2990.jpnprd01.prod.outlook.com>
<e3974716-e77b-9006-b1dc-dc00474d09c3@postgrespro.ru>
<TYAPR01MB29909FDCD92F5EEE68E37767FE649@TYAPR01MB2990.jpnprd01.prod.outlook.com>
<CALNJ-vRi4DjjkCmvmXWWtULzY6PSeEAC9vSpXVNHyVoGEQ8HVQ@mail.gmail.com>
<20210323032322.GW4203@telsasoft.com>
<TYAPR01MB2990124D4D859FE8C7E984C8FE649@TYAPR01MB2990.jpnprd01.prod.outlook.com>
<20210409004956.GN6592@telsasoft.com>
<CAPmGK15rVM+61WHZ3xS3zS6mq7293zCexVMYV4p3xoyTYo8FLg@mail.gmail.com>
<bc489202-9855-7550-d64c-ad2d83c24867@postgrespro.ru>
<CAPmGK15Z1kvZE0oULa+JD_CKzc4NwWEZYAfJ26VkoVYg1md63g@mail.gmail.com>
On 3/22/22 06:54, Etsuro Fujita wrote:
> On Fri, Jun 4, 2021 at 5:26 PM Andrey Lepikhov
> <a.lepikhov@postgrespro.ru> wrote:
>> 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.
>
> The patch has been rewritten to something essentially different, but
> no one reviewed it. (Tsunakawa-san gave some comments without looking
> at it, though.) So the right status of the patch is “Needs review”,
> rather than “Ready for Committer”? Anyway, here are a few review
> comments from me:
>
> * I don’t think this assumption is correct:
>
> @@ -359,6 +386,12 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
> (resultRelInfo->ri_TrigDesc->trig_insert_after_row ||
> resultRelInfo->ri_TrigDesc->trig_insert_new_table))
> {
> + /*
> + * AFTER ROW triggers aren't allowed with the foreign bulk insert
> + * method.
> + */
> + Assert(resultRelInfo->ri_RelationDesc->rd_rel->relkind !=
> RELKIND_FOREIGN_TABLE);
> +
>
> In postgres_fdw we disable foreign batch insert when the target table
> has AFTER ROW triggers, but the core allows it even in that case. No?
Agree
> * To allow foreign multi insert, the patch made an invasive change to
> the existing logic to determine whether to use multi insert for the
> target relation, adding a new member ri_usesMultiInsert to the
> ResultRelInfo struct, as well as introducing a new function
> ExecMultiInsertAllowed(). But I’m not sure we really need such a
> change. Isn’t it reasonable to *adjust* the existing logic to allow
> foreign multi insert when possible?
Of course, such approach would look much better, if we implemented it.
I'll ponder how to do it.
> I didn’t finish my review, but I’ll mark this as “Waiting on Author”.
I rebased the patch onto current master. Now it works correctly. I'll
mark it as "Waiting for review".
--
regards,
Andrey Lepikhov
Postgres Professional
Attachments:v3-0001-Implementation-of-a-Bulk-COPY-FROM.patch (text/x-patch, 23.1K) download
From 2d51d0f5d94a3e4b3400714b5841228d1896fb56 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Fri, 4 Jun 2021 13:21:43 +0500
Subject: [PATCH] Implementation of a Bulk COPY FROM operation into foreign
table.
---
.../postgres_fdw/expected/postgres_fdw.out | 45 +++-
contrib/postgres_fdw/sql/postgres_fdw.sql | 47 ++++
src/backend/commands/copyfrom.c | 210 ++++++++----------
src/backend/executor/execMain.c | 45 ++++
src/backend/executor/execPartition.c | 8 +
src/include/commands/copyfrom_internal.h | 10 -
src/include/executor/executor.h | 1 +
src/include/nodes/execnodes.h | 5 +-
8 files changed, 237 insertions(+), 134 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex f210f91188..a803029f2f 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -8415,6 +8415,7 @@ drop table loct2;
-- ===================================================================
-- test COPY FROM
-- ===================================================================
+alter server loopback options (add batch_size '2');
create table loc2 (f1 int, f2 text);
alter table loc2 set (autovacuum_enabled = 'false');
create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2');
@@ -8437,7 +8438,7 @@ copy rem2 from stdin; -- ERROR
ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive"
DETAIL: Failing row contains (-1, xyzzy).
CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2)
-COPY rem2, line 1: "-1 xyzzy"+COPY rem2, line 2
select * from rem2;
f1 | f2
----+-----
@@ -8448,6 +8449,19 @@ select * from rem2;
alter foreign table rem2 drop constraint rem2_f1positive;
alter table loc2 drop constraint loc2_f1positive;
delete from rem2;
+create table foo (a int) partition by list (a);+create table foo1 (like foo);+create foreign table ffoo1 partition of foo for values in (1)+ server loopback options (table_name 'foo1');+create table foo2 (like foo);+create foreign table ffoo2 partition of foo for values in (2)+ server loopback options (table_name 'foo2');+create function print_new_row() returns trigger language plpgsql as $$+ begin raise notice '%', new; return new; end; $$;+create trigger ffoo1_br_trig before insert on ffoo1+ for each row execute function print_new_row();+copy foo from stdin;+NOTICE: (1)
-- Test local triggers
create trigger trig_stmt_before before insert on rem2
for each statement execute procedure trigger_func();
@@ -8556,6 +8570,34 @@ drop trigger rem2_trig_row_before on rem2;
drop trigger rem2_trig_row_after on rem2;
drop trigger loc2_trig_row_before_insert on loc2;
delete from rem2;
+alter table loc2 drop column f1;+alter table loc2 drop column f2;+copy rem2 from stdin;+ERROR: column "f1" of relation "loc2" does not exist+CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2), ($3, $4)+COPY rem2, line 3+alter table loc2 add column f1 int;+alter table loc2 add column f2 int;+select * from rem2;+ f1 | f2 +----+----+(0 rows)++-- dropped columns locally and on the foreign server+alter table rem2 drop column f1;+alter table rem2 drop column f2;+copy rem2 from stdin;+select * from rem2;+--+(2 rows)++alter table loc2 drop column f1;+alter table loc2 drop column f2;+copy rem2 from stdin;+select * from rem2;+--+(4 rows)+
-- test COPY FROM with foreign table created in the same transaction
create table loc3 (f1 int, f2 text);
begin;
@@ -8572,6 +8614,7 @@ select * from rem3;
drop foreign table rem3;
drop table loc3;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test for TRUNCATE
-- ===================================================================
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sqlindex 95b6b7192e..847b869629 100644--- a/contrib/postgres_fdw/sql/postgres_fdw.sql+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql@@ -2322,6 +2322,7 @@ drop table loct2;
-- test COPY FROM
-- ===================================================================
+alter server loopback options (add batch_size '2');
create table loc2 (f1 int, f2 text);
alter table loc2 set (autovacuum_enabled = 'false');
create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2');
@@ -2354,6 +2355,23 @@ alter table loc2 drop constraint loc2_f1positive;
delete from rem2;
+create table foo (a int) partition by list (a);+create table foo1 (like foo);+create foreign table ffoo1 partition of foo for values in (1)+ server loopback options (table_name 'foo1');+create table foo2 (like foo);+create foreign table ffoo2 partition of foo for values in (2)+ server loopback options (table_name 'foo2');+create function print_new_row() returns trigger language plpgsql as $$+ begin raise notice '%', new; return new; end; $$;+create trigger ffoo1_br_trig before insert on ffoo1+ for each row execute function print_new_row();++copy foo from stdin;+1+2+\.+
-- Test local triggers
create trigger trig_stmt_before before insert on rem2
for each statement execute procedure trigger_func();
@@ -2454,6 +2472,34 @@ drop trigger loc2_trig_row_before_insert on loc2;
delete from rem2;
+alter table loc2 drop column f1;+alter table loc2 drop column f2;+copy rem2 from stdin;+1 foo+2 bar+\.++alter table loc2 add column f1 int;+alter table loc2 add column f2 int;+select * from rem2;++-- dropped columns locally and on the foreign server+alter table rem2 drop column f1;+alter table rem2 drop column f2;+copy rem2 from stdin;+++\.+select * from rem2;++alter table loc2 drop column f1;+alter table loc2 drop column f2;+copy rem2 from stdin;+++\.+select * from rem2;+
-- test COPY FROM with foreign table created in the same transaction
create table loc3 (f1 int, f2 text);
begin;
@@ -2467,6 +2513,7 @@ commit;
select * from rem3;
drop foreign table rem3;
drop table loc3;
+alter server loopback options (drop batch_size);
-- ===================================================================
-- test for TRUNCATE
diff --git a/src/backend/commands/copyfrom.c b/src/backend/commands/copyfrom.cindex db6eb6fae7..ba5ccf8908 100644--- a/src/backend/commands/copyfrom.c+++ b/src/backend/commands/copyfrom.c@@ -320,18 +320,43 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
cstate->line_buf_valid = false;
save_cur_lineno = cstate->cur_lineno;
- /*- * table_multi_insert may leak memory, so switch to short-lived memory- * context before calling it.- */- oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));- table_multi_insert(resultRelInfo->ri_RelationDesc,- slots,- nused,- mycid,- ti_options,- buffer->bistate);- MemoryContextSwitchTo(oldcontext);+ if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_FOREIGN_TABLE)+ {+ int sent = 0;++ Assert(resultRelInfo->ri_BatchSize > 1 &&+ resultRelInfo->ri_FdwRoutine->ExecForeignBatchInsert != NULL &&+ resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize != NULL);++ /* Flush into foreign table or partition */+ do {+ int size = (resultRelInfo->ri_BatchSize < nused - sent) ?+ resultRelInfo->ri_BatchSize : (nused - sent);+ int inserted = size;++ resultRelInfo->ri_FdwRoutine->ExecForeignBatchInsert(estate,+ resultRelInfo,+ &slots[sent],+ NULL,+ &inserted);+ sent += size;+ } while (sent < nused);+ }+ else+ {+ /*+ * table_multi_insert may leak memory, so switch to short-lived memory+ * context before calling it.+ */+ oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));+ table_multi_insert(resultRelInfo->ri_RelationDesc,+ slots,+ nused,+ mycid,+ ti_options,+ buffer->bistate);+ MemoryContextSwitchTo(oldcontext);+ }
for (i = 0; i < nused; i++)
{
@@ -343,6 +368,8 @@ CopyMultiInsertBufferFlush(CopyMultiInsertInfo *miinfo,
{
List *recheckIndexes;
+ Assert(resultRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_FOREIGN_TABLE);+
cstate->cur_lineno = buffer->linenos[i];
recheckIndexes =
ExecInsertIndexTuples(resultRelInfo,
@@ -541,13 +568,11 @@ CopyFrom(CopyFromState cstate)
CommandId mycid = GetCurrentCommandId(true);
int ti_options = 0; /* start with default options for insert */
BulkInsertState bistate = NULL;
- CopyInsertMethod insertMethod;
CopyMultiInsertInfo multiInsertInfo = {0}; /* pacify compiler */
int64 processed = 0;
int64 excluded = 0;
bool has_before_insert_row_trig;
bool has_instead_insert_row_trig;
- bool leafpart_use_multi_insert = false;
Assert(cstate->rel);
Assert(list_length(cstate->range_table) == 1);
@@ -674,10 +699,43 @@ CopyFrom(CopyFromState cstate)
mtstate->resultRelInfo = resultRelInfo;
mtstate->rootResultRelInfo = resultRelInfo;
- if (resultRelInfo->ri_FdwRoutine != NULL &&- resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)- resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,- resultRelInfo);+ if (resultRelInfo->ri_FdwRoutine != NULL)+ {+ if (resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize != NULL)+ resultRelInfo->ri_BatchSize =+ resultRelInfo->ri_FdwRoutine->GetForeignModifyBatchSize(resultRelInfo);++ if (resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)+ resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,+ resultRelInfo);+ }++ Assert(!target_resultRelInfo->ri_usesMultiInsert);++ /*+ * It's generally more efficient to prepare a bunch of tuples for+ * insertion, and insert them in bulk, for example, with one+ * table_multi_insert() call than call table_tuple_insert() separately for+ * every tuple. However, there are a number of reasons why we might not be+ * able to do this. For example, if there any volatile expressions in the+ * table's default values or in the statement's WHERE clause, which may+ * query the table we are inserting into, buffering tuples might produce+ * wrong results. Also, the relation we are trying to insert into itself+ * may not be amenable to buffered inserts.+ *+ * Note: For partitions, this flag is set considering the target table's+ * flag that is being set here and partition's own properties which are+ * checked by calling ExecMultiInsertAllowed(). It does not matter+ * whether partitions have any volatile default expressions as we use the+ * defaults from the target of the COPY command.+ * Also, the COPY command requires a non-zero input list of attributes.+ * Therefore, the length of the attribute list is checked here.+ */+ if (!cstate->volatile_defexprs &&+ list_length(cstate->attnumlist) > 0 &&+ !contain_volatile_functions(cstate->whereClause))+ target_resultRelInfo->ri_usesMultiInsert =+ ExecMultiInsertAllowed(target_resultRelInfo);
/* Prepare to catch AFTER triggers. */
AfterTriggerBeginQuery();
@@ -706,83 +764,9 @@ CopyFrom(CopyFromState cstate)
cstate->qualexpr = ExecInitQual(castNode(List, cstate->whereClause),
&mtstate->ps);
- /*- * It's generally more efficient to prepare a bunch of tuples for- * insertion, and insert them in one table_multi_insert() call, than call- * table_tuple_insert() separately for every tuple. However, there are a- * number of reasons why we might not be able to do this. These are- * explained below.- */- if (resultRelInfo->ri_TrigDesc != NULL &&- (resultRelInfo->ri_TrigDesc->trig_insert_before_row ||- resultRelInfo->ri_TrigDesc->trig_insert_instead_row))- {- /*- * Can't support multi-inserts when there are any BEFORE/INSTEAD OF- * triggers on the table. Such triggers might query the table we're- * inserting into and act differently if the tuples that have already- * been processed and prepared for insertion are not there.- */- insertMethod = CIM_SINGLE;- }- else if (proute != NULL && resultRelInfo->ri_TrigDesc != NULL &&- resultRelInfo->ri_TrigDesc->trig_insert_new_table)- {- /*- * For partitioned tables we can't support multi-inserts when there- * are any statement level insert triggers. It might be possible to- * allow partitioned tables with such triggers in the future, but for- * now, CopyMultiInsertInfoFlush expects that any before row insert- * and statement level insert triggers are on the same relation.- */- insertMethod = CIM_SINGLE;- }- else if (resultRelInfo->ri_FdwRoutine != NULL ||- cstate->volatile_defexprs)- {- /*- * Can't support multi-inserts to foreign tables or if there are any- * volatile default expressions in the table. Similarly to the- * trigger case above, such expressions may query the table we're- * inserting into.- *- * Note: It does not matter if any partitions have any volatile- * default expressions as we use the defaults from the target of the- * COPY command.- */- insertMethod = CIM_SINGLE;- }- else if (contain_volatile_functions(cstate->whereClause))- {- /*- * Can't support multi-inserts if there are any volatile function- * expressions in WHERE clause. Similarly to the trigger case above,- * such expressions may query the table we're inserting into.- */- insertMethod = CIM_SINGLE;- }- else- {- /*- * For partitioned tables, we may still be able to perform bulk- * inserts. However, the possibility of this depends on which types- * of triggers exist on the partition. We must disable bulk inserts- * if the partition is a foreign table or it has any before row insert- * or insert instead triggers (same as we checked above for the parent- * table). Since the partition's resultRelInfos are initialized only- * when we actually need to insert the first tuple into them, we must- * have the intermediate insert method of CIM_MULTI_CONDITIONAL to- * flag that we must later determine if we can use bulk-inserts for- * the partition being inserted into.- */- if (proute)- insertMethod = CIM_MULTI_CONDITIONAL;- else- insertMethod = CIM_MULTI;-+ if (resultRelInfo->ri_usesMultiInsert)
CopyMultiInsertInfoInit(&multiInsertInfo, resultRelInfo, cstate,
estate, mycid, ti_options);
- }
/*
* If not using batch mode (which allocates slots as needed) set up a
@@ -790,7 +774,7 @@ CopyFrom(CopyFromState cstate)
* one, even if we might batch insert, to read the tuple in the root
* partition's form.
*/
- if (insertMethod == CIM_SINGLE || insertMethod == CIM_MULTI_CONDITIONAL)+ if (!resultRelInfo->ri_usesMultiInsert || proute)
{
singleslot = table_slot_create(resultRelInfo->ri_RelationDesc,
&estate->es_tupleTable);
@@ -833,7 +817,7 @@ CopyFrom(CopyFromState cstate)
ResetPerTupleExprContext(estate);
/* select slot to (initially) load row into */
- if (insertMethod == CIM_SINGLE || proute)+ if (!target_resultRelInfo->ri_usesMultiInsert || proute)
{
myslot = singleslot;
Assert(myslot != NULL);
@@ -841,7 +825,6 @@ CopyFrom(CopyFromState cstate)
else
{
Assert(resultRelInfo == target_resultRelInfo);
- Assert(insertMethod == CIM_MULTI);
myslot = CopyMultiInsertInfoNextFreeSlot(&multiInsertInfo,
resultRelInfo);
@@ -908,24 +891,14 @@ CopyFrom(CopyFromState cstate)
has_instead_insert_row_trig = (resultRelInfo->ri_TrigDesc &&
resultRelInfo->ri_TrigDesc->trig_insert_instead_row);
- /*- * Disable multi-inserts when the partition has BEFORE/INSTEAD- * OF triggers, or if the partition is a foreign partition.- */- leafpart_use_multi_insert = insertMethod == CIM_MULTI_CONDITIONAL &&- !has_before_insert_row_trig &&- !has_instead_insert_row_trig &&- resultRelInfo->ri_FdwRoutine == NULL;-
/* Set the multi-insert buffer to use for this partition. */
- if (leafpart_use_multi_insert)+ if (resultRelInfo->ri_usesMultiInsert)
{
if (resultRelInfo->ri_CopyMultiInsertBuffer == NULL)
CopyMultiInsertInfoSetupBuffer(&multiInsertInfo,
resultRelInfo);
}
- else if (insertMethod == CIM_MULTI_CONDITIONAL &&- !CopyMultiInsertInfoIsEmpty(&multiInsertInfo))+ else if (!CopyMultiInsertInfoIsEmpty(&multiInsertInfo))
{
/*
* Flush pending inserts if this partition can't use
@@ -955,7 +928,7 @@ CopyFrom(CopyFromState cstate)
* rowtype.
*/
map = resultRelInfo->ri_RootToPartitionMap;
- if (insertMethod == CIM_SINGLE || !leafpart_use_multi_insert)+ if (!resultRelInfo->ri_usesMultiInsert)
{
/* non batch insert */
if (map != NULL)
@@ -974,9 +947,6 @@ CopyFrom(CopyFromState cstate)
*/
TupleTableSlot *batchslot;
- /* no other path available for partitioned table */- Assert(insertMethod == CIM_MULTI_CONDITIONAL);-
batchslot = CopyMultiInsertInfoNextFreeSlot(&multiInsertInfo,
resultRelInfo);
@@ -1048,7 +1018,7 @@ CopyFrom(CopyFromState cstate)
ExecPartitionCheck(resultRelInfo, myslot, estate, true);
/* Store the slot in the multi-insert buffer, when enabled. */
- if (insertMethod == CIM_MULTI || leafpart_use_multi_insert)+ if (resultRelInfo->ri_usesMultiInsert)
{
/*
* The slot previously might point into the per-tuple
@@ -1127,11 +1097,8 @@ CopyFrom(CopyFromState cstate)
}
/* Flush any remaining buffered tuples */
- if (insertMethod != CIM_SINGLE)- {- if (!CopyMultiInsertInfoIsEmpty(&multiInsertInfo))- CopyMultiInsertInfoFlush(&multiInsertInfo, NULL);- }+ if (!CopyMultiInsertInfoIsEmpty(&multiInsertInfo))+ CopyMultiInsertInfoFlush(&multiInsertInfo, NULL);
/* Done, clean up */
error_context_stack = errcallback.previous;
@@ -1152,12 +1119,11 @@ CopyFrom(CopyFromState cstate)
/* Allow the FDW to shut down */
if (target_resultRelInfo->ri_FdwRoutine != NULL &&
target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
- target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate,- target_resultRelInfo);+ target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate,+ target_resultRelInfo);
/* Tear down the multi-insert buffer data */
- if (insertMethod != CIM_SINGLE)- CopyMultiInsertInfoCleanup(&multiInsertInfo);+ CopyMultiInsertInfoCleanup(&multiInsertInfo);
/* Close all the partitioned tables, leaf partitions, and their indices */
if (proute)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.cindex 473d2e00a2..8727c5ca89 100644--- a/src/backend/executor/execMain.c+++ b/src/backend/executor/execMain.c@@ -1257,9 +1257,54 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_PartitionTupleSlot = NULL; /* ditto */
resultRelInfo->ri_ChildToRootMap = NULL;
resultRelInfo->ri_ChildToRootMapValid = false;
+ resultRelInfo->ri_usesMultiInsert = false;
resultRelInfo->ri_CopyMultiInsertBuffer = NULL;
}
+/*+ * ExecMultiInsertAllowed+ * Does this relation allow caller to use multi-insert mode when+ * inserting rows into it?+ */+bool+ExecMultiInsertAllowed(const ResultRelInfo *rri)+{+ /*+ * Can't support multi-inserts when there are any BEFORE/INSTEAD OF+ * triggers on the table. Such triggers might query the table we're+ * inserting into and act differently if the tuples that have already+ * been processed and prepared for insertion are not there.+ */+ if (rri->ri_TrigDesc != NULL &&+ (rri->ri_TrigDesc->trig_insert_before_row ||+ rri->ri_TrigDesc->trig_insert_instead_row))+ return false;++ /*+ * For partitioned tables we can't support multi-inserts when there are+ * any statement level insert triggers. It might be possible to allow+ * partitioned tables with such triggers in the future, but for now,+ * CopyMultiInsertInfoFlush expects that any before row insert and+ * statement level insert triggers are on the same relation.+ */+ if (rri->ri_RelationDesc->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ rri->ri_TrigDesc != NULL &&+ rri->ri_TrigDesc->trig_insert_new_table)+ return false;++ if (rri->ri_FdwRoutine != NULL &&+ (rri->ri_FdwRoutine->ExecForeignBatchInsert == NULL ||+ rri->ri_BatchSize <= 1))+ /*+ * Foreign tables don't support multi-inserts, unless their FDW+ * provides the necessary bulk insert interface.+ */+ return false;++ /* OK, caller can use multi-insert on this relation. */+ return true;+}+
/*
* ExecGetTriggerResultRel
* Get a ResultRelInfo for a trigger target relation.
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.cindex 90ed1485d1..942bd506f8 100644--- a/src/backend/executor/execPartition.c+++ b/src/backend/executor/execPartition.c@@ -661,6 +661,14 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
ExecInitRoutingInfo(mtstate, estate, proute, dispatch,
leaf_part_rri, partidx, false);
+ /*+ * If a partition's root parent isn't allowed to use it, neither is the+ * partition.+ */+ if (rootResultRelInfo->ri_usesMultiInsert)+ leaf_part_rri->ri_usesMultiInsert =+ ExecMultiInsertAllowed(leaf_part_rri);+
/*
* If there is an ON CONFLICT clause, initialize state for it.
*/
diff --git a/src/include/commands/copyfrom_internal.h b/src/include/commands/copyfrom_internal.hindex 3df1c5a97c..1c733bb9a4 100644--- a/src/include/commands/copyfrom_internal.h+++ b/src/include/commands/copyfrom_internal.h@@ -39,16 +39,6 @@ typedef enum EolType
EOL_CRNL
} EolType;
-/*- * Represents the heap insert method to be used during COPY FROM.- */-typedef enum CopyInsertMethod-{- CIM_SINGLE, /* use table_tuple_insert or fdw routine */- CIM_MULTI, /* always use table_multi_insert */- CIM_MULTI_CONDITIONAL /* use table_multi_insert only if valid */-} CopyInsertMethod;-
/*
* This struct contains all the state variables used throughout a COPY FROM
* operation.
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.hindex 82925b4b63..67d338920f 100644--- a/src/include/executor/executor.h+++ b/src/include/executor/executor.h@@ -203,6 +203,7 @@ extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Index resultRelationIndex,
ResultRelInfo *partition_root_rri,
int instrument_options);
+extern bool ExecMultiInsertAllowed(const ResultRelInfo *rri);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid,
ResultRelInfo *rootRelInfo);
extern List *ExecGetAncestorResultRels(EState *estate, ResultRelInfo *resultRelInfo);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.hindex 44dd73fc80..5617810279 100644--- a/src/include/nodes/execnodes.h+++ b/src/include/nodes/execnodes.h@@ -528,7 +528,10 @@ typedef struct ResultRelInfo
TupleConversionMap *ri_ChildToRootMap;
bool ri_ChildToRootMapValid;
- /* for use by copyfrom.c when performing multi-inserts */+ /* True if okay to use multi-insert on this relation */+ bool ri_usesMultiInsert;++ /* Buffer allocated to this relation when using multi-insert mode */
struct CopyMultiInsertBuffer *ri_CopyMultiInsertBuffer;
/*
--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, etsuro.fujita@gmail.com, pryzby@telsasoft.com, zyu@yugabyte.com, amitlangote09@gmail.com, ashutosh.bapat.oss@gmail.com, tomas.vondra@enterprisedb.com, houzj.fnst@fujitsu.com
Subject: Re: Fast COPY FROM based on batch insert
In-Reply-To: <45ad1b94-339b-a841-8885-67ebe6fc5bc1@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox