Re: Declarative partitioning - another take

I have Continued with testing declarative partitioning with the latest
patch. Got some more observation, given below

-- Observation 1 : Getting overlap error with START with EXCLUSIVE in range
partition.

create table test_range_bound ( a int) partition by range(a);
--creating a partition to contain records {1,2,3,4}, by default 1 is
inclusive and 5 is exclusive
create table test_range_bound_p1 partition of test_range_bound for values
start (1) end (5);
--now trying to create a partition by explicitly mentioning start is
exclusive to contain records {5,6,7}, here trying to create with START with
4 as exclusive so range should be 5 to 8, but getting partition overlap
error.
create table test_range_bound_p2 partition of test_range_bound for values
start (4) EXCLUSIVE end (8);
ERROR: partition "test_range_bound_p2" would overlap partition
"test_range_bound_p1"

-- Observation 2 : able to create sub-partition out of the range set for
main table, causing not able to insert data satisfying any of the partition.

create table test_subpart (c1 int) partition by range (c1);
create table test_subpart_p1 partition of test_subpart for values start (1)
end (100) inclusive partition by range (c1);
create table test_subpart_p1_sub1 partition of test_subpart_p1 for values
start (101) end (200);

\d+ test_subpart
Table "public.test_subpart"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | integer | | plain | |
Partition Key: RANGE (c1)
Partitions: test_subpart_p1 FOR VALUES START (1) END (100) INCLUSIVE

\d+ test_subpart_p1
Table "public.test_subpart_p1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
c1 | integer | | plain | |
Partition Of: test_subpart FOR VALUES START (1) END (100) INCLUSIVE
Partition Key: RANGE (c1)
Partitions: test_subpart_p1_sub1 FOR VALUES START (101) END (200)

insert into test_subpart values (50);
ERROR: no partition of relation "test_subpart_p1" found for row
DETAIL: Failing row contains (50).
insert into test_subpart values (150);
ERROR: no partition of relation "test_subpart" found for row
DETAIL: Failing row contains (150).

-- Observation 3 : Getting cache lookup failed, when selecting list
partition table containing array.

CREATE TABLE test_array ( i int,j int[],k text[]) PARTITION BY LIST (j);
CREATE TABLE test_array_p1 PARTITION OF test_array FOR VALUES IN ('{1}');
CREATE TABLE test_array_p2 PARTITION OF test_array FOR VALUES IN ('{2,2}');

INSERT INTO test_array (i,j[1],k[1]) VALUES (1,1,1);
INSERT INTO test_array (i,j[1],j[2],k[1]) VALUES (2,2,2,2);

postgres=# SELECT tableoid::regclass,* FROM test_array_p1;
tableoid | i | j | k
---------------+---+-----+-----
test_array_p1 | 1 | {1} | {1}
(1 row)

postgres=# SELECT tableoid::regclass,* FROM test_array_p2;
tableoid | i | j | k
---------------+---+-------+-----
test_array_p2 | 2 | {2,2} | {2}
(1 row)

postgres=# SELECT tableoid::regclass,* FROM test_array;
ERROR: cache lookup failed for type 0

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation

On Fri, Sep 9, 2016 at 2:25 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
wrote:

> On 2016/09/06 22:04, Amit Langote wrote:
> > Will fix.
>
> Here is an updated set of patches.
>
> In addition to fixing a couple of bugs reported by Ashutosh and Rajkumar,
> there are a few of major changes:
>
> * change the way individual partition bounds are represented internally
> and the way a collection of partition bounds associated with a
> partitioned table is exposed to other modules. Especially list
> partition bounds which are manipulated more efficiently as discussed
> at [1].
>
> * \d partitioned_table now shows partition count and \d+ lists partition
> names and their bounds as follows:
>
> \d t6
> Table "public.t6"
> Column | Type | Modifiers
> .-------+-------------------+-----------
> a | integer |
> b | character varying |
> Partition Key: LIST (a)
> Number of partitions: 3 (Use \d+ to list them.)
>
> \d+ t6
> Table "public.t6"
> Column | Type | Modifiers | Storage | Stats target |
> Description
> .-------+-------------------+-----------+----------+--------
> ------+-------------
> a | integer | | plain | |
> b | character varying | | extended | |
> Partition Key: LIST (a)
> Partitions: t6_p1 FOR VALUES IN (1, 2, NULL),
> t6_p2 FOR VALUES IN (4, 5),
> t6_p3 FOR VALUES IN (3, 6)
>
> \d+ p
> Table "public.p"
> Column | Type | Modifiers | Storage | Stats target | Description
> .-------+--------------+-----------+----------+-------------
> -+-------------
> a | integer | | plain | |
> b | character(1) | | extended | |
> Partition Key: RANGE (a)
> Partitions: p1 FOR VALUES START (1) END (10),
> p2 FOR VALUES START (10) END (20),
> p3 FOR VALUES START (20) END (30),
> p4 FOR VALUES START (30) EXCLUSIVE END (40) INCLUSIVE,
> p5 FOR VALUES START (40) EXCLUSIVE END (50),
> p6 FOR VALUES START (50) END UNBOUNDED
>
> * Some more regression tests
>
> Thanks,
> Amit
>
> [1]
> https://www.postgresql.org/message-id/CA%2BTgmoZCr0-
> t93KgJA3T1uy9yWxfYaSYL3X35ObyHg%2BZUfERqQ%40mail.gmail.com
>