TTL support in eXtremeSQL

Time-To-Live (TTL) is supported in SQL DDL using the "TTL interval" syntax (see below). "interval" is a string literal containing an integer number and unit specification. Valid unit prefixes are "ms", "sec", "min", "hour", and "day". TTL meta data for a table can be inspected in the Metaschema view. Please note that "ttl" is a keyword now, so in case of using it as a column name (for example in Metaschema view) it should be enclosed in quotes: "TTL".

XSQL>create table foo(x integer primary key) ttl '10 seconds';

XSQL>insert into foo values (1);

XSQL>select * from foo;
x	ttl_timestamp@
------------------------------------------------------------------------------
1	11/29/2019 17:49:40

Selected records: 1



XSQL>insert into foo values (2);

XSQL>select * from foo;
x	ttl_timestamp@
------------------------------------------------------------------------------
2	11/29/2019 17:49:58

Selected records: 1

XSQL>select * from Metaschema;
TableName	Temporary	Local	ReadOnly	TTL
------------------------------------------------------------------------------
foo	true	false	false	10

Selected records: 1

The TTL implementation is changed to store timestamp with configured datetime precision (seconds by default), rather than in microseconds. Only ttl_timestamp@ is supported, ttl_count@ is not supported in SQL.

TTL policies are only checked and enforced when a READ_WRITE transaction is committed, before the commit’s first stage.

(An important side effect of this is that the indexes for the newly created objects (i.e. the ones created within the current transaction) are not updated yet, and thus these objects are not checked against the TTL policies. To mitigate this effect, a transaction checkpoint may be added before the commit.)

Related Topics