[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 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 made changes - 2021-12-27 14:05

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

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

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

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 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 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 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 ]

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