[MDEV-23125] Backfilling System Versioned Tables Created: 2020-07-08  Updated: 2020-07-09  Resolved: 2020-07-09

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: James Chsn Assignee: Sergei Golubchik
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-16029 mysqldump: dump and restore historica... Closed

 Description   

We are using MariaDB 10.3.

As per: https://mariadb.com/kb/en/temporal-data-tables/

Is it possible to allow the user to explicitly update the ROW_START and ROW_END column in the table row, for a table with SYSTEM VERSIONING enabled?

We sometimes import old data and would like to backfill the data.



 Comments   
Comment by Sergei Golubchik [ 2020-07-09 ]

Yes, it is possible, but not as simple as directly updating these values. For example, the following script will create a historical row as created in 1990-01-01 and deleted one year after:

set @@timestamp=unix_timestamp('1990-01-01 00:00:01');
insert into tbl values (100,1,2,'some more data');
set @@timestamp=unix_timestamp('1991-01-01 00:00:01');
delete from tbl where pk=100;

This can be easily automated with a stored procedure or some external script.

For this approach to work you need @@secure_timestamp variable to be not set to YES.

Allowing direct updates of versioning fields is part of MDEV-16029

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