Re: Declarative partitioning

Hi Amit,

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)

I think there should be an error because value for 'b' violates range
constraint for test_2. Now if we query data from 'test' and add filter b
< 100, then planner will exclude partitions 'test_2' (which actually
contains inserted row) and 'test_3' and return nothing:

select * from test where b < 100;
a | b
---+---
(0 rows)

explain (costs off) select * from test where b < 100;
QUERY PLAN
---------------------------
Append
-> Seq Scan on test
Filter: (b < 100)
-> Seq Scan on test_1
Filter: (b < 100)
(5 rows)

--
Ildar Musin
i(dot)musin(at)postgrespro(dot)ru