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

Commit17139ee

Browse files
committed
pathman: auto partitions count determination
1 parent813e6d8 commit17139ee

File tree

4 files changed

+81
-39
lines changed

4 files changed

+81
-39
lines changed

‎contrib/pg_pathman/expected/pg_pathman.out‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -51,7 +51,9 @@ CREATE TABLE test.range_rel (
5151
CREATE INDEX ON test.range_rel (dt);
5252
INSERT INTO test.range_rel (dt, txt)
5353
SELECT g, md5(g::TEXT) FROM generate_series('2015-01-01', '2015-04-30', '1 day'::interval) as g;
54-
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 4);
54+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
55+
ERROR: Not enough partitions to fit all the values of 'dt' P0001
56+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
5557
NOTICE: sequence "range_rel_seq" does not exist, skipping
5658
NOTICE: Copying data to partitions...
5759
create_range_partitions

‎contrib/pg_pathman/pl_funcs.c‎

Lines changed: 1 addition & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,9 +10,6 @@
1010
#include"storage/lmgr.h"
1111

1212

13-
#include"miscadmin.h"
14-
15-
1613
/* declarations */
1714
PG_FUNCTION_INFO_V1(on_partitions_created );
1815
PG_FUNCTION_INFO_V1(on_partitions_updated );
@@ -136,10 +133,8 @@ find_or_create_range_partition(PG_FUNCTION_ARGS)
136133
}
137134

138135
/* Start background worker to create new partitions */
139-
elog(WARNING,"Starting worker");
140136
child_oid=create_partitions_bg_worker(relid,value,value_type);
141-
elog(WARNING,"BACKEND PID >>>%d<<<",MyProcPid);
142-
// sleep(10);
137+
143138
// SPI_connect();
144139
// child_oid = create_partitions(relid, value, value_type);
145140
// SPI_finish();

‎contrib/pg_pathman/range.sql‎

Lines changed: 75 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,3 @@
1-
/* TODO: function that subdivide specified period with specivied interval*/
2-
31
/*
42
* Creates RANGE partitions for specified relation based on datetime attribute
53
*/
@@ -8,13 +6,14 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
86
, p_attributeTEXT
97
, p_start_value ANYELEMENT
108
, p_interval INTERVAL
11-
, p_countINTEGER)
9+
, p_countINTEGER DEFAULTNULL)
1210
RETURNSINTEGERAS
1311
$$
1412
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;
1616
iINTEGER;
17-
sqlTEXT;
1817
BEGIN
1918
p_relation := @extschema@.validate_relname(p_relation);
2019

@@ -26,25 +25,40 @@ BEGIN
2625
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
2726
END IF;
2827

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;
3133

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;
4041

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*/
4151
EXECUTE format('SELECT @extschema@.check_boundaries(''%s'',''%s'',''%s'',''%s''::%s)'
4252
, p_relation
4353
, p_attribute
4454
, p_start_value
4555
, p_start_value+ p_interval*p_count
4656
, pg_typeof(p_start_value));
4757

58+
59+
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
60+
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
61+
4862
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
4963
VALUES (p_relation, p_attribute,2);
5064

@@ -67,6 +81,10 @@ BEGIN
6781
PERFORM @extschema@.partition_data(p_relation);
6882

6983
RETURN p_count;
84+
85+
EXCEPTION WHEN others THEN
86+
PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer);
87+
RAISE EXCEPTION'% %', SQLERRM, SQLSTATE;
7088
END
7189
$$ LANGUAGE plpgsql;
7290

@@ -78,11 +96,13 @@ CREATE OR REPLACE FUNCTION @extschema@.create_range_partitions(
7896
, p_attributeTEXT
7997
, p_start_value ANYELEMENT
8098
, p_interval ANYELEMENT
81-
, p_countINTEGER)
99+
, p_countINTEGER DEFAULTNULL)
82100
RETURNSINTEGERAS
83101
$$
84102
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;
86106
iINTEGER;
87107
BEGIN
88108
p_relation := @extschema@.validate_relname(p_relation);
@@ -95,15 +115,37 @@ BEGIN
95115
RAISE EXCEPTION'Relation "%" has already been partitioned', p_relation;
96116
END IF;
97117

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;
100139

101140
/* check boundaries*/
102141
PERFORM @extschema@.check_boundaries(p_relation
103142
, p_attribute
104143
, p_start_value
105144
, p_start_value+ p_interval*p_count);
106145

