Overview
MariaDB allows storage engines to define additional attributes per index, field, or table. However, per-table attributes cannot be specified per partition. We should support per-partition attributes for more flexible configurations. Also, this functionality is necessary for other important features (see the issue links below). https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/
Syntax
partition_definition:
|
PARTITION partition_name
|
[VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
|
[[STORAGE] ENGINE [=] engine_name]
|
[engine_defined_options ]
|
...
|
[(subpartition_definition [, subpartition_definition] ...)]
|
https://mariadb.com/kb/en/create-table/#partitions
Example
Here is a possible use case of the per-partition attributes support. By the use of per-partition attributes, the Spider storage engine (and other federated engines) can provide users with a cleaner way to specify where to connect.
CREATE TABLE tbl_a ( |
a INT, |
b VARCHAR(255), |
PRIMARY KEY(a) |
) ENGINE=Spider PARTITION BY RANGE(a) ( |
PARTITION pt1 VALUES LESS THAN (100) REMOTE_SERVER="s_2_1" REMOTE_TABLE="ta_r1", |
PARTITION pt2 VALUES LESS THAN MAXVALUE REMOTE_SERVER="s_2_1", REMOTE_TABLE="ta_r2" |
)
|
Note that the example above doesn't work with the current server implementation.
Description
- If an engine-defined attribute is specified at table-level, it applies to all the partitions in the table (backward compatible behavior).
- Specifying an engine-defined attribute at table-level is only possible when all the storage engines composing a partitioned table support this attribute. Currently, it is not possible to use multiple storage engines to create a partitioned table, but this will be possible in MDEV-22168.
- One can use both per-table attributes and per-partition attributes for a single table. If the same attribute is specified both at table-level and partition-level, the per-partition attribute takes precedence. So, we can consider per-table attributes as default values.
- We do not support, at least in the present issue, engine-defined attributes per subpartitions.
TODO
Modify the parser to accept per-partition attributes;- Keep per-partition attributes some in-memory data structure (class partition_element ?);
- Pass per-partition attributes to ha_partition::create() as per-table attributes;
- This is to keep the handler mostly oblivious whether it's a partition or a table.
- The partition storage engine has a hook point to modify TABLE_SHARE. We can also set up per-table attributes there. https://github.com/MariaDB/server/blob/mariadb-10.7.1/sql/ha_partition.cc#L2722
- Per-table attributes and per-partition attributes should be merged before table creation.
- Persist per-partition attributes in FRM files;
-
Modify the SHOW function to handle per-partition attributes.(unnecessary) -
Make it work with subpartitions - Add test cases (subpartitioning, alter table)
- Fix CONNECT tests failures: https://buildbot.mariadb.org/#/builders/236/builds/3094
- Refactor the entire patch.