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

Commit4d9c1ee

Browse files
committed
pathman:
* tests updated
1 parent3cebefb commit4d9c1ee

File tree

3 files changed

+322
-6
lines changed

3 files changed

+322
-6
lines changed

‎contrib/pathman/expected/pathman.out

Lines changed: 303 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,303 @@
1+
CREATE EXTENSION pathman;
2+
CREATE TABLE hash_rel (
3+
id SERIAL PRIMARY KEY,
4+
value INTEGER);
5+
SELECT create_hash_partitions('hash_rel', 'value', 3);
6+
NOTICE: trigger "hash_rel_insert_trigger" for relation "hash_rel" does not exist, skipping
7+
NOTICE: function hash_rel_hash_insert_trigger_func() does not exist, skipping
8+
NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping
9+
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
10+
create_hash_partitions
11+
------------------------
12+
13+
(1 row)
14+
15+
CREATE TABLE range_rel (
16+
id SERIAL PRIMARY KEY,
17+
dt TIMESTAMP,
18+
txt TEXT);
19+
SELECT create_range_partitions('range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 3);
20+
NOTICE: sequence "range_rel_seq" does not exist, skipping
21+
create_range_partitions
22+
-------------------------
23+
24+
(1 row)
25+
26+
CREATE TABLE num_range_rel (
27+
id SERIAL PRIMARY KEY,
28+
txt TEXT);
29+
SELECT create_range_partitions('num_range_rel', 'id', 0, 1000, 3);
30+
NOTICE: sequence "num_range_rel_seq" does not exist, skipping
31+
create_range_partitions
32+
-------------------------
33+
34+
(1 row)
35+
36+
INSERT INTO hash_rel VALUES (1, 1);
37+
INSERT INTO hash_rel VALUES (2, 2);
38+
INSERT INTO hash_rel VALUES (3, 3);
39+
INSERT INTO hash_rel VALUES (4, 4);
40+
INSERT INTO hash_rel VALUES (5, 5);
41+
INSERT INTO hash_rel VALUES (6, 6);
42+
INSERT INTO num_range_rel SELECT g, md5(g::TEXT) FROM generate_series(1, 3000) as g;
43+
VACUUM;
44+
SET enable_indexscan = OFF;
45+
SET enable_bitmapscan = OFF;
46+
SET enable_seqscan = ON;
47+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
48+
QUERY PLAN
49+
------------------------------
50+
Append
51+
-> Seq Scan on hash_rel_0
52+
-> Seq Scan on hash_rel_1
53+
-> Seq Scan on hash_rel_2
54+
(4 rows)
55+
56+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;
57+
QUERY PLAN
58+
------------------------------
59+
Append
60+
-> Seq Scan on hash_rel_2
61+
Filter: (value = 2)
62+
(3 rows)
63+
64+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
65+
QUERY PLAN
66+
------------------------------
67+
Append
68+
-> Seq Scan on hash_rel_1
69+
Filter: (value = 1)
70+
-> Seq Scan on hash_rel_2
71+
Filter: (value = 2)
72+
(5 rows)
73+
74+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
75+
QUERY PLAN
76+
-----------------------------------
77+
Append
78+
-> Seq Scan on num_range_rel_3
79+
Filter: (id > 2500)
80+
-> Seq Scan on num_range_rel_4
81+
(4 rows)
82+
83+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;
84+
QUERY PLAN
85+
-----------------------------------
86+
Append
87+
-> Seq Scan on num_range_rel_2
88+
-> Seq Scan on num_range_rel_3
89+
(3 rows)
90+
91+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
92+
QUERY PLAN
93+
-----------------------------------
94+
Append
95+
-> Seq Scan on num_range_rel_2
96+
Filter: (id >= 1500)
97+
-> Seq Scan on num_range_rel_3
98+
Filter: (id < 2500)
99+
(5 rows)
100+
101+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
102+
QUERY PLAN
103+
-----------------------------------
104+
Append
105+
-> Seq Scan on num_range_rel_1
106+
Filter: (id >= 500)
107+
-> Seq Scan on num_range_rel_2
108+
Filter: (id < 1500)
109+
-> Seq Scan on num_range_rel_3
110+
Filter: (id > 2500)
111+
-> Seq Scan on num_range_rel_4
112+
(8 rows)
113+
114+
SET enable_indexscan = ON;
115+
SET enable_bitmapscan = OFF;
116+
SET enable_seqscan = OFF;
117+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel;
118+
QUERY PLAN
119+
------------------------------
120+
Append
121+
-> Seq Scan on hash_rel_0
122+
-> Seq Scan on hash_rel_1
123+
-> Seq Scan on hash_rel_2
124+
(4 rows)
125+
126+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2;
127+
QUERY PLAN
128+
------------------------------
129+
Append
130+
-> Seq Scan on hash_rel_2
131+
Filter: (value = 2)
132+
(3 rows)
133+
134+
EXPLAIN (COSTS OFF) SELECT * FROM hash_rel WHERE value = 2 OR value = 1;
135+
QUERY PLAN
136+
------------------------------
137+
Append
138+
-> Seq Scan on hash_rel_1
139+
Filter: (value = 1)
140+
-> Seq Scan on hash_rel_2
141+
Filter: (value = 2)
142+
(5 rows)
143+
144+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id > 2500;
145+
QUERY PLAN
146+
----------------------------------------------------------------
147+
Append
148+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
149+
Index Cond: (id > 2500)
150+
-> Seq Scan on num_range_rel_4
151+
(4 rows)
152+
153+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1000 AND id < 3000;
154+
QUERY PLAN
155+
-----------------------------------
156+
Append
157+
-> Seq Scan on num_range_rel_2
158+
-> Seq Scan on num_range_rel_3
159+
(3 rows)
160+
161+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id >= 1500 AND id < 2500;
162+
QUERY PLAN
163+
----------------------------------------------------------------
164+
Append
165+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
166+
Index Cond: (id >= 1500)
167+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
168+
Index Cond: (id < 2500)
169+
(5 rows)
170+
171+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500) OR (id > 2500);
172+
QUERY PLAN
173+
----------------------------------------------------------------
174+
Append
175+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
176+
Index Cond: (id >= 500)
177+
-> Index Scan using num_range_rel_2_pkey on num_range_rel_2
178+
Index Cond: (id < 1500)
179+
-> Index Scan using num_range_rel_3_pkey on num_range_rel_3
180+
Index Cond: (id > 2500)
181+
-> Seq Scan on num_range_rel_4
182+
(8 rows)
183+
184+
/*
185+
* Test split and merge
186+
*/
187+
/* Split first partition in half */
188+
SELECT split_range_partition('num_range_rel_1', 500);
189+
NOTICE: Creating new partition...
190+
NOTICE: Copying data to new partition...
191+
NOTICE: Altering original partition...
192+
NOTICE: Done!
193+
split_range_partition
194+
-----------------------
195+
{0,1000}
196+
(1 row)
197+
198+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
199+
QUERY PLAN
200+
----------------------------------------------------------------
201+
Append
202+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
203+
Index Cond: (id >= 100)
204+
-> Index Scan using num_range_rel_5_pkey on num_range_rel_5
205+
Index Cond: (id <= 700)
206+
(5 rows)
207+
208+
SELECT split_range_partition('range_rel_1', '2015-01-15'::DATE);
209+
NOTICE: Creating new partition...
210+
NOTICE: Copying data to new partition...
211+
NOTICE: Altering original partition...
212+
NOTICE: Done!
213+
split_range_partition
214+
-------------------------
215+
{01-01-2015,02-01-2015}
216+
(1 row)
217+
218+
/* Merge two partitions into one */
219+
SELECT merge_range_partitions('num_range_rel_1', 'num_range_rel_' || currval('num_range_rel_seq'));
220+
NOTICE: Altering first partition...
221+
NOTICE: Copying data...
222+
NOTICE: Dropping second partition...
223+
NOTICE: Done!
224+
merge_range_partitions
225+
------------------------
226+
227+
(1 row)
228+
229+
EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE id BETWEEN 100 AND 700;
230+
QUERY PLAN
231+
----------------------------------------------------------------
232+
Append
233+
-> Index Scan using num_range_rel_1_pkey on num_range_rel_1
234+
Index Cond: ((id >= 100) AND (id <= 700))
235+
(3 rows)
236+
237+
SELECT merge_range_partitions('range_rel_1', 'range_rel_' || currval('range_rel_seq'));
238+
NOTICE: Altering first partition...
239+
NOTICE: Copying data...
240+
NOTICE: Dropping second partition...
241+
NOTICE: Done!
242+
merge_range_partitions
243+
------------------------
244+
245+
(1 row)
246+
247+
/* Append and prepend partitions */
248+
SELECT append_partition('num_range_rel');
249+
NOTICE: Appending new partition...
250+
NOTICE: Done!
251+
append_partition
252+
------------------
253+
254+
(1 row)
255+
256+
SELECT prepend_partition('num_range_rel');
257+
NOTICE: Prepending new partition...
258+
NOTICE: Done!
259+
prepend_partition
260+
-------------------
261+
262+
(1 row)
263+
264+
SELECT append_partition('range_rel');
265+
NOTICE: Appending new partition...
266+
NOTICE: Done!
267+
append_partition
268+
------------------
269+
270+
(1 row)
271+
272+
SELECT prepend_partition('range_rel');
273+
NOTICE: Prepending new partition...
274+
NOTICE: Done!
275+
prepend_partition
276+
-------------------
277+
278+
(1 row)
279+
280+
/*
281+
* Clean up
282+
*/
283+
SELECT drop_hash_partitions('hash_rel');
284+
NOTICE: trigger "hash_rel_update_trigger" for relation "hash_rel" does not exist, skipping
285+
NOTICE: function hash_rel_hash_update_trigger_func() does not exist, skipping
286+
drop_hash_partitions
287+
----------------------
288+
289+
(1 row)
290+
291+
DROP TABLE hash_rel CASCADE;
292+
NOTICE: drop cascades to 3 other objects
293+
DETAIL: drop cascades to table hash_rel_0
294+
drop cascades to table hash_rel_1
295+
drop cascades to table hash_rel_2
296+
SELECT drop_range_partitions('num_range_rel');
297+
drop_range_partitions
298+
-----------------------
299+
300+
(1 row)
301+
302+
DROP TABLE num_range_rel CASCADE;
303+
DROP EXTENSION pathman;

