Re: Query regarding RANGE Partitioning

Hi Amit,

> 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.

Thanks for the explanation.

> 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);

Thanks for the clarification.

Thanks & Regards,
Nitin Jadhav

On Wed, May 12, 2021 at 5:41 PM Amit Langote <amitlangote09(at)gmail(dot)com>
wrote:

> 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
>