Skip to content

Commit 08cb364

Browse files
robertmhaasrobertmhaas
committed
Even if some partitions are foreign, allow tuple routing.
This doesn't allow routing tuple to the foreign partitions themselves, but it permits tuples to be routed to regular partitions despite the presence of foreign partitions in the same inheritance hierarchy. Etsuro Fujita, reviewed by Amit Langote and by me. Discussion: http://postgr.es/m/bc3db4c1-1693-3b8a-559f-33ad2b50b7ad@lab.ntt.co.jp
1 parent a6c678f commit 08cb364

File tree

8 files changed

+126
-11
lines changed

8 files changed

+126
-11
lines changed

‎contrib/file_fdw/data/list1.csv

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
1,foo
2+
1,bar

‎contrib/file_fdw/data/list2.bad

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
2,baz
2+
1,qux

‎contrib/file_fdw/data/list2.csv

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
2,baz
2+
2,qux

‎contrib/file_fdw/input/file_fdw.source

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -162,6 +162,27 @@ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
162162
ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
163163
DROP TABLE agg;
164164

165+
-- declarative partitioning tests
166+
SET ROLE regress_file_fdw_superuser;
167+
CREATE TABLE pt (a int, b text) partition by list (a);
168+
CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
169+
OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
170+
CREATE TABLE p2 partition of pt for values in (2);
171+
SELECT tableoid::regclass, * FROM pt;
172+
SELECT tableoid::regclass, * FROM p1;
173+
SELECT tableoid::regclass, * FROM p2;
174+
COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
175+
COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
176+
SELECT tableoid::regclass, * FROM pt;
177+
SELECT tableoid::regclass, * FROM p1;
178+
SELECT tableoid::regclass, * FROM p2;
179+
INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
180+
INSERT INTO pt VALUES (2, 'xyzzy');
181+
SELECT tableoid::regclass, * FROM pt;
182+
SELECT tableoid::regclass, * FROM p1;
183+
SELECT tableoid::regclass, * FROM p2;
184+
DROP TABLE pt;
185+
165186
-- privilege tests
166187
SET ROLE regress_file_fdw_superuser;
167188
SELECT * FROM agg_text ORDER BY a;

‎contrib/file_fdw/output/file_fdw.source

Lines changed: 81 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -289,6 +289,87 @@ SELECT tableoid::regclass, * FROM agg FOR UPDATE;
289289

290290
ALTER FOREIGN TABLE agg_csv NO INHERIT agg;
291291
DROP TABLE agg;
292+
-- declarative partitioning tests
293+
SET ROLE regress_file_fdw_superuser;
294+
CREATE TABLE pt (a int, b text) partition by list (a);
295+
CREATE FOREIGN TABLE p1 partition of pt for values in (1) SERVER file_server
296+
OPTIONS (format 'csv', filename '@abs_srcdir@/data/list1.csv', delimiter ',');
297+
CREATE TABLE p2 partition of pt for values in (2);
298+
SELECT tableoid::regclass, * FROM pt;
299+
tableoid | a | b
300+
----------+---+-----
301+
p1 | 1 | foo
302+
p1 | 1 | bar
303+
(2 rows)
304+
305+
SELECT tableoid::regclass, * FROM p1;
306+
tableoid | a | b
307+
----------+---+-----
308+
p1 | 1 | foo
309+
p1 | 1 | bar
310+
(2 rows)
311+
312+
SELECT tableoid::regclass, * FROM p2;
313+
tableoid | a | b
314+
----------+---+---
315+
(0 rows)
316+
317+
COPY pt FROM '@abs_srcdir@/data/list2.bad' with (format 'csv', delimiter ','); -- ERROR
318+
ERROR: cannot route inserted tuples to a foreign table
319+
CONTEXT: COPY pt, line 2: "1,qux"
320+
COPY pt FROM '@abs_srcdir@/data/list2.csv' with (format 'csv', delimiter ',');
321+
SELECT tableoid::regclass, * FROM pt;
322+
tableoid | a | b
323+
----------+---+-----
324+
p1 | 1 | foo
325+
p1 | 1 | bar
326+
p2 | 2 | baz
327+
p2 | 2 | qux
328+
(4 rows)
329+
330+
SELECT tableoid::regclass, * FROM p1;
331+
tableoid | a | b
332+
----------+---+-----
333+
p1 | 1 | foo
334+
p1 | 1 | bar
335+
(2 rows)
336+
337+
SELECT tableoid::regclass, * FROM p2;
338+
tableoid | a | b
339+
----------+---+-----
340+
p2 | 2 | baz
341+
p2 | 2 | qux
342+
(2 rows)
343+
344+
INSERT INTO pt VALUES (1, 'xyzzy'); -- ERROR
345+
ERROR: cannot route inserted tuples to a foreign table
346+
INSERT INTO pt VALUES (2, 'xyzzy');
347+
SELECT tableoid::regclass, * FROM pt;
348+
tableoid | a | b
349+
----------+---+-------
350+
p1 | 1 | foo
351+
p1 | 1 | bar
352+
p2 | 2 | baz
353+
p2 | 2 | qux
354+
p2 | 2 | xyzzy
355+
(5 rows)
356+
357+
SELECT tableoid::regclass, * FROM p1;
358+
tableoid | a | b
359+
----------+---+-----
360+
p1 | 1 | foo
361+
p1 | 1 | bar
362+
(2 rows)
363+
364+
SELECT tableoid::regclass, * FROM p2;
365+
tableoid | a | b
366+
----------+---+-------
367+
p2 | 2 | baz
368+
p2 | 2 | qux
369+
p2 | 2 | xyzzy
370+
(3 rows)
371+
372+
DROP TABLE pt;
292373
-- privilege tests
293374
SET ROLE regress_file_fdw_superuser;
294375
SELECT * FROM agg_text ORDER BY a;

