Temporal Tables Extension
=========================
1. Introduction
2. Requirements
3. Installation
4. Usage
5. Examples and hints
6. Performance benchmarks
7. Notes
1. Introduction
===============
A temporal table is a table that records the period of time when a row is valid.
There are two types of periods: the application period (also known as valid-time
or business-time) and the system period (also known as transaction-time).
The system period is a column (or a pair of columns) with a system-maintained
value that contains the period of time when a row is valid from a database
perspective. When you insert a row into such table, the system automatically
generates the values for the start and end of the period. When you update or
delete a row from a system-period temporal table, the old row is archived into
another table, which is called the history table.
The application period is a column (or a pair of columns) with an
application-maintained value that contains the period of time when a row is
valid from an application perspective. This column is populated by an
application.
Note that these two time periods do not have to be the same for a single fact.
For example, you may have a temporal table storing data about historical or even
future facts. The application period of these facts differs from the system
period which is set when we add or modify the facts into the table.
Currently Temporal Tables Extension supports the system-period temporal tables
only.
Additional information on temporal databases can be found at the following
sites:
1. http://en.wikipedia.org/wiki/Temporal_database
2. http://www.cs.arizona.edu/~rts/tdbbook.pdf
3. http://metadata-standards.org/Document-library/Documents-by-number/WG2-N1501-N1550/WG2_N1536_koa046-Temporal-features-in-SQL-standard.pdf
2. Requirements
===============
Temporal Tables Extension requires PostgreSQL 9.2 or higher.
3. Installation
===============
To build it, just do this:
$ make
$ make installcheck
$ make install
If you encounter an error such as:
"Makefile", line 8: Need an operator
You need to use GNU make, which may well be installed on your system as gmake:
$ gmake
$ gmake installcheck
$ gmake install
If you encounter an error such as:
make: pg_config: Command not found
Be sure that you have pg_config installed and in your path. If you used a
package management system such as RPM to install PostgreSQL, be sure that the
-devel package is also installed. If necessary tell the build process where to
find it:
$ env PG_CONFIG=/path/to/pg_config make && make installcheck && make install
If you encounter an error such as:
ERROR: must be owner of database regression
You need to run the test suite using a super user, such as the default
"postgres" super user:
$ make installcheck PGUSER=postgres
Once the extension is installed, you can add it to a database. Connect to a
database as a super user and do this:
$ CREATE EXTENSION temporal_tables;
4. Usage
========
Creating a system-period temporal table
---------------------------------------
Temporal Tables Extension uses a general trigger function to maintain
system-period temporal tables behaviour:
versioning(<system_period_column_name>, <history_table_name>, <adjust>)
The function must be fired before INSERT or UPDATE or DELETE on a
system-period temporal table. You are to specify a system period column name, a
history table name and "adjust" parameter (see Updating data section for
details). If there are other triggers defined on a system-period temporal table
you may wish to fire them after the system-period temporal table trigger to have
system-period column value generated. In PostgreSQL triggers are fired in
alphabetical order by name, so to fire them in appropriate order you should
choose the right name for the system-period temporal table trigger.
Let's have a look at a simple example.
First, create a table:
CREATE TABLE employees
(
name text NOT NULL PRIMARY KEY,
department text,
salary numeric(20, 2)
);
In order to make this table system-period temporal table we should first add a
system period column:
ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;
Then we need a history table that contains archived rows of our table. The
easiest way to create it is by using LIKE statement:
CREATE TABLE employees_history (LIKE employees);
Note that a history table does not have to have the same structure as the
original one. For example, you may want to archive some columns of an original
row but ignore others, or a history table may contain some useful information
that is not necessary in the original table. The only two requirements for a
history table are:
1. A history table must contain system period column with the same name and
data type as in the original one.
2. If the history table and the original one both contain the column then the
data type of this column must be the same in these two tables.
Finally we create a trigger on our table to link it with the history table:
CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period',
'employees_history',
true);
Inserting data
--------------
For a user inserting data into a system-period temporal table is similar to
inserting data into a regular table. For example, the following data was
inserted on August 8, 2006 to the table employees:
INSERT INTO employees (name, department, salary)
VALUES ('Bernard Marx', 'Hatchery and Conditioning Centre', 10000);
INSERT INTO employees (name, department, salary)
VALUES ('Lenina Crowne', 'Hatchery and Conditioning Centre', 7000);
INSERT INTO employees (name, department, salary)
VALUES ('Helmholtz Watson', 'College of Emotional Engineering', 18500);
The employees table now contains the following data:
name department salary sys_period
----------- -------------------------------- ------ --------------
Bernard Marx Hatchery and Conditioning Centre 10000 [2006-08-08, )
Lenina Crowne Hatchery and Conditioning Centre 7000 [2006-08-08, )
Helmholtz Watson College of Emotional Engineering 18500 [2006-08-08, )
The history table employees_history is empty:
name department salary sys_period
----------- -------------------------------- ------ --------------
The start of sys_period column represents the time when the row became current.
The trigger generates this value by using a CURRENT_TIMESTAMP value which
denotes the time when the first data change statement was executed in the
current transaction.
Updating data
-------------
When a user updates the values of columns in rows of system-period temporal
table, the trigger inserts a copy of the old row into the associated history
table. If a single transaction makes multiple updates to the same row, only
one history row is generated. For example, the following data was updated on
February 27, 2007 in the table employees:
UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';
The employees table now contains the following data:
name department salary sys_period
----------- -------------------------------- ------ --------------
Bernard Marx Hatchery and Conditioning Centre 10000 [2007-02-27, )
Lenina Crowne Hatchery and Conditioning Centre 7000 [2006-08-08, )
Helmholtz Watson College of Emotional Engineering 18500 [2006-08-08, )
The history table employees_history now contains the following data:
name department salary sys_period
----------- -------------------------------- ------ ------------------------
Bernard Marx Hatchery and Conditioning Centre 10000 [2006-08-08, 2007-02-27)
Update conflicts and time adjustment
------------------------------------
Update conflicts can occur when multiple transactions are updating the same row.
For example, two transactions A and B are executing statements against the
employees table at the same time:
Time Transaction A Transaction B
---- ------------- -------------
T1 INSERT INTO employees (name, salary)
VALUES ('Bernard Marx', 10000);
T2 INSERT INTO employees (name, salary)
VALUES ('Lenina Crowne', 7000);
T3 COMMIT;
T4 UPDATE employees SET salary = 6800
WHERE name = 'Lenina Crowne';
T5 INSERT INTO employees (name, salary)
VALUES ('Helmholtz Watson', 18500);
T6 COMMIT;
After the inserts at T1 and T2 the employees history contains the following
data:
name department salary sys_period
----------- -------------------------------- ------ ----------
Bernard Marx Hatchery and Conditioning Centre 10000 [T1, )
Lenina Crowne Hatchery and Conditioning Centre 7000 [T2, )
The history table employee_history is empty.
At time T4 the trigger must set the start of sys_period column of the row to T1
and insert the following row into the history table:
name department salary sys_period
----------- -------------------------------- ------ ----------
Lenina Crowne Hatchery and Conditioning Centre 7000 [T2, T1)
However T2 > T1 and such row cannot be inserted. In this situation the update
at time T4 would fail with SQLSTATE 22000. To avoid such failures you can
specify "adjust" parameter of the trigger and set it to "true". Then the start
of sys_period column at time T4 is set to time T2 plus a delta (where delta is a
small interval of time, typically equals to 1 microsecond). After this
adjustment and the completion of transaction A the employees table looks like
this:
name department salary sys_period
----------- -------------------------------- ------ --------------
Bernard Marx Hatchery and Conditioning Centre 10000 [T1, )
Lenina Crowne Hatchery and Conditioning Centre 6800 [T2 + delta, )
Helmholtz Watson College of Emotional Engineering 18500 [T1, )
The history table employees_history contains the following data:
name department salary sys_period
----------- -------------------------------- ------ ----------------
Lenina Crowne Hatchery and Conditioning Centre 7000 [T2, T2 + delta)
Deleting data
-------------
When a user deletes data from a system-period temporal table, the trigger adds
rows to the associated history table. For example, the following data was
deleted on 24 December, 2012 from the table employees:
DELETE FROM employees WHERE name = 'Helmholtz Watson';
The employees table now contains the following data:
name department salary sys_period
----------- -------------------------------- ------ --------------
Bernard Marx Hatchery and Conditioning Centre 10000 [2007-02-27, )
Lenina Crowne Hatchery and Conditioning Centre 7000 [2006-08-08, )
The history table employees_history now looks like this:
name department salary sys_period
----------- -------------------------------- ------ ------------------------
Bernard Marx Hatchery and Conditioning Centre 10000 [2006-08-08, 2007-02-27)
Helmholtz Watson College of Emotional Engineering 18500 [2006-08-08, 2012-12-24)
5. Examples and hints
=====================
Using inheritance when creating history tables
----------------------------------------------
In the example above we used LIKE statement to create the history table,
sometimes it is better to use inheritance for this task. For example:
CREATE TABLE employees_history
(
name text NOT NULL PRIMARY KEY,
department text,
salary numeric(20, 2),
sys_period tstzrange NOT NULL
);
Then create the employees table:
CREATE TABLE employees () INHERITS (employees_history);
Pruning history tables
----------------------
History tables are always growing and so are consuming an increasing amount of
storage. There are several ways you can prune old data from a history table:
1. Periodically delete old data from a history table.
2. Use partitioning and detach old partitions from a history table (for more
information on table parititioning see PostgreSQL documentation).
There are many possible rules for pruning old rows:
1. Prune rows older than a certain age.
2. Retain only the latest N versions of a row.
3. Prune rows when a corresponding row is deleted from the system-period
temporal table.
4. Prune rows that satisfy the specified business rules.
You can also set another tablespace for a history table to move it on a more
cheap storage.
Using system-period temporal tables for data audit
--------------------------------------------------
It is possible to use system-period temporal tables for data audit. For
example, you can add the following triggers to save user that modified or
deleted the current row:
CREATE FUNCTION employees_modify()
RETURNS TRIGGER AS $$
BEGIN
NEW.user_modified = SESSION_USER;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_modify
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE employees_modify();
CREATE FUNCTION employees_delete()
RETURNS TRIGGER AS $$
BEGIN
NEW.user_deleted = SESSION_USER;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER employees_delete
BEFORE INSERT ON employees_history
FOR EACH ROW EXECUTE PROCEDURE employees_delete();
6. Performance benchmarks
=========================
TODO
7. Notes
========
Temporal Tables Extension is distributed under the terms of BSD 2-clause
license. See LICENSE or http://www.opensource.org/licenses/bsd-license.php for
more details.
