Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5271

Support engine-defined attributes per partition

    XMLWordPrintable

    Details

      Description

      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;
      • 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.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              serg Sergei Golubchik
              Votes:
              6 Vote for this issue
              Watchers:
              14 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.