Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 97da482

Browse filesBrowse files
author
Etsuro Fujita
committed
Allow batch insertion during COPY into a foreign table.
Commit 3d956d9 allowed the COPY, but it's done by inserting individual rows to the foreign table, so it can be inefficient due to the overhead caused by each round-trip to the foreign server. To improve performance of the COPY in such a case, this patch allows batch insertion, by extending the multi-insert machinery in CopyFrom() to the foreign-table case so that we insert multiple rows to the foreign table at once using the FDW callback routine added by commit b663a41. This patch also allows this for postgres_fdw. It is enabled by the "batch_size" option added by commit b663a41, which is disabled by default. When doing batch insertion, we update progress of the COPY command after performing the FDW callback routine, to count rows not suppressed by the FDW as well as a BEFORE ROW INSERT trigger. For consistency, this patch changes the timing of updating it for plain tables: previously, we updated it immediately after adding each row to the multi-insert buffer, but we do so only after writing the rows stored in the buffer out to the table using table_multi_insert(), which I think would be consistent even with non-batching mode, because in that mode we update it after writing each row out to the table using table_tuple_insert(). Andrey Lepikhov, heavily revised by me, with review from Ian Barwick, Andrey Lepikhov, and Zhihong Yu. Discussion: https://postgr.es/m/bc489202-9855-7550-d64c-ad2d83c24867%40postgrespro.ru
1 parent 56c19fe commit 97da482
Copy full SHA for 97da482

File tree

7 files changed

+460
-89
lines changed
Filter options

7 files changed

+460
-89
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/expected/postgres_fdw.out
+105
Original file line numberDiff line numberDiff line change
@@ -8608,6 +8608,39 @@ select tableoid::regclass, * FROM remp1;
86088608
remp1 | 1 | bar
86098609
(2 rows)
86108610

8611+
delete from ctrtest;
8612+
-- Test copy tuple routing with the batch_size option enabled
8613+
alter server loopback options (add batch_size '2');
8614+
copy ctrtest from stdin;
8615+
select tableoid::regclass, * FROM ctrtest;
8616+
tableoid | a | b
8617+
----------+---+-------
8618+
remp1 | 1 | foo
8619+
remp1 | 1 | bar
8620+
remp1 | 1 | test1
8621+
remp2 | 2 | baz
8622+
remp2 | 2 | qux
8623+
remp2 | 2 | test2
8624+
(6 rows)
8625+
8626+
select tableoid::regclass, * FROM remp1;
8627+
tableoid | a | b
8628+
----------+---+-------
8629+
remp1 | 1 | foo
8630+
remp1 | 1 | bar
8631+
remp1 | 1 | test1
8632+
(3 rows)
8633+
8634+
select tableoid::regclass, * FROM remp2;
8635+
tableoid | b | a
8636+
----------+-------+---
8637+
remp2 | baz | 2
8638+
remp2 | qux | 2
8639+
remp2 | test2 | 2
8640+
(3 rows)
8641+
8642+
delete from ctrtest;
8643+
alter server loopback options (drop batch_size);
86118644
drop table ctrtest;
86128645
drop table loct1;
86138646
drop table loct2;
@@ -8771,6 +8804,78 @@ select * from rem3;
87718804

