Now with System Versioned Tables – Announcing MariaDB Server 10.3 Second Beta
We are happy to announce the second beta release of MariaDB Server 10.3, the fastest growing open source relational database. Beta is an important time in our release and we encourage you to download this release today! Please note that we do not recommend running beta releases in production.
MariaDB Server 10.2 added enhancements like Window Functions, Common Table Expressions, JSON functions and CHECK constraints. MariaDB Server 10.3 is the next evolution. For MariaDB Server 10.3 a lot of effort has been spent on database compatibility enhancements, especially for stored routines. This will allow easier migration of stored functions and better usability of stored functions in general.
With System Versioned Tables we are now adding a new powerful enhancement to MariaDB Server, the ability to process temporal data. This opens new use cases to MariaDB Server, like retrospective and trend data analysis, forensic discovery, or data auditing.
System Versioned Tables could be used for compliance, audit, risk analysis, or position analysis.
Enabling the System Versioned Tables feature is as easy as altering an existing table:
ALTER TABLE products ADD SYSTEM VERSIONING;
or when creating a new table:
CREATE TABLE products ( pname VARCHAR(30), price decimal(8,2) ) WITH SYSTEM VERSIONING;
System versioned tables are storing timestamps for when data has been added until it has been updated or deleted. This allows to query the data “as of” a given time, or to compare the data “as of” a different date and time.
SELECT * FROM products FOR SYSTEM_TIME AS OF TIMESTAMP @t1;
Now, with MariaDB Server 10.3.4 beta, several significant features and enhancements are available for our users and customers, including:
- Temporal Data Processing
- System Versioned Tables store information relating to past and present time
- Database Compatibility Enhancements
- PL/SQL Compatibility for MariaDB Stored Functions: The server now understands a subset of Oracle’s PL/SQL language instead of the traditional MariaDB syntax for stored routines
- New option for CURSOR in stored routines: A CURSOR can now have parameters used by the associated query
- New data types for stored routines: ROW data type, TYPE OF and ROW TYPE OF anchored data types
- Generation of unique primary keys by SEQUENCES: As an alternative to AUTO INCREMENT It is now possible to define names sequence objects to create a sequence of numeric values
- Operations over result sets with INTERSECT and EXCEPT: In addition to the already existing UNION an intersection and subtraction of result sets is now possible
- Define Columns to be invisible: Columns now can be defined to be invisible. There exist 3 levels of invisibility, user defined, system level and completely invisible
- Window Function Enhancement: percentile and median window functions have been added
- User Flexibility
- User Defined Aggregate Functions: In addition to creating SQL functions it is now also possible to create aggregate functions
- Lifted limitations for updates and deletes: A DELETE statement can now delete from a table used in the WHERE clause. UPDATE can be the same for source and target
- Performance/Storage Enhancements
- Add columns to a InnoDB table instantly: New columns can be appended instantly to a InnoDB Table
- Statement based timeouts: Via WAIT and NOWAIT the lock wait timeout can be explicitly set for a statement
- Column based Compression: Compression is now possible per column for most data types. The compression is storage engine independent
- Storage Engine Enhancements
- Spider Storage Engine: The partitioning storage engine has been updated to the newest release of the Spider Storage engine to support new Spider features including direct join support, direct update and delete, direct aggregates
- Proxy Layer Support for MariaDB Server: Client / Server authentication via a Proxy like MariaDB MaxScale using a Server Proxy Protocol Support
Try out MariaDB Server Beta software and share your feedback!
Sponsored by MariaDB.