Query regarding RANGE Partitioning

From: Nitin Jadhav <nitinjadhavpostgres(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Query regarding RANGE Partitioning
Date: 2021-05-07 10:50:39
Message-ID: CAMm1aWZ0G1Q1ZW2ePKJ6Ew6BOvojhRPC1hRw=2k8AFoB00RdqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)68941=#create table r(a int, b int) partition by range(a,b);
CREATE TABLE
postgres(at)68941=#create table r1 partition of r for values from (100,0) to
(200,100);
CREATE TABLE
postgres(at)68941=#create table r2 partition of r for values from (400,200) to
(500,300);
CREATE TABLE
postgres(at)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(at)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(at)68941=#insert into r values(300, 50);
INSERT 0 1
postgres(at)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.

Thanks and Regards,
Nitin Jadhav

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Bapat 2021-05-07 11:14:01 Re: Query regarding RANGE Partitioning
Previous Message Pavel Stehule 2021-05-07 10:41:27 Re: batch fdw insert bug (Postgres 14)