- Notifications
You must be signed in to change notification settings - Fork67
Infinite bounds
Dmitry Ivanov edited this pageAug 24, 2017 ·8 revisions
Release 1.3 introduced support for semi-infinite partition bounds, e.g.(-inf, 10) or(-100, +inf). NULL value of a corresponding type is used to specify infinite range bounds.
/* case #1 - 100% manual partition creation*/createtabletest_1 (val int8not null);select add_to_pathman_config('test_1','val',NULL);/* don't set default interval*//* case #2 - 95% manual (auto naming)*/createtabletest_2 (val int8not null);select create_naming_sequence('test_2');select add_to_pathman_config('test_2','val','1000');/* use default interval 1000*/select add_range_partition('test_2',1,100);/* initial partition [1, 100)*//* case #3 - create some partitions*/createtabletest_3 (val int8not null);select create_range_partitions('test_3','val',1,1000,10);
As we can see, tabletest_1 has no partitions yet:
table pathman_partition_list; parent | partition | parttype | expr | range_min | range_max--------+-----------+----------+------+-----------+----------- test_2 | test_2_1 |2 | val |1 |100 test_3 | test_3_1 |2 | val |1 |1001 test_3 | test_3_2 |2 | val |1001 |2001 test_3 | test_3_3 |2 | val |2001 |3001 test_3 | test_3_4 |2 | val |3001 |4001 test_3 | test_3_5 |2 | val |4001 |5001 test_3 | test_3_6 |2 | val |5001 |6001 test_3 | test_3_7 |2 | val |6001 |7001 test_3 | test_3_8 |2 | val |7001 |8001 test_3 | test_3_9 |2 | val |8001 |9001 test_3 | test_3_10 |2 | val |9001 |10001(11 rows)
Let's create a semi-infinite partitiontest_1_minus_inf:
select add_range_partition('test_1',NULL,1,'test_1_minus_inf'); add_range_partition--------------------- test_1_minus_inf(1 row)
As we can see, its left bound is NULL:
select*from pathman_partition_listwhere parent='test_1'::regclass; parent | partition | parttype | expr | range_min | range_max--------+------------------+----------+------+-----------+----------- test_1 | test_1_minus_inf |2 | val | |1(1 row)select range_min isnullfrom pathman_partition_listwhere parent='test_1'::regclass; ?column?---------- t(1 row)
We can insert some data into it:
insert into test_1values (0), (-10), (-100), (-1000) returning*, tableoid::regclass; val | tableoid-------+------------------0 | test_1_minus_inf-10 | test_1_minus_inf-100 | test_1_minus_inf-1000 | test_1_minus_inf(4 rows)INSERT04
Good, the data was successfully forwarded to the partitiontest_1_inf! Let's completely cover the range with a new partitiontest_1_plus_inf:
select add_range_partition('test_1',1,NULL,'test_1_plus_inf'); add_range_partition--------------------- test_1_plus_inf(1 row)
Add a few more rows:
insert into test_1select random()*10000-5000from generate_series(1,6)returning*, tableoid::regclass; val | tableoid-------+------------------4525 | test_1_plus_inf4912 | test_1_plus_inf-1878 | test_1_minus_inf-718 | test_1_minus_inf2114 | test_1_plus_inf2915 | test_1_plus_inf(6 rows)INSERT06
Note that we cannot append or prepend new partitions to tabletest_1:
select append_range_partition('test_1');ERROR: Cannot append partition because last partition's range is half openselect prepend_range_partition('test_1');ERROR: Cannot prepend partition because first partition's range is half open
We can also add semi-infinite partitions to an existing set of finite children that we've created beforehand:
/* add new partition [100, +inf)*/select add_range_partition('test_2',100,NULL); add_range_partition--------------------- test_2_2/* prepend will still work!*/select prepend_range_partition('test_2'); prepend_range_partition------------------------- test_2_3(1 row)/* check the partitions of table 'test_2'*/select*from pathman_partition_listwhere parent='test_2'::regclass; parent | partition | parttype | expr | range_min | range_max--------+-----------+----------+------+-----------+----------- test_2 | test_2_3 |2 | val |-999 |1 test_2 | test_2_1 |2 | val |1 |100 test_2 | test_2_2 |2 | val |100 |(3 rows)
All semi-infinite partitions are 1st class citizens, which means that operations like merge, split, drop will work as expected:
select merge_range_partitions('test_2_1','test_2_2'); merge_range_partitions------------------------(1 row)select*from pathman_partition_listwhere parent='test_2'::regclass; parent | partition | parttype | expr | range_min | range_max--------+-----------+----------+------+-----------+----------- test_2 | test_2_3 |2 | val |-999 |1 test_2 | test_2_1 |2 | val |1 |(2 rows)select split_range_partition('test_2_1',2000); split_range_partition----------------------- {1,NULL}(1 row)select*from pathman_partition_listwhere parent='test_2'::regclass; parent | partition | parttype | expr | range_min | range_max--------+-----------+----------+------+-----------+----------- test_2 | test_2_3 |2 | val |-999 |1 test_2 | test_2_1 |2 | val |1 |2000 test_2 | test_2_4 |2 | val |2000 |(3 rows)select drop_range_partition('test_2_4'); drop_range_partition---------------------- test_2_4(1 row)select*from pathman_partition_listwhere parent='test_2'::regclass; parent | partition | parttype | expr | range_min | range_max--------+-----------+----------+------+-----------+----------- test_2 | test_2_3 |2 | val |-999 |1 test_2 | test_2_1 |2 | val |1 |2000(2 rows)
- Infinite bounds can only be used with RANGE partitioning;
- Only one bound (left or right) of a partition may be infinite (i.e.
(NULL, NULL)is prohibited);