Re: [POC] hash partitioning

On 2017/06/06 17:50, Dilip Kumar wrote:
> On Tue, Jun 6, 2017 at 1:03 PM, amul sul <sulamul(at)gmail(dot)com> wrote:
>> May I ask you, how you sure about 8 is an unfit value for t1 relation?
>> And what if the value other than 8, for e.g. 7?
>
> Well, First I created t1 as a leaf relation like below, and I tested
> insert into t1 with value 8 and it was violating the partition
> constraint of t1, however, 7 was fine.
>
> create table t (a int) partition by hash(a);
> create table t1 partition of t for values with (modulus 2, remainder 1);
>
> Later I dropped this t1 and created 2 level partition with the leaf as a range.
>
> drop table t1;
> create table t1 partition of t for values with (modulus 2, remainder
> 1) partition by range(a);
> create table t1_1 partition of t1 for values from (8) to (10);
>
> So now, I am sure that t1_1 can accept the value 8 and its parent t1 can't.
>
> So I think this can only happen in the case of partitioned by hash
> that a value is legal for the child but illegal for the parent? Isn't
> it a good idea that if a user is inserting in the top level relation
> he should know for which partition exactly the constraint got
> violated?

It's how the original partitioning code around ExecInsert/CopyFrom works,
not something that only affects hash partitioning. So, I think that
Amul's patch is fine and if we want to change something here, it should be
done by an independent patch. See the explanation below:

If we insert into a partition directly, we must check its partition
constraint. If the partition happens to be itself a partitioned table,
the constraint will be checked *after* tuple-routing and ExecConstraints()
is passed the leaf partition's ResultRelInfo, so if an error occurs there
we will use the leaf partition's name in the message. Since we combine
the leaf partition's own constraint with all of the ancestors' into a
single expression that is passed to ExecCheck(), it is hard to say exactly
which ancestor's constraint is violated. However, if the partition
constraint of some intervening ancestor had been violated, we wouldn't be
in ExecConstraints() at all; tuple-routing itself would have failed. So
it seems that we need worry (if at all) only about partition constraints
of the table mentioned in the insert statement.

Consider an example using the partition hierarchy:

root (a int, b char, c int) partition by range (a)

-> level1 from (1) to (10) partition by list (b)

-> level2 in ('a') parition by range (c)

-> leaf from (1) to (10)

Inserting (1, 'b', 1) into level1 will fail, because tuple can't be routed
at level1 (no partition defined for b = 'b').

Inserting (1, 'a', 10) into level1 will fail, because tuple can't be
routed at level2 (no partition defined for c >= 10).

Inserting (10, 'a', 1) into level1 will fail, because, although it was
able to get through level1 and level2 into leaf, a = 10 falls out of
level1's defined range. We don't check that 1 <= a < 10 before starting
the tuple-routing.

I wonder if we should... Since we don't allow BR triggers on partitioned
tables, there should not be any harm in doing it just before calling
ExecFindPartition(). Perhaps, topic for a new thread.

Thanks,
Amit