Re: Declarative partitioning

On Tue, Apr 19, 2016 at 4:57 PM, Ildar Musin <i(dot)musin(at)postgrespro(dot)ru> wrote:

> On 15.04.2016 07:35, Amit Langote wrote:
>
>> Thanks a lot for the comments. The patch set changed quite a bit since
>> the last version. Once the CF entry was marked returned with feedback on
>> March 22, I held off sending the new version at all. Perhaps, it would have
>> been OK. Anyway here it is, if you are interested. I will create an entry
>> in CF 2016-09 for the same. Also, see below replies to you individual
>> comments.
>>
>
> Thanks for your new patch! I've tried it and discovered some strange
> behavior for partitioning by composite key. Here is an example of my setup:
>
> create table test(a int, b int) partition by range (a, b);
> create table test_1 partition of test for values start (0, 0) end (100,
> 100);
> create table test_2 partition of test for values start (100, 100) end
> (200, 200);
> create table test_3 partition of test for values start (200, 200) end
> (300, 300);
>
> It's alright so far. But if we try to insert record in which attribute 'a'
> belongs to one partition and attribute 'b' belongs to another then record
> will be inserted in the first one:
>
> insert into test(a, b) values (150, 50);
>
> select tableoid::regclass, * from test;
> tableoid | a | b
> ----------+-----+----
> test_2 | 150 | 50
> (1 row)

That's how composite keys work. First subkey is checked. If it's equal then
second subkey is checked and so on.

# SELECT (100, 100) < (150, 50), (150, 50) < (200, 200);
?column? | ?column?
----------+----------
t | t
(1 row)

Another question is that it might be NOT what users expect from that. From
the syntax side it very looks like defining something boxes regions for two
keys which could be replacement for subpartitioning. But it isn't so.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company