Re: Declarative partitioning

>
> >
> > Consider lists ('e', 'i', 'f'), ('h', 'd','m') and ('l', 'b', 'a') for a
> > list partitioned tables. I am suggesting that we arrange them as
> > ('a','b','l'), ('d', 'h', 'm') and ('e', 'f', 'i'). If the given row
> (either
> > for comparison or for inserting) has value 'c', we will search for it in
> > ('a','b','l') but will be eliminate other two partitions since the
> second's
> > partition's lowest value is higher than 'c' and lowest values of rest of
> the
> > partitions are higher than that of the second partition. Without this
> order
> > among the partitions, we will have to compare lowest values of all the
> > partitions.
>
> I would think that for that case what we'd want to do is create two
> lists. One looks like this:
>
> [ 'a', 'b', 'd', 'e', f', 'h', 'i', 'l', 'm' ]
>
> The other looks like this:
>
> [3, 3, 2, 1, 1, 2, 1, 1, 3, 2]
>

small correction; there's an extra 1 here. Every partition in the example
has only three values.

>
> Given a particular value, bsearch the first list. If the value is not
> found, it's not in any partition. If it is found, then go look up the
> same array index in the second list; that's the containing partition.
>

+1, if we could do it. It will need a change in the way Amit's patch stores
partitioning scheme in PartitionDesc.

This way specifications {('e', 'i', 'f'), ('h', 'd','m') and ('l', 'b',
'a')} and {('h', 'd','m') , ('e', 'i', 'f'), and ('l', 'b', 'a')} which are
essentially same, are represented in different ways. It makes matching
partitions for partition-wise join a bit tedius. We have to make sure that
the first array matches for both the joining relations and then make sure
that all the values belonging to the same partition for one table also
belong to the same partition in the other table. Some more complex logic
for matching subsets of lists for partition-wise join.

At least for straight forward partitioned table matching it helps to have
both these array look same independent of the user specification. From that
point of view, the partition be ordered by their lowest or highest list
values and the second array is the index in the ordered set. For both the
specifications above, the list will look like

[ 'a', 'b', 'd', 'e', f', 'h', 'i', 'l', 'm' ]
[1, 1, 2, 3, 3, 2, 3, 1, 2]
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company