146+
EXECUTE format('DROP SEQUENCE IF EXISTS %s_seq', p_relation);
147+
EXECUTE format('CREATE SEQUENCE %s_seq START 1', p_relation);
148+
107149
INSERT INTO @extschema@.pathman_config (relname, attname, parttype)
108150
VALUES (p_relation, p_attribute,2);
109151

@@ -128,8 +170,7 @@ BEGIN
128170
RETURN p_count;
129171

130172
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);
133174
RAISE EXCEPTION'% %', SQLERRM, SQLSTATE;
134175
END
135176
$$ LANGUAGE plpgsql;
@@ -146,7 +187,6 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
146187
RETURNSINTEGERAS
147188
$$
148189
DECLARE
149-
v_valueTEXT;
150190
v_intervalDOUBLE PRECISION;
151191
v_dt_interval INTERVAL;
152192
v_type REGTYPE;
@@ -191,9 +231,13 @@ BEGIN
191231
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
192232

193233
/* Copy data*/
194-
--PERFORM @extschema@.partition_data(p_relation);
234+
PERFORM @extschema@.partition_data(p_relation);
195235

196236
RETURN i;
237+
238+
EXCEPTION WHEN others THEN
239+
PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer);
240+
RAISE EXCEPTION'% %', SQLERRM, SQLSTATE;
197241
END
198242
$$ LANGUAGE plpgsql;
199243

@@ -209,7 +253,6 @@ CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
209253
RETURNSINTEGERAS
210254
$$
211255
DECLARE
212-
v_valueTEXT;
213256
v_intervalDOUBLE PRECISION;
214257
v_dt_interval INTERVAL;
215258
v_type REGTYPE;
@@ -249,9 +292,13 @@ BEGIN
249292
PERFORM @extschema@.on_create_partitions(p_relation::regclass::oid);
250293

251294
/* Copy data*/
252-
--PERFORM @extschema@.partition_data(p_relation);
295+
PERFORM @extschema@.partition_data(p_relation);
253296

254297
RETURN i;
298+
299+
EXCEPTION WHEN others THEN
300+
PERFORM @extschema@.on_remove_partitions(p_relation::regclass::integer);
301+
RAISE EXCEPTION'% %', SQLERRM, SQLSTATE;
255302
END
256303
$$ LANGUAGE plpgsql;
257304

@@ -270,28 +317,25 @@ DECLARE
270317
v_max p_start_value%TYPE;
271318
v_countINTEGER;
272319
BEGIN
273-
RAISE NOTICE'check_boundaries(%)', p_relname;
274320
/* Get min and max values*/
275321
EXECUTE format('SELECT count(*), min(%s), max(%s) FROM %s WHERE NOT %s IS NULL',
276322
p_attribute, p_attribute, p_relname, p_attribute)
277323
INTO v_count, v_min, v_max;
278324

279-
RAISE NOTICE'>>> MIN, MAX <<< %, %, %', v_count, v_min, v_max;
280-
281325
/* check if column has NULL values*/
282326
IF v_count>0AND (v_min ISNULLOR v_max ISNULL) THEN
283327
RAISE EXCEPTION'''%'' column has NULL values', p_attribute;
284328
END IF;
285329

286330
/* check lower boundary*/
287-
IF p_start_value< v_min THEN
331+
IF p_start_value> v_min THEN
288332
RAISE EXCEPTION'Start value is less than minimum value of''%'''
289333
, p_attribute;
290334
END IF;
291335

292336
/* 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''%'''
295339
, p_attribute;
296340
END IF;
297341
END

‎contrib/pg_pathman/sql/pg_pathman.sql‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,8 @@ CREATE TABLE test.range_rel (
2626
CREATEINDEXONtest.range_rel (dt);
2727
INSERT INTOtest.range_rel (dt, txt)
2828
SELECT g, md5(g::TEXT)FROM generate_series('2015-01-01','2015-04-30','1 day'::interval)as g;
29-
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,4);
29+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL,2);
30+
SELECTpathman.create_range_partitions('test.range_rel','dt','2015-01-01'::DATE,'1 month'::INTERVAL);
3031
SELECTCOUNT(*)FROMtest.range_rel;
3132
SELECTCOUNT(*)FROM ONLYtest.range_rel;
3233

0 commit comments

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