Re: Query regarding RANGE Partitioning

Hi Nitin,

On Sat, May 8, 2021 at 5:20 PM Nitin Jadhav
<nitinjadhavpostgres(at)gmail(dot)com> wrote:
> Thanks Ashutosh and Jeevan for replying.
>
> > "When creating a range partition, the lower bound specified with
> > FROM is an inclusive bound, whereas the upper bound specified with
> > TO is an exclusive bound. That is, the values specified in the FROM
> > list are valid values of the corresponding partition key columns
> > for this partition, whereas those in the TO list are not. Note that
> > this statement must be understood according to the rules of row-wise
> > comparison (Section 9.24.5). For example, given PARTITION BY RANGE
> > (x,y), a partition bound FROM (1, 2) TO (3, 4) allows x=1 with any y>=2,
> > x=2 with any non-null y, and x=3 with any y<4."
>
> Thanks for the detailed explanation. I understood more about how the
> partition constraints are prepared based on the RANGE bound values and
> how the tuple routing takes place based on that.
>
> > overlapping range is (100, 0), (100, 200)
>
> > Similarly, for the case-1 you mention above:
> > create table r1 partition of r for values from (100,0) to (200,100);
> > create table r3 partition of r for values from (0,100) to (100,200);
> > here, (100, 0) or r1 would overlap with (100, 200) of r3.

Thanks Jeevan, that's right.

Another way to look at this: the partition key (100, 0) would be
insertable into r3, because the key satisfies its proposed exclusive
upper bound (< (100, 200)). The same key is also insertable into r1,
because it satisfies the latter's inclusive upper bound (>= (100, 0)).
That is, the key (100, 0) is insertable into both r1 and r3, so the
error that the proposed range of r3 would overlap r1's.

> postgres(at)68941=#\d+ r1
> Table "public.r1"
> Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> a | integer | | | | plain | | |
> b | integer | | | | plain | | |
> Partition of: r FOR VALUES FROM (100, 0) TO (200, 100)
> Partition constraint: ((a IS NOT NULL) AND (b IS NOT NULL) AND ((a > 100) OR ((a = 100) AND (b >= 0))) AND ((a < 200) OR ((a = 200) AND (b < 100))))
> Access method: heap
>
> I understand that partition 'r1' says, when column 'a' value is '100', column 'b'
> values should be greater than '0'. Because of this constraint, creation of
> partition 'r3' for values from (0,100) to (100,200) failed since the condition
> when value of column 'a' is 100, column 'b' should be less than '200' which
> overlaps with the constraints of 'r1'. So, based on the documentation, the
> behaviour is correct.
>
> So in the above scenarios, users cannot create a partition for column 'a' values
> from (0) to (100). If user tries insert any values for column 'a' between '0' to '100',
> either it should go to default partition if exists. Otherwise it should fail saying, no partition
> found. I feel there should be some way to create partitions in these scenarios.

Well, you simply need to come up with bound values for r3 that don't
overlap with existing partitions' ranges; the following will work for
example:

create table r3 partition of r for values from (0,100) to (100,0);

--
Amit Langote
EDB: http://www.enterprisedb.com