v5-0002-postgres_fdw-Only-use-COPY-if-batch_size-is-1.patch
text/plain
Filename: v5-0002-postgres_fdw-Only-use-COPY-if-batch_size-is-1.patch
Type: text/plain
Part: 1
From 1fb93c8b1548b836c68c3e2e4c124eb57f2ee518 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths.dev@pm.me>
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