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

Commit6acf7e0

Browse files
committed
pathman: create partitions from whole range (PL)
1 parentfc069f5 commit6acf7e0

File tree

3 files changed

+99
-37
lines changed

3 files changed

+99
-37
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -637,4 +637,37 @@ EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt < '2010-03-01';
637637
-> Seq Scan on range_rel_13
638638
(5 rows)
639639

640+
/* Create range partitions from whole range */
641+
SELECT drop_range_partitions('range_rel');
642+
drop_range_partitions
643+
-----------------------
644+
645+
(1 row)
646+
647+
SELECT create_partitions_from_range('range_rel', 'id', 1, 1000, 100);
648+
create_partitions_from_range
649+
------------------------------
650+
10
651+
(1 row)
652+
653+
SELECT drop_range_partitions('range_rel');
654+
drop_range_partitions
655+
-----------------------
656+
657+
(1 row)
658+
659+
SELECT create_partitions_from_range('range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
660+
create_partitions_from_range
661+
------------------------------
662+
12
663+
(1 row)
664+
665+
EXPLAIN (COSTS OFF) SELECT * FROM range_rel WHERE dt = '2015-12-15';
666+
QUERY PLAN
667+
--------------------------------------------------------------------------------
668+
Append
669+
-> Seq Scan on range_rel_12
670+
Filter: (dt = 'Tue Dec 15 00:00:00 2015'::timestamp without time zone)
671+
(3 rows)
672+
640673
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -166,4 +166,11 @@ CREATE TABLE range_rel_archive (CHECK (dt >= '2000-01-01' AND dt < '2005-01-01')
166166
SELECT on_update_partitions('range_rel'::regclass::oid);
167167
EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt<'2010-03-01';
168168

169+
/* Create range partitions from whole range*/
170+
SELECT drop_range_partitions('range_rel');
171+
SELECT create_partitions_from_range('range_rel','id',1,1000,100);
172+
SELECT drop_range_partitions('range_rel');
173+
SELECT create_partitions_from_range('range_rel','dt','2015-01-01'::date,'2015-12-01'::date,'1 month'::interval);
174+
EXPLAIN (COSTS OFF)SELECT*FROM range_relWHERE dt='2015-12-15';
175+
169176
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/sql/range.sql‎

Lines changed: 59 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -108,7 +108,7 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
108108
, p_attributeTEXT
109109
, p_start_value ANYELEMENT
110110
, p_end_value ANYELEMENT
111-
,p_countINTEGER)
111+
,p_interval ANYELEMENT)
112112
RETURNSINTEGERAS
113113
$$
114114
DECLARE
@@ -117,12 +117,12 @@ DECLARE
117117
v_dt_interval INTERVAL;
118118
v_type REGTYPE;
119119
v_is_date BOOL;
120-
iINTEGER;
120+
iINTEGER :=0;
121121
BEGIN
122122
p_relation := @extschema@.validate_relname(p_relation);
123123

124-
IFp_count<=0 THEN
125-
RAISE EXCEPTION'Partitions countmust begreater than zero';
124+
IFp_interval<=0 THEN
125+
RAISE EXCEPTION'Intervalmust bepositive';
126126
END IF;
127127

128128
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
@@ -135,49 +135,71 @@ BEGIN
135135
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
136136
VALUES (p_relation, p_attribute,2);
137137

138-
v_type := pg_typeof(p_start_value);
139-
v_interval := (p_end_value- p_start_value)/ cast(p_countasDOUBLE PRECISION);
138+
WHILE p_start_value<= p_end_value
139+
LOOP
140+
PERFORM @extschema@.create_single_range_partition(p_relation
141+
, p_start_value
142+
, p_start_value+ p_interval);
143+
p_start_value := p_start_value+ p_interval;
144+
i := i+1;
145+
END LOOP;
140146

141-
IF v_typeIN ('date'::regtype,'timestamp'::regtype,'timestamptz'::regtype) THEN
142-
v_is_date= TRUE;
143-
IF v_interval BETWEEN30AND31 THEN
144-
v_dt_interval :='1 month';
145-
ELSIF v_interval BETWEEN365AND366 THEN
146-
v_dt_interval :='1 year';
147-
ELSE
148-
v_dt_interval := format('%s day', ceil(v_interval));
149-
END IF;
150-
ELSIF v_typeIN ('integer'::regtype,'smallint'::regtype,'bigint'::regtype) THEN
151-
v_interval := ceil(v_interval);
152-
END IF;
147+
/* Create triggers*/
148+
PERFORM @extschema@.create_range_insert_trigger(p_relation, p_attribute);
153149

154-
/* create first partition*/
155-
IF v_is_date THEN
156-
FOR iIN1..p_count
157-
LOOP
158-
p_end_value := p_start_value+ v_dt_interval;
159-
PERFORM @extschema@.create_single_range_partition(p_relation
160-
, p_start_value
161-
, p_end_value);
162-
p_start_value := p_end_value;
163-
END LOOP;
164-
ELSE
165-
FOR iIN1..p_count
166-
LOOP
167-
PERFORM @extschema@.create_single_range_partition(p_relation
168-
, p_start_value
169-
, p_start_value+ v_interval);
170-
p_start_value := p_start_value+ v_interval;
171-
END LOOP;
150+
/* Notify backend about changes*/
151+
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
152+
153+
RETURN i;
154+
END
155+
$$ LANGUAGE plpgsql;
156+
157+
/*
158+
* Creates RANGE partitions for specified range based on datetime attribute
159+
*/
160+
CREATEOR REPLACE FUNCTION @extschema@.create_partitions_from_range(
161+
p_relationTEXT
162+
, p_attributeTEXT
163+
, p_start_value ANYELEMENT
164+
, p_end_value ANYELEMENT
165+
, p_interval INTERVAL)
166+
RETURNSINTEGERAS
167+
$$
168+
DECLARE
169+
v_valueTEXT;
170+
v_intervalDOUBLE PRECISION;
171+
v_dt_interval INTERVAL;
172+
v_type REGTYPE;
173+
v_is_date BOOL;
174+
iINTEGER :=0;
175+
BEGIN
176+
p_relation := @extschema@.validate_relname(p_relation);
177+
178+
IF EXISTS (SELECT*FROM @extschema@.pathman_configWHERE relname= p_relation) THEN
179+
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
172180
END IF;
173181

182+
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
183+
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
184+
185+
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
186+
VALUES (p_relation, p_attribute,2);
187+
188+
WHILE p_start_value<= p_end_value
189+
LOOP
190+
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s);', pg_typeof(p_start_value))
191+
USING p_relation, p_start_value, p_start_value+ p_interval;
192+
p_start_value := p_start_value+ p_interval;
193+
i := i+1;
194+
END LOOP;
195+
174196
/* Create triggers*/
175197
PERFORM @extschema@.create_range_insert_trigger(p_relation, p_attribute);
176198

177199
/* Notify backend about changes*/
178200
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
179201

180-
RETURNp_count;
202+
RETURNi;
181203
END
182204
$$ LANGUAGE plpgsql;
183205

0 commit comments

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