87728805
drop foreign table rem3;
87738806
drop table loc3;
8807+
-- Test COPY FROM with the batch_size option enabled
8808+
alter server loopback options (add batch_size '2');
8809+
-- Test basic functionality
8810+
copy rem2 from stdin;
8811+
select * from rem2;
8812+
f1 | f2
8813+
----+-----
8814+
1 | foo
8815+
2 | bar
8816+
3 | baz
8817+
(3 rows)
8818+
8819+
delete from rem2;
8820+
-- Test check constraints
8821+
alter table loc2 add constraint loc2_f1positive check (f1 >= 0);
8822+
alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0);
8823+
-- check constraint is enforced on the remote side, not locally
8824+
copy rem2 from stdin;
8825+
copy rem2 from stdin; -- ERROR
8826+
ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive"
8827+
DETAIL: Failing row contains (-1, xyzzy).
8828+
CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2)
8829+
COPY rem2
8830+
select * from rem2;
8831+
f1 | f2
8832+
----+-----
8833+
1 | foo
8834+
2 | bar
8835+
3 | baz
8836+
(3 rows)
8837+
8838+
alter foreign table rem2 drop constraint rem2_f1positive;
8839+
alter table loc2 drop constraint loc2_f1positive;
8840+
delete from rem2;
8841+
-- Test remote triggers
8842+
create trigger trig_row_before_insert before insert on loc2
8843+
for each row execute procedure trig_row_before_insupdate();
8844+
-- The new values are concatenated with ' triggered !'
8845+
copy rem2 from stdin;
8846+
select * from rem2;
8847+
f1 | f2
8848+
----+-----------------
8849+
1 | foo triggered !
8850+
2 | bar triggered !
8851+
3 | baz triggered !
8852+
(3 rows)
8853+
8854+
drop trigger trig_row_before_insert on loc2;
8855+
delete from rem2;
8856+
create trigger trig_null before insert on loc2
8857+
for each row execute procedure trig_null();
8858+
-- Nothing happens
8859+
copy rem2 from stdin;
8860+
select * from rem2;
8861+
f1 | f2
8862+
----+----
8863+
(0 rows)
8864+
8865+
drop trigger trig_null on loc2;
8866+
delete from rem2;
8867+
-- Check with zero-column foreign table; batch insert will be disabled
8868+
alter table loc2 drop column f1;
8869+
alter table loc2 drop column f2;
8870+
alter table rem2 drop column f1;
8871+
alter table rem2 drop column f2;
8872+
copy rem2 from stdin;
8873+
select * from rem2;
8874+
--
8875+
(3 rows)
8876+
8877+
delete from rem2;
8878+
alter server loopback options (drop batch_size);
87748879
-- ===================================================================
87758880
-- test for TRUNCATE
87768881
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/postgres_fdw.c
+9
Original file line numberDiff line numberDiff line change
@@ -2057,6 +2057,15 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20572057
resultRelInfo->ri_TrigDesc->trig_insert_after_row)))
20582058
return 1;
20592059

