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

Reorganize multiple history partitions

Details

    Description

      Implement merging of multiple history partitions by the syntax:

      create or replace table t1 (x int) with system versioning
      		 
      partition by system_time partitions 3;
      		 
      alter table t1 reorganize partition p0, p1 into (partition p0 history);

      Consider implementing splitting into multiple history partitions:

      create or replace table t1 (x int) with system versioning
      		 
      partition by system_time;
      		 
      alter table t1 reorganize partition p0 into (partition p0 history, partition p1 history);

      Splitting distributes rows equally between specified partitions.

      For INTERVAL:
      Merging and splitting is forbidden.

      For LIMIT:
      Merging is forbidden when LIMIT is overflown.

      Currently splitting and merging fail with error:

      ERROR 1510 (HY000): REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers

      More observations

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-17554 Auto-create history partitions for system-versioned tables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-19903 Setup default partitions for system versioning

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik added a comment -

            I don't understand.

            create or replace table t1 (x int) with system versioning partition by system_time partitions 3;

            Does it mean that p1 is not used at all? Why is it allowed then?
            With INTERVAL one cannot split or reorganize partitions, it'll break the interval logic.
            So LIMIT seems to be the only use case when such reorganization could make sense.

            serg Sergei Golubchik added a comment - I don't understand. create or replace table t1 (x int ) with system versioning partition by system_time partitions 3; Does it mean that p1 is not used at all? Why is it allowed then? With INTERVAL one cannot split or reorganize partitions, it'll break the interval logic. So LIMIT seems to be the only use case when such reorganization could make sense.
            midenok Aleksey Midenkov added a comment -

            The use case for merging/splitting is when switching from INTERVAL to LIMIT. Yes, the use of `PARTITIONS 3` is not evident without INTERVAL/LIMIT though might be useful to quickly drop current history (after maybe backed up).

            midenok Aleksey Midenkov added a comment - The use case for merging/splitting is when switching from INTERVAL to LIMIT. Yes, the use of `PARTITIONS 3` is not evident without INTERVAL/LIMIT though might be useful to quickly drop current history (after maybe backed up).

            People

              midenok Aleksey Midenkov
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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