1
- /* TODO: function that subdivide specified period with specivied interval*/
2
-
3
1
/*
4
2
* Creates RANGE partitions for specified relation based on datetime attribute
5
3
*/
@@ -8,13 +6,14 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
8
6
, p_attributeTEXT
9
7
, p_start_value ANYELEMENT
10
8
, p_interval INTERVAL
11
- , p_countINTEGER )
9
+ , p_countINTEGER DEFAULT NULL )
12
10
RETURNSINTEGER AS
13
11
$$
14
12
DECLARE
15
- v_valueTEXT ;
13
+ v_rows_countINTEGER ;
14
+ v_max p_start_value%TYPE;
15
+ v_cur_value p_start_value%TYPE := p_start_value;
16
16
iINTEGER ;
17
- sqlTEXT ;
18
17
BEGIN
19
18
p_relation := @extschema@.validate_relname(p_relation);
20
19
@@ -26,25 +25,40 @@ BEGIN
26
25
RAISE EXCEPTION' Relation "%" has already been partitioned' , p_relation;
27
26
END IF;
28
27
29
- EXECUTE format(' DROP SEQUENCE IF EXISTS %s_seq' , p_relation);
30
- EXECUTE format(' CREATE SEQUENCE %s_seq START 1' , p_relation);
28
+ /* Try to determine partitions count if not set*/
29
+ IF p_count ISNULL THEN
30
+ EXECUTE format(' SELECT count(*), max(%s) FROM %s'
31
+ , p_attribute, p_relation)
32
+ INTO v_rows_count, v_max;
31
33
32
- /* check boundaries*/
33
- sql := format(' SELECT @extschema@.check_boundaries(' ' %s' ' ,' ' %s' ' ,' ' %s' ' ,' ' %s' ' ::%s)'
34
- , p_relation
35
- , p_attribute
36
- , p_start_value
37
- , p_start_value+ p_interval* p_count
38
- , pg_typeof(p_start_value));
39
- RAISE NOTICE' sql: %' , sql;
34
+ IF v_rows_count= 0 THEN
35
+ RAISE EXCEPTION' Cannot determine partitions count for empty table' ;
36
+ END IF;
37
+
38
+ IF v_max ISNULL THEN
39
+ RAISE EXCEPTION' ' ' %' ' column has NULL values' , p_attribute;
40
+ END IF;
40
41
42
+ p_count := 0 ;
43
+ WHILE v_cur_value<= v_max
44
+ LOOP
45
+ v_cur_value := v_cur_value+ p_interval;
46
+ p_count := p_count+ 1 ;
47
+ END LOOP;
48
+ END IF;
49
+
50
+ /* check boundaries*/
41
51
EXECUTE format(' SELECT @extschema@.check_boundaries(' ' %s' ' ,' ' %s' ' ,' ' %s' ' ,' ' %s' ' ::%s)'
42
52
, p_relation
43
53
, p_attribute
44
54
, p_start_value
45
55
, p_start_value+ p_interval* p_count
46
56
, pg_typeof(p_start_value));
47
57
58
+
59
+ EXECUTE format(' DROP SEQUENCE IF EXISTS %s_seq' , p_relation);
60
+ EXECUTE format(' CREATE SEQUENCE %s_seq START 1' , p_relation);
61
+
48
62
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
49
63
VALUES (p_relation, p_attribute,2 );
50
64
67
81
PERFORM @extschema@.partition_data(p_relation);
68
82
69
83
RETURN p_count;
84
+
85
+ EXCEPTION WHEN others THEN
86
+ PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer );
87
+ RAISE EXCEPTION' % %' , SQLERRM, SQLSTATE;
70
88
END
71
89
$$ LANGUAGE plpgsql;
72
90
@@ -78,11 +96,13 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
78
96
, p_attributeTEXT
79
97
, p_start_value ANYELEMENT
80
98
, p_interval ANYELEMENT
81
- , p_countINTEGER )
99
+ , p_countINTEGER DEFAULT NULL )
82
100
RETURNSINTEGER AS
83
101
$$
84
102
DECLARE
85
- v_valueTEXT ;
103
+ v_rows_countINTEGER ;
104
+ v_max p_start_value%TYPE;
105
+ v_cur_value p_start_value%TYPE := p_start_value;
86
106
iINTEGER ;
87
107
BEGIN
88
108
p_relation := @extschema@.validate_relname(p_relation);
@@ -95,15 +115,37 @@ BEGIN
95
115
RAISE EXCEPTION' Relation "%" has already been partitioned' , p_relation;
96
116
END IF;
97
117
98
- EXECUTE format(' DROP SEQUENCE IF EXISTS %s_seq' , p_relation);
99
- EXECUTE format(' CREATE SEQUENCE %s_seq START 1' , p_relation);
118
+ /* Try to determine partitions count if not set*/
119
+ IF p_count ISNULL THEN
120
+ EXECUTE format(' SELECT count(*), max(%s) FROM %s'
121
+ , p_attribute, p_relation)
122
+ INTO v_rows_count, v_max;
123
+
124
+ IF v_rows_count= 0 THEN
125
+ RAISE EXCEPTION' Cannot determine partitions count for empty table' ;
126
+ END IF;
127
+
128
+ IF v_max ISNULL THEN
129
+ RAISE EXCEPTION' ' ' %' ' column has NULL values' , p_attribute;
130
+ END IF;
131
+
132
+ p_count := 0 ;
133
+ WHILE v_cur_value<= v_max
134
+ LOOP
135
+ v_cur_value := v_cur_value+ p_interval;
136
+ p_count := p_count+ 1 ;
137
+ END LOOP;
138
+ END IF;
100
139
101
140
/* check boundaries*/
102
141
PERFORM @extschema@.check_boundaries(p_relation
103
142
, p_attribute
104
143
, p_start_value
105
144
, p_start_value+ p_interval* p_count);
106
145
146
+ EXECUTE format(' DROP SEQUENCE IF EXISTS %s_seq' , p_relation);
147
+ EXECUTE format(' CREATE SEQUENCE %s_seq START 1' , p_relation);
148
+
107
149
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
108
150
VALUES (p_relation, p_attribute,2 );
109
151
@@ -128,8 +170,7 @@ BEGIN
128
170
RETURN p_count;
129
171
130
172
EXCEPTION WHEN others THEN
131
- EXECUTE format(' DROP TABLE %s CASCADE' , p_relation);
132
- PERFORM on_remove_partitions(p_relation::regclass::integer );
173
+ PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer );
133
174
RAISE EXCEPTION' % %' , SQLERRM, SQLSTATE;
134
175
END
135
176
$$ LANGUAGE plpgsql;
@@ -146,7 +187,6 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
146
187
RETURNSINTEGER AS
147
188
$$
148
189
DECLARE
149
- v_valueTEXT ;
150
190
v_intervalDOUBLE PRECISION ;
151
191
v_dt_interval INTERVAL;
152
192
v_type REGTYPE;
@@ -191,9 +231,13 @@ BEGIN
191
231
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid );
192
232
193
233
/* Copy data*/
194
- -- PERFORM @extschema@.partition_data(p_relation);
234
+ PERFORM @extschema@.partition_data(p_relation);
195
235
196
236
RETURN i;
237
+
238
+ EXCEPTION WHEN others THEN
239
+ PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer );
240
+ RAISE EXCEPTION' % %' , SQLERRM, SQLSTATE;
197
241
END
198
242
$$ LANGUAGE plpgsql;
199
243
@@ -209,7 +253,6 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
209
253
RETURNSINTEGER AS
210
254
$$
211
255
DECLARE
212
- v_valueTEXT ;
213
256
v_intervalDOUBLE PRECISION ;
214
257
v_dt_interval INTERVAL;
215
258
v_type REGTYPE;
@@ -249,9 +292,13 @@ BEGIN
249
292
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid );
250
293
251
294
/* Copy data*/
252
- -- PERFORM @extschema@.partition_data(p_relation);
295
+ PERFORM @extschema@.partition_data(p_relation);
253
296
254
297
RETURN i;
298
+
299
+ EXCEPTION WHEN others THEN
300
+ PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer );
301
+ RAISE EXCEPTION' % %' , SQLERRM, SQLSTATE;
255
302
END
256
303
$$ LANGUAGE plpgsql;
257
304
@@ -270,28 +317,25 @@ DECLARE
270
317
v_max p_start_value%TYPE;
271
318
v_countINTEGER ;
272
319
BEGIN
273
- RAISE NOTICE' check_boundaries(%)' , p_relname;
274
320
/* Get min and max values*/
275
321
EXECUTE format(' SELECT count(*), min(%s), max(%s) FROM %s WHERE NOT %s IS NULL' ,
276
322
p_attribute, p_attribute, p_relname, p_attribute)
277
323
INTO v_count, v_min, v_max;
278
324
279
- RAISE NOTICE' >>> MIN, MAX <<< %, %, %' , v_count, v_min, v_max;
280
-
281
325
/* check if column has NULL values*/
282
326
IF v_count> 0 AND (v_min ISNULL OR v_max ISNULL ) THEN
283
327
RAISE EXCEPTION' ' ' %' ' column has NULL values' , p_attribute;
284
328
END IF;
285
329
286
330
/* check lower boundary*/
287
- IF p_start_value< v_min THEN
331
+ IF p_start_value> v_min THEN
288
332
RAISE EXCEPTION' Start value is less than minimum value of' ' %' ' '
289
333
, p_attribute;
290
334
END IF;
291
335
292
336
/* check upper boundary*/
293
- IF p_end_value> = v_max THEN
294
- RAISE EXCEPTION' End value is greater than maximum value of' ' %' ' '
337
+ IF p_end_value< = v_max THEN
338
+ RAISE EXCEPTION' Not enough partitions to fit all the values of' ' %' ' '
295
339
, p_attribute;
296
340
END IF;
297
341
END