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
