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

From Ashutosh Bapat
Subject Re: Query regarding RANGE Partitioning
Date
Msg-id CAExHW5t4-OOM6mrv5xp2648vXJ+QqXt2t6Hc+kmkc_A1-U779A@mail.gmail.com
Whole thread Raw
In response to Query regarding RANGE Partitioning  (Nitin Jadhav <nitinjadhavpostgres@gmail.com>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Nitin Jadhav
Date:
Subject: Query regarding RANGE Partitioning
Next
From: ahsan hadi
Date:
Subject: Re: [BUG] Logical replication failure "ERROR: could not map filenode "base/13237/442428" to relation OID" with catalog modifying txns