[MDEV-27328] Change of SYSTEM_TIME partitioning options is not possible without data copy Created: 2021-12-20  Updated: 2022-08-03  Resolved: 2022-05-07

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Versioned Tables
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.9.1

Type: Bug Priority: Major
Reporter: Aleksey Midenkov Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-19903 Setup default partitions for system v... Closed
Duplicate
is duplicated by MDEV-25540 Auto-create: There is no clear way to... Closed

 Description   

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:

alter table 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:

create or replace table t1 (x int) with system versioning
 
partition by system_time limit 100 partitions 4;
 
# Change LIMIT
 
alter table t1 partition by system_time limit 33;
 
# Remove LIMIT
 
alter table t1 partition by system_time;
 
# This does full repartitioning
 
alter table t1 partition by system_time limit 33 partitions 4;
 
# This does data copy as pruning will require records in correct partitions
 
alter table 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
 
alter table t1 partition by system_time limit 33;



 Comments   
Comment by Aleksey Midenkov [ 2021-12-21 ]

Please review bb-10.5-midenok

Comment by Nikita Malyavin [ 2021-12-27 ]

Agree about semantics, and approve it.

For docs team:
after this ticket is done, ALTER for SYSTEM_TIME partitioning will do
full repartitioning when:

  • INTERVAL is added or changed;
  • partition list or partition number was specified;

Otherwise it will do fast alter table.

For the context and some reasoning, one can also see MDEV-25540 from duplicates.

cc GeoffMontee jacob.moorman

Comment by Aleksey Midenkov [ 2021-12-28 ]

Actually there was some worry from nikitamalyavin side about the syntax so please confirm the approach. In favour of the approach elenst tried that syntax intuitively in MDEV-25540 and I see no better syntax than that.

Comment by Oleksandr Byelkin [ 2022-01-03 ]

As I unterstand AUTO is applicable only for new partitios, so it is OK.

But how it will behave with LIMIT and old partitions is not clear, could we discuss it?

Comment by Elena Stepanova [ 2022-01-03 ]

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.

Comment by Oleksandr Byelkin [ 2022-01-11 ]

We agreed that server behaviour better to change in the new versions. Also it should be documentated.

Comment by Aleksey Midenkov [ 2022-01-12 ]

Please update documenation for SYSTEM_TIME partitioning in 10.8 according to this task.

Comment by Aleksey Midenkov [ 2022-04-28 ]

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.

Comment by Aleksey Midenkov [ 2022-08-03 ]

julien.fritsch Why you did not automate that on closing the issue?

Generated at Thu Feb 08 09:52:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.