Re: Declarative partitioning - another take

Hi,

On Tue, Sep 6, 2016 at 8:15 PM, Rajkumar Raghuwanshi
<rajkumar.raghuwanshi@enterprisedb.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