Re: Multi column range partition table

On 2017/06/22 20:48, amul sul wrote:
> Hi,
>
> While working on the another patch, I came across the case where
> I need an auto generated partition for a mutil-column range partitioned
> table having following range bound:
>
> PARTITION p1 FROM (UNBOUNDED, UNBOUNDED) TO (10, 10)
> PARTITION p2 FROM (10, 10) TO (10, UNBOUNDED)
> PARTITION p3 FROM (10, UNBOUNDED) TO (20, 10)
> PARTITION p4 FROM (20, 10) TO (20, UNBOUNDED)
> PARTITION p5 FROM (20, UNBOUNDED) TO (UNBOUNDED, UNBOUNDED)
>
> In this, a lower bound of the partition is an upper bound of the
> previous partition.
>
> While trying to create p3 partition with (10, UNBOUNDED) to (20, 10) bound,
> got an overlap partition error.
>
> Here is the SQL to reproduced this error:
>
> CREATE TABLE range_parted ( i1 int, i2 int ) PARTITION BY RANGE (i1, i2);
> CREATE TABLE p1 PARTITION OF range_parted FOR VALUES FROM (UNBOUNDED,
> UNBOUNDED) TO (10, 10);
> CREATE TABLE p2 PARTITION OF range_parted FOR VALUES FROM (10, 10) TO
> (10, UNBOUNDED);
> CREATE TABLE p3 PARTITION OF tab1 FOR VALUES FROM (10, UNBOUNDED) TO (20, 10);
>
> ERROR: partition "p3" would overlap partition "tab1_p_10_10"
>
> This happened because of UNBOUNDED handling, where it is a negative infinite
> if it is in FROM clause. Wondering can't we explicitly treat this as
> a positive infinite value, can we?

No, we cannot. What would be greater than (or equal to) +infinite?
Nothing. So, even if you will want p3 to accept (10, 9890148), it won't
because 9890148 is not >= +infinite. It will accept only the rows where
the first column is > 10 (second column is not checked in that case).

You will have to define p3 as follows:

CREATE TABLE p3 PARTITION OF tab1 FOR VALUES FROM (11, UNBOUNDED) TO (20, 10);

It's fine to use the previous partition's upper bound as the lower bound
of the current partition, if the former does contain an UNBOUNDED value,
because whereas a finite value divides the range into two parts (assigned
to the two partitions respectively), an UNBOUNDED value does not. The
latter represents an abstract end of the range (either on the positive
side or the negative).

Does that make sense?

Thanks,
Amit