Thread: [BUGS] BUG #14725: Partition constraint issue on multiple columns as the keyof range partition
[BUGS] BUG #14725: Partition constraint issue on multiple columns as the keyof range partition
From
tianbing@highgo.com
Date:
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
Re: [BUGS] BUG #14725: Partition constraint issue on multiple columnsas the key of range partition
From
Dean Rasheed
Date:
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