Skip to content

Commit e5caa24

Browse files
zilderzilder
committed
pathman: interval is stored in config
1 parent 17139ee commit e5caa24

File tree

3 files changed

+130
-44
lines changed

3 files changed

+130
-44
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -537,8 +537,8 @@ SELECT * FROM test.range_rel WHERE dt = '2015-03-15';
537537
DROP TABLE test.range_rel CASCADE;
538538
NOTICE: drop cascades to 16 other objects
539539
SELECT * FROM pathman.pathman_config;
540-
id | relname | attname | parttype
541-
----+---------+---------+----------
540+
id | relname | attname | parttype | range_interval
541+
----+---------+---------+----------+----------------
542542
(0 rows)
543543

544544
DROP EXTENSION pg_pathman;

‎contrib/pg_pathman/init.sql

Lines changed: 24 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,18 @@
11
/*
2-
* Relations using partitioning
2+
* Pathman config
3+
* relname - schema qualified relation name
4+
* attname - partitioning key
5+
* parttype - partitioning type:
6+
* 1 - HASH
7+
* 2 - RANGE
8+
* range_interval - base interval for RANGE partitioning in string representation
39
*/
410
CREATE TABLE IF NOT EXISTS @extschema@.pathman_config (
5-
id SERIAL PRIMARY KEY,
6-
relname VARCHAR(127),
7-
attname VARCHAR(127),
8-
parttype INTEGER
11+
id SERIAL PRIMARY KEY,
12+
relname VARCHAR(127),
13+
attname VARCHAR(127),
14+
parttype INTEGER,
15+
range_interval TEXT
916
);
1017

1118

@@ -139,6 +146,18 @@ END
139146
$$
140147
LANGUAGE plpgsql;
141148

149+
/*
150+
* Check if regclass if date or timestamp
151+
*/
152+
CREATE OR REPLACE FUNCTION @extschema@.is_date(cls REGTYPE)
153+
RETURNS BOOLEAN AS
154+
$$
155+
BEGIN
156+
RETURN cls IN ('timestamp'::regtype, 'timestamptz'::regtype, 'date'::regtype);
157+
END
158+
$$
159+
LANGUAGE plpgsql;
160+
142161
/*
143162
* DDL trigger that deletes entry from pathman_config
144163
*/

‎contrib/pg_pathman/range.sql

Lines changed: 104 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -17,9 +17,9 @@ DECLARE
1717
BEGIN
1818
p_relation := @extschema@.validate_relname(p_relation);
1919

20-
IF p_count <= 0 THEN
21-
RAISE EXCEPTION 'Partitions count must be greater than zero';
22-
END IF;
20+
-- IF p_count <= 0 THEN
21+
-- RAISE EXCEPTION 'Partitions count must be greater than zero';
22+
-- END IF;
2323

2424
IF EXISTS (SELECT * FROM @extschema@.pathman_config WHERE relname = p_relation) THEN
2525
RAISE EXCEPTION 'Relation "%" has already been partitioned', p_relation;
@@ -47,20 +47,32 @@ BEGIN
4747
END LOOP;
4848
END IF;
4949

50-
/* check boundaries */
50+
/* TODO: think about reusing code */
51+
-- EXECUTE format('SELECT @extschema@.create_partitions_from_range(''%s'', ''%s'', ''%s'', ''%s''::%s, ''%s''::interval)'
52+
-- , p_relation
53+
-- , p_attribute
54+
-- , p_start_value
55+
-- , p_start_value + p_interval*p_count
56+
-- , pg_typeof(p_start_value)
57+
-- , p_interval);
58+
59+
-- RETURN p_count;
60+
61+
/* Check boundaries */
5162
EXECUTE format('SELECT @extschema@.check_boundaries(''%s'', ''%s'', ''%s'', ''%s''::%s)'
5263
, p_relation
5364
, p_attribute
5465
, p_start_value
5566
, p_start_value + p_interval*p_count
5667
, pg_typeof(p_start_value));
5768

58-
69+
/* Create sequence for child partitions names */
5970
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
6071
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
6172

62-
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
63-
VALUES (p_relation, p_attribute, 2);
73+
/* Insert new entry to pathman config */
74+
INSERT INTO @extschema@.pathman_config (relname, attname, parttype, range_interval)
75+
VALUES (p_relation, p_attribute, 2, p_interval::text);
6476

6577
/* create first partition */
6678
FOR i IN 1..p_count
@@ -146,8 +158,9 @@ BEGIN
146158
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
147159
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
148160

149-
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
150-
VALUES (p_relation, p_attribute, 2);
161+
/* Insert new entry to pathman config */
162+
INSERT INTO @extschema@.pathman_config (relname, attname, parttype, range_interval)
163+
VALUES (p_relation, p_attribute, 2, p_interval::text);
151164

