Skip to content

Commit 17139ee

Browse files
zilderzilder
committed
pathman: auto partitions count determination
1 parent 813e6d8 commit 17139ee

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_attribute TEXT
97
, p_start_value ANYELEMENT
108
, p_interval INTERVAL
11-
, p_count INTEGER)
9+
, p_count INTEGER DEFAULT NULL)
1210
RETURNS INTEGER AS
1311
$$
1412
DECLARE
15-
v_value TEXT;
13+
v_rows_count INTEGER;
14+
v_max p_start_value%TYPE;
15+
v_cur_value p_start_value%TYPE := p_start_value;
1616
i INTEGER;
17-
sql TEXT;
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 IS NULL 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 IS NULL 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_attribute TEXT
7997
, p_start_value ANYELEMENT
8098
, p_interval ANYELEMENT
81-
, p_count INTEGER)
99+
, p_count INTEGER DEFAULT NULL)
82100
RETURNS INTEGER AS
83101
$$
84102
DECLARE
85-
v_value TEXT;
103+
v_rows_count INTEGER;
104+
v_max p_start_value%TYPE;
105+
v_cur_value p_start_value%TYPE := p_start_value;
86106
i INTEGER;
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 IS NULL 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 IS NULL 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
RETURNS INTEGER AS
147188
$$
148189
DECLARE
149-
v_value TEXT;
150190
v_interval DOUBLE 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
RETURNS INTEGER AS
210254
$$
211255
DECLARE
212-
v_value TEXT;
213256
v_interval DOUBLE 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_count INTEGER;
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 > 0 AND (v_min IS NULL OR v_max IS NULL) 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
CREATE INDEX ON test.range_rel (dt);
2727
INSERT INTO test.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-
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 4);
29+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL, 2);
30+
SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2015-01-01'::DATE, '1 month'::INTERVAL);
3031
SELECT COUNT(*) FROM test.range_rel;
3132
SELECT COUNT(*) FROM ONLY test.range_rel;
3233

0 commit comments

Comments
 (0)