Re: Declarative partitioning

> IIUC, this seems like a combination of 2 and 3 above:
>
> So, we have the following list partitions (as read from the catalog)
>
> Table 1: p1 {'b', 'f'}, p2 {'c', 'd'}, p3 {'a', 'e'}
> Table 2: p1 {'c', 'd'}, p2 {'a', 'e'}, p3 {'b', 'f'}
>
> By applying the method of 3:
>
> Table 1: p3 {'a', 'e'}, p2 {'b', 'f'}, p1 {'c', 'd'}
> Table 2: p2 {'a', 'e'}, p1 {'b', 'f'}, p3 {'c', 'd'}
>
> Then applying 2:
>
> Table 1: ['a', 'b', 'c', 'd', 'e', 'f'], [1, 2, 3, 3, 1, 2], [3, 1, 2]
> Table 2: ['a', 'b', 'c', 'd', 'e', 'f'], [1, 2, 3, 3, 1, 2], [2, 3, 1]
>

We should arrange the OID arrays to follow canonical order. Assuming that
OIDs of p1, pt2, and p3 of table 1 are t1o1, t1o2 and t1o3 respectively,
and those of p1, p2 and p3 of table 2 are t2o1, t2o2, t2o3 resp. the last
arrays should be [t1o3, t1o2, t1o1] and [t2o2, t2o1, t2o3].Thus the last
arrays from both representation give the OIDs of children that should be
joined pair-wise. IOW, OID array should just follow the canonical order
instead of specification order. AFAIU, your patch arranges the range
partition OIDs in the canonical order and not specification order.

>
> This is user-specification independent representation wherein the
> partition numbers in the 2nd array are based on canonical representation
> (ordered lists). To check pairwise join compatibility, simply compare the
> first two arrays. As to which partitions (think OIDs, RTEs whatever) pair
> with each other, simply pair corresponding elements of the 3rd array which
> are original partitions numbers (or OIDs). Also when routing a tuple, we
> find partition number in the array 2 and then look up the array 3 to get
> the actual partition to insert the tuple.
>
> Neither of these representations make the logic of checking pairwise-join
> compatibility and pairing a subset of partitions (on either side) any
> easier, although I haven't given it a good thought yet.
>
> With the small change suggested above, it should be easy to check
partition-wise join compatibilty for simplest case. I agree that for
generic case it will be difficult. E.g. Table 1#: p3 {'a', 'e'}, p4{'a#',
'l'}, p2 {'b', 'f'}, p1 {'c', 'd'} being (INNER) joined with Table 2: p2
{'a', 'e'}, p1 {'b', 'f'}, p3 {'c', 'd'} assuming 'a'< 'a#' < 'b'; in this
case, we should be able to match p3-p2, p2-p1, p1-p3 for partition-wise
join, even though canonical representations of both partitions differ,
because of an extra partition in between.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company