Skip to content

Home

Jump to bottom
Alexander K edited this page Dec 12, 2017 · 64 revisions

MariaDB System Versioning

System versioning is defined by SQL:2011. System versioned tables contain historical data for all transactions ever run against the table. Consider following example (note the new syntax extensions with system versioning):

> create table t(x int) with system versioning;
> insert into t values (1);
> update t set x=2;
> delete from t;

Now the table t doesn't have any data:

> select * from t;
Empty set (0.00 sec)

However, the system versioned table stores all versions of the row and we can query any of them:

> select * from t for system_time all;
+------+---------------+-------------+
| x    | sys_trx_start | sys_trx_end |
+------+---------------+-------------+
|    2 |          2323 |        2330 |
|    1 |          2320 |        2323 |
+------+---------------+-------------+

Note the invisible columns sys_trx_stat and sys_trx_end. The columns contain transaction IDs for the appropriate row versions: sys_trx_stat describes which transaction created the row version and sys_trx_end describes which transaction deleted the row version. Besides the new hidden columns, the example above uses for system_time syntax extension, which is also new.

This is the most trivial example. Of course you can rum much more complicated historical queries.

References

Contents

Clone this wiki locally