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

Commit3d956d9

Browse files
committed
Allow insert and update tuple routing and COPY for foreign tables.
Also enable this for postgres_fdw.Etsuro Fujita, based on an earlier patch by Amit Langote. The largerpatch series of which this is a part has been reviewed by AmitLangote, David Fetter, Maksim Milyutin, Álvaro Herrera, Stephen Frost,and me. Minor documentation changes to the final version by me.Discussion:http://postgr.es/m/29906a26-da12-8c86-4fb9-d8f88442f2b9@lab.ntt.co.jp
1 parentcb1ff1e commit3d956d9

File tree

16 files changed

+924
-91
lines changed

16 files changed

+924
-91
lines changed

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -136,6 +136,11 @@ DELETE FROM agg_csv WHERE a = 100;
136136
-- but this should be allowed
137137
SELECT * FROM agg_csv FOR UPDATE;
138138

139+
-- copy from isn't supported either
140+
COPY agg_csv FROM STDIN;
141+
123.4
142+
\.
143+
139144
-- constraint exclusion tests
140145
\t on
141146
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 6 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -221,6 +221,9 @@ SELECT * FROM agg_csv FOR UPDATE;
221221
42 | 324.78
222222
(3 rows)
223223

224+
-- copy from isn't supported either
225+
COPY agg_csv FROM STDIN;
226+
ERROR: cannot insert into foreign table "agg_csv"
224227
-- constraint exclusion tests
225228
\t on
226229
EXPLAIN (VERBOSE, COSTS FALSE) SELECT * FROM agg_csv WHERE a < 0;
@@ -315,7 +318,7 @@ SELECT tableoid::regclass, * FROM p2;
315318
(0 rows)
316319

317320
COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
318-
ERROR: cannotroute inserted tuples to aforeign table
321+
ERROR: cannotinsert intoforeign table "p1"
319322
CONTEXT: COPY pt, line 2: "1,qux"
320323
COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
321324
SELECT tableoid::regclass, * FROM pt;
@@ -342,10 +345,10 @@ SELECT tableoid::regclass, * FROM p2;
342345
(2 rows)
343346

