Re: Declarative partitioning

Hi Idlar, Alexander,

On Tue, Apr 19, 2016 at 11:26 PM, Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Tue, Apr 19, 2016 at 4:57 PM, Ildar Musin <i(dot)musin(at)postgrespro(dot)ru> wrote:
>>
>> 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)

Yes.

> 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.

Need to check why query with qual b < 100 behaves the way it does.
Something's going wrong there with the constraints (partition
predicates) that are being generated internally (as mentioned before,
still driven by constraint exclusion using the constraints generated
on-the-fly).

As for the composite range partition bounds in Ildar's example, it's
as if the second value in the key never determines the fate of a row
going into some partition, therefore no constraints should have been
generated for column b of the key. I'm afraid that's not the case as
per the latest patch. Will fix.

Thanks a lot for trying it out and the report.

Thanks,
Amit