Re: Declarative partitioning - another take

Hi,

On Tue, Sep 6, 2016 at 8:15 PM, Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> wrote:
> Hi,
>
> I have applied updated patches given by you, and observe below.
>
> here in the given example, t6_p3 partition is not allowed to have null, but
> I am able to insert it, causing two nulls in the table.
>
> --create a partition table
> create table t6 (a int, b varchar) partition by list(a);
> create table t6_p1 partition of t6 for values in (1,2,null);
> create table t6_p2 partition of t6 for values in (4,5);
> create table t6_p3 partition of t6 for values in (3,6);
>
> --insert some values
> insert into t6 select i,i::varchar from generate_series(1,6) i;
> insert into t6 values (null,'A');
>
> --try inserting null to t6_p3 partition table
> insert into t6_p3 values (null,'A');
>
> select tableoid::regclass,* from t6;
> tableoid | a | b
> ----------+---+---
> t6_p1 | 1 | 1
> t6_p1 | 2 | 2
> t6_p1 | | A
> t6_p2 | 4 | 4
> t6_p2 | 5 | 5
> t6_p3 | 3 | 3
> t6_p3 | 6 | 6
> t6_p3 | | A
> (8 rows)

Thanks for testing!

That looks like a bug. The same won't occur if you had inserted
through the parent (it would be correctly routed to the partition that
has been defined to accept nulls (that is, t6_p1 of your example). The
bug seems to have to do with the handling of nulls in generating
implicit constraints from the list of values of a given list
partition. The direct insert on t6_p1 should have failed because
partition key has a value (null) violating the partition boundary
condition. Will fix.

Thanks,
Amit