344347
INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
345-
ERROR: cannotroute inserted tuples to aforeign table
348+
ERROR: cannotinsert intoforeign table "p1"
346349
INSERT INTO pt VALUES (2, 'xyzzy');
347350
UPDATE pt set a = 1 where a = 2; -- ERROR
348-
ERROR: cannotroute inserted tuples to aforeign table
351+
ERROR: cannotinsert intoforeign table "p1"
349352
SELECT tableoid::regclass, * FROM pt;
350353
tableoid | a | b
351354
----------+---+-------

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 334 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7371,6 +7371,340 @@ NOTICE: drop cascades to foreign table bar2
73717371
drop table loct1;
73727372
drop table loct2;
73737373
-- ===================================================================
7374+
-- test tuple routing for foreign-table partitions
7375+
-- ===================================================================
7376+
-- Test insert tuple routing
7377+
create table itrtest (a int, b text) partition by list (a);
7378+
create table loct1 (a int check (a in (1)), b text);
7379+
create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1');
7380+
create table loct2 (a int check (a in (2)), b text);
7381+
create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2');
7382+
alter table itrtest attach partition remp1 for values in (1);
7383+
alter table itrtest attach partition remp2 for values in (2);
7384+
insert into itrtest values (1, 'foo');
7385+
insert into itrtest values (1, 'bar') returning *;
7386+
a | b
7387+
---+-----
7388+
1 | bar
7389+
(1 row)
7390+
7391+
insert into itrtest values (2, 'baz');
7392+
insert into itrtest values (2, 'qux') returning *;
7393+
a | b
7394+
---+-----
7395+
2 | qux
7396+
(1 row)
7397+
7398+
insert into itrtest values (1, 'test1'), (2, 'test2') returning *;
7399+
a | b
7400+
---+-------
7401+
1 | test1
7402+
2 | test2
7403+
(2 rows)
7404+
7405+
select tableoid::regclass, * FROM itrtest;
7406+
tableoid | a | b
7407+
----------+---+-------
7408+
remp1 | 1 | foo
7409+
remp1 | 1 | bar
7410+
remp1 | 1 | test1
7411+
remp2 | 2 | baz
7412+
remp2 | 2 | qux
7413+
remp2 | 2 | test2
7414+
(6 rows)
7415+
7416+
select tableoid::regclass, * FROM remp1;
7417+
tableoid | a | b
7418+
----------+---+-------
7419+
remp1 | 1 | foo
7420+
remp1 | 1 | bar
7421+
remp1 | 1 | test1
7422+
(3 rows)
7423+
7424+
select tableoid::regclass, * FROM remp2;
7425+
tableoid | b | a
7426+
----------+-------+---
7427+
remp2 | baz | 2
7428+
remp2 | qux | 2
7429+
remp2 | test2 | 2
7430+
(3 rows)
7431+
7432+
delete from itrtest;
7433+
create unique index loct1_idx on loct1 (a);
7434+
-- DO NOTHING without an inference specification is supported
7435+
insert into itrtest values (1, 'foo') on conflict do nothing returning *;
7436+
a | b
7437+
---+-----
7438+
1 | foo
7439+
(1 row)
7440+
7441+
insert into itrtest values (1, 'foo') on conflict do nothing returning *;
7442+
a | b
7443+
---+---
7444+
(0 rows)
7445+
7446+
-- But other cases are not supported
7447+
insert into itrtest values (1, 'bar') on conflict (a) do nothing;
7448+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
7449+
insert into itrtest values (1, 'bar') on conflict (a) do update set b = excluded.b;
7450+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
7451+
select tableoid::regclass, * FROM itrtest;
7452+
tableoid | a | b
7453+
----------+---+-----
7454+
remp1 | 1 | foo
7455+
(1 row)
7456+
7457+
drop table itrtest;
7458+
drop table loct1;
7459+
drop table loct2;
7460+
-- Test update tuple routing
7461+
create table utrtest (a int, b text) partition by list (a);
7462+
create table loct (a int check (a in (1)), b text);
7463+
create foreign table remp (a int check (a in (1)), b text) server loopback options (table_name 'loct');
7464+
create table locp (a int check (a in (2)), b text);
7465+
alter table utrtest attach partition remp for values in (1);
7466+
alter table utrtest attach partition locp for values in (2);
7467+
insert into utrtest values (1, 'foo');
7468+
insert into utrtest values (2, 'qux');
7469+
select tableoid::regclass, * FROM utrtest;
7470+
tableoid | a | b
7471+
----------+---+-----
7472+
remp | 1 | foo
7473+
locp | 2 | qux
7474+
(2 rows)
7475+
7476+
select tableoid::regclass, * FROM remp;
7477+
tableoid | a | b
7478+
----------+---+-----
7479+
remp | 1 | foo
7480+
(1 row)
7481+
7482+
select tableoid::regclass, * FROM locp;
7483+
tableoid | a | b
7484+
----------+---+-----
7485+
locp | 2 | qux
7486+
(1 row)
7487+
7488+
-- It's not allowed to move a row from a partition that is foreign to another
7489+
update utrtest set a = 2 where b = 'foo' returning *;
7490+
ERROR: new row for relation "loct" violates check constraint "loct_a_check"
7491+
DETAIL: Failing row contains (2, foo).
7492+
CONTEXT: remote SQL command: UPDATE public.loct SET a = 2 WHERE ((b = 'foo'::text)) RETURNING a, b
7493+
-- But the reverse is allowed
7494+
update utrtest set a = 1 where b = 'qux' returning *;
7495+
a | b
7496+
---+-----
7497+
1 | qux
7498+
(1 row)
7499+
7500+
select tableoid::regclass, * FROM utrtest;
7501+
tableoid | a | b
7502+
----------+---+-----
7503+
remp | 1 | foo
7504+
remp | 1 | qux
7505+
(2 rows)
7506+
7507+
select tableoid::regclass, * FROM remp;
7508+
tableoid | a | b
7509+
----------+---+-----
7510+
remp | 1 | foo
7511+
remp | 1 | qux
7512+
(2 rows)
7513+
7514+
select tableoid::regclass, * FROM locp;
7515+
tableoid | a | b
7516+
----------+---+---
7517+
(0 rows)
7518+
7519+
-- The executor should not let unexercised FDWs shut down
7520+
update utrtest set a = 1 where b = 'foo';
7521+
drop table utrtest;
7522+
drop table loct;
7523+
-- Test copy tuple routing
7524+
create table ctrtest (a int, b text) partition by list (a);
7525+
create table loct1 (a int check (a in (1)), b text);
7526+
create foreign table remp1 (a int check (a in (1)), b text) server loopback options (table_name 'loct1');
7527+
create table loct2 (a int check (a in (2)), b text);
7528+
create foreign table remp2 (b text, a int check (a in (2))) server loopback options (table_name 'loct2');
7529+
alter table ctrtest attach partition remp1 for values in (1);
7530+
alter table ctrtest attach partition remp2 for values in (2);
7531+
copy ctrtest from stdin;
7532+
select tableoid::regclass, * FROM ctrtest;
7533+
tableoid | a | b
7534+
----------+---+-----
7535+
remp1 | 1 | foo
7536+
remp2 | 2 | qux
7537+
(2 rows)
7538+
7539+
select tableoid::regclass, * FROM remp1;
7540+
tableoid | a | b
7541+
----------+---+-----
7542+
remp1 | 1 | foo
7543+
(1 row)
7544+
7545+
select tableoid::regclass, * FROM remp2;
7546+
tableoid | b | a
7547+
----------+-----+---
7548+
remp2 | qux | 2
7549+
(1 row)
7550+
7551+
-- Copying into foreign partitions directly should work as well
7552+
copy remp1 from stdin;
7553+
select tableoid::regclass, * FROM remp1;
7554+
tableoid | a | b
7555+
----------+---+-----
7556+
remp1 | 1 | foo
7557+
remp1 | 1 | bar
7558+
(2 rows)
7559+
7560+
drop table ctrtest;
7561+
drop table loct1;
7562+
drop table loct2;
7563+
-- ===================================================================
7564+
-- test COPY FROM
7565+
-- ===================================================================
7566+
create table loc2 (f1 int, f2 text);
7567+
alter table loc2 set (autovacuum_enabled = 'false');
7568+
create foreign table rem2 (f1 int, f2 text) server loopback options(table_name 'loc2');
7569+
-- Test basic functionality
7570+
copy rem2 from stdin;
7571+
select * from rem2;
7572+
f1 | f2
7573+
----+-----
7574+
1 | foo
7575+
2 | bar
7576+
(2 rows)
7577+
7578+
delete from rem2;
7579+
-- Test check constraints
7580+
alter table loc2 add constraint loc2_f1positive check (f1 >= 0);
7581+
alter foreign table rem2 add constraint rem2_f1positive check (f1 >= 0);
7582+
-- check constraint is enforced on the remote side, not locally
7583+
copy rem2 from stdin;
7584+
copy rem2 from stdin; -- ERROR
7585+
ERROR: new row for relation "loc2" violates check constraint "loc2_f1positive"
7586+
DETAIL: Failing row contains (-1, xyzzy).
7587+
CONTEXT: remote SQL command: INSERT INTO public.loc2(f1, f2) VALUES ($1, $2)
7588+
COPY rem2, line 1: "-1xyzzy"
7589+
select * from rem2;
7590+
f1 | f2
7591+
----+-----
7592+
1 | foo
7593+
2 | bar
7594+
(2 rows)
7595+
7596+
alter foreign table rem2 drop constraint rem2_f1positive;
7597+
alter table loc2 drop constraint loc2_f1positive;
7598+
delete from rem2;
7599+
-- Test local triggers
7600+
create trigger trig_stmt_before before insert on rem2
7601+
for each statement execute procedure trigger_func();
7602+
create trigger trig_stmt_after after insert on rem2
7603+
for each statement execute procedure trigger_func();
7604+
create trigger trig_row_before before insert on rem2
7605+
for each row execute procedure trigger_data(23,'skidoo');
7606+
create trigger trig_row_after after insert on rem2
7607+
for each row execute procedure trigger_data(23,'skidoo');
7608+
copy rem2 from stdin;
7609+
NOTICE: trigger_func(<NULL>) called: action = INSERT, when = BEFORE, level = STATEMENT
7610+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2
7611+
NOTICE: NEW: (1,foo)
7612+
NOTICE: trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2
7613+
NOTICE: NEW: (2,bar)
7614+
NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2
7615+
NOTICE: NEW: (1,foo)
7616+
NOTICE: trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2
7617+
NOTICE: NEW: (2,bar)
7618+
NOTICE: trigger_func(<NULL>) called: action = INSERT, when = AFTER, level = STATEMENT
7619+
select * from rem2;
7620+
f1 | f2
7621+
----+-----
7622+
1 | foo
7623+
2 | bar
7624+
(2 rows)
7625+
7626+
drop trigger trig_row_before on rem2;
7627+
drop trigger trig_row_after on rem2;
7628+
drop trigger trig_stmt_before on rem2;
7629+
drop trigger trig_stmt_after on rem2;
7630+
delete from rem2;
7631+
create trigger trig_row_before_insert before insert on rem2
7632+
for each row execute procedure trig_row_before_insupdate();
7633+
-- The new values are concatenated with ' triggered !'
7634+
copy rem2 from stdin;
7635+
select * from rem2;
7636+
f1 | f2
7637+
----+-----------------
7638+
1 | foo triggered !
7639+
2 | bar triggered !
7640+
(2 rows)
7641+
7642+
drop trigger trig_row_before_insert on rem2;
7643+
delete from rem2;
7644+
create trigger trig_null before insert on rem2
7645+
for each row execute procedure trig_null();
7646+
-- Nothing happens
7647+
copy rem2 from stdin;
7648+
select * from rem2;
7649+
f1 | f2
7650+
----+----
7651+
(0 rows)
7652+
7653+
drop trigger trig_null on rem2;
7654+
delete from rem2;
7655+
-- Test remote triggers
7656+
create trigger trig_row_before_insert before insert on loc2
7657+
for each row execute procedure trig_row_before_insupdate();
7658+
-- The new values are concatenated with ' triggered !'
7659+
copy rem2 from stdin;
7660+
select * from rem2;
7661+
f1 | f2
7662+
----+-----------------
7663+
1 | foo triggered !
7664+
2 | bar triggered !
7665+
(2 rows)
7666+
7667+
drop trigger trig_row_before_insert on loc2;
7668+
delete from rem2;
7669+
create trigger trig_null before insert on loc2
7670+
for each row execute procedure trig_null();
7671+
-- Nothing happens
7672+
copy rem2 from stdin;
7673+
select * from rem2;
7674+
f1 | f2
7675+
----+----
7676+
(0 rows)
7677+
7678+
drop trigger trig_null on loc2;
7679+
delete from rem2;
7680+
-- Test a combination of local and remote triggers
7681+
create trigger rem2_trig_row_before before insert on rem2
7682+
for each row execute procedure trigger_data(23,'skidoo');
7683+
create trigger rem2_trig_row_after after insert on rem2
7684+
for each row execute procedure trigger_data(23,'skidoo');
7685+
create trigger loc2_trig_row_before_insert before insert on loc2
7686+
for each row execute procedure trig_row_before_insupdate();
7687+
copy rem2 from stdin;
7688+
NOTICE: rem2_trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2
7689+
NOTICE: NEW: (1,foo)
7690+
NOTICE: rem2_trig_row_before(23, skidoo) BEFORE ROW INSERT ON rem2
7691+
NOTICE: NEW: (2,bar)
7692+
NOTICE: rem2_trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2
7693+
NOTICE: NEW: (1,"foo triggered !")
7694+
NOTICE: rem2_trig_row_after(23, skidoo) AFTER ROW INSERT ON rem2
7695+
NOTICE: NEW: (2,"bar triggered !")
7696+
select * from rem2;
7697+
f1 | f2
7698+
----+-----------------
7699+
1 | foo triggered !
7700+
2 | bar triggered !
7701+
(2 rows)
7702+
7703+
drop trigger rem2_trig_row_before on rem2;
7704+
drop trigger rem2_trig_row_after on rem2;
7705+
drop trigger loc2_trig_row_before_insert on loc2;
7706+
delete from rem2;
7707+
-- ===================================================================
73747708
-- test IMPORT FOREIGN SCHEMA
73757709
-- ===================================================================
73767710
CREATE SCHEMA import_source;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp