[MDEV-26934] ADD SYSTEM VERSIONING and modify column WITHOUT SYSTEM VERSIONING in single ALTER TABLE command Created: 2021-10-28  Updated: 2021-11-23

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Versioned Tables
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Geoff Montee (Inactive) Assignee: Aleksey Midenkov
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-12894 System-versioned tables Closed
Relates
relates to MDEV-26993 Add system versioning via modify colu... Open

 Description   

It does not currently seem possible to use a single ALTER TABLE statement to add system versioning to a table and exclude columns from system versioning. For example, try running the following statements:

CREATE TABLE accounts_exclude_col (
 
   id SERIAL PRIMARY KEY,
 
   name VARCHAR(255),
 
   amount INT,
 
   comment VARCHAR(1000)
 
);
 
 
 
ALTER TABLE accounts_exclude_col
 
   ADD SYSTEM VERSIONING,
 
   MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
 
 
 
ALTER TABLE accounts_exclude_col
 
   ADD SYSTEM VERSIONING,
 
   MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
 
   ALGORITHM=COPY;

This produces the following output:

MariaDB [test]> CREATE TABLE accounts_exclude_col (
 
    ->    id SERIAL PRIMARY KEY,
 
    ->    name VARCHAR(255),
 
    ->    amount INT,
 
    ->    comment VARCHAR(1000)
 
    -> );
 
Query OK, 0 rows affected (0.019 sec)
 
 
 
MariaDB [test]> ALTER TABLE accounts_exclude_col
 
    ->    ADD SYSTEM VERSIONING,
 
    ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
 
ERROR 4124 (HY000): Table `accounts_exclude_col` is not system-versioned
 
MariaDB [test]> ALTER TABLE accounts_exclude_col
 
    ->    ADD SYSTEM VERSIONING,
 
    ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING,
 
    ->    ALGORITHM=COPY;
 
ERROR 4124 (HY000): Table `accounts_exclude_col` is not system-versioned

The workaround is to use multiple ALTER TABLE statements:

MariaDB [test]> ALTER TABLE accounts_exclude_col
 
    ->    ADD SYSTEM VERSIONING;
 
Query OK, 0 rows affected (0.032 sec)              
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> SET SESSION system_versioning_alter_history=KEEP;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> ALTER TABLE accounts_exclude_col
 
    ->    MODIFY name VARCHAR(255) WITHOUT SYSTEM VERSIONING;
 
Query OK, 0 rows affected (0.024 sec)
 
Records: 0  Duplicates: 0  Warnings: 0



 Comments   
Comment by Nikita Malyavin [ 2021-11-09 ]

I remember MDEV-16490 which is close to this

Comment by Nikita Malyavin [ 2021-11-09 ]

midenok However it is possible to add a key:

ALTER TABLE accounts_exclude_col
 
  CHANGE name name2 VARCHAR(255),
 
  add KEY(name2);

another example:

create table t (
 
  x int,
 
  y int,
 
  x2 int as (x)
 
);
 
 
 
alter table t
 
  change y z int,
 
  change x2 z2 int as (z);

We can refer to the changes from alter table.

I think it is affordable to refer to a new versioning state of the table in CHANGE clause as well.

Comment by Aleksey Midenkov [ 2021-11-23 ]

That didn't work by design. As a new feature would be nice to have it. Should be implemented with MDEV-26993.

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