‎contrib/pathman/sql/pathman.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,12 @@ CREATE TABLE hash_rel (
55
valueINTEGER);
66
SELECT create_hash_partitions('hash_rel','value',3);
77

8+
CREATETABLErange_rel (
9+
idSERIALPRIMARY KEY,
10+
dtTIMESTAMP,
11+
txtTEXT);
12+
SELECT create_range_partitions('range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,3);
13+
814
CREATETABLEnum_range_rel (
915
idSERIALPRIMARY KEY,
1016
txtTEXT);
@@ -52,10 +58,21 @@ EXPLAIN (COSTS OFF) SELECT * FROM num_range_rel WHERE (id >= 500 AND id < 1500)
5258
SELECT split_range_partition('num_range_rel_1',500);
5359
EXPLAIN (COSTS OFF)SELECT*FROM num_range_relWHERE id BETWEEN100AND700;
5460

61+
SELECT split_range_partition('range_rel_1','2015-01-15'::DATE);
62+
5563
/* Merge two partitions into one*/
5664
SELECT merge_range_partitions('num_range_rel_1','num_range_rel_'|| currval('num_range_rel_seq'));
5765
EXPLAIN (COSTS OFF)SELECT*FROM num_range_relWHERE id BETWEEN100AND700;
5866

67+
SELECT merge_range_partitions('range_rel_1','range_rel_'|| currval('range_rel_seq'));
68+
69+
/* Append and prepend partitions*/
70+
SELECT append_partition('num_range_rel');
71+
SELECT prepend_partition('num_range_rel');
72+
73+
SELECT append_partition('range_rel');
74+
SELECT prepend_partition('range_rel');
75+
5976
/*
6077
* Clean up
6178
*/

‎contrib/pathman/sql/range.sql

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ BEGIN
1818
FROM pg_classWHERE relname= v_relation;
1919

2020
IF EXISTS (SELECT*FROM pg_pathman_relsWHERE relname= v_relation) THEN
21-
RAISE EXCEPTION'Reltion "%s" has already been partitioned', v_relation;
21+
RAISE EXCEPTION'Reltion "%" has already been partitioned', v_relation;
2222
END IF;
2323

2424
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', v_relation);
@@ -61,7 +61,7 @@ BEGIN
6161
FROM pg_classWHERE relname= v_relation;
6262

6363
IF EXISTS (SELECT*FROM pg_pathman_relsWHERE relname= v_relation) THEN
64-
RAISE EXCEPTION'Reltion "%s" has already been partitioned', v_relation;
64+
RAISE EXCEPTION'Reltion "%" has already been partitioned', v_relation;
6565
END IF;
6666

6767
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', v_relation);
@@ -379,10 +379,6 @@ BEGIN
379379
END IF;
380380

381381
v_atttype := get_attribute_type_name(v_parent_relid1::regclass::text, v_attname);
382-
-- SELECT typname INTO v_atttype
383-
-- FROM pg_type
384-
-- JOIN pg_attribute on atttypid = "oid"
385-
-- WHERE attrelid = 'num_range_rel'::regclass::oid and attname = lower(v_attname);
386382

387383
EXECUTE format('SELECT merge_range_partitions_internal($1, $2 , $3, NULL::%s)', v_atttype)
388384
USING v_parent_relid1, v_part1_relid , v_part2_relid;

0 commit comments

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