When we need to add/remove or change LIMIT, INTERVAL, AUTO we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:
altertable t1 partition by system_time;
The command checks whether it is new or existing SYSTEM_TIME partitioning. And in the case of new it behaves as CREATE would do: adds default number of partitions (2). If SYSTEM_TIME partitioning already existed it just changes its options: removes unspecified ones and adds/changes those specified explicitly. In case when partitions list was supplied it behaves as usual: does full repartitioning.
Examples:
createorreplacetable t1 (x int) with system versioning
partition by system_time limit 100 partitions 4;
# Change LIMIT
altertable t1 partition by system_time limit 33;
# Remove LIMIT
altertable t1 partition by system_time;
# This does full repartitioning
altertable t1 partition by system_time limit 33 partitions 4;
# This does data copy as pruning will require records in correct partitions
altertable t1 partition by system_time interval 1 hour
starts '2000-01-01 00:00:00';
# But this works fast, LIMIT will apply to DML commands
altertable t1 partition by system_time limit 33;
Attachments
Issue Links
is blocked by
MDEV-19903Setup default partitions for system versioning
Closed
is duplicated by
MDEV-25540Auto-create: There is no clear way to disable auto-creation without ruining existing partitions
In favour of the approach Elena Stepanova tried that syntax intuitively in MDEV-25540
I don't think MDEV-25540 is a suitable justification for this. AUTO attribute in MDEV-25540 only affects the process of partitioning – that is, defines how new partitions are created or not created. For already existing ones, it has no meaning and thus its change doesn't anyhow affect them. LIMIT and INTERVAL can be seen, at least by users, as attributes of both partitioning (the process) and individual partitions. Some users can reasonably expect that the change will affect existing partitions and distribution of existing data, especially since that's how it already works.
Elena Stepanova
added a comment - - edited
In favour of the approach Elena Stepanova tried that syntax intuitively in MDEV-25540
I don't think MDEV-25540 is a suitable justification for this.
AUTO attribute in MDEV-25540 only affects the process of partitioning – that is, defines how new partitions are created or not created. For already existing ones, it has no meaning and thus its change doesn't anyhow affect them.
LIMIT and INTERVAL can be seen, at least by users, as attributes of both partitioning (the process) and individual partitions . Some users can reasonably expect that the change will affect existing partitions and distribution of existing data, especially since that's how it already works.
LIMIT and INTERVAL can be seen, at least by users, as attributes of both partitioning (the process) and individual partitions. Some users can reasonably expect that the change will affect existing partitions and distribution of existing data, especially since that's how it already works.
This keyword should apply to partitioning only. Individual attributes apply in partitions list.
Aleksey Midenkov
added a comment - LIMIT and INTERVAL can be seen, at least by users, as attributes of both partitioning (the process) and individual partitions. Some users can reasonably expect that the change will affect existing partitions and distribution of existing data, especially since that's how it already works.
This keyword should apply to partitioning only. Individual attributes apply in partitions list.
Bug
Major
MDEV-19903 Setup default partitions for system versioning
I don't think
MDEV-25540is a suitable justification for this.AUTO attribute in
MDEV-25540only affects the process of partitioning – that is, defines how new partitions are created or not created. For already existing ones, it has no meaning and thus its change doesn't anyhow affect them.LIMIT and INTERVAL can be seen, at least by users, as attributes of both partitioning (the process) and individual partitions. Some users can reasonably expect that the change will affect existing partitions and distribution of existing data, especially since that's how it already works.