On Fri Oct 31, 2025 at 4:02 PM -03, I wrote: > It's showing a bit complicated to decide at runtime if we should use the > COPY or INSERT for batch insert into a foreign table. Perhaps we could > add a new option on CREATE FOREIGN TABLE to enable this usage or not? We > could document the performance improvements and the limitations so the > user can decide if it should enable or not. > Here is v5 that implement this idea.
On this version I've introduced a foreign table and foreign server option "use_copy_for_insert" (I'm open for a better name) that enable the use of the COPY as remote command to execute an INSERT into a foreign table. The COPY can be used if the user enable this option on the foreign table or the foreign server and if the original INSERT statement don't have a RETURNING clause. See the benchmark results: pgbench -n -c 10 -j 10 -t 100 -f bench.sql postgres Master (batch_size = 1 with a single row to insert): tps = 16000.768037 Master (batch_size = 1 with 1000 rows to insert): tps = 133.451518 Master (batch_size = 100 with 1000 rows to insert): tps = 1274.096347 ----------------- Patch(batch_size = 1, use_copy_for_insert = false with single row to insert) tps = 15734.155705 Master (batch_size = 1, use_copy_for_insert = false with 1000 rows to insert): tps = 132.644801 Master (batch_size = 100, use_copy_for_insert = false with 1000 rows to insert): tps = 1245.514591 ----------------- Patch(batch_size = 1, use_copy_for_insert = true with single row to insert) tps = 17604.394057 Master (batch_size = 1, use_copy_for_insert = true with 1000 rows to insert): tps = 88.998804 Master (batch_size = 100, use_copy_for_insert = true with 1000 rows to insert): tps = 2406.009249 ----------------- We can see that when batching inserting with the batch_size configured properly we have a very significant performance improvement and when the "use_copy_for_insert" option is disabled the performance are close compared with master. The problem is when the "batch_size" is 1 (default) and "use_copy_for_insert" is enabled. This is because on this scenario we are sending multiple COPY commands with a single row to the foreign server. One way to fix this would to decide at runtime (at execute_foreign_modify()) if the COPY can be used based on the number of rows being insert. I don't think that I like this option because it would make the EXPLAIN output different when the ANALYZE option is used since during planning time we don't have the number of rows being inserted, so if just EXPLAIN(VERBOSE) is executed we would show the INSERT as remote SQL, and if the ANALYZE is included and we have enough rows to enable the COPY usage, the remote SQL would show the COPY command. Since the new "use_copy_for_insert" option is be disabled by default I think that we could document this limitation and mention the performance improvements when used correctly with the batch_size option. Another option would be to use the COPY command only if the "use_copy_for_insert" is true and also if the "batch_size" is > 1. We would still have the performance issue if the user insert a single row but we would close to less scenarios. The attached 0002 implement this idea. Thoughts? -- Matheus Alcantara EDB: http://www.enterprisedb.com
From dffe3f86da95451c85277a17de7fda204b678a21 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> 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(©_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..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
From 1fb93c8b1548b836c68c3e2e4c124eb57f2ee518 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <[email protected]> Date: Thu, 6 Nov 2025 20:17:19 -0300 Subject: [PATCH v5 2/2] postgres_fdw: Only use COPY if batch_size is > 1 --- .../postgres_fdw/expected/postgres_fdw.out | 39 +++++++++---------- contrib/postgres_fdw/postgres_fdw.c | 9 ++++- contrib/postgres_fdw/sql/postgres_fdw.sql | 9 ++--- 3 files changed, 30 insertions(+), 27 deletions(-) diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index bc99e278f00..956ff12b590 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -148,20 +148,20 @@ CREATE FOREIGN TABLE ft8 ( x int ) SERVER loopback -OPTIONS (schema_name 'S 1', table_name 'T 5', use_copy_for_insert 'true'); +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'); +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'); +OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true', batch_size '10'); -- =================================================================== -- tests for validator -- =================================================================== @@ -235,18 +235,18 @@ 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 | 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') | + 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 @@ -12709,7 +12709,7 @@ INSERT INTO ft8 SELECT * FROM generate_series(1, 10) i; --------------------------------------------------------------------------------- Insert on public.ft8 (actual rows=0.00 loops=1) Remote SQL: COPY "S 1"."T 5"(x) FROM STDIN - Batch Size: 1 + 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) @@ -12730,8 +12730,7 @@ SELECT * FROM ft8; 10 (10 rows) --- Test outer of order columns and batch_size with COPY -ALTER FOREIGN TABLE ft9 OPTIONS(ADD batch_size '10'); +-- 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, @@ -12790,7 +12789,7 @@ INSERT INTO ft8 VALUES (10); ------------------------------------------------------ Insert on public.ft8 Remote SQL: INSERT INTO "S 1"."T 5"(x) VALUES ($1) - Batch Size: 1 + Batch Size: 10 -> Result Output: NULL::integer, 10 (5 rows) @@ -12804,7 +12803,7 @@ INSERT INTO ft8 VALUES (20); ---------------------------------------------- Insert on public.ft8 Remote SQL: COPY "S 1"."T 5"(x) FROM STDIN - Batch Size: 1 + Batch Size: 10 -> Result Output: NULL::integer, 20 (5 rows) diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 77effdffeb2..4a89522a221 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -1877,7 +1877,12 @@ postgresPlanForeignModify(PlannerInfo *root, (int) plan->onConflictAction); if (operation == CMD_INSERT && plan->returningLists == NULL) - use_copy_for_insert = get_use_copy_for_insert(rel); + { + 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. @@ -4075,7 +4080,7 @@ create_foreign_modify(EState *estate, if (operation == CMD_INSERT) fmstate->batch_size = get_batch_size_option(rel); - if (operation == CMD_INSERT && !fmstate->has_returning) + if (operation == CMD_INSERT && !fmstate->has_returning && fmstate->batch_size > 1) fmstate->use_copy_for_insert = get_use_copy_for_insert(rel); fmstate->num_slots = 1; diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 0d29b9d9bae..093f86abeb4 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -162,7 +162,7 @@ CREATE FOREIGN TABLE ft8 ( x int ) SERVER loopback -OPTIONS (schema_name 'S 1', table_name 'T 5', use_copy_for_insert 'true'); +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, @@ -170,14 +170,14 @@ CREATE FOREIGN TABLE ft9 ( value int NOT NULL ) SERVER loopback -OPTIONS (schema_name 'S 1', table_name 'T 6', use_copy_for_insert 'true'); +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'); +OPTIONS (schema_name 'S 1', table_name 'T 7', use_copy_for_insert 'true', batch_size '10'); -- =================================================================== -- tests for validator @@ -4423,8 +4423,7 @@ 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'); +-- 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, -- 2.51.2
