Skip to content

Commit 6acf7e0

Browse files
zilderzilder
committed
pathman: create partitions from whole range (PL)
1 parent fc069f5 commit 6acf7e0

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_rel WHERE 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_rel WHERE 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_attribute TEXT
109109
, p_start_value ANYELEMENT
110110
, p_end_value ANYELEMENT
111-
, p_count INTEGER)
111+
, p_interval ANYELEMENT)
112112
RETURNS INTEGER AS
113113
$$
114114
DECLARE
@@ -117,12 +117,12 @@ DECLARE
117117
v_dt_interval INTERVAL;
118118
v_type REGTYPE;
119119
v_is_date BOOL;
120-
i INTEGER;
120+
i INTEGER := 0;
121121
BEGIN
122122
p_relation := @extschema@.validate_relname(p_relation);
123123

124-
IF p_count <= 0 THEN
125-
RAISE EXCEPTION 'Partitions count must be greater than zero';
124+
IF p_interval <= 0 THEN
125+
RAISE EXCEPTION 'Interval must be positive';
126126
END IF;
127127

128128
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE 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_count as DOUBLE 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_type IN ('date'::regtype, 'timestamp'::regtype, 'timestamptz'::regtype) THEN
142-
v_is_date = TRUE;
143-
IF v_interval BETWEEN 30 AND 31 THEN
144-
v_dt_interval := '1 month';
145-
ELSIF v_interval BETWEEN 365 AND 366 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_type IN ('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 i IN 1..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 i IN 1..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+
CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
161+
p_relation TEXT
162+
, p_attribute TEXT
163+
, p_start_value ANYELEMENT
164+
, p_end_value ANYELEMENT
165+
, p_interval INTERVAL)
166+
RETURNS INTEGER AS
167+
$$
168+
DECLARE
169+
v_value TEXT;
170+
v_interval DOUBLE PRECISION;
171+
v_dt_interval INTERVAL;
172+
v_type REGTYPE;
173+
v_is_date BOOL;
174+
i INTEGER := 0;
175+
BEGIN
176+
p_relation := @extschema@.validate_relname(p_relation);
177+
178+
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE 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-
RETURN p_count;
202+
RETURN i;
181203
END
182204
$$ LANGUAGE plpgsql;
183205

0 commit comments

Comments
 (0)