v5-0001-postgres_fdw-Enable-the-use-of-COPY-to-speed-up-i.patch

From dffe3f86da95451c85277a17de7fda204b678a21 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
Date: Fri, 10 Oct 2025 16:07:08 -0300
Subject: [PATCH v5 1/2] postgres_fdw: Enable the use of COPY to speed up
 inserts

---
 contrib/postgres_fdw/deparse.c                |  30 +++
 .../postgres_fdw/expected/postgres_fdw.out    | 168 ++++++++++++++-
 contrib/postgres_fdw/option.c                 |   3 +
 contrib/postgres_fdw/postgres_fdw.c           | 191 +++++++++++++++++-
 contrib/postgres_fdw/postgres_fdw.h           |   1 +
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  74 +++++++
 6 files changed, 452 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..bc99e278f00 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');
+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');
+CREATE FOREIGN TABLE ft10 (
+    id int,
+    t text
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true');
 -- ===================================================================
 -- 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') | 
+ 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') | 
+ public | ft9   | loopback  | (schema_name 'S 1', table_name 'T 6', use_copy_for_insert 'true') | 
+(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,121 @@ 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: 1
+   ->  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 and batch_size with COPY
+ALTER FOREIGN TABLE ft9 OPTIONS(ADD batch_size '10');
+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: 1
+   ->  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: 1
+   ->  Result
+         Output: NULL::integer, 20
+(5 rows)
+
+-- 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..77effdffeb2 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,25 @@ postgresPlanForeignModify(PlannerInfo *root,
 		elog(ERROR, "unexpected ON CONFLICT specification: %d",
 			 (int) plan->onConflictAction);
 
+	if (operation == CMD_INSERT && plan->returningLists == NULL)
+		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,
@@ -4058,6 +4075,9 @@ create_foreign_modify(EState *estate,
 	if (operation == CMD_INSERT)
 		fmstate->batch_size = get_batch_size_option(rel);
 
+	if (operation == CMD_INSERT && !fmstate->has_returning)
+		fmstate->use_copy_for_insert = get_use_copy_for_insert(rel);
+
 	fmstate->num_slots = 1;
 
 	/* Initialize auxiliary state */
@@ -4066,6 +4086,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 +4161,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 +7958,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(&copy_data);
+	for (i = 0; i < *numSlots; i++)
+	{
+		convert_slot_to_copy_text(&copy_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(&copy_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..0d29b9d9bae 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');
+
+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');
+
+CREATE FOREIGN TABLE ft10 (
+    id int,
+    t text
+)
+SERVER loopback
+OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true');
+
 -- ===================================================================
 -- tests for validator
 -- ===================================================================
@@ -4379,6 +4412,47 @@ 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 and batch_size with COPY
+ALTER FOREIGN TABLE ft9 OPTIONS(ADD batch_size '10');
+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);
+
+-- 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