[MDEV-27328] Change of SYSTEM_TIME partitioning options is not possible without data copy - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL)
Fix Version/s: 10.9.1
Component/s: Partitioning, Versioned Tables
Labels:
None

Description

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;

Attachments

Issue Links

is blocked by

MDEV-19903 Setup default partitions for system versioning

Closed

is duplicated by

MDEV-25540 Auto-create: There is no clear way to disable auto-creation without ruining existing partitions

Closed

Activity

Ascending order - Click to sort in descending order

Aleksey Midenkov created issue - 2021-12-20 21:07

Aleksey Midenkov made changes - 2021-12-20 21:07

Field	Original Value	New Value
Description	When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) 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 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;	When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write: {code:sql} alter table t1 partition by system_time; {code} 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: {code:sql} 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 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; {code}

Aleksey Midenkov made changes - 2021-12-20 21:09

Component/s		Partitioning [ 10802 ]
Component/s		Versioned Tables [ 14303 ]
Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.8 [ 26121 ]
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.8 [ 26121 ]
Assignee		Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2021-12-20 21:09

Status

Open [ 1 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2021-12-20 21:11

Link

This issue is duplicated by ~~MDEV-25540~~ [ ~~MDEV-25540~~ ]

Aleksey Midenkov made changes - 2021-12-20 21:12

Description

When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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 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;
{code}

When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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 6;
# 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;
{code}

Aleksey Midenkov made changes - 2021-12-20 21:13

Description

When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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 6;
# 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;
{code}

When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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;
{code}

Aleksey Midenkov made changes - 2021-12-20 21:19

Summary

ALTER of SYSTEM_TIME partitioning is not possible without data copy

Change of SYSTEM_TIME partitioning options is not possible without data copy

Aleksey Midenkov made changes - 2021-12-20 21:22

Fix Version/s	10.3 [ 22126 ]
Affects Version/s	10.3 [ 22126 ]

Aleksey Midenkov added a comment - 2021-12-21 10:14 - edited

Please review bb-10.5-midenok

Aleksey Midenkov added a comment - 2021-12-21 10:14 - edited Please review bb-10.5-midenok

Aleksey Midenkov made changes - 2021-12-21 12:12

Assignee	Aleksey Midenkov [ midenok ]	Nikita Malyavin [ nikitamalyavin ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Aleksey Midenkov made changes - 2021-12-21 22:51

Link

This issue is blocked by ~~MDEV-19903~~ [ ~~MDEV-19903~~ ]

Aleksey Midenkov made changes - 2021-12-21 23:03

Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]

Aleksey Midenkov made changes - 2021-12-21 23:03

Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]

Nikita Malyavin added a comment - 2021-12-27 14:05

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

Nikita Malyavin added a comment - 2021-12-27 14:05 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

Nikita Malyavin made changes - 2021-12-27 14:05

Assignee	Nikita Malyavin [ nikitamalyavin ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov added a comment - 2021-12-28 11:22

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.

Aleksey Midenkov added a comment - 2021-12-28 11:22 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.

Aleksey Midenkov made changes - 2021-12-28 11:22

Assignee	Aleksey Midenkov [ midenok ]	Oleksandr Byelkin [ sanja ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2022-01-03 10:20

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?

Oleksandr Byelkin added a comment - 2022-01-03 10:20 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?

Elena Stepanova added a comment - 2022-01-03 10:26 - 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.

Elena Stepanova added a comment - 2022-01-03 10:26 - 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.

Oleksandr Byelkin made changes - 2022-01-11 09:26

Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]

Oleksandr Byelkin added a comment - 2022-01-11 09:27

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

Oleksandr Byelkin added a comment - 2022-01-11 09:27 We agreed that server behaviour better to change in the new versions. Also it should be documentated.

Oleksandr Byelkin made changes - 2022-01-11 09:27

Assignee	Oleksandr Byelkin [ sanja ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2022-01-11 09:54

Description

When we need to add/remove or change LIMIT, INTERVAL, AUTO (10.8 and more) we have to recreate partitioning from scratch. Such operations should be done fast. To remove options like LIMIT or INTERVAL one should write:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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;
{code}

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:

{code:sql}
alter table t1 partition by system_time;
{code}

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:
{code:sql}
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;
{code}

Aleksey Midenkov made changes - 2022-01-12 21:32

Fix Version/s		10.8.1 [ 26815 ]
Fix Version/s	10.8 [ 26121 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Aleksey Midenkov made changes - 2022-01-12 21:33

Resolution	Fixed [ 1 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Aleksey Midenkov added a comment - 2022-01-12 21:38

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

Aleksey Midenkov added a comment - 2022-01-12 21:38 Please update documenation for SYSTEM_TIME partitioning in 10.8 according to this task.

Aleksey Midenkov made changes - 2022-01-12 21:38

Assignee

Aleksey Midenkov [ midenok ]

Geoff Montee [ geoffmontee ]

Sergei Golubchik made changes - 2022-01-17 11:01

Fix Version/s		10.8 [ 26121 ]
Fix Version/s	10.8.1 [ 26815 ]

Aleksey Midenkov added a comment - 2022-04-28 13:58

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 - 2022-04-28 13:58 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 made changes - 2022-04-28 13:58

Assignee

Geoff Montee [ geoffmontee ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2022-05-07 10:02

Fix Version/s		10.9.1 [ 27114 ]
Fix Version/s	10.8 [ 26121 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Aleksey Midenkov added a comment - 2022-08-03 15:14

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

Aleksey Midenkov added a comment - 2022-08-03 15:14 julien.fritsch Why you did not automate that on closing the issue?

People

Assignee:: Aleksey Midenkov

Reporter:: Aleksey Midenkov

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2021-12-20 21:07

Updated:: 2022-08-03 15:14

Resolved:: 2022-05-07 10:02

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration