diff options
Diffstat (limited to 'contrib/postgres_fdw')
| -rw-r--r-- | contrib/postgres_fdw/expected/postgres_fdw.out | 105 | ||||
| -rw-r--r-- | contrib/postgres_fdw/postgres_fdw.c | 9 | ||||
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 102 |
3 files changed, 216 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index cc9e39c4a5f..9746998751a 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8608,6 +8608,39 @@ select tableoid::regclass, * FROM remp1; remp1 | 1 | bar (2 rows) +delete from ctrtest; +-- Test copy tuple routing with the batch_size option enabled +alter server loopback options (add batch_size '2'); +copy ctrtest from stdin; +select tableoid::regclass, * FROM ctrtest; + tableoid | a | b +----------+---+------- + remp1 | 1 | foo + remp1 | 1 | bar + remp1 | 1 | test1 + remp2 | 2 | baz + remp2 | 2 | qux + remp2 | 2 | test2 +(6 rows) + +select tableoid::regclass, * FROM remp1; + tableoid | a | b +----------+---+------- + remp1 | 1 | foo + remp1 | 1 | bar + remp1 | 1 | test1 +(3 rows) + +select tableoid::regclass, * FROM remp2; + tableoid | b | a +----------+-------+--- + remp2 | baz | 2 + remp2 | qux | 2 + remp2 | test2 | 2 +(3 rows) + +delete from ctrtest; +alter server loopback options (drop batch_size); drop table ctrtest; drop table loct1; drop table loct2; @@ -8771,6 +8804,78 @@ select * from rem3; drop foreign table rem3; drop table loc3; +-- Test COPY FROM with the batch_size option enabled +alter server loopback options (add batch_size '2'); +-- Test basic functionality +copy rem2 from stdin; +select * from rem2; + f1 | f2 +----+----- + 1 | foo + 2 | bar + 3 | baz +(3 rows) + +delete from rem2; +-- Test check constraints +alter table loc2 add constraint loc2_f1positive check (f1 >= 0); +alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0); +-- check constraint is enforced on the remote side, not locally +copy rem2 from stdin; +copy rem2 from stdin; -- ERROR +ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive" +DETAIL: Failing row contains (-1, xyzzy). +CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2) +COPY rem2 +select * from rem2; + f1 | f2 +----+----- + 1 | foo + 2 | bar + 3 | baz +(3 rows) + +alter foreign table rem2 drop constraint rem2_f1positive; +alter table loc2 drop constraint loc2_f1positive; +delete from rem2; +-- Test remote triggers +create trigger trig_row_before_insert before insert on loc2 + for each row execute procedure trig_row_before_insupdate(); +-- The new values are concatenated with ' triggered !' +copy rem2 from stdin; +select * from rem2; + f1 | f2 +----+----------------- + 1 | foo triggered ! + 2 | bar triggered ! + 3 | baz triggered ! +(3 rows) + +drop trigger trig_row_before_insert on loc2; +delete from rem2; +create trigger trig_null before insert on loc2 + for each row execute procedure trig_null(); +-- Nothing happens +copy rem2 from stdin; +select * from rem2; + f1 | f2 +----+---- +(0 rows) + +drop trigger trig_null on loc2; +delete from rem2; +-- Check with zero-column foreign table; batch insert will be disabled +alter table loc2 drop column f1; +alter table loc2 drop column f2; +alter table rem2 drop column f1; +alter table rem2 drop column f2; +copy rem2 from stdin; +select * from rem2; +-- +(3 rows) + +delete from rem2; +alter server loopback options (drop batch_size); -- =================================================================== -- test for TRUNCATE -- =================================================================== diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index 8d013f5b1af..d98709e5e88 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -2058,6 +2058,15 @@ postgresGetForeignModifyBatchSize(ResultRelInfo *resultRelInfo) return 1; /* + * If the foreign table has no columns, disable batching as the INSERT + * syntax doesn't allow batching multiple empty rows into a zero-column + * table in a single statement. This is needed for COPY FROM, in which + * case fmstate must be non-NULL. + */ + if (fmstate && list_length(fmstate->target_attrs) == 0) + return 1; + + /* * Otherwise use the batch size specified for server/table. The number of * parameters in a batch is limited to 65535 (uint16), so make sure we * don't exceed this limit by using the maximum batch_size possible. diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index e48ccd286bb..1962051e541 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2373,6 +2373,28 @@ copy remp1 from stdin; select tableoid::regclass, * FROM remp1; +delete from ctrtest; + +-- Test copy tuple routing with the batch_size option enabled +alter server loopback options (add batch_size '2'); + +copy ctrtest from stdin; +1 foo +1 bar +2 baz +2 qux +1 test1 +2 test2 +\. + +select tableoid::regclass, * FROM ctrtest; +select tableoid::regclass, * FROM remp1; +select tableoid::regclass, * FROM remp2; + +delete from ctrtest; + +alter server loopback options (drop batch_size); + drop table ctrtest; drop table loct1; drop table loct2; @@ -2527,6 +2549,86 @@ select * from rem3; drop foreign table rem3; drop table loc3; +-- Test COPY FROM with the batch_size option enabled +alter server loopback options (add batch_size '2'); + +-- Test basic functionality +copy rem2 from stdin; +1 foo +2 bar +3 baz +\. +select * from rem2; + +delete from rem2; + +-- Test check constraints +alter table loc2 add constraint loc2_f1positive check (f1 >= 0); +alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0); + +-- check constraint is enforced on the remote side, not locally +copy rem2 from stdin; +1 foo +2 bar +3 baz +\. +copy rem2 from stdin; -- ERROR +-1 xyzzy +\. +select * from rem2; + +alter foreign table rem2 drop constraint rem2_f1positive; +alter table loc2 drop constraint loc2_f1positive; + +delete from rem2; + +-- Test remote triggers +create trigger trig_row_before_insert before insert on loc2 + for each row execute procedure trig_row_before_insupdate(); + +-- The new values are concatenated with ' triggered !' +copy rem2 from stdin; +1 foo +2 bar +3 baz +\. +select * from rem2; + +drop trigger trig_row_before_insert on loc2; + +delete from rem2; + +create trigger trig_null before insert on loc2 + for each row execute procedure trig_null(); + +-- Nothing happens +copy rem2 from stdin; +1 foo +2 bar +3 baz +\. +select * from rem2; + +drop trigger trig_null on loc2; + +delete from rem2; + +-- Check with zero-column foreign table; batch insert will be disabled +alter table loc2 drop column f1; +alter table loc2 drop column f2; +alter table rem2 drop column f1; +alter table rem2 drop column f2; +copy rem2 from stdin; + + + +\. +select * from rem2; + +delete from rem2; + +alter server loopback options (drop batch_size); + -- =================================================================== -- test for TRUNCATE -- =================================================================== |