Repository files navigation

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-timeor 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-maintainedvalue that contains the period of time when a row is valid from a databaseperspective. When you insert a row into such table, the system automaticallygenerates the values for the start and end of the period. When you update ordelete a row from a system-period temporal table, the old row is archived intoanother table, which is called the history table.

The application period is a column (or a pair of columns) with anapplication-maintained value that contains the period of time when a row isvalid from an application perspective. This column is populated by anapplication.

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 evenfuture facts. The application period of these facts differs from the systemperiod which is set when we add or modify the facts into the table.

Currently, Temporal Tables Extension supports the system-period temporal tablesonly.

Additional information on temporal databases can be found at the followingsites:

1. Wikipedia: Temporal Database
2. Developing Time-Oriented Database Applications in SQL, Richard T. Snodgrass, Morgan Kaufmann Publishers, Inc., San Francisco, July, 1999, 504+xxiii pages, ISBN 1-55860-436-7.
3. WG2 N1536. WG3: KOA-046. Temporal Features in SQL standard. Krishna Kulkarni,. IBM Corporation

There isa fantastic tutorialon using and querying temporal tables in PostgreSQL with the Temporal TablesExtension written byClark Dave.

Temporal Tables Extension requires PostgreSQL 9.2 or higher.

If you are running Linux, the easiest way to install the extension is to to usethePGXN client.

$ pgxn install temporal_tables

Or if you prefer to stick with the good old Make, you can set up the extensionlike this:

$ make$ make install$ make installcheck

If you encounter an error such as:

make: no target to make.

You need to use GNU make, which may well be installed on your system asgmake:

$ gmake$ gmake install$ gmake installcheck

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 apackage management system such as RPM to install PostgreSQL, be sure that the-devel package is also installed. If necessary tell the build process where tofind it:

$ env PG_CONFIG=/path/to/pg_config make && make install && make installcheck

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

If you are running Windows, you need to run theMSBuildcommand in theVisual Studio command prompt.

> msbuild /p:pgversion=9.4 /p:configuration=Release /p:platform=x64

The platforms available are x64 and x86 and the PostgreSQL versions are 9.2, 9.3, 9.4, 9.5, 9.6, 10, 11, 12, 13, 14, 15.

Or you can download the latest released ziphere.

Then you must copy the DLL from the project into the PostgreSQL'slibdirectory and the.sql and.control files into the directoryshare\extension.

> copy x64\9.4\temporal_tables.dll "C:\Program Files\PostgreSQL\9.4\lib"> copy *.control "C:\Program Files\PostgreSQL\9.4\share\extension"> copy *.sql "C:\Program Files\PostgreSQL\9.4\share\extension"

Once the extension is installed, you can add it to a database. Connect to adatabase as a super user and do this:

$ CREATE EXTENSION temporal_tables;

Temporal Tables Extension uses a general trigger function to maintainsystem-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 asystem-period temporal table. You are to specify a system period column name, ahistory table name and "adjust" parameter (see Updating data section fordetails).

Let's have a look at a simple example.

First, create a table:

CREATETABLEemployees(  nametextNOT NULLPRIMARY KEY,  departmenttext,  salarynumeric(20,2));

In order to make this table system-period temporal table we should first add asystem period column:

ALTERTABLE employees ADD COLUMN sys_period tstzrangeNOT NULL;

Then we need a history table that contains archived rows of our table. Theeasiest way to create it is by using LIKE statement:

CREATETABLEemployees_history (LIKE employees);

Note that a history table does not have to have the same structure as theoriginal one. For example, you may want to archive some columns of an originalrow but ignore others, or a history table may contain some useful informationthat is not necessary in the original table. The only two requirements for ahistory table are:

1. A history table must contain system period column with the same name anddata type as in the original one.
2. If the history table and the original one both contain the column then thedata 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:

CREATETRIGGERversioning_triggerBEFORE INSERTORUPDATEORDELETEON employeesFOR EACH ROW EXECUTE PROCEDURE versioning('sys_period','employees_history',                                          true);

For a user inserting data into a system-period temporal table is similar toinserting data into a regular table. For example, the following data wasinserted 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:

The history table employees_history is empty:

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 whichdenotes the time when the first data change statement was executed in thecurrent transaction.

When a user updates the values of columns in rows of system-period temporaltable, the trigger inserts a copy of the old row into the associated historytable. If a single transaction makes multiple updates to the same row, onlyone history row is generated. For example, the following data was updated onFebruary 27, 2007 in the table employees:

UPDATE employeesSET salary=11200WHERE name='Bernard Marx';

The employees table now contains the following data:

The history table employees_history now contains the following data:

Update conflicts can occur when multiple transactions are updating the same row.For example, two transactions A and B are executing statements against theemployees table at the same time:

After the inserts at T1 and T2, the employees history contains the followingdata:

The history table employee_history is empty.

At time T4 the trigger must set the start of sys_period column of the row to T1and insert the following row into the history table:

However, T2 > T1 and the row cannot be inserted. In this situation, the updateat time T4 would fail with SQLSTATE 22000. To avoid such failures, you canspecify "adjust" parameter of the trigger and set it to "true". Then the startof sys_period column at time T4 is set to time T2 plus delta (a small intervalof time, typically equals to 1 microsecond). After this adjustment and thecompletion of transaction A, the employees table looks like this:

The history table employees_history contains the following data:

When a user deletes data from a system-period temporal table, the trigger addsrows to the associated history table. For example, the following data wasdeleted on 24 December, 2012 from the table employees:

DELETEFROM employeesWHERE name='Helmholtz Watson';

The employees table now contains the following data:

The history table employees_history now looks like this:

Instead of using CURRENT_TIMESTAMP, you may want to set a custom system time forversioning triggers. It is useful for creating a data warehouse from a systemthat recorded a system time and you want to use that time instead.

SELECT set_system_time('1985-08-08 06:42:00+08');

To revert it back to the default behaviour, call the function withNULL as itsargument.

SELECT set_system_time(NULL);

If theset_system_time function is issued within a transaction that is lateraborted, all the changes are undone. If the transaction is committed, thechanges will persist until the end of the session.

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:

CREATETABLEemployees_history(  nametextNOT NULL,  departmenttext,  salarynumeric(20,2),  sys_period tstzrangeNOT NULL);

Then create the employees table:

CREATETABLEemployees (PRIMARY KEY(name)) INHERITS (employees_history);

History tables are always growing and so are consuming an increasing amount ofstorage. 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 moreinformation 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-periodtemporal 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 cheaperstorage.

It is possible to use system-period temporal tables for data audit. Forexample, you can add the following triggers to save user that modified ordeleted the current row:

CREATEFUNCTIONemployees_modify()RETURNS TRIGGERAS $$BEGINNEW.user_modified=SESSION_USER;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATETRIGGERemployees_modifyBEFORE INSERTORUPDATEON employeesFOR EACH ROW EXECUTE PROCEDURE employees_modify();CREATEFUNCTIONemployees_delete()RETURNS TRIGGERAS $$BEGINNEW.user_deleted=SESSION_USER;  RETURN NEW;END;$$ LANGUAGE plpgsql;CREATETRIGGERemployees_deleteBEFORE INSERTON employees_historyFOR EACH ROW EXECUTE PROCEDURE employees_delete();

Temporal Tables Extension is distributed under the terms of BSD 2-clauselicense. See LICENSE orhttp://www.opensource.org/licenses/bsd-license.php formore details.