Comments - System-Versioned Tables

If the system versioning was activated by creating coluns row_start, row_end being visible and you need to drop it, execute:

alter table 'table name' drop system versioning, drop PERIOD FOR SYSTEM_TIME, DROP COLUMN `row_start`, DROP COLUMN `row_end`;

I would like to create a bitemporal schema utilizing the standardized approach. Currently (10.4.6) application-time periods cannot be used to enforce temporal uniqueness and temporal referential integrity on the application time dimension (MDEV-16978, MDEV-16983). To compensate I have added the app-time start and end columns to primary, unique and foreign keys, and attempted to enforce said aspects via trigger. Unfortunately even the trivial INSERT has proven challenging: A BEFORE INSERT trigger failed to run, producing a "field <system-time (tx) start col> doesn't have a default value" error. An AFTER INSERT trigger did run, but only to leave me wondering how I am supposed to reliably distinguish the newly-inserted row from pre-existing ones for validation purposes in this case (my only clue, NEW.<system-time (tx) start col> is set to 0). Am I missing something, or should I better resort to a "userspace" approach until 10.5 arrives?

As an aside, AUTO_INCREMENT fires for rows inserted as a consequence of (UPDATE | DELETE) ... FOR PORTION OF. Is this intentional?

According to SQL 2011, system versioned tables can be use to implement "transaction time". My understanding is that this is table is uni-temporal in nature.

https://en.wikipedia.org/wiki/Temporal_database

Do we have bi-temporal tables, or a feature request in place to include a feature, where we can have both "valid time" and a "transaction time" ?

So there is no way to control/limit the versioning to say the LAST x changes made...it's all changes no matter what?

Is there a way to delete history for a specific record? I have a situation where once I delete a record, I'm under contract to remove all records (including historical ones). I don't see that anywhere here.

No, sorry. This is exactly the scenario that system versioning is supposed to prevent. The history is immutable.

Having said that, two more points:

• you can make one particular column (that stores your sensitive data) non-versioned using WITHOUT VERSIONING clause
• we might, in a future version, allow direct editing of timestamp fields (very limited, but still). then you'll be able, for example, to insert...select all data excluding that one record and get a new table without that one row.

Is there a way to add add the transaction_end to a transaction_precise versioned table so I can reference it directly without having to join mysql.transaction_registry? In our system I give users a list of dates/times when records were changed, on a normally versioned table if a large change was made that change may span seconds or even minutes so it's difficult to figure out which was the "last" change for a set - transaction precise history fixes that problem but now I have no way of getting that list of dates/times without joining in a table external to the current database.

Unfortunately, no. The only reason for mysql.transaction_registry to exist is because transaction end cannot be stored directly in the versioned table. (simply, because it's not known at the time of the operation, it is only known at the time of commit, and it wold be too costly to postpone all updates till the commit time)

You can create a view that joins with mysql.transaction_registry so for your users the join will be hidden and implicit.

If it's a question of privileges, you can just grant everyone SELECT privilege on mysql.transaction_registry, and they can join, when needed.

Not the answer I wanted, but it does make perfect sense. The next best thing would be to somehow put the transaction_registry table directly in the database with the versioned tables themselves so it's at least self-contained.

In my case, I need versioning of my stored procedure/function... Is this feature allow versioning for system table eg: mysql.proc or information_schema.routines?

No at the moment it does not support versioning of system tables.

Hello,

you discribe, how I can drop a historical partition. Can I also add a historical partition? How is the syntax to do this?

Particulary with regard to historical partitions by time it is useful to add new historical partitions.

Kind regards, K. Budde

to drop a historical partition use DROP PARTITION. To add a historical partition use ADD PARTITION. Like

alter table t1 drop partition p_hist1;

alter table t1 add partition (partition p_hist3 history);

You have described how to create partitions when creating the table. Is it also possible to add it later? I tried to add a partition, but unfortunately I couldn't do that: Both failed:

alter table t1 add partition 
             (partition p_hist history, 
              partition p_cur current);
alter table t1 add partition by SYSTEM_TIME
             (partition p_hist history, 
              partition p_cur current);

The syntax for adding partitioning to a not partitioned table would be

alter table t1 partition by SYSTEM_TIME (partition p_hist history, partition p_cur current);

"add" only can be used for already partitioned tables.

Thank you. For one Table it worked. But for another table, this error occurred: Cannot delete or update a parent row: a foreign key constraint fails

What have I done wrong?

OK, I found in the documentation of partitioning limitations: A partitioned table cannot contain, or be referenced by, foreign keys

This is a pity.

See https://jira.mariadb.org/browse/MDEV-12483