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.