Query regarding RANGE Partitioning

On Fri, May 7, 2021 at 4:21 PM Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:

Hi,

I am not convinced with the following behaviour of RANGE Partitioning.
Kindly let me know if this is expected behaviour or it should be changed.

Case-1:
postgres@68941=#create table r(a int, b int) partition by range(a,b);
CREATE TABLE
postgres@68941=#create table r1 partition of r for values from (100,0) to (200,100);
CREATE TABLE
postgres@68941=#create table r2 partition of r for values from (400,200) to (500,300);
CREATE TABLE
postgres@68941=#create table r3 partition of r for values from (0,100) to (100,200);
ERROR: partition "r3" would overlap partition "r1"
LINE 1: ...able r3 partition of r for values from (0,100) to (100,200);

As we can see here, I am trying to create a partition table with ranges from (0,100) to (100,200)
which is actually not overlapped with any of the existing partitions. But I am getting error saying,
it overlaps with partition 'r1'.

overlapping range is (100, 0), (100, 200)

Case-2:
postgres@68941=#\d+ r
Partitioned table "public.r"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | integer | | | | plain | | |
Partition key: RANGE (a, b)
Partitions: r1 FOR VALUES FROM (100, 0) TO (200, 100),
r2 FOR VALUES FROM (400, 200) TO (500, 300),
r3 FOR VALUES FROM (200, 100) TO (300, 200)

postgres@68941=#insert into r values(300, 50);
INSERT 0 1
postgres@68941=#select * from r3;
a | b
-----+-----
300 | 50
(2 rows)

As per my understanding, in the range partitioned table, lower bound is included and upper bound is excluded.
and in case of multi-column partition keys, the row comparison operator is used for tuple routing which means
the columns are compared left to right. If the partition key value is equal to the upper bound of that column then
the next column will be considered.

So, In case of insertion of row (300, 50). Based on the understanding, partition 'r3' should have rejected it.

r3 contains (300, 0) to (300, 200) which contains (300, 50). First key
300 is equal to upper bound 300, so it compares 50, which is less than
the upper bound of the second column. Am I missing something?

--
Best Wishes,
Ashutosh Bapat

Hi Nitin,

On Fri, May 7, 2021 at 4:21 PM Nitin Jadhav <nitinjadhavpostgres@gmail.com>
wrote:

Hi,

I am not convinced with the following behaviour of RANGE Partitioning.
Kindly let me know if this is expected behaviour or it should be changed.

*Case-1*:
postgres@68941=#create table r(a int, b int) partition by range(a,b);
CREATE TABLE
postgres@68941=#create table r1 partition of r for values from (100,0) to
(200,100);
CREATE TABLE
postgres@68941=#create table r2 partition of r for values from (400,200)
to (500,300);
CREATE TABLE
postgres@68941=#create table r3 partition of r for values from (0,100) to
(100,200);
ERROR: partition "r3" would overlap partition "r1"
LINE 1: ...able r3 partition of r for values from (0,100) to (100,200);

As we can see here, I am trying to create a partition table with ranges
from (0,100) to (100,200)
which is actually not overlapped with any of the existing partitions. But
I am getting error saying,
it overlaps with partition 'r1'.

*Case-2:*

postgres@68941=#\d+ r
Partitioned table "public.r"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description

--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain |
| |
b | integer | | | | plain |
| |
Partition key: RANGE (a, b)
Partitions: r1 FOR VALUES FROM (100, 0) TO (200, 100),
r2 FOR VALUES FROM (400, 200) TO (500, 300),
r3 FOR VALUES FROM (200, 100) TO (300, 200)

postgres@68941=#insert into r values(300, 50);
INSERT 0 1
postgres@68941=#select * from r3;
a | b
-----+-----
300 | 50
(2 rows)

As per my understanding, in the range partitioned table, lower bound is
included and upper bound is excluded.
and in case of multi-column partition keys, the row comparison operator is
used for tuple routing which means
the columns are compared left to right. If the partition key value is
equal to the upper bound of that column then
the next column will be considered.

