Infinite bounds

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.

Prerequisites

/* case #1 - 100% manual partition creation */
create table test_1 (val int8 not null);
select add_to_pathman_config('test_1', 'val', NULL);   /* don't set default interval */


/* case #2 - 95% manual (auto naming) */
create table test_2 (val int8 not 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 */
create table test_3 (val int8 not null);
select create_range_partitions('test_3', 'val', 1, 1000, 10);

Partitions

As we can see, table test_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 partition test_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_list where 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 is null from pathman_partition_list
where parent = 'test_1'::regclass;
 ?column?
----------
 t
(1 row)

We can insert some data into it:

insert into test_1 values (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)

INSERT 0 4

Good, the data was successfully forwarded to the partition test_1_inf! Let's completely cover the range with a new partition test_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_1
select random() * 10000 - 5000 from generate_series(1, 6)
returning *, tableoid::regclass;
  val  |     tableoid
-------+------------------
  4525 | test_1_plus_inf
  4912 | test_1_plus_inf
 -1878 | test_1_minus_inf
  -718 | test_1_minus_inf
  2114 | test_1_plus_inf
  2915 | test_1_plus_inf
(6 rows)

INSERT 0 6

Note that we cannot append or prepend new partitions to table test_1:

select append_range_partition('test_1');
ERROR:  Cannot append partition because last partition's range is half open

select 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_list where 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_list where 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_list where 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_list where 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)

Limitations

• 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);