152165
/* create first partition */
153166
FOR i IN 1..p_count
@@ -190,7 +203,6 @@ DECLARE
190203
v_interval DOUBLE PRECISION;
191204
v_dt_interval INTERVAL;
192205
v_type REGTYPE;
193-
v_is_date BOOL;
194206
i INTEGER := 0;
195207
BEGIN
196208
p_relation := @extschema@.validate_relname(p_relation);
@@ -212,8 +224,9 @@ BEGIN
212224
, p_start_value
213225
, p_end_value);
214226

215-
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
216-
VALUES (p_relation, p_attribute, 2);
227+
/* Insert new entry to pathman config */
228+
INSERT INTO @extschema@.pathman_config (relname, attname, parttype, range_interval)
229+
VALUES (p_relation, p_attribute, 2, p_interval::text);
217230

218231
WHILE p_start_value <= p_end_value
219232
LOOP
@@ -256,7 +269,6 @@ DECLARE
256269
v_interval DOUBLE PRECISION;
257270
v_dt_interval INTERVAL;
258271
v_type REGTYPE;
259-
v_is_date BOOL;
260272
i INTEGER := 0;
261273
BEGIN
262274
p_relation := @extschema@.validate_relname(p_relation);
@@ -274,8 +286,9 @@ BEGIN
274286
, p_start_value
275287
, p_end_value);
276288

277-
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
278-
VALUES (p_relation, p_attribute, 2);
289+
/* Insert new entry to pathman config */
290+
INSERT INTO @extschema@.pathman_config (relname, attname, parttype, range_interval)
291+
VALUES (p_relation, p_attribute, 2, p_interval::text);
279292

280293
WHILE p_start_value <= p_end_value
281294
LOOP
@@ -642,15 +655,18 @@ DECLARE
642655
v_attname TEXT;
643656
v_atttype TEXT;
644657
v_part_name TEXT;
658+
v_interval TEXT;
645659
BEGIN
646660
p_relation := @extschema@.validate_relname(p_relation);
661+
662+
SELECT attname, range_interval INTO v_attname, v_interval
663+
FROM @extschema@.pathman_config WHERE relname = p_relation;
647664

648-
v_attname := attname FROM @extschema@.pathman_config WHERE relname = p_relation;
649665
v_atttype := @extschema@.get_attribute_type_name(p_relation, v_attname);
650-
EXECUTE format('SELECT @extschema@.append_partition_internal($1, ARRAY[]::%s[])'
651-
, v_atttype)
666+
667+
EXECUTE format('SELECT @extschema@.append_partition_internal($1, $2, $3, ARRAY[]::%s[])', v_atttype)
652668
INTO v_part_name
653-
USING p_relation;
669+
USING p_relation, v_atttype, v_interval;
654670

655671
RETURN v_part_name;
656672
END
@@ -660,6 +676,8 @@ LANGUAGE plpgsql;
660676

661677
CREATE OR REPLACE FUNCTION @extschema@.append_partition_internal(
662678
p_relation TEXT
679+
, p_atttype TEXT
680+
, p_interval TEXT
663681
, p_range ANYARRAY DEFAULT NULL)
664682
RETURNS TEXT AS
665683
$$
@@ -668,9 +686,15 @@ DECLARE
668686
BEGIN
669687
p_range := @extschema@.get_range_by_idx(p_relation::regclass::oid, -1, 0);
670688
RAISE NOTICE 'Appending new partition...';
671-
v_part_name := @extschema@.create_single_range_partition(p_relation
672-
, p_range[2]
673-
, p_range[2] + (p_range[2] - p_range[1]));
689+
IF @extschema@.is_date(p_atttype::regtype) THEN
690+
v_part_name := @extschema@.create_single_range_partition(p_relation
691+
, p_range[2]
692+
, p_range[2] + p_interval::interval);
693+
ELSE
694+
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $2 + $3::%s)', p_atttype)
695+
USING p_relation, p_range[2], p_interval
696+
INTO v_part_name;
697+
END IF;
674698

675699
/* Tell backend to reload configuration */
676700
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
@@ -682,7 +706,7 @@ LANGUAGE plpgsql;
682706

683707

684708
/*
685-
* Append new partition
709+
* Prepend new partition
686710
*/
687711
CREATE OR REPLACE FUNCTION @extschema@.prepend_partition(p_relation TEXT)
688712
RETURNS TEXT AS
@@ -691,15 +715,17 @@ DECLARE
691715
v_attname TEXT;
692716
v_atttype TEXT;
693717
v_part_name TEXT;
718+
v_interval TEXT;
694719
BEGIN
695720
p_relation := @extschema@.validate_relname(p_relation);
696721