So, In case of insertion of row (300, 50). Based on the understanding,
partition 'r3' should have rejected it.

Kindly confirm whether the above is expected or not. If expected, kindly
explain.

If you describe the partition r3, you can see the way partition
constraints are formed:

postgres=# \d+ r3
Table "public.r3"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain |
| |
b | integer | | | | plain |
| |
Partition of: r FOR VALUES FROM (200, 100) TO (300, 200)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 200)
OR ((a = 200) AND (b >= 100))) AND ((a < 300) OR ((a = 300) AND (b < 200))))
Access method: heap

The above constraint very well fits the tuple you are trying to insert
that is: (a, b) = (300, 50) (where (a = 300) AND (b < 200))

Also, the table partition syntax documentation[1]https://www.postgresql.org/docs/current/sql-createtable.html
<https://www.postgresql.org/docs/current/sql-createtable.html&gt;clarifies
this (look
for "partition_bound_expr"):

"When creating a range partition, the lower bound specified with
FROM is an inclusive bound, whereas the upper bound specified with
TO is an exclusive bound. That is, the values specified in the FROM
list are valid values of the corresponding partition key columns
for this partition, whereas those in the TO list are not. Note that
this statement must be understood according to the rules of row-wise
comparison (Section 9.24.5). For example, given PARTITION BY RANGE
(x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2,
x=2 with any non-null y, and x=3 with any y<4."

So, in your case the partition (a, b) for bound (200, 100) TO (300, 200)
would transform to allowing:
a = 200 with any b >= 100 OR
a > 200 and a < 300 with any non-null b
OR a=300 with any b<200

Your particular tuple (300, 50) fits in the last part of the OR i.e
(a=300 with any b<200).

So, IMHO, the range partitioning is behaving as expected.

Similarly, for the case-1 you mention above:
create table r1 partition of r for values from (100,0) to (200,100);
create table r3 partition of r for values from (0,100) to (100,200);
here, (100, 0) or r1 would overlap with (100, 200) of r3.

[1]: https://www.postgresql.org/docs/current/sql-createtable.html

Regards,
Jeevan Ladhe

Thanks Ashutosh and Jeevan for replying.

"When creating a range partition, the lower bound specified with
FROM is an inclusive bound, whereas the upper bound specified with
TO is an exclusive bound. That is, the values specified in the FROM
list are valid values of the corresponding partition key columns
for this partition, whereas those in the TO list are not. Note that
this statement must be understood according to the rules of row-wise
comparison (Section 9.24.5). For example, given PARTITION BY RANGE
(x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2,
x=2 with any non-null y, and x=3 with any y<4."

Thanks for the detailed explanation. I understood more about how the
partition constraints are prepared based on the RANGE bound values and
how the tuple routing takes place based on that.

overlapping range is (100, 0), (100, 200)

Similarly, for the case-1 you mention above:
create table r1 partition of r for values from (100,0) to (200,100);
create table r3 partition of r for values from (0,100) to (100,200);
here, (100, 0) or r1 would overlap with (100, 200) of r3.

postgres@68941=#\d+ r1
Table "public.r1"
Column | Type | Collation | Nullable | Default | Storage | Compression
| Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain |
| |
b | integer | | | | plain |
| |
Partition of: r FOR VALUES FROM (100, 0) TO (200, 100)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 100)
OR ((a = 100) AND (b >= 0))) AND ((a < 200) OR ((a = 200) AND (b < 100))))
Access method: heap

I understand that partition 'r1' says, when column 'a' value is '100',
column 'b'
values should be greater than '0'. Because of this constraint, creation of
partition 'r3' for values from (0,100) to (100,200) failed since the
condition
when value of column 'a' is 100, column 'b' should be less than '200' which
overlaps with the constraints of 'r1'. So, based on the documentation, the
behaviour is correct.