2060+
/*
2061+
* If the foreign table has no columns, disable batching as the INSERT
2062+
* syntax doesn't allow batching multiple empty rows into a zero-column
2063+
* table in a single statement. This is needed for COPY FROM, in which
2064+
* case fmstate must be non-NULL.
2065+
*/
2066+
if (fmstate && list_length(fmstate->target_attrs) == 0)
2067+
return 1;
2068+
20602069
/*
20612070
* Otherwise use the batch size specified for server/table. The number of
20622071
* parameters in a batch is limited to 65535 (uint16), so make sure we

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Copy file name to clipboardExpand all lines: contrib/postgres_fdw/sql/postgres_fdw.sql
+102
Original file line numberDiff line numberDiff line change
@@ -2373,6 +2373,28 @@ copy remp1 from stdin;
23732373

23742374
select tableoid::regclass, * FROM remp1;
23752375

2376+
delete from ctrtest;
2377+
2378+
-- Test copy tuple routing with the batch_size option enabled
2379+
alter server loopback options (add batch_size '2');
2380+
2381+
copy ctrtest from stdin;
2382+
1 foo
2383+
1 bar
2384+
2 baz
2385+
2 qux
2386+
1 test1
2387+
2 test2
2388+
\.
2389+
2390+
select tableoid::regclass, * FROM ctrtest;
2391+
select tableoid::regclass, * FROM remp1;
2392+
select tableoid::regclass, * FROM remp2;
2393+
2394+
delete from ctrtest;
2395+
2396+
alter server loopback options (drop batch_size);
2397+
23762398
drop table ctrtest;
23772399
drop table loct1;
23782400
drop table loct2;
@@ -2527,6 +2549,86 @@ select * from rem3;
25272549
drop foreign table rem3;
25282550
drop table loc3;
25292551

2552+
-- Test COPY FROM with the batch_size option enabled
2553+
alter server loopback options (add batch_size '2');
2554+
2555+
-- Test basic functionality
2556+
copy rem2 from stdin;
2557+
1 foo
2558+
2 bar
2559+
3 baz
2560+
\.
2561+
select * from rem2;
2562+
2563+
delete from rem2;
2564+
2565+
-- Test check constraints
2566+
alter table loc2 add constraint loc2_f1positive check (f1 >= 0);
2567+
alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0);
2568+
2569+
-- check constraint is enforced on the remote side, not locally
2570+
copy rem2 from stdin;
2571+
1 foo
2572+
2 bar
2573+
3 baz
2574+
\.
2575+
copy rem2 from stdin; -- ERROR
2576+
-1 xyzzy
2577+
\.
2578+
select * from rem2;
2579+
2580+
alter foreign table rem2 drop constraint rem2_f1positive;
2581+
alter table loc2 drop constraint loc2_f1positive;
2582+
2583+
delete from rem2;
2584+
2585+
-- Test remote triggers
2586+
create trigger trig_row_before_insert before insert on loc2
2587+
for each row execute procedure trig_row_before_insupdate();
2588+
2589+
-- The new values are concatenated with ' triggered !'
2590+
copy rem2 from stdin;
2591+
1 foo
2592+
2 bar
2593+
3 baz
2594+
\.
2595+
select * from rem2;
2596+
2597+
drop trigger trig_row_before_insert on loc2;
2598+
2599+
delete from rem2;
2600+
2601+
create trigger trig_null before insert on loc2
2602+
for each row execute procedure trig_null();
2603+
2604+
-- Nothing happens
2605+
copy rem2 from stdin;
2606+
1 foo
2607+
2 bar
2608+
3 baz
2609+
\.
2610+
select * from rem2;
2611+
2612+
drop trigger trig_null on loc2;
2613+
2614+
delete from rem2;
2615+
2616+
-- Check with zero-column foreign table; batch insert will be disabled
2617+
alter table loc2 drop column f1;
2618+
alter table loc2 drop column f2;
2619+
alter table rem2 drop column f1;
2620+
alter table rem2 drop column f2;
2621+
copy rem2 from stdin;
2622+
2623+
2624+
2625+
\.
2626+
select * from rem2;
2627+
2628+
delete from rem2;
2629+
2630+
alter server loopback options (drop batch_size);
2631+
25302632
-- ===================================================================
25312633
-- test for TRUNCATE
25322634
-- ===================================================================

‎doc/src/sgml/fdwhandler.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/fdwhandler.sgml
+3-1
Original file line numberDiff line numberDiff line change
@@ -665,7 +665,9 @@ ExecForeignBatchInsert(EState *estate,
665665

666666
<para>
667667
Note that this function is also called when inserting routed tuples into
668-
a foreign-table partition. See the callback functions
668+
a foreign-table partition or executing <command>COPY FROM</command> on
669+
a foreign table, in which case it is called in a different way than it
670+
is in the <command>INSERT</command> case. See the callback functions
669671
described below that allow the FDW to support that.
670672
</para>
671673

‎doc/src/sgml/postgres-fdw.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/postgres-fdw.sgml
+4
Original file line numberDiff line numberDiff line change
@@ -398,6 +398,10 @@ OPTIONS (ADD password_required 'false');
398398
exceeds the limit, the <literal>batch_size</literal> will be adjusted to
399399
avoid an error.
400400
</para>
401+
402+
<para>
403+
This option also applies when copying into foreign tables.
404+
</para>
401405
</listitem>
402406
</varlistentry>
403407

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.