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

Commite5caa24

Browse files
committed
pathman: interval is stored in config
1 parent17139ee commite5caa24

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
CREATETABLEIF NOT EXISTS @extschema@.pathman_config (
5-
idSERIALPRIMARY KEY,
6-
relnameVARCHAR(127),
7-
attnameVARCHAR(127),
8-
parttypeINTEGER
11+
idSERIALPRIMARY KEY,
12+
relnameVARCHAR(127),
13+
attnameVARCHAR(127),
14+
parttypeINTEGER,
15+
range_intervalTEXT
916
);
1017

1118

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

149+
/*
150+
* Check if regclass if date or timestamp
151+
*/
152+
CREATEOR REPLACE FUNCTION @extschema@.is_date(cls REGTYPE)
153+
RETURNSBOOLEANAS
154+
$$
155+
BEGIN
156+
RETURN clsIN ('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_configWHERE 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 iIN1..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 iIN1..p_count
@@ -190,7 +203,6 @@ DECLARE
190203
v_intervalDOUBLE PRECISION;
191204
v_dt_interval INTERVAL;
192205
v_type REGTYPE;
193-
v_is_date BOOL;
194206
iINTEGER :=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_intervalDOUBLE PRECISION;
257270
v_dt_interval INTERVAL;
258271
v_type REGTYPE;
259-
v_is_date BOOL;
260272
iINTEGER :=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_attnameTEXT;
643656
v_atttypeTEXT;
644657
v_part_nameTEXT;
658+
v_intervalTEXT;
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_configWHERE relname= p_relation;
647664

648-
v_attname := attnameFROM @extschema@.pathman_configWHERE 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
CREATEOR REPLACE FUNCTION @extschema@.append_partition_internal(
662678
p_relationTEXT
679+
, p_atttypeTEXT
680+
, p_intervalTEXT
663681
, p_range ANYARRAY DEFAULTNULL)
664682
RETURNSTEXTAS
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
CREATEOR REPLACE FUNCTION @extschema@.prepend_partition(p_relationTEXT)
688712
RETURNSTEXTAS
@@ -691,15 +715,17 @@ DECLARE
691715
v_attnameTEXT;
692716
v_atttypeTEXT;
693717
v_part_nameTEXT;
718+
v_intervalTEXT;
694719
BEGIN
695720
p_relation := @extschema@.validate_relname(p_relation);
696721

697-
v_attname := attnameFROM @extschema@.pathman_configWHERE relname= p_relation;
722+
SELECT attname, range_interval INTO v_attname, v_interval
723+
FROM @extschema@.pathman_configWHERE 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
CREATEOR REPLACE FUNCTION @extschema@.prepend_partition_internal(
711737
p_relationTEXT
738+
, p_atttypeTEXT
739+
, p_intervalTEXT
712740
, p_range ANYARRAY DEFAULTNULL)
713741
RETURNSTEXTAS
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
RETURNSOIDAS
902940
$$
903941
DECLARE
942+
v_relationTEXT;
904943
v_cntINTEGER :=0;
905944
iINTEGER :=0;
906945
v_partTEXT;
946+
v_intervalTEXT;
947+
v_attnameTEXT;
948+
v_cur_value p_new_value%TYPE;
949+
v_next_value p_new_value%TYPE;
950+
v_is_dateBOOLEAN;
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_configWHERE 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_valueAND i<1000
961+
v_cur_value := p_max;
962+
WHILE v_cur_value<= p_new_valueAND 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_valueAND i<1000
979+
v_cur_value := p_min;
980+
WHILEv_cur_value>= p_new_valueAND 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)
[8]ページ先頭
©2009-2025 Movatter.jp