So in the above scenarios, users cannot create a partition for column 'a'
values
from (0) to (100). If user tries insert any values for column 'a' between
'0' to '100',
either it should go to default partition if exists. Otherwise it should
fail saying, no partition
found. I feel there should be some way to create partitions in these
scenarios.

Please correct if I am wrong and please share your thoughts on this.

Thanks & Regards,
Nitin Jadhav

On Fri, May 7, 2021 at 6:23 PM Jeevan Ladhe <jeevan.ladhe@enterprisedb.com>
wrote:

Hi Nitin,

On Sat, May 8, 2021 at 5:20 PM Nitin Jadhav
<nitinjadhavpostgres@gmail.com> wrote:

Thanks Ashutosh and Jeevan for replying.

"When creating a range partition, the lower bound specified with
FROM is an inclusive bound, whereas the upper bound specified with
TO is an exclusive bound. That is, the values specified in the FROM
list are valid values of the corresponding partition key columns
for this partition, whereas those in the TO list are not. Note that
this statement must be understood according to the rules of row-wise
comparison (Section 9.24.5). For example, given PARTITION BY RANGE
(x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2,
x=2 with any non-null y, and x=3 with any y<4."

Thanks for the detailed explanation. I understood more about how the
partition constraints are prepared based on the RANGE bound values and
how the tuple routing takes place based on that.

overlapping range is (100, 0), (100, 200)

Similarly, for the case-1 you mention above:
create table r1 partition of r for values from (100,0) to (200,100);
create table r3 partition of r for values from (0,100) to (100,200);
here, (100, 0) or r1 would overlap with (100, 200) of r3.

Thanks Jeevan, that's right.

Another way to look at this: the partition key (100, 0) would be
insertable into r3, because the key satisfies its proposed exclusive
upper bound (< (100, 200)). The same key is also insertable into r1,
because it satisfies the latter's inclusive upper bound (>= (100, 0)).
That is, the key (100, 0) is insertable into both r1 and r3, so the
error that the proposed range of r3 would overlap r1's.

postgres@68941=#\d+ r1
Table "public.r1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | integer | | | | plain | | |
Partition of: r FOR VALUES FROM (100, 0) TO (200, 100)
Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 100) OR ((a = 100) AND (b >= 0))) AND ((a < 200) OR ((a = 200) AND (b < 100))))
Access method: heap

I understand that partition 'r1' says, when column 'a' value is '100', column 'b'
values should be greater than '0'. Because of this constraint, creation of
partition 'r3' for values from (0,100) to (100,200) failed since the condition
when value of column 'a' is 100, column 'b' should be less than '200' which
overlaps with the constraints of 'r1'. So, based on the documentation, the
behaviour is correct.

So in the above scenarios, users cannot create a partition for column 'a' values
from (0) to (100). If user tries insert any values for column 'a' between '0' to '100',
either it should go to default partition if exists. Otherwise it should fail saying, no partition
found. I feel there should be some way to create partitions in these scenarios.

Well, you simply need to come up with bound values for r3 that don't
overlap with existing partitions' ranges; the following will work for
example:

create table r3 partition of r for values from (0,100) to (100,0);

--
Amit Langote
EDB: http://www.enterprisedb.com

Hi Amit,

Thanks Jeevan, that's right.

Another way to look at this: the partition key (100, 0) would be
insertable into r3, because the key satisfies its proposed exclusive
upper bound (< (100, 200)). The same key is also insertable into r1,
because it satisfies the latter's inclusive upper bound (>= (100, 0)).
That is, the key (100, 0) is insertable into both r1 and r3, so the
error that the proposed range of r3 would overlap r1's.

Thanks for the explanation.

Well, you simply need to come up with bound values for r3 that don't
overlap with existing partitions' ranges; the following will work for
example:

create table r3 partition of r for values from (0,100) to (100,0);

Thanks for the clarification.

Thanks & Regards,
Nitin Jadhav

On Wed, May 12, 2021 at 5:41 PM Amit Langote <amitlangote09@gmail.com>
wrote: