Обсуждение: [BUGS] BUG #14725: Partition constraint issue on multiple columns as the keyof range partition

The following bug has been logged on the website:

Bug reference:      14725
Logged by:          tian bing
Email address:      tianbing@highgo.com
PostgreSQL version: 10beta1
Operating system:   Linux
Description:

Hi,
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".

Looking forward to your reply.


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

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