Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

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

 Sign up
Appearance settings

Commit97da482

Browse files
author
Etsuro Fujita
committed
Allow batch insertion during COPY into a foreign table.
Commit3d956d9 allowed the COPY, but it's done by inserting individualrows to the foreign table, so it can be inefficient due to the overheadcaused by each round-trip to the foreign server. To improve performanceof the COPY in such a case, this patch allows batch insertion, byextending the multi-insert machinery in CopyFrom() to the foreign-tablecase so that we insert multiple rows to the foreign table at once usingthe FDW callback routine added by commitb663a41. This patch alsoallows this for postgres_fdw. It is enabled by the "batch_size" optionadded by commitb663a41, which is disabled by default.When doing batch insertion, we update progress of the COPY command afterperforming the FDW callback routine, to count rows not suppressed by theFDW as well as a BEFORE ROW INSERT trigger. For consistency, this patchchanges the timing of updating it for plain tables: previously, weupdated 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 thetable using table_multi_insert(), which I think would be consistent evenwith non-batching mode, because in that mode we update it after writingeach 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 parent56c19fe commit97da482

File tree

7 files changed

+460
-89
lines changed

7 files changed

+460
-89
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 105 additions & 0 deletions
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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2057,6 +2057,15 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo)
20572057
resultRelInfo->ri_TrigDesc->trig_insert_after_row)))
20582058
return1;
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+
return1;
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

Lines changed: 102 additions & 0 deletions
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+
deletefrom 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 ctrtestfrom stdin;
2382+
1foo
2383+
1bar
2384+
2baz
2385+
2qux
2386+
1test1
2387+
2test2
2388+
\.
2389+
2390+
select tableoid::regclass,*FROM ctrtest;
2391+
select tableoid::regclass,*FROM remp1;
2392+
select tableoid::regclass,*FROM remp2;
2393+
2394+
deletefrom ctrtest;
2395+
2396+
alter server loopback options (drop batch_size);
2397+
23762398
droptable ctrtest;
23772399
droptable loct1;
23782400
droptable loct2;
@@ -2527,6 +2549,86 @@ select * from rem3;
25272549
drop foreign table rem3;
25282550
droptable 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 rem2from stdin;
2557+
1foo
2558+
2bar
2559+
3baz
2560+
\.
2561+
select*from rem2;
2562+
2563+
deletefrom rem2;
2564+
2565+
-- Test check constraints
2566+
altertable loc2 addconstraint loc2_f1positivecheck (f1>=0);
2567+
alter foreign table rem2 addconstraint rem2_f1positivecheck (f1>=0);
2568+
2569+
-- check constraint is enforced on the remote side, not locally
2570+
copy rem2from stdin;
2571+
1foo
2572+
2bar
2573+
3baz
2574+
\.
2575+
copy rem2from stdin;-- ERROR
2576+
-1xyzzy
2577+
\.
2578+
select*from rem2;
2579+
2580+
alter foreign table rem2 dropconstraint rem2_f1positive;
2581+
altertable loc2 dropconstraint loc2_f1positive;
2582+
2583+
deletefrom rem2;
2584+
2585+
-- Test remote triggers
2586+
createtriggertrig_row_before_insert before inserton loc2
2587+
for each row execute procedure trig_row_before_insupdate();
2588+
2589+
-- The new values are concatenated with ' triggered !'
2590+
copy rem2from stdin;
2591+
1foo
2592+
2bar
2593+
3baz
2594+
\.
2595+
select*from rem2;
2596+
2597+
droptrigger trig_row_before_inserton loc2;
2598+
2599+
deletefrom rem2;
2600+
2601+
createtriggertrig_null before inserton loc2
2602+
for each row execute procedure trig_null();
2603+
2604+
-- Nothing happens
2605+
copy rem2from stdin;
2606+
1foo
2607+
2bar
2608+
3baz
2609+
\.
2610+
select*from rem2;
2611+
2612+
droptrigger trig_nullon loc2;
2613+
2614+
deletefrom rem2;
2615+
2616+
-- Check with zero-column foreign table; batch insert will be disabled
2617+
altertable loc2 drop column f1;
2618+
altertable loc2 drop column f2;
2619+
altertable rem2 drop column f1;
2620+
altertable rem2 drop column f2;
2621+
copy rem2from stdin;
2622+
2623+
2624+
2625+
\.
2626+
select*from rem2;
2627+
2628+
deletefrom rem2;
2629+
2630+
alter server loopback options (drop batch_size);
2631+
25302632
-- ===================================================================
25312633
-- test for TRUNCATE
25322634
-- ===================================================================

‎doc/src/sgml/fdwhandler.sgml

Lines changed: 3 additions & 1 deletion
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

Lines changed: 4 additions & 0 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp