19.10.2020 09:12, tsunakawa.ta...@fujitsu.com пишет:
Hello Andrey-san,
Thank you for challenging an interesting feature. Below are my review comments.
(1)
- /* for use by copy.c when performing multi-inserts */
+ /*
+ * The following fields are currently only relevant to copy.c.
+ *
+ * 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;
} ResultRelInfo;
It's better to place the new bool member next to an existing bool member, so
that the structure doesn't get larger.
Here the variable position chosen in accordance with the logical
meaning. I don't see large problem with size of this structure.
(2)
+ Assert(rri->ri_usesMultiInsert == false);
As the above assertion represents, I'm afraid the semantics of
ExecRelationAllowsMultiInsert() and ResultRelInfo->ri_usesMultiInsert are
unclear. In CopyFrom(), ri_usesMultiInsert is set by also considering the
COPY-specific conditions:
+ if (!cstate->volatile_defexprs &&
+ !contain_volatile_functions(cstate->whereClause) &&
+ ExecRelationAllowsMultiInsert(target_resultRelInfo, NULL))
+ target_resultRelInfo->ri_usesMultiInsert = true;
On the other hand, in below ExecInitPartitionInfo(), ri_usesMultiInsert is set
purely based on the relation's characteristics.
+ leaf_part_rri->ri_usesMultiInsert =
+ ExecRelationAllowsMultiInsert(leaf_part_rri, rootResultRelInfo);
In addition to these differences, I think it's a bit confusing that the
function itself doesn't record the check result in ri_usesMultiInsert.
It's probably easy to understand to not add ri_usesMultiInsert, and the
function just encapsulates the check logic based solely on the relation
characteristics and returns the result. So, the argument is just one
ResultRelInfo. The caller (e.g. COPY) combines the function result with other
specific conditions.
I can't fully agreed with this suggestion. We do so because in the
future anyone can call this code from another subsystem for another
purposes. And we want all the relation-related restrictions contains in
one routine. CopyState-related restrictions used in copy.c only and
taken out of this function.
(3)
+typedef void (*BeginForeignCopy_function) (ModifyTableState *mtstate,
+
ResultRelInfo *rinfo);
+
+typedef void (*EndForeignCopy_function) (EState *estate,
+
ResultRelInfo *rinfo);
+
+typedef void (*ExecForeignCopy_function) (ResultRelInfo *rinfo,
+
TupleTableSlot **slots,
+
int nslots);
To align with other function groups, it's better to place the functions in
order of Begin, Exec, and End.
Ok, thanks.
(4)
+ /* COPY a bulk of tuples into a foreign relation */
+ BeginForeignCopy_function BeginForeignCopy;
+ EndForeignCopy_function EndForeignCopy;
+ ExecForeignCopy_function ExecForeignCopy;
To align with the other functions' comment, the comment should be:
/* Support functions for COPY */
Agreed
(5)
+<programlisting>
+TupleTableSlot *
+ExecForeignCopy(ResultRelInfo *rinfo,
+ TupleTableSlot **slots,
+ int nslots);
+</programlisting>
+
+ Copy a bulk of tuples into the foreign table.
+ <literal>estate</literal> is global execution state for the query.
The return type is void.
Agreed
(6)
+ <literal>nslots</literal> cis a number of tuples in the
<literal>slots</literal>
cis -> is
Ok
(7)
+ <para>
+ If the <function>ExecForeignCopy</function> pointer is set to
+ <literal>NULL</literal>, attempts to insert into the foreign table will
fail
+ with an error message.
+ </para>
"attempts to insert into" should be "attempts to run COPY on", because it's
used for COPY.
Furthermore, if ExecForeignCopy is NULL, COPY should use ExecForeignInsert()
instead, right? Otherwise, existing FDWs would become unable to be used for
COPY.
Thanks
(8)
+ bool pipe = (filename == NULL) && (data_dest_cb == NULL);
The above pipe in BeginCopyTo() is changed to not match pipe in DoCopyTo(),
which only refers to filename. Should pipe in DoCopyTo() also be changed? If
no, the use of the same variable name for different conditions is confusing.
Ok
(9)
- * partitions will be done later.
+- * partitions will be done later.
This is an unintended addition of '-'?
Ok
(10)
- if (resultRelInfo->ri_FdwRoutine != NULL &&
- resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
- resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
-
resultRelInfo);
+ if (target_resultRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (target_resultRelInfo->ri_usesMultiInsert)
+
target_resultRelInfo->ri_FdwRoutine->BeginForeignCopy(mtstate,
+
resultRelInfo);
+ else if
(target_resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
+
target_resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
+
resultRelInfo);
+ }
BeginForeignCopy() should be called if it's defined, because BeginForeignCopy()
is an optional function.
Maybe
(11)
+ oldcontext =
MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+
+ table_multi_insert(resultRelInfo->ri_RelationDesc,
+ slots,
The extra empty line seems unintended.
Ok
(12)
@@ -585,7 +583,8 @@ CopySendEndOfRow(CopyState cstate)
(void) pq_putmessage('d', fe_msgbuf->data,
fe_msgbuf->len);
break;
case COPY_CALLBACK:
- Assert(false); /* Not yet supported. */
+ CopySendChar(cstate, '\n');
+ cstate->data_dest_cb(fe_msgbuf->data, fe_msgbuf->len);
As in the COPY_FILENAME case, shouldn't the line terminator be sent only in
text format, and be changed to \r\n on Windows? I'm asking this as I'm
probably a bit confused about in what situation COPY_CALLBACK could be used. I
thought the binary format and \r\n line terminator could be necessary depending
on the FDW implementation.
Ok. I don't want to allow binary format in callback mode right now. It
is not a subject of this patch. Maybe it will be done later.
(13)
@@ -1001,9 +1001,13 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
* If the partition is a foreign table, let the FDW init itself for
* routing tuples to the partition.
*/
- if (partRelInfo->ri_FdwRoutine != NULL &&
- partRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
- partRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
partRelInfo);
+ if (partRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (partRelInfo->ri_usesMultiInsert)
+ partRelInfo->ri_FdwRoutine->BeginForeignCopy(mtstate,
partRelInfo);
+ else if (partRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
+ partRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
partRelInfo);
+ }
BeginForeignCopy() should be called only if it's defined, because
BeginForeignCopy() is an optional function.
Ok
(14)
@@ -1205,10 +1209,18 @@ ExecCleanupTupleRouting(ModifyTableState *mtstate,
ResultRelInfo *resultRelInfo = proute->partitions[i];
/* Allow any FDWs to shut down */
- if (resultRelInfo->ri_FdwRoutine != NULL &&
- resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
-
resultRelInfo->ri_FdwRoutine->EndForeignInsert(mtstate->ps.state,
-
resultRelInfo);
+ if (resultRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (resultRelInfo->ri_usesMultiInsert)
+ {
+
Assert(resultRelInfo->ri_FdwRoutine->EndForeignCopy != NULL);
+
resultRelInfo->ri_FdwRoutine->EndForeignCopy(mtstate->ps.state,
+
resultRelInfo);
+ }
+ else if (resultRelInfo->ri_FdwRoutine->EndForeignInsert
!= NULL)
+
resultRelInfo->ri_FdwRoutine->EndForeignInsert(mtstate->ps.state,
+
resultRelInfo);
+ }
EndForeignCopy() is an optional function, isn't it? That is, it's called if
it's defined.
ri_usesMultiInsert must guarantee that we will use multi-insertions. And
we use only assertions to control this.
(15)
+static void
+pgfdw_copy_dest_cb(void *buf, int len)
+{
+ PGconn *conn = copy_fmstate->conn;
+
+ if (PQputCopyData(conn, (char *) buf, len) <= 0)
+ {
+ PGresult *res = PQgetResult(conn);
+
+ pgfdw_report_error(ERROR, res, conn, true, copy_fmstate->query);
+ }
+}
The following page says "Use PQerrorMessage to retrieve details if the return value
is -1." So, it's correct to not use PGresult here and pass NULL as the second
argument to pgfdw_report_error().
https://www.postgresql.org/docs/devel/libpq-copy.html
Ok
(16)
+ for (i = 0; i < nslots; i++)
+ CopyOneRowTo(fmstate->cstate, slots[i]);
+
+ status = true;
+ }
I'm afraid it's not intuitive what "status is true" means. I think
copy_data_sent or copy_send_success would be better for the variable name.
Agreed. renamed to 'OK'. In accordance with psql/copy.c.
(17)
+ if (PQputCopyEnd(conn, status ? NULL : _("canceled by server"))
<= 0 ||
+ PQflush(conn))
+ ereport(ERROR,
+ (errmsg("error returned by PQputCopyEnd:
%s",
+ PQerrorMessage(conn))));
As the places that call PQsendQuery(), it seems preferrable to call
pgfdw_report_error() here too.
Agreed
--
regards,
Andrey Lepikhov
Postgres Professional
>From 1d7e4fb1f7990a5d5db53e8ddf6809f048c17f34 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Mon, 19 Oct 2020 16:24:55 +0500
Subject: [PATCH 2/2] Fast COPY FROM into the foreign or sharded table.
This feature enables bulk COPY into foreign table in the case of
multi inserts is possible and foreign table has non-zero number of columns.
FDWAPI was extended by next routines:
* BeginForeignCopy
* EndForeignCopy
* ExecForeignCopy
BeginForeignCopy and EndForeignCopy initialize and free
the CopyState of bulk COPY. The ExecForeignCopy routine send
'COPY ... FROM STDIN' command to the foreign server, in iterative
manner send tuples by CopyTo() machinery, send EOF to this connection.
Code that constructed list of columns for a given foreign relation
in the deparseAnalyzeSql() routine is separated to the deparseRelColumnList().
It is reused in the deparseCopyFromSql().
Added TAP-tests on the specific corner cases of COPY FROM STDIN operation.
By the analogy of CopyFrom() the CopyState structure was extended
with data_dest_cb callback. It is used for send text representation
of a tuple to a custom destination.
The PgFdwModifyState structure is extended with the cstate field.
It is needed for avoid repeated initialization of CopyState. ALso for this
reason CopyTo() routine was split into the set of routines CopyToStart()/
CopyTo()/CopyToFinish().
Enum CopyInsertMethod removed. This logic implements by ri_usesMultiInsert
field of the ResultRelInfo sructure.
Discussion:
https://www.postgresql.org/message-id/flat/3d0909dc-3691-a576-208a-90986e55489f%40postgrespro.ru
Authors: Andrey Lepikhov, Ashutosh Bapat, Amit Langote
---
contrib/postgres_fdw/deparse.c | 60 ++++-
.../postgres_fdw/expected/postgres_fdw.out | 46 +++-
contrib/postgres_fdw/postgres_fdw.c | 137 ++++++++++
contrib/postgres_fdw/postgres_fdw.h | 1 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 45 ++++
doc/src/sgml/fdwhandler.sgml | 73 ++++++
src/backend/commands/copy.c | 236 +++++++++++-------
src/backend/executor/execMain.c | 8 +-
src/backend/executor/execPartition.c | 29 ++-
src/include/commands/copy.h | 11 +
src/include/foreign/fdwapi.h | 15 ++
11 files changed, 554 insertions(+), 107 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index 2d44df19fe..fa7740163d 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -184,6 +184,8 @@ static void appendAggOrderBy(List *orderList, List *targetList,
static void appendFunctionName(Oid funcid, deparse_expr_cxt *context);
static Node *deparseSortGroupClause(Index ref, List *tlist, bool force_colno,
deparse_expr_cxt *context);
+static List *deparseRelColumnList(StringInfo buf, Relation rel,
+ bool enclose_in_parens);
/*
* Helper functions
@@ -1758,6 +1760,20 @@ deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
withCheckOptionList, returningList, retrieved_attrs);
}
+/*
+ * Deparse COPY FROM into given buf.
+ * We need to use list of parameters at each query.
+ */
+void
+deparseCopyFromSql(StringInfo buf, Relation rel)
+{
+ appendStringInfoString(buf, "COPY ");
+ deparseRelation(buf, rel);
+ (void) deparseRelColumnList(buf, rel, true);
+
+ appendStringInfoString(buf, " FROM STDIN ");
+}
+
/*
* deparse remote UPDATE statement
*
@@ -2061,6 +2077,30 @@ deparseAnalyzeSizeSql(StringInfo buf, Relation rel)
*/
void
deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
+{
+ appendStringInfoString(buf, "SELECT ");
+ *retrieved_attrs = deparseRelColumnList(buf, rel, false);
+
+ /* Don't generate bad syntax for zero-column relation. */
+ if (list_length(*retrieved_attrs) == 0)
+ appendStringInfoString(buf, "NULL");
+
+ /*
+ * Construct FROM clause
+ */
+ appendStringInfoString(buf, " FROM ");
+ deparseRelation(buf, rel);
+}
+
+/*
+ * Construct the list of columns of given foreign relation in the order they
+ * appear in the tuple descriptor of the relation. Ignore any dropped columns.
+ * Use column names on the foreign server instead of local names.
+ *
+ * Optionally enclose the list in parantheses.
+ */
+static List *
+deparseRelColumnList(StringInfo buf, Relation rel, bool enclose_in_parens)
{
Oid relid = RelationGetRelid(rel);
TupleDesc tupdesc = RelationGetDescr(rel);
@@ -2069,10 +2109,8 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
List *options;
ListCell *lc;
bool first = true;
+ List *retrieved_attrs = NIL;
- *retrieved_attrs = NIL;
-
- appendStringInfoString(buf, "SELECT ");
for (i = 0; i < tupdesc->natts; i++)
{
/* Ignore dropped columns. */
@@ -2081,6 +2119,9 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
if (!first)
appendStringInfoString(buf, ", ");
+ else if (enclose_in_parens)
+ appendStringInfoChar(buf, '(');
+
first = false;
/* Use attribute name or column_name option. */
@@ -2100,18 +2141,13 @@ deparseAnalyzeSql(StringInfo buf, Relation rel, List **retrieved_attrs)
appendStringInfoString(buf, quote_identifier(colname));
- *retrieved_attrs = lappend_int(*retrieved_attrs, i + 1);
+ retrieved_attrs = lappend_int(retrieved_attrs, i + 1);
}
- /* Don't generate bad syntax for zero-column relation. */
- if (first)
- appendStringInfoString(buf, "NULL");
+ if (enclose_in_parens && list_length(retrieved_attrs) > 0)
+ appendStringInfoChar(buf, ')');
- /*
- * Construct FROM clause
- */
- appendStringInfoString(buf, " FROM ");
- deparseRelation(buf, rel);
+ return retrieved_attrs;
}
/*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 2d88d06358..be8db5ac63 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8076,8 +8076,9 @@ copy rem2 from stdin;
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"
+CONTEXT: COPY loc2, line 1: "-1 xyzzy"
+remote SQL command: COPY public.loc2(f1, f2) FROM STDIN
+COPY rem2, line 2
select * from rem2;
f1 | f2
----+-----
@@ -8088,6 +8089,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();
@@ -8196,6 +8210,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: COPY public.loc2(f1, f2) FROM STDIN
+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;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 9c5aaacc51..1657a20d9b 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -18,6 +18,7 @@
#include "access/sysattr.h"
#include "access/table.h"
#include "catalog/pg_class.h"
+#include "commands/copy.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/vacuum.h"
@@ -190,6 +191,7 @@ typedef struct PgFdwModifyState
/* for update row movement if subplan result rel */
struct PgFdwModifyState *aux_fmstate; /* foreign-insert state, if
* created */
+ CopyState cstate; /* foreign COPY state, if used */
} PgFdwModifyState;
/*
@@ -356,6 +358,13 @@ static void postgresBeginForeignInsert(ModifyTableState *mtstate,
ResultRelInfo *resultRelInfo);
static void postgresEndForeignInsert(EState *estate,
ResultRelInfo *resultRelInfo);
+static void postgresBeginForeignCopy(ModifyTableState *mtstate,
+ ResultRelInfo *resultRelInfo);
+static void postgresEndForeignCopy(EState *estate,
+ ResultRelInfo *resultRelInfo);
+static void postgresExecForeignCopy(ResultRelInfo *resultRelInfo,
+ TupleTableSlot **slots,
+ int nslots);
static int postgresIsForeignRelUpdatable(Relation rel);
static bool postgresPlanDirectModify(PlannerInfo *root,
ModifyTable *plan,
@@ -534,6 +543,9 @@ postgres_fdw_handler(PG_FUNCTION_ARGS)
routine->EndForeignModify = postgresEndForeignModify;
routine->BeginForeignInsert = postgresBeginForeignInsert;
routine->EndForeignInsert = postgresEndForeignInsert;
+ routine->BeginForeignCopy = postgresBeginForeignCopy;
+ routine->EndForeignCopy = postgresEndForeignCopy;
+ routine->ExecForeignCopy = postgresExecForeignCopy;
routine->IsForeignRelUpdatable = postgresIsForeignRelUpdatable;
routine->PlanDirectModify = postgresPlanDirectModify;
routine->BeginDirectModify = postgresBeginDirectModify;
@@ -2051,6 +2063,131 @@ postgresEndForeignInsert(EState *estate,
finish_foreign_modify(fmstate);
}
+static PgFdwModifyState *copy_fmstate = NULL;
+
+static void
+pgfdw_copy_dest_cb(void *buf, int len)
+{
+ PGconn *conn = copy_fmstate->conn;
+
+ if (PQputCopyData(conn, (char *) buf, len) <= 0)
+ pgfdw_report_error(ERROR, NULL, conn, false, copy_fmstate->query);
+}
+
+/*
+ *
+ * postgresBeginForeignCopy
+ * Begin an COPY operation on a foreign table
+ */
+static void
+postgresBeginForeignCopy(ModifyTableState *mtstate,
+ ResultRelInfo *resultRelInfo)
+{
+ PgFdwModifyState *fmstate;
+ StringInfoData sql;
+ RangeTblEntry *rte;
+ Relation rel = resultRelInfo->ri_RelationDesc;
+
+ rte = exec_rt_fetch(resultRelInfo->ri_RangeTableIndex, mtstate->ps.state);
+ initStringInfo(&sql);
+ deparseCopyFromSql(&sql, rel);
+
+ fmstate = create_foreign_modify(mtstate->ps.state,
+ rte,
+ resultRelInfo,
+ CMD_INSERT,
+ NULL,
+ sql.data,
+ NIL,
+ false,
+ NIL);
+
+ fmstate->cstate = BeginCopyTo(NULL, NULL, RelationGetDescr(rel), NULL,
+ InvalidOid, NULL, false, pgfdw_copy_dest_cb,
+ NIL, NIL);
+ CopyToStart(fmstate->cstate);
+ resultRelInfo->ri_FdwState = fmstate;
+}
+
+/*
+ * postgresEndForeignCopy
+ * Finish an COPY operation on a foreign table
+ */
+static void
+postgresEndForeignCopy(EState *estate, ResultRelInfo *resultRelInfo)
+{
+ PgFdwModifyState *fmstate = (PgFdwModifyState *) resultRelInfo->ri_FdwState;
+
+ /* Check correct use of CopyIn FDW API. */
+ Assert(fmstate->cstate != NULL);
+ CopyToFinish(fmstate->cstate);
+ pfree(fmstate->cstate);
+ fmstate->cstate = NULL;
+ finish_foreign_modify(fmstate);
+}
+
+/*
+ *
+ * postgresExecForeignCopy
+ * Send a number of tuples to the foreign relation.
+ */
+static void
+postgresExecForeignCopy(ResultRelInfo *resultRelInfo,
+ TupleTableSlot **slots, int nslots)
+{
+ PgFdwModifyState *fmstate = resultRelInfo->ri_FdwState;
+ PGresult *res;
+ PGconn *conn = fmstate->conn;
+ bool OK = false;
+ int i;
+
+ /* Check correct use of CopyIn FDW API. */
+ Assert(fmstate->cstate != NULL);
+ Assert(copy_fmstate == NULL);
+
+ res = PQexec(conn, fmstate->query);
+ if (PQresultStatus(res) != PGRES_COPY_IN)
+ pgfdw_report_error(ERROR, res, conn, true, fmstate->query);
+ PQclear(res);
+
+ PG_TRY();
+ {
+ copy_fmstate = fmstate;
+ for (i = 0; i < nslots; i++)
+ CopyOneRowTo(fmstate->cstate, slots[i]);
+
+ OK = true;
+ }
+ PG_FINALLY();
+ {
+ copy_fmstate = NULL; /* Detect problems */
+
+ /* Finish COPY IN protocol. It is needed to do after successful copy or
+ * after an error.
+ */
+ if (PQputCopyEnd(conn, OK ? NULL : _("canceled by server")) <= 0 ||
+ PQflush(conn))
+ pgfdw_report_error(ERROR, NULL, fmstate->conn, false, fmstate->query);
+
+ /* After successfully sending an EOF signal, check command OK. */
+ res = PQgetResult(conn);
+ if ((!OK && PQresultStatus(res) != PGRES_FATAL_ERROR) ||
+ (OK && PQresultStatus(res) != PGRES_COMMAND_OK))
+ pgfdw_report_error(ERROR, res, fmstate->conn, true, fmstate->query);
+
+ PQclear(res);
+ /* Do this to ensure we've pumped libpq back to idle state */
+ if (PQgetResult(conn) != NULL)
+ ereport(ERROR,
+ (errmsg("unexpected extra results during COPY of table: %s",
+ PQerrorMessage(conn))));
+
+ if (!OK)
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+}
+
/*
* postgresIsForeignRelUpdatable
* Determine whether a foreign table supports INSERT, UPDATE and/or
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index eef410db39..8fc5ff018f 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -162,6 +162,7 @@ extern void deparseInsertSql(StringInfo buf, RangeTblEntry *rte,
List *targetAttrs, bool doNothing,
List *withCheckOptionList, List *returningList,
List **retrieved_attrs);
+extern void deparseCopyFromSql(StringInfo buf, Relation rel);
extern void deparseUpdateSql(StringInfo buf, RangeTblEntry *rte,
Index rtindex, Relation rel,
List *targetAttrs,
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 7581c5417b..22dcd12f02 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2212,6 +2212,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();
@@ -2312,6 +2329,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;
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index 9c9293414c..a9a7402440 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -796,6 +796,79 @@ EndForeignInsert(EState *estate,
<para>
<programlisting>
+void
+BeginForeignCopy(ModifyTableState *mtstate,
+ ResultRelInfo *rinfo);
+</programlisting>
+
+ Begin executing an copy operation on a foreign table. This routine is
+ called right before the first call of <function>ExecForeignCopy</function>
+ routine for the foreign table. It should perform any initialization needed
+ prior to the actual COPY FROM operation.
+ Subsequently, <function>ExecForeignCopy</function> will be called for
+ a bulk of tuples to be copied into the foreign table.
+ </para>
+
+ <para>
+ <literal>mtstate</literal> is the overall state of the
+ <structname>ModifyTable</structname> plan node being executed; global data about
+ the plan and execution state is available via this structure.
+ <literal>rinfo</literal> is the <structname>ResultRelInfo</structname> struct describing
+ the target foreign table. (The <structfield>ri_FdwState</structfield> field of
+ <structname>ResultRelInfo</structname> is available for the FDW to store any
+ private state it needs for this operation.)
+ </para>
+
+ <para>
+ When this is called by a <command>COPY FROM</command> command, the
+ plan-related global data in <literal>mtstate</literal> is not provided.
+ </para>
+
+ <para>
+ If the <function>BeginForeignCopy</function> pointer is set to
+ <literal>NULL</literal>, no action is taken for the initialization.
+ </para>
+
+ <para>
+<programlisting>
+void
+EndForeignCopy(EState *estate,
+ ResultRelInfo *rinfo);
+</programlisting>
+
+ End the copy operation and release resources. It is normally not important
+ to release palloc'd memory, but for example open files and connections
+ to remote servers should be cleaned up.
+ </para>
+
+ <para>
+ If the <function>EndForeignCopy</function> pointer is set to
+ <literal>NULL</literal>, no action is taken for the termination.
+ </para>
+
+ <para>
+<programlisting>
+void
+ExecForeignCopy(ResultRelInfo *rinfo,
+ TupleTableSlot **slots,
+ int nslots);
+</programlisting>
+
+ Copy a bulk of tuples into the foreign table.
+ <literal>rinfo</literal> is the <structname>ResultRelInfo</structname> struct describing
+ the target foreign table.
+ <literal>slots</literal> contains the tuples to be inserted; it will match the
+ row-type definition of the foreign table.
+ <literal>nslots</literal> is a number of tuples in the <literal>slots</literal>
+ </para>
+
+ <para>
+ If the <function>ExecForeignCopy</function> pointer is set to
+ <literal>NULL</literal>, the <function>ExecForeignInsert</function> routine will be used to run COPY on the foreign table.
+ </para>
+
+ <para>
+<programlisting>
int
IsForeignRelUpdatable(Relation rel);
</programlisting>
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 83ce196a45..26d79ad051 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -118,11 +118,14 @@ typedef struct CopyStateData
/* parameters from the COPY command */
Relation rel; /* relation to copy to or from */
+ TupleDesc tupDesc; /* COPY TO will be used for manual tuple copying
+ * into the destination */
QueryDesc *queryDesc; /* executable query to copy from */
List *attnumlist; /* integer list of attnums to copy */
char *filename; /* filename, or NULL for STDIN/STDOUT */
bool is_program; /* is 'filename' a program to popen? */
copy_data_source_cb data_source_cb; /* function for reading data */
+ copy_data_dest_cb data_dest_cb; /* function for writing data */
bool binary; /* binary format? */
bool freeze; /* freeze rows on loading? */
bool csv_mode; /* Comma Separated Value format? */
@@ -349,17 +352,12 @@ static const char BinarySignature[11] = "PGCOPY\n\377\r\n\0";
/* non-export function prototypes */
static CopyState BeginCopy(ParseState *pstate, bool is_from, Relation rel,
- RawStmt *raw_query, Oid queryRelId, List *attnamelist,
- List *options);
+ TupleDesc srcTupDesc, RawStmt *raw_query,
+ Oid queryRelId, List *attnamelist, List *options);
static void EndCopy(CopyState cstate);
static void ClosePipeToProgram(CopyState cstate);
-static CopyState BeginCopyTo(ParseState *pstate, Relation rel, RawStmt *query,
- Oid queryRelId, const char *filename, bool is_program,
- List *attnamelist, List *options);
-static void EndCopyTo(CopyState cstate);
static uint64 DoCopyTo(CopyState cstate);
static uint64 CopyTo(CopyState cstate);
-static void CopyOneRowTo(CopyState cstate, TupleTableSlot *slot);
static bool CopyReadLine(CopyState cstate);
static bool CopyReadLineText(CopyState cstate);
static int CopyReadAttributesText(CopyState cstate);
@@ -585,7 +583,13 @@ CopySendEndOfRow(CopyState cstate)
(void) pq_putmessage('d', fe_msgbuf->data, fe_msgbuf->len);
break;
case COPY_CALLBACK:
- Assert(false); /* Not yet supported. */
+ Assert(!cstate->binary);
+#ifndef WIN32
+ CopySendChar(cstate, '\n');
+#else
+ CopySendString(cstate, "\r\n");
+#endif
+ cstate->data_dest_cb(fe_msgbuf->data, fe_msgbuf->len);
break;
}
@@ -1114,8 +1118,8 @@ DoCopy(ParseState *pstate, const CopyStmt *stmt,
}
else
{
- cstate = BeginCopyTo(pstate, rel, query, relid,
- stmt->filename, stmt->is_program,
+ cstate = BeginCopyTo(pstate, rel, NULL, query, relid,
+ stmt->filename, stmt->is_program, NULL,
stmt->attlist, stmt->options);
*processed = DoCopyTo(cstate); /* copy from database to file */
EndCopyTo(cstate);
@@ -1501,6 +1505,7 @@ static CopyState
BeginCopy(ParseState *pstate,
bool is_from,
Relation rel,
+ TupleDesc srcTupDesc,
RawStmt *raw_query,
Oid queryRelId,
List *attnamelist,
@@ -1536,6 +1541,11 @@ BeginCopy(ParseState *pstate,
tupDesc = RelationGetDescr(cstate->rel);
}
+ else if (srcTupDesc)
+ {
+ Assert(!raw_query && !is_from);
+ tupDesc = cstate->tupDesc = srcTupDesc;
+ }
else
{
List *rewritten;
@@ -1862,20 +1872,25 @@ EndCopy(CopyState cstate)
/*
* Setup CopyState to read tuples from a table or a query for COPY TO.
*/
-static CopyState
+CopyState
BeginCopyTo(ParseState *pstate,
Relation rel,
+ TupleDesc tupDesc,
RawStmt *query,
Oid queryRelId,
const char *filename,
bool is_program,
+ copy_data_dest_cb data_dest_cb,
List *attnamelist,
List *options)
{
CopyState cstate;
- bool pipe = (filename == NULL);
+ bool pipe = (filename == NULL) && (data_dest_cb == NULL);
MemoryContext oldcontext;
+ /* Impossible to mix CopyTo modes */
+ Assert(rel == NULL || tupDesc == NULL);
+
if (rel != NULL && rel->rd_rel->relkind != RELKIND_RELATION)
{
if (rel->rd_rel->relkind == RELKIND_VIEW)
@@ -1914,8 +1929,9 @@ BeginCopyTo(ParseState *pstate,
RelationGetRelationName(rel))));
}
- cstate = BeginCopy(pstate, false, rel, query, queryRelId, attnamelist,
- options);
+ cstate = BeginCopy(pstate, false, rel, tupDesc, query, queryRelId,
+ attnamelist, options);
+
oldcontext = MemoryContextSwitchTo(cstate->copycontext);
if (pipe)
@@ -1924,6 +1940,11 @@ BeginCopyTo(ParseState *pstate,
if (whereToSendOutput != DestRemote)
cstate->copy_file = stdout;
}
+ else if (data_dest_cb)
+ {
+ cstate->copy_dest = COPY_CALLBACK;
+ cstate->data_dest_cb = data_dest_cb;
+ }
else
{
cstate->filename = pstrdup(filename);
@@ -2001,7 +2022,7 @@ BeginCopyTo(ParseState *pstate,
static uint64
DoCopyTo(CopyState cstate)
{
- bool pipe = (cstate->filename == NULL);
+ bool pipe = (cstate->filename == NULL) && (cstate->data_dest_cb == NULL);
bool fe_copy = (pipe && whereToSendOutput == DestRemote);
uint64 processed;
@@ -2010,7 +2031,9 @@ DoCopyTo(CopyState cstate)
if (fe_copy)
SendCopyBegin(cstate);
+ CopyToStart(cstate);
processed = CopyTo(cstate);
+ CopyToFinish(cstate);
if (fe_copy)
SendCopyEnd(cstate);
@@ -2033,7 +2056,7 @@ DoCopyTo(CopyState cstate)
/*
* Clean up storage and release resources for COPY TO.
*/
-static void
+void
EndCopyTo(CopyState cstate)
{
if (cstate->queryDesc != NULL)
@@ -2049,19 +2072,22 @@ EndCopyTo(CopyState cstate)
EndCopy(cstate);
}
-/*
- * Copy from relation or query TO file.
+/* Start COPY TO operation.
+ * Separated to the routine to prevent duplicate operations in the case of
+ * manual mode, where tuples are copied to the destination one by one, by call of
+ * the CopyOneRowTo() routine.
*/
-static uint64
-CopyTo(CopyState cstate)
+void
+CopyToStart(CopyState cstate)
{
TupleDesc tupDesc;
int num_phys_attrs;
ListCell *cur;
- uint64 processed;
if (cstate->rel)
tupDesc = RelationGetDescr(cstate->rel);
+ else if (cstate->tupDesc)
+ tupDesc = cstate->tupDesc;
else
tupDesc = cstate->queryDesc->tupDesc;
num_phys_attrs = tupDesc->natts;
@@ -2148,6 +2174,32 @@ CopyTo(CopyState cstate)
CopySendEndOfRow(cstate);
}
}
+}
+
+/*
+ * Finish COPY TO operation.
+ */
+void
+CopyToFinish(CopyState cstate)
+{
+ if (cstate->binary)
+ {
+ /* Generate trailer for a binary copy */
+ CopySendInt16(cstate, -1);
+ /* Need to flush out the trailer */
+ CopySendEndOfRow(cstate);
+ }
+
+ MemoryContextDelete(cstate->rowcontext);
+}
+
+/*
+ * Copy from relation or query TO file.
+ */
+static uint64
+CopyTo(CopyState cstate)
+{
+ uint64 processed;
if (cstate->rel)
{
@@ -2179,24 +2231,13 @@ CopyTo(CopyState cstate)
ExecutorRun(cstate->queryDesc, ForwardScanDirection, 0L, true);
processed = ((DR_copy *) cstate->queryDesc->dest)->processed;
}
-
- if (cstate->binary)
- {
- /* Generate trailer for a binary copy */
- CopySendInt16(cstate, -1);
- /* Need to flush out the trailer */
- CopySendEndOfRow(cstate);
- }
-
- MemoryContextDelete(cstate->rowcontext);
-
return processed;
}
/*
* Emit one row during CopyTo().
*/
-static void
+void
CopyOneRowTo(CopyState cstate, TupleTableSlot *slot)
{
bool need_delim = false;
@@ -2486,54 +2527,63 @@ 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);
-
- for (i = 0; i < nused; i++)
+ if (resultRelInfo->ri_RelationDesc->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /* Flush into foreign table or partition */
+ resultRelInfo->ri_FdwRoutine->ExecForeignCopy(resultRelInfo,
+ slots,
+ nused);
+ }
+ else
{
/*
- * If there are any indexes, update them for all the inserted tuples,
- * and run AFTER ROW INSERT triggers.
+ * table_multi_insert may leak memory, so switch to short-lived memory
+ * context before calling it.
*/
- if (resultRelInfo->ri_NumIndices > 0)
- {
- List *recheckIndexes;
-
- cstate->cur_lineno = buffer->linenos[i];
- recheckIndexes =
- ExecInsertIndexTuples(resultRelInfo,
- buffer->slots[i], estate, false, NULL,
- NIL);
- ExecARInsertTriggers(estate, resultRelInfo,
- slots[i], recheckIndexes,
- cstate->transition_capture);
- list_free(recheckIndexes);
- }
+ oldcontext = MemoryContextSwitchTo(GetPerTupleMemoryContext(estate));
+ table_multi_insert(resultRelInfo->ri_RelationDesc,
+ slots,
+ nused,
+ mycid,
+ ti_options,
+ buffer->bistate);
+ MemoryContextSwitchTo(oldcontext);
- /*
- * There's no indexes, but see if we need to run AFTER ROW INSERT
- * triggers anyway.
- */
- else if (resultRelInfo->ri_TrigDesc != NULL &&
- (resultRelInfo->ri_TrigDesc->trig_insert_after_row ||
- resultRelInfo->ri_TrigDesc->trig_insert_new_table))
+ for (i = 0; i < nused; i++)
{
- cstate->cur_lineno = buffer->linenos[i];
- ExecARInsertTriggers(estate, resultRelInfo,
- slots[i], NIL, cstate->transition_capture);
- }
+ /*
+ * If there are any indexes, update them for all the inserted tuples,
+ * and run AFTER ROW INSERT triggers.
+ */
+ if (resultRelInfo->ri_NumIndices > 0)
+ {
+ List *recheckIndexes;
+
+ cstate->cur_lineno = buffer->linenos[i];
+ recheckIndexes =
+ ExecInsertIndexTuples(resultRelInfo, buffer->slots[i],
+ estate, false, NULL, NIL);
+ ExecARInsertTriggers(estate, resultRelInfo,
+ slots[i], recheckIndexes,
+ cstate->transition_capture);
+ list_free(recheckIndexes);
+ }
- ExecClearTuple(slots[i]);
+ /*
+ * There's no indexes, but see if we need to run AFTER ROW INSERT
+ * triggers anyway.
+ */
+ else if (resultRelInfo->ri_TrigDesc != NULL &&
+ (resultRelInfo->ri_TrigDesc->trig_insert_after_row ||
+ resultRelInfo->ri_TrigDesc->trig_insert_new_table))
+ {
+ cstate->cur_lineno = buffer->linenos[i];
+ ExecARInsertTriggers(estate, resultRelInfo,
+ slots[i], NIL, cstate->transition_capture);
+ }
+
+ ExecClearTuple(slots[i]);
+ }
}
/* Mark that all slots are free */
@@ -2838,8 +2888,11 @@ CopyFrom(CopyState cstate)
* checked by calling ExecRelationAllowsMultiInsert(). 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 =
ExecRelationAllowsMultiInsert(target_resultRelInfo, NULL);
@@ -2863,10 +2916,18 @@ CopyFrom(CopyState cstate)
* Init COPY into foreign table. Initialization of copying into foreign
* partitions will be done later.
*/
- if (resultRelInfo->ri_FdwRoutine != NULL &&
- resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
- resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
- resultRelInfo);
+ if (target_resultRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (target_resultRelInfo->ri_usesMultiInsert)
+ {
+ Assert(target_resultRelInfo->ri_FdwRoutine->BeginForeignCopy != NULL);
+ target_resultRelInfo->ri_FdwRoutine->BeginForeignCopy(mtstate,
+ resultRelInfo);
+ }
+ else if (target_resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
+ target_resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
+ resultRelInfo);
+ }
/* Prepare to catch AFTER triggers. */
AfterTriggerBeginQuery();
@@ -3261,10 +3322,16 @@ CopyFrom(CopyState cstate)
ExecResetTupleTable(estate->es_tupleTable, false);
/* 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);
+ if (target_resultRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (target_resultRelInfo->ri_usesMultiInsert &&
+ target_resultRelInfo->ri_FdwRoutine->EndForeignCopy != NULL)
+ target_resultRelInfo->ri_FdwRoutine->EndForeignCopy(estate,
+ target_resultRelInfo);
+ else if (target_resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
+ target_resultRelInfo->ri_FdwRoutine->EndForeignInsert(estate,
+ target_resultRelInfo);
+ }
/* Tear down the multi-insert buffer data */
CopyMultiInsertInfoCleanup(&multiInsertInfo);
@@ -3315,7 +3382,8 @@ BeginCopyFrom(ParseState *pstate,
MemoryContext oldcontext;
bool volatile_defexprs;
- cstate = BeginCopy(pstate, true, rel, NULL, InvalidOid, attnamelist, options);
+ cstate = BeginCopy(pstate, true, rel, NULL, NULL, InvalidOid, attnamelist,
+ options);
oldcontext = MemoryContextSwitchTo(cstate->copycontext);
/* Initialize state variables */
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 0ad98ff0e7..9d465cebaf 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1291,8 +1291,12 @@ ExecRelationAllowsMultiInsert(const ResultRelInfo *rri,
rri->ri_TrigDesc->trig_insert_new_table)
return false;
- /* Foreign tables don't support multi-inserts. */
- if (rri->ri_FdwRoutine != NULL)
+ if (rri->ri_FdwRoutine != NULL &&
+ rri->ri_FdwRoutine->ExecForeignCopy == NULL)
+ /*
+ * Foreign tables don't support multi-inserts, unless their FDW
+ * provides the necessary COPY interface.
+ */
return false;
/* OK, caller can use multi-insert on this relation. */
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 121484374f..fae21356c7 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -1001,9 +1001,16 @@ ExecInitRoutingInfo(ModifyTableState *mtstate,
* If the partition is a foreign table, let the FDW init itself for
* routing tuples to the partition.
*/
- if (partRelInfo->ri_FdwRoutine != NULL &&
- partRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
- partRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate, partRelInfo);
+ if (partRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (partRelInfo->ri_usesMultiInsert)
+ {
+ Assert(partRelInfo->ri_FdwRoutine->BeginForeignCopy != NULL);
+ partRelInfo->ri_FdwRoutine->BeginForeignCopy(mtstate, partRelInfo);
+ }
+ else if (partRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
+ partRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate, partRelInfo);
+ }
partRelInfo->ri_PartitionInfo = partrouteinfo;
partRelInfo->ri_CopyMultiInsertBuffer = NULL;
@@ -1205,10 +1212,18 @@ ExecCleanupTupleRouting(ModifyTableState *mtstate,
ResultRelInfo *resultRelInfo = proute->partitions[i];
/* Allow any FDWs to shut down */
- if (resultRelInfo->ri_FdwRoutine != NULL &&
- resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
- resultRelInfo->ri_FdwRoutine->EndForeignInsert(mtstate->ps.state,
- resultRelInfo);
+ if (resultRelInfo->ri_FdwRoutine != NULL)
+ {
+ if (resultRelInfo->ri_usesMultiInsert)
+ {
+ Assert(resultRelInfo->ri_FdwRoutine->EndForeignCopy != NULL);
+ resultRelInfo->ri_FdwRoutine->EndForeignCopy(mtstate->ps.state,
+ resultRelInfo);
+ }
+ else if (resultRelInfo->ri_FdwRoutine->EndForeignInsert != NULL)
+ resultRelInfo->ri_FdwRoutine->EndForeignInsert(mtstate->ps.state,
+ resultRelInfo);
+ }
/*
* Check if this result rel is one belonging to the node's subplans,
diff --git a/src/include/commands/copy.h b/src/include/commands/copy.h
index c639833565..08309149ea 100644
--- a/src/include/commands/copy.h
+++ b/src/include/commands/copy.h
@@ -22,6 +22,7 @@
/* CopyStateData is private in commands/copy.c */
typedef struct CopyStateData *CopyState;
typedef int (*copy_data_source_cb) (void *outbuf, int minread, int maxread);
+typedef void (*copy_data_dest_cb) (void *outbuf, int len);
extern void DoCopy(ParseState *state, const CopyStmt *stmt,
int stmt_location, int stmt_len,
@@ -39,6 +40,16 @@ extern void CopyFromErrorCallback(void *arg);
extern uint64 CopyFrom(CopyState cstate);
+extern CopyState BeginCopyTo(ParseState *pstate, Relation rel,
+ TupleDesc tupDesc, RawStmt *query,
+ Oid queryRelId, const char *filename, bool is_program,
+ copy_data_dest_cb data_dest_cb, List *attnamelist,
+ List *options);
+extern void EndCopyTo(CopyState cstate);
+extern void CopyOneRowTo(CopyState cstate, TupleTableSlot *slot);
+extern void CopyToStart(CopyState cstate);
+extern void CopyToFinish(CopyState cstate);
+
extern DestReceiver *CreateCopyDestReceiver(void);
#endif /* COPY_H */
diff --git a/src/include/foreign/fdwapi.h b/src/include/foreign/fdwapi.h
index 95556dfb15..52b213f5aa 100644
--- a/src/include/foreign/fdwapi.h
+++ b/src/include/foreign/fdwapi.h
@@ -104,6 +104,16 @@ typedef void (*BeginForeignInsert_function) (ModifyTableState *mtstate,
typedef void (*EndForeignInsert_function) (EState *estate,
ResultRelInfo *rinfo);
+typedef void (*BeginForeignCopy_function) (ModifyTableState *mtstate,
+ ResultRelInfo *rinfo);
+
+typedef void (*ExecForeignCopy_function) (ResultRelInfo *rinfo,
+ TupleTableSlot **slots,
+ int nslots);
+
+typedef void (*EndForeignCopy_function) (EState *estate,
+ ResultRelInfo *rinfo);
+
typedef int (*IsForeignRelUpdatable_function) (Relation rel);
typedef bool (*PlanDirectModify_function) (PlannerInfo *root,
@@ -220,6 +230,11 @@ typedef struct FdwRoutine
IterateDirectModify_function IterateDirectModify;
EndDirectModify_function EndDirectModify;
+ /* Support functions for COPY into foreign tables */
+ BeginForeignCopy_function BeginForeignCopy;
+ ExecForeignCopy_function ExecForeignCopy;
+ EndForeignCopy_function EndForeignCopy;
+
/* Functions for SELECT FOR UPDATE/SHARE row locking */
GetForeignRowMarkType_function GetForeignRowMarkType;
RefetchForeignRow_function RefetchForeignRow;
--
2.17.1
>From fd074709c606671c130b1951362b9ddfca74543c Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Mon, 19 Oct 2020 14:02:51 +0500
Subject: [PATCH 1/2] Move multi-insert decision logic into executor
When 0d5f05cde introduced support for using multi-insert mode when
copying into partitioned tables, it introduced single variable of
enum type CopyInsertMethod shared across all potential target
relations (partitions) that, along with some target relation
proprties, dictated whether to engage multi-insert mode for a given
target relation.
Move that decision logic into InitResultRelInfo which now sets a new
boolean field ri_usesMultiInsert of ResultRelInfo when a target
relation is first initialized. That prevents repeated computation
of the same information in some cases, especially for partitions,
and the new arrangement results in slightly more readability.
---
src/backend/commands/copy.c | 152 +++++++--------------------
src/backend/executor/execMain.c | 52 +++++++++
src/backend/executor/execPartition.c | 7 ++
src/include/executor/execPartition.h | 2 +
src/include/executor/executor.h | 2 +
src/include/nodes/execnodes.h | 9 +-
6 files changed, 109 insertions(+), 115 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 531bd7c73a..83ce196a45 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -85,16 +85,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
* operation. For simplicity, we use the same struct for all variants of COPY,
@@ -2717,12 +2707,10 @@ CopyFrom(CopyState 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 */
uint64 processed = 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);
@@ -2832,6 +2820,30 @@ CopyFrom(CopyState cstate)
resultRelInfo = target_resultRelInfo = makeNode(ResultRelInfo);
ExecInitResultRelation(estate, resultRelInfo, 1);
+ Assert(target_resultRelInfo->ri_usesMultiInsert == false);
+
+ /*
+ * 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 ExecRelationAllowsMultiInsert(). It does not matter
+ * whether partitions have any volatile default expressions as we use the
+ * defaults from the target of the COPY command.
+ */
+ if (!cstate->volatile_defexprs &&
+ !contain_volatile_functions(cstate->whereClause))
+ target_resultRelInfo->ri_usesMultiInsert =
+ ExecRelationAllowsMultiInsert(target_resultRelInfo, NULL);
+
/* Verify the named relation is a valid target for INSERT */
CheckValidResultRel(resultRelInfo, CMD_INSERT);
@@ -2847,6 +2859,10 @@ CopyFrom(CopyState cstate)
mtstate->operation = CMD_INSERT;
mtstate->resultRelInfo = resultRelInfo;
+ /*
+ * Init COPY into foreign table. Initialization of copying into foreign
+ * partitions will be done later.
+ */
if (resultRelInfo->ri_FdwRoutine != NULL &&
resultRelInfo->ri_FdwRoutine->BeginForeignInsert != NULL)
resultRelInfo->ri_FdwRoutine->BeginForeignInsert(mtstate,
@@ -2879,83 +2895,9 @@ CopyFrom(CopyState 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
@@ -2963,7 +2905,7 @@ CopyFrom(CopyState 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);
@@ -3006,7 +2948,7 @@ CopyFrom(CopyState 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);
@@ -3014,7 +2956,6 @@ CopyFrom(CopyState cstate)
else
{
Assert(resultRelInfo == target_resultRelInfo);
- Assert(insertMethod == CIM_MULTI);
myslot = CopyMultiInsertInfoNextFreeSlot(&multiInsertInfo,
resultRelInfo);
@@ -3073,24 +3014,14 @@ CopyFrom(CopyState 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
@@ -3137,7 +3068,7 @@ CopyFrom(CopyState cstate)
* rowtype.
*/
map = resultRelInfo->ri_PartitionInfo->pi_RootToPartitionMap;
- if (insertMethod == CIM_SINGLE || !leafpart_use_multi_insert)
+ if (!resultRelInfo->ri_usesMultiInsert)
{
/* non batch insert */
if (map != NULL)
@@ -3156,9 +3087,6 @@ CopyFrom(CopyState cstate)
*/
TupleTableSlot *batchslot;
- /* no other path available for partitioned table */
- Assert(insertMethod == CIM_MULTI_CONDITIONAL);
-
batchslot = CopyMultiInsertInfoNextFreeSlot(&multiInsertInfo,
resultRelInfo);
@@ -3230,7 +3158,7 @@ CopyFrom(CopyState 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
@@ -3306,11 +3234,8 @@ CopyFrom(CopyState 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;
@@ -3342,8 +3267,7 @@ CopyFrom(CopyState cstate)
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.c
index 293f53d07c..0ad98ff0e7 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1245,6 +1245,58 @@ InitResultRelInfo(ResultRelInfo *resultRelInfo,
resultRelInfo->ri_PartitionRoot = partition_root;
resultRelInfo->ri_PartitionInfo = NULL; /* may be set later */
resultRelInfo->ri_CopyMultiInsertBuffer = NULL;
+
+ /* Define multi-insert mode possibility later if needed */
+ resultRelInfo->ri_usesMultiInsert = false;
+}
+
+/*
+ * ExecRelationAllowsMultiInsert
+ * Does this relation allow caller to use multi-insert mode when
+ * inserting rows into it?
+ */
+bool
+ExecRelationAllowsMultiInsert(const ResultRelInfo *rri,
+ const ResultRelInfo *partition_root)
+{
+ Assert(rri->ri_usesMultiInsert == false);
+
+ /*
+ * If a partition's root parent isn't allowed to use it, neither is the
+ * partition.
+ */
+ if (partition_root && !partition_root->ri_usesMultiInsert)
+ return false;
+
+ /*
+ * 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;
+
+ /* Foreign tables don't support multi-inserts. */
+ if (rri->ri_FdwRoutine != NULL)
+ return false;
+
+ /* OK, caller can use multi-insert on this relation. */
+ return true;
}
/*
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c
index 33d2c6f63d..121484374f 100644
--- a/src/backend/executor/execPartition.c
+++ b/src/backend/executor/execPartition.c
@@ -583,6 +583,13 @@ ExecInitPartitionInfo(ModifyTableState *mtstate, EState *estate,
rootrel,
estate->es_instrument);
+ /*
+ * Use multi-insert mode if the condition checking passes for the
+ * parent and its child.
+ */
+ leaf_part_rri->ri_usesMultiInsert =
+ ExecRelationAllowsMultiInsert(leaf_part_rri, rootResultRelInfo);
+
/*
* Verify result relation is a valid target for an INSERT. An UPDATE of a
* partition-key becomes a DELETE+INSERT operation, so this check is still
diff --git a/src/include/executor/execPartition.h b/src/include/executor/execPartition.h
index 6d1b722198..895bcd01c6 100644
--- a/src/include/executor/execPartition.h
+++ b/src/include/executor/execPartition.h
@@ -145,6 +145,8 @@ extern ResultRelInfo *ExecFindPartition(ModifyTableState *mtstate,
PartitionTupleRouting *proute,
TupleTableSlot *slot,
EState *estate);
+extern bool checkMultiInsertMode(const ResultRelInfo *rri,
+ const ResultRelInfo *parent);
extern void ExecCleanupTupleRouting(ModifyTableState *mtstate,
PartitionTupleRouting *proute);
extern PartitionPruneState *ExecCreatePartitionPruneState(PlanState *planstate,
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index b7978cd22e..849f207691 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -190,6 +190,8 @@ extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
Index resultRelationIndex,
Relation partition_root,
int instrument_options);
+extern bool ExecRelationAllowsMultiInsert(const ResultRelInfo *rri,
+ const ResultRelInfo *partition_root);
extern ResultRelInfo *ExecGetTriggerResultRel(EState *estate, Oid relid);
extern void ExecConstraints(ResultRelInfo *resultRelInfo,
TupleTableSlot *slot, EState *estate);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index b7e9e5d539..b1d22ae7ea 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -486,7 +486,14 @@ typedef struct ResultRelInfo
/* info for partition tuple routing (NULL if not set up yet) */
struct PartitionRoutingInfo *ri_PartitionInfo;
- /* for use by copy.c when performing multi-inserts */
+ /*
+ * The following fields are currently only relevant to copy.c.
+ *
+ * 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;
} ResultRelInfo;
--
2.17.1
