Re: Query regarding RANGE Partitioning - Mailing list pgsql-hackers

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] 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