Re: [BUGS] BUG #14725: Partition constraint issue on multiple columnsas the key of range partition

On 1 July 2017 at 07:52,  <tianbing@highgo.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs