Skip to content

Commit 1823fbb

Browse files
funbringerfunbringer
committed
fix INSERT INTO ... SELECT ... FROM partitioned_table (+tests)
1 parent 30556ad commit 1823fbb

File tree

3 files changed

+87
-5
lines changed

3 files changed

+87
-5
lines changed

‎expected/pathman_basic.out

Lines changed: 59 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -262,6 +262,64 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.improved_dummy WHERE id = 101 OR id = 5 A
262262

263263
DROP TABLE test.improved_dummy CASCADE;
264264
NOTICE: drop cascades to 11 other objects
265+
/* Test pathman_rel_pathlist_hook() with INSERT query */
266+
CREATE TABLE test.insert_into_select(val int NOT NULL);
267+
INSERT INTO test.insert_into_select SELECT generate_series(1, 100);
268+
SELECT pathman.create_range_partitions('test.insert_into_select', 'val', 1, 20);
269+
NOTICE: sequence "insert_into_select_seq" does not exist, skipping
270+
create_range_partitions
271+
-------------------------
272+
5
273+
(1 row)
274+
275+
CREATE TABLE test.insert_into_select_copy (LIKE test.insert_into_select); /* INSERT INTO ... SELECT ... */
276+
EXPLAIN (COSTS OFF)
277+
INSERT INTO test.insert_into_select_copy
278+
SELECT * FROM test.insert_into_select
279+
WHERE val <= 80;
280+
QUERY PLAN
281+
----------------------------------------------
282+
Insert on insert_into_select_copy
283+
-> Append
284+
-> Seq Scan on insert_into_select_1
285+
-> Seq Scan on insert_into_select_2
286+
-> Seq Scan on insert_into_select_3
287+
-> Seq Scan on insert_into_select_4
288+
Filter: (val <= 80)
289+
(7 rows)
290+
291+
SELECT pathman.set_enable_parent('test.insert_into_select', true);
292+
set_enable_parent
293+
-------------------
294+
295+
(1 row)
296+
297+
EXPLAIN (COSTS OFF)
298+
INSERT INTO test.insert_into_select_copy
299+
SELECT * FROM test.insert_into_select
300+
WHERE val <= 80;
301+
QUERY PLAN
302+
----------------------------------------------
303+
Insert on insert_into_select_copy
304+
-> Append
305+
-> Seq Scan on insert_into_select
306+
Filter: (val <= 80)
307+
-> Seq Scan on insert_into_select_1
308+
-> Seq Scan on insert_into_select_2
309+
-> Seq Scan on insert_into_select_3
310+
-> Seq Scan on insert_into_select_4
311+
Filter: (val <= 80)
312+
(9 rows)
313+
314+
INSERT INTO test.insert_into_select_copy SELECT * FROM test.insert_into_select;
315+
SELECT count(*) FROM test.insert_into_select_copy;
316+
count
317+
-------
318+
100
319+
(1 row)
320+
321+
DROP TABLE test.insert_into_select_copy, test.insert_into_select CASCADE;
322+
NOTICE: drop cascades to 5 other objects
265323
/* test special case: ONLY statement with not-ONLY for partitioned table */
266324
CREATE TABLE test.from_only_test(val INT NOT NULL);
267325
INSERT INTO test.from_only_test SELECT generate_series(1, 20);
@@ -1909,6 +1967,6 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.index_on_childs WHERE c1 > 100 AND c1 < 2
19091967
(12 rows)
19101968

19111969
DROP SCHEMA test CASCADE;
1912-
NOTICE: drop cascades to 47 other objects
1970+
NOTICE: drop cascades to 48 other objects
19131971
DROP EXTENSION pg_pathman CASCADE;
19141972
DROP SCHEMA pathman CASCADE;

‎sql/pathman_basic.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,29 @@ EXPLAIN (COSTS OFF) SELECT * FROM test.improved_dummy WHERE id = 101 OR id = 5 A
7878
DROP TABLE test.improved_dummy CASCADE;
7979

8080

81+
/* Test pathman_rel_pathlist_hook() with INSERT query */
82+
CREATE TABLE test.insert_into_select(val int NOT NULL);
83+
INSERT INTO test.insert_into_select SELECT generate_series(1, 100);
84+
SELECT pathman.create_range_partitions('test.insert_into_select', 'val', 1, 20);
85+
CREATE TABLE test.insert_into_select_copy (LIKE test.insert_into_select); /* INSERT INTO ... SELECT ... */
86+
87+
EXPLAIN (COSTS OFF)
88+
INSERT INTO test.insert_into_select_copy
89+
SELECT * FROM test.insert_into_select
90+
WHERE val <= 80;
91+
92+
SELECT pathman.set_enable_parent('test.insert_into_select', true);
93+
94+
EXPLAIN (COSTS OFF)
95+
INSERT INTO test.insert_into_select_copy
96+
SELECT * FROM test.insert_into_select
97+
WHERE val <= 80;
98+
99+
INSERT INTO test.insert_into_select_copy SELECT * FROM test.insert_into_select;
100+
SELECT count(*) FROM test.insert_into_select_copy;
101+
DROP TABLE test.insert_into_select_copy, test.insert_into_select CASCADE;
102+
103+
81104
/* test special case: ONLY statement with not-ONLY for partitioned table */
82105
CREATE TABLE test.from_only_test(val INT NOT NULL);
83106
INSERT INTO test.from_only_test SELECT generate_series(1, 20);

‎src/hooks.c

Lines changed: 5 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -204,10 +204,11 @@ pathman_rel_pathlist_hook(PlannerInfo *root,
204204
if (!IsPathmanReady())
205205
return;
206206

207-
/* This works only for SELECTs on simple relations */
208-
if (root->parse->commandType != CMD_SELECT ||
209-
rte->rtekind != RTE_RELATION ||
210-
rte->relkind != RELKIND_RELATION)
207+
/* This works only for SELECTs or INSERTs on simple relations */
208+
if (rte->rtekind != RTE_RELATION ||
209+
rte->relkind != RELKIND_RELATION ||
210+
(root->parse->commandType != CMD_SELECT &&
211+
root->parse->commandType != CMD_INSERT)) /* INSERT INTO ... SELECT ... */
211212
return;
212213

213214
/* Skip if this table is not allowed to act as parent (see FROM ONLY) */

0 commit comments

Comments
 (0)