Skip to content

Infinite bounds

Jump to bottom
Dmitry Ivanov edited this page Aug 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.

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

Clone this wiki locally