On Mon Nov 17, 2025 at 11:03 PM -03, Masahiko Sawada wrote:
> IIUC the performance regression occurs when users insert many rows
> into a foreign table with batch_size = 1 and use_copy_for_insert =
> true (tps: 133.451518 vs. 132.644801 vs. 88.998804). Since batch_size
> defaults to 1, users might experience performance issues if they
> enable use_copy_for_insert without adjusting the batch_size. I'm
> worried that users could easily find themselves in this situation.
>
Yes, you are correct. The 0002 patch aims to reduce this issue by using
the COPY command only if the use_copy_for_insert = true and if
batch_size > 1 which will reduce the cases but the regression can still
happen if the user send a single row to insert into a foreign table.
Inserting a single row into a foreign table using COPY is a bit slower
compared with using INSERT. See the followinw pgbench results:
(Single row using INSERT)
tps = 19814.535944
(Single row using COPY)
tps = 16562.324025
I think that the documentation should mention that just changing
use_copy_for_insert without also changing the batch_size option could
cause performance regression.
> One possible solution would be to introduce a threshold, like
> copy_min_row, which would specify the minimum number of rows needed
> before switching to the COPY command. However, this would require
> coordination with batch_size since having copy_min_row lower than
> batch_size wouldn't make sense.
>
The only problem that I see with this approach is that it would make
EXPLAIN(VERBOSE) and EXPLAIN(ANALYZE, VERBOSE) remote SQL output
different. The user will never know with EXPLAIN (without analyze) if
the COPY will be used or not. Is this a problem or I'm being to much
conservative?
I think that we can do such coordination on postgres_fdw_validator().
Also if we decide to go with this idea it seems to me that we would have
to much table options to configure to enable the COPY opitimization, we
would need "copy_min_row", "batch_size" and "use_copy_for_insert". What
about decide to use the COPY command if use_copy_for_insert = true and
the number of rows being inserted is >= batch_size?
> Alternatively, when users are using batch insertion (batch_size > 0),
> we could use the COPY command only for full batches and fall back to
> INSERT for partial ones.
>
IIUC in this case we would sent COPY and INSERT statements to the
foreign server for the same execution, for example, if batch_size = 100
and the user try insert 105 rows into the foreign table we will send a
COPY statement with 100 rows and then an INSERT with the 5 rows
remaining? If that's the case which SQL we should show on Remote SQL
from EXPLAIN(ANALYZE, VERBOSE) output? I think that this can cause some
confusion.
> BTW I noticed that use_copy_for_insert option doesn't work with COPY
> FROM command. I got the following error with use_copy_for_insert=true
> and batch_size=3:
>
> postgres(1:2546195)=# copy t from '/tmp/a.csv'; -- table 't' is a foreign
> table.
> ERROR: there is no parameter $1
> CONTEXT: remote SQL command: INSERT INTO public.t(c) VALUES ($1)
> COPY t
>
Thanks for testing this case. The problem was that I as checking if the
COPY can be used inside create_foreign_modify() that is called by
BeginForeignInsert and also BeginForeignModify() and the COPY can be
used only by the foreign modify path. To fix this issue I've moved the
check to postgresBeginForeignModify().
I'm attaching v6 with the following changes:
- I've squashed 0002 into 0001, so now the COPY will only be used if
use_copy_for_insert = true and if batch_size > 1
- Fix for the bug of COPY FROM a foreign table
- New test case for the COPY bug
--
Matheus Alcantara
EDB: http://www.enterprisedb.com
From dd29e8b3b0092a4ba54cef9b00892f577d2461da Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Fri, 10 Oct 2025 16:07:08 -0300
Subject: [PATCH v6] postgres_fdw: Enable the use of COPY to speed up inserts
---
contrib/postgres_fdw/deparse.c | 30 +++
.../postgres_fdw/expected/postgres_fdw.out | 170 ++++++++++++++-
contrib/postgres_fdw/option.c | 3 +
contrib/postgres_fdw/postgres_fdw.c | 198 +++++++++++++++++-
contrib/postgres_fdw/postgres_fdw.h | 1 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 79 +++++++
6 files changed, 466 insertions(+), 15 deletions(-)
diff --git a/contrib/postgres_fdw/deparse.c b/contrib/postgres_fdw/deparse.c
index f2fb0051843..1cdf1d8cc8d 100644
--- a/contrib/postgres_fdw/deparse.c
+++ b/contrib/postgres_fdw/deparse.c
@@ -2236,6 +2236,36 @@ rebuildInsertSql(StringInfo buf, Relation rel,
appendStringInfoString(buf, orig_query + values_end_len);
}
+/*
+ * Build a COPY FROM STDIN statement using the TEXT format
+ */
+void
+deparseCopySql(StringInfo buf, Relation rel, List *target_attrs)
+{
+ TupleDesc tupdesc = RelationGetDescr(rel);
+ bool first = true;
+
+ appendStringInfo(buf, "COPY ");
+ deparseRelation(buf, rel);
+ appendStringInfo(buf, "(");
+
+ foreach_int(attnum, target_attrs)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, attnum - 1);
+
+ if (attr->attgenerated)
+ continue;
+
+ if (!first)
+ appendStringInfoString(buf, ", ");
+
+ first = false;
+
+ appendStringInfoString(buf,
quote_identifier(NameStr(attr->attname)));
+ }
+ appendStringInfoString(buf, ") FROM STDIN");
+}
+
/*
* deparse remote UPDATE statement
*
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..0648a964522 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -50,6 +50,18 @@ CREATE TABLE "S 1"."T 4" (
c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
+CREATE TABLE "S 1"."T 5"(
+ x int
+);
+CREATE TABLE "S 1"."T 6"(
+ id int not null,
+ note text,
+ value int NOT NULL
+);
+CREATE TABLE "S 1"."T 7"(
+ id int,
+ t text
+);
-- Disable autovacuum for these tables to avoid unexpected effects of that
ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
ALTER TABLE "S 1"."T 2" SET (autovacuum_enabled = 'false');
@@ -132,6 +144,24 @@ CREATE FOREIGN TABLE ft7 (
c2 int NOT NULL,
c3 text
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE FOREIGN TABLE ft8 (
+ x int
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 5', use_copy_for_insert 'true',
batch_size '10');
+CREATE FOREIGN TABLE ft9 (
+ id int not null,
+ note text,
+ value int NOT NULL
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 6', use_copy_for_insert 'true',
batch_size '10');
+CREATE FOREIGN TABLE ft10 (
+ id int,
+ t text
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true',
batch_size '10');
-- ===================================================================
-- tests for validator
-- ===================================================================
@@ -205,16 +235,19 @@ ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1',
table_name 'T 1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
\det+
- List of foreign tables
- Schema | Table | Server | FDW options |
Description
---------+-------+-----------+---------------------------------------+-------------
- public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1') |
- public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1') |
- public | ft4 | loopback | (schema_name 'S 1', table_name 'T 3') |
- public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4') |
- public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4') |
- public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4') |
-(6 rows)
+ List of foreign tables
+ Schema | Table | Server | FDW options
| Description
+--------+-------+-----------+------------------------------------------------------------------------------------+-------------
+ public | ft1 | loopback | (schema_name 'S 1', table_name 'T 1')
|
+ public | ft10 | loopback | (schema_name 'S 1', table_name 'T 7',
use_copy_for_insert 'true', batch_size '10') |
+ public | ft2 | loopback | (schema_name 'S 1', table_name 'T 1')
|
+ public | ft4 | loopback | (schema_name 'S 1', table_name 'T 3')
|
+ public | ft5 | loopback | (schema_name 'S 1', table_name 'T 4')
|
+ public | ft6 | loopback2 | (schema_name 'S 1', table_name 'T 4')
|
+ public | ft7 | loopback3 | (schema_name 'S 1', table_name 'T 4')
|
+ public | ft8 | loopback | (schema_name 'S 1', table_name 'T 5',
use_copy_for_insert 'true', batch_size '10') |
+ public | ft9 | loopback | (schema_name 'S 1', table_name 'T 6',
use_copy_for_insert 'true', batch_size '10') |
+(9 rows)
-- Test that alteration of server options causes reconnection
-- Remote's errors might be non-English, so hide them to ensure stable results
@@ -12665,6 +12698,123 @@ ANALYZE analyze_ftable;
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
-- ===================================================================
+-- test for COPY usage to perform INSERT's
+-- ===================================================================
+-- Test that target attr is correctly used to build the COPY command
+ALTER FOREIGN TABLE ft8 DROP COLUMN x;
+ALTER FOREIGN TABLE ft8 add COLUMN x int;
+EXPLAIN(ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 SELECT * FROM generate_series(1, 10) i;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Insert on public.ft8 (actual rows=0.00 loops=1)
+ Remote SQL: COPY "S 1"."T 5"(x) FROM STDIN
+ Batch Size: 10
+ -> Function Scan on pg_catalog.generate_series i (actual rows=10.00
loops=1)
+ Output: NULL::integer, i.i
+ Function Call: generate_series(1, 10)
+(6 rows)
+
+SELECT * FROM ft8;
+ x
+----
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+-- Test outer of order columns
+EXPLAIN(ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
INSERT INTO ft9 (id, value, note)
+SELECT g,
+ g * 2,
+ 'batch insert test data' || g
+FROM generate_series(1, 20) g;
+ QUERY PLAN
+---------------------------------------------------------------------------------
+ Insert on public.ft9 (actual rows=0.00 loops=1)
+ Remote SQL: COPY "S 1"."T 6"(id, note, value) FROM STDIN
+ Batch Size: 10
+ -> Function Scan on pg_catalog.generate_series g (actual rows=20.00
loops=1)
+ Output: g.g, ('batch insert test data'::text || (g.g)::text), (g.g *
2)
+ Function Call: generate_series(1, 20)
+(6 rows)
+
+SELECT * FROM ft9;
+ id | note | value
+----+--------------------------+-------
+ 1 | batch insert test data1 | 2
+ 2 | batch insert test data2 | 4
+ 3 | batch insert test data3 | 6
+ 4 | batch insert test data4 | 8
+ 5 | batch insert test data5 | 10
+ 6 | batch insert test data6 | 12
+ 7 | batch insert test data7 | 14
+ 8 | batch insert test data8 | 16
+ 9 | batch insert test data9 | 18
+ 10 | batch insert test data10 | 20
+ 11 | batch insert test data11 | 22
+ 12 | batch insert test data12 | 24
+ 13 | batch insert test data13 | 26
+ 14 | batch insert test data14 | 28
+ 15 | batch insert test data15 | 30
+ 16 | batch insert test data16 | 32
+ 17 | batch insert test data17 | 34
+ 18 | batch insert test data18 | 36
+ 19 | batch insert test data19 | 38
+ 20 | batch insert test data20 | 40
+(20 rows)
+
+-- Test buffer limit of copy data on COPYBUFSIZ
+INSERT INTO ft10 (id, t)
+SELECT s, repeat(md5(s::text), 10000) from generate_series(100, 103) s;
+SELECT COUNT(*) FROM ft10;
+ count
+-------
+ 4
+(1 row)
+
+-- Disable the use_copy_for_insert table option and check that the INSERT is
+-- used
+ALTER FOREIGN TABLE ft8 OPTIONS(DROP use_copy_for_insert);
+EXPLAIN(VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 VALUES (10);
+ QUERY PLAN
+------------------------------------------------------
+ Insert on public.ft8
+ Remote SQL: INSERT INTO "S 1"."T 5"(x) VALUES ($1)
+ Batch Size: 10
+ -> Result
+ Output: NULL::integer, 10
+(5 rows)
+
+-- Enable the use_copy_for_insert for the foreign server and check that the
+-- COPY is used
+ALTER SERVER loopback OPTIONS(ADD use_copy_for_insert 'true');
+EXPLAIN(VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 VALUES (20);
+ QUERY PLAN
+----------------------------------------------
+ Insert on public.ft8
+ Remote SQL: COPY "S 1"."T 5"(x) FROM STDIN
+ Batch Size: 10
+ -> Result
+ Output: NULL::integer, 20
+(5 rows)
+
+-- Check that COPY work correctly for a foreign table that has
+-- use_copy_for_insert enabled
+COPY ft8(x) FROM stdin;
+-- Reset state
+ALTER SERVER loopback OPTIONS(DROP use_copy_for_insert);
+ALTER FOREIGN TABLE ft8 OPTIONS(ADD use_copy_for_insert 'true');
+-- ===================================================================
-- test for postgres_fdw_get_connections function with check_conn = true
-- ===================================================================
-- Disable debug_discard_caches in order to manage remote connections
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 04788b7e8b3..de0f59332c3 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -263,6 +263,9 @@ InitPgFdwOptions(void)
/* batch_size is available on both server and table */
{"batch_size", ForeignServerRelationId, false},
{"batch_size", ForeignTableRelationId, false},
+ /* use_copy_for_insert is available on both server and table */
+ {"use_copy_for_insert", ForeignServerRelationId, false},
+ {"use_copy_for_insert", ForeignTableRelationId, false},
/* async_capable is available on both server and table */
{"async_capable", ForeignServerRelationId, false},
{"async_capable", ForeignTableRelationId, false},
diff --git a/contrib/postgres_fdw/postgres_fdw.c
b/contrib/postgres_fdw/postgres_fdw.c
index 06b52c65300..64174727d09 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -63,6 +63,9 @@ PG_MODULE_MAGIC_EXT(
/* If no remote estimates, assume a sort costs 20% extra */
#define DEFAULT_FDW_SORT_MULTIPLIER 1.2
+/* Buffer size to send COPY IN data*/
+#define COPYBUFSIZ 8192
+
/*
* Indexes of FDW-private information stored in fdw_private lists.
*
@@ -197,6 +200,7 @@ typedef struct PgFdwModifyState
int batch_size; /* value of FDW option
"batch_size" */
bool has_returning; /* is there a RETURNING clause? */
List *retrieved_attrs; /* attr numbers retrieved by RETURNING
*/
+ bool use_copy_for_insert; /* is the COPY enabled for
INSERT's? */
/* info about parameters for prepared statement */
AttrNumber ctidAttno; /* attnum of input resjunk ctid
column */
@@ -545,6 +549,10 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
const
PgFdwRelationInfo *fpinfo_o,
const
PgFdwRelationInfo *fpinfo_i);
static int get_batch_size_option(Relation rel);
+static bool get_use_copy_for_insert(Relation rel);
+static TupleTableSlot **execute_foreign_insert_using_copy(PgFdwModifyState
*fmstate,
+
TupleTableSlot **slots,
+
int *numSlots);
/*
@@ -1788,6 +1796,7 @@ postgresPlanForeignModify(PlannerInfo *root,
List *retrieved_attrs = NIL;
bool doNothing = false;
int values_end_len = -1;
+ bool use_copy_for_insert = false;
initStringInfo(&sql);
@@ -1867,17 +1876,30 @@ postgresPlanForeignModify(PlannerInfo *root,
elog(ERROR, "unexpected ON CONFLICT specification: %d",
(int) plan->onConflictAction);
+ if (operation == CMD_INSERT && plan->returningLists == NULL)
+ {
+ int batch_size = get_batch_size_option(rel);
+
+ if (batch_size > 1)
+ use_copy_for_insert = get_use_copy_for_insert(rel);
+ }
+
/*
* Construct the SQL command string.
*/
switch (operation)
{
case CMD_INSERT:
- deparseInsertSql(&sql, rte, resultRelation, rel,
- targetAttrs, doNothing,
- withCheckOptionList,
returningList,
- &retrieved_attrs,
&values_end_len);
- break;
+ {
+ if (use_copy_for_insert)
+ deparseCopySql(&sql, rel, targetAttrs);
+ else
+ deparseInsertSql(&sql, rte,
resultRelation, rel,
+
targetAttrs, doNothing,
+
withCheckOptionList, returningList,
+
&retrieved_attrs, &values_end_len);
+ break;
+ }
case CMD_UPDATE:
deparseUpdateSql(&sql, rte, resultRelation, rel,
targetAttrs,
@@ -1925,6 +1947,7 @@ postgresBeginForeignModify(ModifyTableState *mtstate,
int values_end_len;
List *retrieved_attrs;
RangeTblEntry *rte;
+ Relation rel = resultRelInfo->ri_RelationDesc;
/*
* Do nothing in EXPLAIN (no ANALYZE) case. resultRelInfo->ri_FdwState
@@ -1961,6 +1984,10 @@ postgresBeginForeignModify(ModifyTableState *mtstate,
has_returning,
retrieved_attrs);
+ /* Can COPY be used for batch insert? */
+ if (mtstate->operation == CMD_INSERT && !fmstate->has_returning &&
fmstate->batch_size > 1)
+ fmstate->use_copy_for_insert = get_use_copy_for_insert(rel);
+
resultRelInfo->ri_FdwState = fmstate;
}
@@ -4066,6 +4093,50 @@ create_foreign_modify(EState *estate,
return fmstate;
}
+/*
+ * Write target attribute values from fmstate into buf buffer to be sent as
+ * COPY FROM STDIN data
+ */
+static void
+convert_slot_to_copy_text(StringInfo buf,
+ PgFdwModifyState *fmstate,
+ TupleTableSlot *slot)
+{
+ TupleDesc tupdesc = RelationGetDescr(fmstate->rel);
+ bool first = true;
+ int i = 0;
+
+ foreach_int(attnum, fmstate->target_attrs)
+ {
+ CompactAttribute *attr = TupleDescCompactAttr(tupdesc, attnum -
1);
+ Datum datum;
+ bool isnull;
+
+ /* Ignore generated columns; they are set to DEFAULT */
+ if (attr->attgenerated)
+ continue;
+
+ if (!first)
+ appendStringInfoCharMacro(buf, '\t');
+ first = false;
+
+ datum = slot_getattr(slot, attnum, &isnull);
+
+ if (isnull)
+ appendStringInfoString(buf, "\\N");
+ else
+ {
+ const char *value =
OutputFunctionCall(&fmstate->p_flinfo[i],
+
datum);
+
+ appendStringInfoString(buf, value);
+ }
+ i++;
+ }
+
+ appendStringInfoCharMacro(buf, '\n');
+}
+
/*
* execute_foreign_modify
* Perform foreign-table modification as required, and fetch
RETURNING
@@ -4097,6 +4168,14 @@ execute_foreign_modify(EState *estate,
if (fmstate->conn_state->pendingAreq)
process_pending_request(fmstate->conn_state->pendingAreq);
+ /* Check if the COPY command is enabled to use for INSERT's */
+ if (operation == CMD_INSERT && fmstate->use_copy_for_insert)
+ {
+ /* COPY should only be used with INSERT without RETURNING
clause. */
+ Assert(!fmstate->has_returning);
+ return execute_foreign_insert_using_copy(fmstate, slots,
numSlots);
+ }
+
/*
* If the existing query was deparsed and prepared for a different
number
* of rows, rebuild it for the proper number.
@@ -7886,3 +7965,112 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+/*
+ * Determine if the usage of the COPY command to execute a INSERT into a
foreign
+ * table is enabled. The option specified for a table has precedence.
+ */
+static bool
+get_use_copy_for_insert(Relation rel)
+{
+ Oid foreigntableid = RelationGetRelid(rel);
+ List *options = NIL;
+ ListCell *lc;
+ ForeignTable *table;
+ ForeignServer *server;
+ bool enable_batch_with_copy = false;
+
+ /*
+ * Load options for table and server. We append server options after
table
+ * options, because table options take precedence.
+ */
+ table = GetForeignTable(foreigntableid);
+ server = GetForeignServer(table->serverid);
+
+ options = list_concat(options, table->options);
+ options = list_concat(options, server->options);
+
+ /* See if either table or server specifies enable_batch_with_copy. */
+ foreach(lc, options)
+ {
+ DefElem *def = (DefElem *) lfirst(lc);
+
+ if (strcmp(def->defname, "use_copy_for_insert") == 0)
+ {
+ (void) parse_bool(defGetString(def),
&enable_batch_with_copy);
+ break;
+ }
+ }
+ return enable_batch_with_copy;
+}
+
+/* Execute an insert into a foreign table using the COPY command */
+static TupleTableSlot **
+execute_foreign_insert_using_copy(PgFdwModifyState *fmstate,
+
TupleTableSlot **slots,
+ int *numSlots)
+{
+ PGresult *res;
+ StringInfoData copy_data;
+ int n_rows;
+ int i;
+
+ /* Send COPY command */
+ if (!PQsendQuery(fmstate->conn, fmstate->query))
+ pgfdw_report_error(NULL, fmstate->conn, fmstate->query);
+
+ /* get the COPY result */
+ res = pgfdw_get_result(fmstate->conn);
+ if (PQresultStatus(res) != PGRES_COPY_IN)
+ pgfdw_report_error(res, fmstate->conn, fmstate->query);
+
+ /* Convert the TupleTableSlot data into a TEXT-formatted line */
+ initStringInfo(©_data);
+ for (i = 0; i < *numSlots; i++)
+ {
+ convert_slot_to_copy_text(©_data, fmstate, slots[i]);
+
+ /*
+ * Send initial COPY data if the buffer reach the limit to
avoid large
+ * memory usage.
+ */
+ if (copy_data.len >= COPYBUFSIZ)
+ {
+ if (PQputCopyData(fmstate->conn, copy_data.data,
copy_data.len) <= 0)
+ pgfdw_report_error(NULL, fmstate->conn,
fmstate->query);
+ resetStringInfo(©_data);
+ }
+ }
+
+ /* Send the remaining COPY data */
+ if (copy_data.len > 0)
+ {
+ if (PQputCopyData(fmstate->conn, copy_data.data, copy_data.len)
<= 0)
+ pgfdw_report_error(NULL, fmstate->conn, fmstate->query);
+ }
+
+ /* End the COPY operation */
+ if (PQputCopyEnd(fmstate->conn, NULL) < 0 || PQflush(fmstate->conn))
+ pgfdw_report_error(NULL, fmstate->conn, fmstate->query);
+
+ /*
+ * Get the result, and check for success.
+ */
+ res = pgfdw_get_result(fmstate->conn);
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ pgfdw_report_error(res, fmstate->conn, fmstate->query);
+
+ n_rows = atoi(PQcmdTuples(res));
+
+ /* And clean up */
+ PQclear(res);
+
+ MemoryContextReset(fmstate->temp_cxt);
+
+ *numSlots = n_rows;
+
+ /*
+ * Return NULL if nothing was inserted on the remote end
+ */
+ return (n_rows > 0) ? slots : NULL;
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h
b/contrib/postgres_fdw/postgres_fdw.h
index e69735298d7..aa54d6bba53 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -204,6 +204,7 @@ extern void rebuildInsertSql(StringInfo buf, Relation rel,
char *orig_query, List
*target_attrs,
int values_end_len,
int num_params,
int num_rows);
+extern void deparseCopySql(StringInfo buf, Relation rel, List *target_attrs);
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 9a8f9e28135..0c483cd49a5 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -54,6 +54,18 @@ CREATE TABLE "S 1"."T 4" (
c3 text,
CONSTRAINT t4_pkey PRIMARY KEY (c1)
);
+CREATE TABLE "S 1"."T 5"(
+ x int
+);
+CREATE TABLE "S 1"."T 6"(
+ id int not null,
+ note text,
+ value int NOT NULL
+);
+CREATE TABLE "S 1"."T 7"(
+ id int,
+ t text
+);
-- Disable autovacuum for these tables to avoid unexpected effects of that
ALTER TABLE "S 1"."T 1" SET (autovacuum_enabled = 'false');
@@ -146,6 +158,27 @@ CREATE FOREIGN TABLE ft7 (
c3 text
) SERVER loopback3 OPTIONS (schema_name 'S 1', table_name 'T 4');
+CREATE FOREIGN TABLE ft8 (
+ x int
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 5', use_copy_for_insert 'true',
batch_size '10');
+
+CREATE FOREIGN TABLE ft9 (
+ id int not null,
+ note text,
+ value int NOT NULL
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 6', use_copy_for_insert 'true',
batch_size '10');
+
+CREATE FOREIGN TABLE ft10 (
+ id int,
+ t text
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true',
batch_size '10');
+
-- ===================================================================
-- tests for validator
-- ===================================================================
@@ -4379,6 +4412,52 @@ ANALYZE analyze_ftable;
DROP FOREIGN TABLE analyze_ftable;
DROP TABLE analyze_table;
+-- ===================================================================
+-- test for COPY usage to perform INSERT's
+-- ===================================================================
+
+-- Test that target attr is correctly used to build the COPY command
+ALTER FOREIGN TABLE ft8 DROP COLUMN x;
+ALTER FOREIGN TABLE ft8 add COLUMN x int;
+EXPLAIN(ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 SELECT * FROM generate_series(1, 10) i;
+SELECT * FROM ft8;
+
+-- Test outer of order columns
+EXPLAIN(ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
INSERT INTO ft9 (id, value, note)
+SELECT g,
+ g * 2,
+ 'batch insert test data' || g
+FROM generate_series(1, 20) g;
+SELECT * FROM ft9;
+
+-- Test buffer limit of copy data on COPYBUFSIZ
+INSERT INTO ft10 (id, t)
+SELECT s, repeat(md5(s::text), 10000) from generate_series(100, 103) s;
+SELECT COUNT(*) FROM ft10;
+
+-- Disable the use_copy_for_insert table option and check that the INSERT is
+-- used
+ALTER FOREIGN TABLE ft8 OPTIONS(DROP use_copy_for_insert);
+EXPLAIN(VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 VALUES (10);
+
+-- Enable the use_copy_for_insert for the foreign server and check that the
+-- COPY is used
+ALTER SERVER loopback OPTIONS(ADD use_copy_for_insert 'true');
+EXPLAIN(VERBOSE, COSTS OFF, SUMMARY OFF, BUFFERS OFF, TIMING OFF)
+INSERT INTO ft8 VALUES (20);
+
+-- Check that COPY work correctly for a foreign table that has
+-- use_copy_for_insert enabled
+COPY ft8(x) FROM stdin;
+30
+\.
+
+-- Reset state
+ALTER SERVER loopback OPTIONS(DROP use_copy_for_insert);
+ALTER FOREIGN TABLE ft8 OPTIONS(ADD use_copy_for_insert 'true');
+
-- ===================================================================
-- test for postgres_fdw_get_connections function with check_conn = true
-- ===================================================================
--
2.51.2
