Horton

Re: Declarative partitioning - another take

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2016-09-06T13:04:09Z
Lists: pgsql-hackers 
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

Commits

  1. Fix typo.

  2. Document trigger-firing behavior for inheritance/partitioning.

  3. Fire per-statement triggers on partitioned tables.

  4. Set ecxt_scantuple correctly for tuple routing.

  5. Fix interaction of partitioned tables with BulkInsertState.

  6. Avoid core dump for empty prepared statement in an aborted transaction.

  7. Fix some problems in check_new_partition_bound().

  8. Remove unnecessary arguments from partitioning functions.

  9. Fix reporting of constraint violations for table partitioning.

  10. Fix tuple routing in cases where tuple descriptors don't match.

  11. Invalid parent's relcache after CREATE TABLE .. PARTITION OF.

  12. Doc: improve documentation about inheritance.