‎src/backend/executor/execMain.c

Lines changed: 16 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1097,8 +1097,9 @@ InitPlan(QueryDesc *queryDesc, int eflags)
10971097
* CheckValidRowMarkRel.
10981098
*/
10991099
void
1100-
CheckValidResultRel(Relation resultRel, CmdType operation)
1100+
CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation)
11011101
{
1102+
Relation resultRel = resultRelInfo->ri_RelationDesc;
11021103
TriggerDesc *trigDesc = resultRel->trigdesc;
11031104
FdwRoutine *fdwroutine;
11041105

@@ -1169,10 +1170,16 @@ CheckValidResultRel(Relation resultRel, CmdType operation)
11691170
break;
11701171
case RELKIND_FOREIGN_TABLE:
11711172
/* Okay only if the FDW supports it */
1172-
fdwroutine = GetFdwRoutineForRelation(resultRel, false);
1173+
fdwroutine = resultRelInfo->ri_FdwRoutine;
11731174
switch (operation)
11741175
{
11751176
case CMD_INSERT:
1177+
/*
1178+
* If foreign partition to do tuple-routing for, skip the
1179+
* check; it's disallowed elsewhere.
1180+
*/
1181+
if (resultRelInfo->ri_PartitionRoot)
1182+
break;
11761183
if (fdwroutine->ExecForeignInsert == NULL)
11771184
ereport(ERROR,
11781185
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -3305,11 +3312,6 @@ ExecSetupPartitionTupleRouting(Relation rel,
33053312
partrel = heap_open(lfirst_oid(cell), NoLock);
33063313
part_tupdesc = RelationGetDescr(partrel);
33073314

3308-
/*
3309-
* Verify result relation is a valid target for the current operation.
3310-
*/
3311-
CheckValidResultRel(partrel, CMD_INSERT);
3312-
33133315
/*
33143316
* Save a tuple conversion map to convert a tuple routed to this
33153317
* partition from the parent's type to the partition's.
@@ -3323,8 +3325,10 @@ ExecSetupPartitionTupleRouting(Relation rel,
33233325
rel,
33243326
estate->es_instrument);
33253327

3326-
estate->es_leaf_result_relations =
3327-
lappend(estate->es_leaf_result_relations, leaf_part_rri);
3328+
/*
3329+
* Verify result relation is a valid target for INSERT.
3330+
*/
3331+
CheckValidResultRel(leaf_part_rri, CMD_INSERT);
33283332

33293333
/*
33303334
* Open partition indices (remember we do not support ON CONFLICT in
@@ -3335,6 +3339,9 @@ ExecSetupPartitionTupleRouting(Relation rel,
33353339
leaf_part_rri->ri_IndexRelationDescs == NULL)
33363340
ExecOpenIndices(leaf_part_rri, false);
33373341

3342+
estate->es_leaf_result_relations =
3343+
lappend(estate->es_leaf_result_relations, leaf_part_rri);
3344+
33383345
leaf_part_rri++;
33393346
i++;
33403347
}

‎src/backend/executor/nodeModifyTable.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1853,7 +1853,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
18531853
/*
18541854
* Verify result relation is a valid target for the current operation
18551855
*/
1856-
CheckValidResultRel(resultRelInfo->ri_RelationDesc, operation);
1856+
CheckValidResultRel(resultRelInfo, operation);
18571857

18581858
/*
18591859
* If there are indices on the result relation, open them and save

‎src/include/executor/executor.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -177,7 +177,7 @@ extern void ExecutorEnd(QueryDesc *queryDesc);
177177
extern void standard_ExecutorEnd(QueryDesc *queryDesc);
178178
extern void ExecutorRewind(QueryDesc *queryDesc);
179179
extern bool ExecCheckRTPerms(List *rangeTable, bool ereport_on_violation);
180-
extern void CheckValidResultRel(Relation resultRel, CmdType operation);
180+
extern void CheckValidResultRel(ResultRelInfo *resultRelInfo, CmdType operation);
181181
extern void InitResultRelInfo(ResultRelInfo *resultRelInfo,
182182
Relation resultRelationDesc,
183183
Index resultRelationIndex,

0 commit comments

Comments
 (0)