Skip to content

Commit 0184282

Browse files
funbringerfunbringer
committed
drop function create_partitions_from_range()
1 parent b38f5ff commit 0184282

File tree

5 files changed

+1
-262
lines changed

5 files changed

+1
-262
lines changed

‎README.md

Lines changed: 0 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -122,22 +122,6 @@ create_range_partitions(relation REGCLASS,
122122
```
123123
Performs RANGE partitioning for `relation` by partitioning key `attribute`, `start_value` argument specifies initial value, `p_interval` sets the default range for auto created partitions or partitions created with `append_range_partition()` or `prepend_range_partition()` (if `NULL` then auto partition creation feature won't work), `p_count` is the number of premade partitions (if not set then `pg_pathman` tries to determine it based on attribute values). Partition creation callback is invoked for each partition if set beforehand.
124124

125-
```plpgsql
126-
create_partitions_from_range(relation REGCLASS,
127-
attribute TEXT,
128-
start_value ANYELEMENT,
129-
end_value ANYELEMENT,
130-
p_interval ANYELEMENT,
131-
partition_data BOOLEAN DEFAULT TRUE)
132-
133-
create_partitions_from_range(relation REGCLASS,
134-
attribute TEXT,
135-
start_value ANYELEMENT,
136-
end_value ANYELEMENT,
137-
p_interval INTERVAL,
138-
partition_data BOOLEAN DEFAULT TRUE)
139-
```
140-
Performs RANGE-partitioning from specified range for `relation` by partitioning key `attribute`. Partition creation callback is invoked for each partition if set beforehand.
141125

142126
### Data migration
143127

‎expected/pathman_basic.out

Lines changed: 1 addition & 103 deletions
Original file line numberDiff line numberDiff line change
@@ -404,50 +404,6 @@ SELECT count(*) FROM test.insert_into_select_copy;
404404

405405
DROP TABLE test.insert_into_select_copy, test.insert_into_select CASCADE;
406406
NOTICE: drop cascades to 6 other objects
407-
/* Test INSERT hooking with DATE type */
408-
CREATE TABLE test.insert_date_test(val DATE NOT NULL);
409-
SELECT pathman.create_partitions_from_range('test.insert_date_test', 'val',
410-
date '20161001', date '20170101', interval '1 month');
411-
create_partitions_from_range
412-
------------------------------
413-
4
414-
(1 row)
415-
416-
INSERT INTO test.insert_date_test VALUES ('20161201'); /* just insert the date */
417-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
418-
count
419-
-------
420-
4
421-
(1 row)
422-
423-
INSERT INTO test.insert_date_test VALUES ('20170311'); /* append new partitions */
424-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
425-
count
426-
-------
427-
6
428-
(1 row)
429-
430-
INSERT INTO test.insert_date_test VALUES ('20160812'); /* prepend new partitions */
431-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
432-
count
433-
-------
434-
8
435-
(1 row)
436-
437-
SELECT min(val) FROM test.insert_date_test; /* check first date */
438-
min
439-
------------
440-
08-12-2016
441-
(1 row)
442-
443-
SELECT max(val) FROM test.insert_date_test; /* check last date */
444-
max
445-
------------
446-
03-11-2017
447-
(1 row)
448-
449-
DROP TABLE test.insert_date_test CASCADE;
450-
NOTICE: drop cascades to 9 other objects
451407
SET pg_pathman.enable_runtimeappend = OFF;
452408
SET pg_pathman.enable_runtimemergeappend = OFF;
453409
VACUUM;
@@ -1527,23 +1483,6 @@ SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);
15271483
{12-31-2014,01-02-2015}
15281484
(1 row)
15291485

1530-
SELECT pathman.drop_partitions('test."RangeRel"');
1531-
NOTICE: 0 rows copied from test."RangeRel_1"
1532-
NOTICE: 1 rows copied from test."RangeRel_2"
1533-
NOTICE: 1 rows copied from test."RangeRel_3"
1534-
NOTICE: 0 rows copied from test."RangeRel_4"
1535-
NOTICE: 1 rows copied from test."RangeRel_6"
1536-
drop_partitions
1537-
-----------------
1538-
5
1539-
(1 row)
1540-
1541-
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);
1542-
create_partitions_from_range
1543-
------------------------------
1544-
5
1545-
(1 row)
1546-
15471486
DROP TABLE test."RangeRel" CASCADE;
15481487
NOTICE: drop cascades to 6 other objects
15491488
SELECT * FROM pathman.pathman_config;
@@ -1562,21 +1501,6 @@ SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
15621501
3
15631502
(1 row)
15641503

1565-
SELECT pathman.drop_partitions('test."RangeRel"');
1566-
NOTICE: 0 rows copied from test."RangeRel_1"
1567-
NOTICE: 0 rows copied from test."RangeRel_2"
1568-
NOTICE: 0 rows copied from test."RangeRel_3"
1569-
drop_partitions
1570-
-----------------
1571-
3
1572-
(1 row)
1573-
1574-
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);
1575-
create_partitions_from_range
1576-
------------------------------
1577-
3
1578-
(1 row)
1579-
15801504
DROP TABLE test."RangeRel" CASCADE;
15811505
NOTICE: drop cascades to 4 other objects
15821506
DROP EXTENSION pg_pathman;
@@ -1739,32 +1663,6 @@ NOTICE: 0 rows copied from test.range_rel_15
17391663
14
17401664
(1 row)
17411665

1742-
SELECT create_partitions_from_range('test.range_rel', 'id', 1, 1000, 100);
1743-
create_partitions_from_range
1744-
------------------------------
1745-
10
1746-
(1 row)
1747-
1748-
SELECT drop_partitions('test.range_rel', TRUE);
1749-
drop_partitions
1750-
-----------------
1751-
10
1752-
(1 row)
1753-
1754-
SELECT create_partitions_from_range('test.range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
1755-
create_partitions_from_range
1756-
------------------------------
1757-
12
1758-
(1 row)
1759-
1760-
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2015-12-15';
1761-
QUERY PLAN
1762-
--------------------------------------------------------------------------------
1763-
Append
1764-
-> Seq Scan on range_rel_12
1765-
Filter: (dt = 'Tue Dec 15 00:00:00 2015'::timestamp without time zone)
1766-
(3 rows)
1767-
17681666
/* Test NOT operator */
17691667
CREATE TABLE bool_test(a INT NOT NULL, b BOOLEAN);
17701668
SELECT create_hash_partitions('bool_test', 'a', 3);
@@ -1943,6 +1841,6 @@ ORDER BY partition;
19431841
DROP TABLE test.provided_part_names CASCADE;
19441842
NOTICE: drop cascades to 2 other objects
19451843
DROP SCHEMA test CASCADE;
1946-
NOTICE: drop cascades to 42 other objects
1844+
NOTICE: drop cascades to 29 other objects
19471845
DROP EXTENSION pg_pathman CASCADE;
19481846
DROP SCHEMA pathman CASCADE;

‎expected/pathman_mergejoin_0.out renamed to ‎expected/pathman_mergejoin_1.out

File renamed without changes.

‎range.sql

Lines changed: 0 additions & 115 deletions
Original file line numberDiff line numberDiff line change
@@ -301,121 +301,6 @@ END
301301
$$
302302
LANGUAGE plpgsql;
303303

304-
/*
305-
* Creates RANGE partitions for specified range
306-
*/
307-
CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
308-
parent_relid REGCLASS,
309-
expression TEXT,
310-
start_value ANYELEMENT,
311-
end_value ANYELEMENT,
312-
p_interval ANYELEMENT,
313-
partition_data BOOLEAN DEFAULT TRUE)
314-
RETURNS INTEGER AS $$
315-
DECLARE
316-
part_count INTEGER := 0;
317-
318-
BEGIN
319-
PERFORM @extschema@.prepare_for_partitioning(parent_relid,
320-
expression,
321-
partition_data);
322-
323-
/* Check boundaries */
324-
PERFORM @extschema@.check_boundaries(parent_relid,
325-
expression,
326-
start_value,
327-
end_value);
328-
329-
/* Create sequence for child partitions names */
330-
PERFORM @extschema@.create_naming_sequence(parent_relid);
331-
332-
/* Insert new entry to pathman config */
333-
PERFORM @extschema@.add_to_pathman_config(parent_relid, expression,
334-
p_interval::TEXT);
335-
336-
WHILE start_value <= end_value
337-
LOOP
338-
PERFORM @extschema@.create_single_range_partition(
339-
parent_relid,
340-
start_value,
341-
start_value + p_interval,
342-
tablespace := @extschema@.get_tablespace(parent_relid));
343-
344-
start_value := start_value + p_interval;
345-
part_count := part_count + 1;
346-
END LOOP;
347-
348-
/* Relocate data if asked to */
349-
IF partition_data = true THEN
350-
PERFORM @extschema@.set_enable_parent(parent_relid, false);
351-
PERFORM @extschema@.partition_data(parent_relid);
352-
ELSE
353-
PERFORM @extschema@.set_enable_parent(parent_relid, true);
354-
END IF;
355-
356-
RETURN part_count; /* number of created partitions */
357-
END
358-
$$ LANGUAGE plpgsql;
359-
360-
/*
361-
* Creates RANGE partitions for specified range based on datetime expression
362-
*/
363-
CREATE OR REPLACE FUNCTION @extschema@.create_partitions_from_range(
364-
parent_relid REGCLASS,
365-
expression TEXT,
366-
start_value ANYELEMENT,
367-
end_value ANYELEMENT,
368-
p_interval INTERVAL,
369-
partition_data BOOLEAN DEFAULT TRUE)
370-
RETURNS INTEGER AS $$
371-
DECLARE
372-
part_count INTEGER := 0;
373-
374-
BEGIN
375-
PERFORM @extschema@.prepare_for_partitioning(parent_relid,
376-
expression,
377-
partition_data);
378-
379-
/* Check boundaries */
380-
PERFORM @extschema@.check_boundaries(parent_relid,
381-
expression,
382-
start_value,
383-
end_value);
384-
385-
/* Create sequence for child partitions names */
386-
PERFORM @extschema@.create_naming_sequence(parent_relid);
387-
388-
/* Insert new entry to pathman config */
389-
PERFORM @extschema@.add_to_pathman_config(parent_relid, expression,
390-
p_interval::TEXT);
391-
392-
WHILE start_value <= end_value
393-
LOOP
394-
EXECUTE
395-
format('SELECT @extschema@.create_single_range_partition($1, $2, $3::%s, tablespace:=$4);',
396-
@extschema@.get_base_type(pg_typeof(start_value))::TEXT)
397-
USING
398-
parent_relid,
399-
start_value,
400-
start_value + p_interval,
401-
@extschema@.get_tablespace(parent_relid);
402-
403-
start_value := start_value + p_interval;
404-
part_count := part_count + 1;
405-
END LOOP;
406-
407-
/* Relocate data if asked to */
408-
IF partition_data = true THEN
409-
PERFORM @extschema@.set_enable_parent(parent_relid, false);
410-
PERFORM @extschema@.partition_data(parent_relid);
411-
ELSE
412-
PERFORM @extschema@.set_enable_parent(parent_relid, true);
413-
END IF;
414-
415-
RETURN part_count; /* number of created partitions */
416-
END
417-
$$ LANGUAGE plpgsql;
418-
419304

420305
/*
421306
* Split RANGE partition

‎sql/pathman_basic.sql

Lines changed: 0 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -145,26 +145,6 @@ SELECT count(*) FROM test.insert_into_select_copy;
145145
DROP TABLE test.insert_into_select_copy, test.insert_into_select CASCADE;
146146

147147

148-
/* Test INSERT hooking with DATE type */
149-
CREATE TABLE test.insert_date_test(val DATE NOT NULL);
150-
SELECT pathman.create_partitions_from_range('test.insert_date_test', 'val',
151-
date '20161001', date '20170101', interval '1 month');
152-
153-
INSERT INTO test.insert_date_test VALUES ('20161201'); /* just insert the date */
154-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
155-
156-
INSERT INTO test.insert_date_test VALUES ('20170311'); /* append new partitions */
157-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
158-
159-
INSERT INTO test.insert_date_test VALUES ('20160812'); /* prepend new partitions */
160-
SELECT count(*) FROM pathman.pathman_partition_list WHERE parent = 'test.insert_date_test'::REGCLASS;
161-
162-
SELECT min(val) FROM test.insert_date_test; /* check first date */
163-
SELECT max(val) FROM test.insert_date_test; /* check last date */
164-
165-
DROP TABLE test.insert_date_test CASCADE;
166-
167-
168148
SET pg_pathman.enable_runtimeappend = OFF;
169149
SET pg_pathman.enable_runtimemergeappend = OFF;
170150

@@ -474,17 +454,13 @@ SELECT pathman.append_range_partition('test."RangeRel"');
474454
SELECT pathman.prepend_range_partition('test."RangeRel"');
475455
SELECT pathman.merge_range_partitions('test."RangeRel_1"', 'test."RangeRel_' || currval('test."RangeRel_seq"') || '"');
476456
SELECT pathman.split_range_partition('test."RangeRel_1"', '2015-01-01'::DATE);
477-
SELECT pathman.drop_partitions('test."RangeRel"');
478-
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'dt', '2015-01-01'::DATE, '2015-01-05'::DATE, '1 day'::INTERVAL);
479457
DROP TABLE test."RangeRel" CASCADE;
480458
SELECT * FROM pathman.pathman_config;
481459
CREATE TABLE test."RangeRel" (
482460
id SERIAL PRIMARY KEY,
483461
dt TIMESTAMP NOT NULL,
484462
txt TEXT);
485463
SELECT pathman.create_range_partitions('test."RangeRel"', 'id', 1, 100, 3);
486-
SELECT pathman.drop_partitions('test."RangeRel"');
487-
SELECT pathman.create_partitions_from_range('test."RangeRel"', 'id', 1, 300, 100);
488464
DROP TABLE test."RangeRel" CASCADE;
489465

490466
DROP EXTENSION pg_pathman;
@@ -533,10 +509,6 @@ DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id
533509

534510
/* Create range partitions from whole range */
535511
SELECT drop_partitions('test.range_rel');
536-
SELECT create_partitions_from_range('test.range_rel', 'id', 1, 1000, 100);
537-
SELECT drop_partitions('test.range_rel', TRUE);
538-
SELECT create_partitions_from_range('test.range_rel', 'dt', '2015-01-01'::date, '2015-12-01'::date, '1 month'::interval);
539-
EXPLAIN (COSTS OFF) SELECT * FROM test.range_rel WHERE dt = '2015-12-15';
540512

541513
/* Test NOT operator */
542514
CREATE TABLE bool_test(a INT NOT NULL, b BOOLEAN);

0 commit comments

Comments
 (0)