697-
v_attname := attname FROM @extschema@.pathman_config WHERE relname = p_relation;
722+
SELECT attname, range_interval INTO v_attname, v_interval
723+
FROM @extschema@.pathman_config WHERE relname = p_relation;
698724
v_atttype := @extschema@.get_attribute_type_name(p_relation, v_attname);
699-
EXECUTE format('SELECT @extschema@.prepend_partition_internal($1, ARRAY[]::%s[])'
700-
, v_atttype)
725+
726+
EXECUTE format('SELECT @extschema@.prepend_partition_internal($1, $2, $3, ARRAY[]::%s[])', v_atttype)
701727
INTO v_part_name
702-
USING p_relation;
728+
USING p_relation, v_atttype, v_interval;
703729

704730
RETURN v_part_name;
705731
END
@@ -709,6 +735,8 @@ LANGUAGE plpgsql;
709735

710736
CREATE OR REPLACE FUNCTION @extschema@.prepend_partition_internal(
711737
p_relation TEXT
738+
, p_atttype TEXT
739+
, p_interval TEXT
712740
, p_range ANYARRAY DEFAULT NULL)
713741
RETURNS TEXT AS
714742
$$
@@ -717,9 +745,19 @@ DECLARE
717745
BEGIN
718746
p_range := @extschema@.get_range_by_idx(p_relation::regclass::oid, 0, 0);
719747
RAISE NOTICE 'Prepending new partition...';
720-
v_part_name := @extschema@.create_single_range_partition(p_relation
721-
, p_range[1] - (p_range[2] - p_range[1])
722-
, p_range[1]);
748+
-- v_part_name := @extschema@.create_single_range_partition(p_relation
749+
-- , p_range[1] - (p_range[2] - p_range[1])
750+
-- , p_range[1]);
751+
752+
IF @extschema@.is_date(p_atttype::regtype) THEN
753+
v_part_name := @extschema@.create_single_range_partition(p_relation
754+
, p_range[1]
755+
, p_range[1] - p_interval::interval);
756+
ELSE
757+
EXECUTE format('SELECT @extschema@.create_single_range_partition($1, $2, $2 - $3::%s)', p_atttype)
758+
USING p_relation, p_range[1], p_interval
759+
INTO v_part_name;
760+
END IF;
723761

724762
/* Tell backend to reload configuration */
725763
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
@@ -901,29 +939,58 @@ CREATE OR REPLACE FUNCTION @extschema@.append_partitions_on_demand_internal(
901939
RETURNS OID AS
902940
$$
903941
DECLARE
942+
v_relation TEXT;
904943
v_cnt INTEGER := 0;
905944
i INTEGER := 0;
906945
v_part TEXT;
946+
v_interval TEXT;
947+
v_attname TEXT;
948+
v_cur_value p_new_value%TYPE;
949+
v_next_value p_new_value%TYPE;
950+
v_is_date BOOLEAN;
907951
BEGIN
952+
v_relation := @extschema@.validate_relname(p_relid::regclass::text);
953+
954+
/* get attribute name and interval */
955+
SELECT attname, range_interval INTO v_attname, v_interval
956+
FROM @extschema@.pathman_config WHERE relname = v_relation;
957+
958+
v_is_date := @extschema@.is_date(pg_typeof(p_new_value)::regtype);
959+
908960
IF p_new_value >= p_max THEN
909-
WHILE (p_max + i * (p_max - p_min)) <= p_new_value AND i < 1000
961+
v_cur_value := p_max;
962+
WHILE v_cur_value <= p_new_value AND i < 1000
910963
LOOP
964+
IF v_is_date THEN
965+
v_next_value := v_cur_value + v_interval::interval;
966+
ELSE
967+
v_next_value := v_cur_value + v_interval;
968+
END IF;
969+
911970
v_part := @extschema@.create_single_range_partition(
912971
@extschema@.get_schema_qualified_name(p_relid::regclass, '.')
913-
, p_max + (i * (p_max - p_min))
914-
, p_max + ((i+1) * (p_max - p_min)));
972+
, v_cur_value
973+
, v_next_value);
915974
i := i + 1;
975+
v_cur_value := v_next_value;
916976
RAISE NOTICE 'partition % created', v_part;
917977
END LOOP;
918978
ELSIF p_new_value <= p_min THEN
919-
920-
WHILE (p_min - i * (p_max - p_min)) >= p_new_value AND i < 1000
979+
v_cur_value := p_min;
980+
WHILE v_cur_value >= p_new_value AND i < 1000
921981
LOOP
982+
IF v_is_date THEN
983+
v_next_value := v_cur_value - v_interval::interval;
984+
ELSE
985+
v_next_value := v_cur_value - v_interval;
986+
END IF;
987+
922988
v_part := @extschema@.create_single_range_partition(
923989
@extschema@.get_schema_qualified_name(p_relid::regclass, '.')
924-
, p_min - ((i+1) * (p_max - p_min))
925-
, p_min - (i * (p_max - p_min)));
990+
, v_next_value
991+
, v_cur_value);
926992
i := i + 1;
993+
v_cur_value := v_next_value;
927994
RAISE NOTICE 'partition % created', v_part;
928995
END LOOP;
929996
ELSE

0 commit comments

Comments
 (0)