From: | Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> |
---|---|
To: | tianbing(at)highgo(dot)com |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14725: Partition constraint issue on multiple columns as the key of range partition |
Date: | 2017-07-02 10:02:54 |
Message-ID: | CAEZATCX0DVRvtiuTCJza=uh7yL07kFSiJBKywiO3ELR9ngaQ4g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On 1 July 2017 at 07:52, <tianbing(at)highgo(dot)com> wrote:
> When I use two columns as the key of range partition to create partition
> table.
> postgres=# create table test(n1 int, n2 int) partition by range(n1, n2);
> CREATE TABLE
> postgres=# create table test_1 partition of test for values from (0,0) to
> (10,100);
> CREATE TABLE
> postgres=# create table test_2 partition of test for values from (10,100) to
> (20,50);
> CREATE TABLE
>
> In partition test_2, partition constraint of the second column seem to be
> improper(from 100 to 50). The value behind keyword "TO" should be larger
> than value behind keyword "FROM".
>
This is not a bug, it's how multi-column range partitioning works. In
a multi-column range partition, the first column has precedence and is
checked first, so the range covered by partition test_2:
FROM (10,100) TO (20,50)
is equivalent to:
(n1 > 10 OR (n1 = 10 AND n2 >= 100))
AND
(n1 < 20 OR (n1 = 20 AND n2 < 50))
rather than being a hard constraint on the second column. So, the
following values are all in this partition:
(10,n2) for n2 >= 100
(11,n2) for all n2
(12,n2) for all n2
...
(19,n2) for all n2
(20,n2) for n2 < 50
Regards,
Dean
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-07-02 16:36:26 | Re: BUG #14726: Memory consumption of PreparedStatement |
Previous Message | dmigowski | 2017-07-02 09:09:56 | BUG #14726: Memory consumption of PreparedStatement |