Suppose we have partitioned table defined as below: P(a, b, c, d) partition by (a) p1 (a=1) p2 (a=2) p3 (a=3)
Since in PG, we can define different indexes among partitions, so each child may have different UniqueKeys, and some of them might be invalidated in parent's level. For example, 1). we only define unique index p1(c), so (c) would be an uniquekey of p1 only. so it is invalidate on appendrel level. 2). We define unique index p_n(c) on each childrel, so every childrel has UniqueKey (c). However it is invalid on appendrel as well. 3). We define unique index p_n(a, c), since a is the partition key, so (a, c) would be valid for both child rel and parent rel. In my v1 implementation[1] before, I maintained the child rel exactly the same as non-partitioned table. But when calculating the UniqueKey for partitioned table, I first introduce a global_unique_indexlist which handles the above 3 cases. The indexes for case 1 and case 2 will not be in global_unique_indexlist but the index in case 3 will be even if they are only built in child level. After we have build the global_unique_indexlist on appendrel, we will build the UnqiueKey exactly same as non partitioned table. With this way, I'm not happy with the above method now is because 1). the global_unique_indexlist is build in a hard way. 2). I have to totally ignored the UniqueKey on child level and re-compute it on appendrel level. 3). The 3 cases should rarely happen in real life, I guess. When I re-implement the UniqueKey with EquivalenceClass, I re-think about how to handle the above stuff. Now my preferred idea is just not handle it. When building the uniquekey on parent rel, we just handle 2 cases. If the appendrel only have 1 child, we just copy (and modified if needed due to col-order-mismatch case) the uniquekey. 2). Only handle the Unique index defined in top level, for this case it would yield the below situation. create unique index on p(a, b); --> (A, B) will be the UniqueKey of p. create unique index on p_nn(a, b); --> (A, B) will not be the UniqueKey of p even we create it on ALL the child rel. The result is not perfect but I think it is practical. Any suggestions? The attached is a UnqiueKey with EquivalenceClass patch, I just complete the single relation part and may have bugs. I just attached it here for design review only. and the not-null-attrs is just v1 which we can continue discussing on the original thread[2]. [1] https://www.postgresql.org/message-id/CAKU4AWr1BmbQB4F7j22G%2BNS4dNuem6dKaUf%2B1BK8me61uBgqqg%40mail.gmail.com [2] https://www.postgresql.org/message-id/flat/caku4awpqjaqjwq2x-ar9g3+zhrzu1k8hnp7a+_mluov-n5a...@mail.gmail.com -- Best Regards Andy Fan (https://www.aliyun.com/)
v1-0001-Introduce-notnullattrs-field-in-RelOptInfo-to-ind.patch
Description: Binary data
v1-0002-UniqueKey-with-EquivalenceClass-for-single-rel-on.patch
Description: Binary data
