Support EXCHANGE PARTITION with GLOBAL INDEX

[mjonss]

Enhancement

This is a placeholder issue for supporting GLOBAL INDEX in EXCHANGE PARTITION. EXCHANGE PARTITION needs extra handling to support Global Index, #45133.

[mjonss]

Like DROP PARTITION and TRUNCATE PARTITION, EXCHANGE PARTITION should be a fast meta-data only DDL (with the exception for 'WITH VALIDATION').

DROP/TRUNCATE PARTITION have implemented an optimized way, where it has delayed cleanup of the global index, by simply filtering out the old partition ids, and currently accepting that duplicate key errors will be given in-case writing not-yet cleaned up global index entries.

But for EXCHANGE PARTITION it is more complicated. Similar to how global index for REORGANIZE PARTITION is implemented, where a full new global index is created, since it needs two versions, one for the old set of partitions and one for the new set of partitions. This is to support both a current schema version and a previous schema version, when changing to the new set of partitions, since a unique index cannot point to both the old and new entries.

So this is what is needed to support EXCHANGE PARTITION with GLOBAL INDEX:

• Similar handling (states) as REORGANIZE PARTITION, including duplicate the global index.
• manage double write of the local unique index in the non-partitioned table, also when it is in the first state of being included in the partitioned table (since it can be used as a non-partitioned table at the same time).
• manage double write to the new global index from the non-partitioned table, same reason as previous.

[mjonss]

Due to the amount of time and resources needed for executing EXCHANGE PARTITION, while I don't think all of it is needed, I wonder if we should not implement: ALTER TABLE t CONVERT PARTITION p TO TABLE npt and ALTER TABLE t CONVERT TABLE npt TO PARTITION (partition p values ...) and allow it to take longer, in case of GLOBAL INDEX, not support EXCHANGE PARTITION. The reason is that most times EXCHANGE PARTITION is used, it is only needed for moving data one way, not both ways. And the 'both ways' is what adds complexity for GLOBAL INDEX, needing to keep multiple indexes in-sync during the DDL.

We could even consider if the UNIQUE/GLOBAL Index would be required, i.e. for ALTER TABLE t CONVERT PARTITION p TO TABLE npt it would not create the local unique index, but leave that to the user to decide if needed or not, to speed up the operation. Same for ALTER TABLE t CONVERT TABLE npt TO PARTITION (partition p values ...) would not require it to have a unique index matching the Global index, since it will not be used, and the entries for the Global index will be written anyway.

[mjonss]

MariaDB has implemented CONVERT TO here.

[mjonss]

Note that CONVERT TO does only support LIST/RANGE, not KEY/HASH partitioning, since those needs to keep the same number of partitions, which EXCHANGE PARTITION does.