Re: Declarative partitioning

Hi Amit,
I am trying multi-column/expression partitions.

create table t1_multi_col (a int, b int) partition by range (a, b);
create table t1_mc_p1 partition of t1_multi_col for values start (1, 200)
end (100, 300);
create table t1_mc_p2 partition of t1_multi_col for values start (200, 1)
end (300, 100);
insert into t1_multi_col values (1, 250);
insert into t1_multi_col values (250, 1);
insert into t1_multi_col values (100, 100);
select tableoid::regclass, * from t1_multi_col;
tableoid | a | b
----------+-----+-----
t1_mc_p1 | 1 | 250
t1_mc_p1 | 100 | 100
t1_mc_p2 | 250 | 1
The row (100, 100) landed in t1_mc_p1 which has partition bounds as (1,
200) and (100, 300) which should not accept a row with b = 100. It looks
like the binary search got confused with the reversed order of ranges
(should that be allowed?)

Symantec of multiple columns for ranges (may be list as well) looks
confusing. The current scheme doesn't allow overlapping range for one of
the partitioning keys even if the combined range is non-overlapping.
create table t1_multi_col (a int, b int) partition by range (a, b);
create table t1_mc_p1 partition of t1_multi_col for values start (1, 100)
end (100, 200);
create table t1_mc_p2 partition of t1_multi_col for values start (1, 200)
end (100, 300);
ERROR: new partition's range overlaps with that of partition "t1_mc_p1" of
"t1_multi_col"
HINT: Please specify a range that does not overlap with any existing
partition's range.
create table t1_mc_p2 partition of t1_multi_col for values start (1, 300)
end (100, 400);
ERROR: new partition's range overlaps with that of partition "t1_mc_p1" of
"t1_multi_col"
HINT: Please specify a range that does not overlap with any existing
partition's range.

That should be better realised using subpartitioning on b. The question is,
if one column's value is enough to identify partition (since they can not
contain overlapping values for that column), why do we need mutliple
columns/expressions as partition keys? IIUC, all the other column does is
to disallow certain range of values for that column, which can better be
done by a CHECK constraint. It looks like Oracle looks at combined range
and not just one column.

On Thu, Apr 21, 2016 at 7:35 AM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

>
> Hi Ildar,
>
> On 2016/04/21 1:06, Amit Langote wrote:
> > On Wed, Apr 20, 2016 at 11:46 PM, Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>
> wrote:
> >> Crash occurs in get_check_expr_from_partbound(). It seems that function
> is
> >> not yet expecting an expression key and designed to handle only simple
> >> attributes keys. Backtrace:
> >
> > You're right, silly mistake. :-(
> >
> > Will fix
>
> Attached updated version fixes this. I'll take time to send the next
> version but I'd very much appreciate it if you keep reporting anything
> that doesn't look/work right like you did so far.
>
> Thanks,
> Amit
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company