Temporal tables with PostgreSQL

By Mouhamadou Diaw

In this blog we are going to talk about a nice extension in PostgreSQL: temporal_tables. This extension provides support for temporal tables.
What is a temporal table? Just a table that tracks the period of validity of a row.
When implemented, this feature allows you to specify that old rows are archived into another table (that is called the history table). This can be useful for many purposes
-Audit
-Comparison
-Checking table state in the past
First we have to install the temporal_table extension. We are going to use the pgxn client to install the extension.
Install the yum repository for PostgreSQL

[root@pgserver1 ~]# rpm -ivh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-oraclelinux96-9.6-3.noarch.rpm
Retrieving https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-oraclelinux96-9.6-3.noarch.rpm
warning: /var/tmp/rpm-tmp.3q9X12: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:pgdg-oraclelinux96-9.6-3 ################################# [100%]
[root@pgserver1 ~]#

And after we install the pgxn client

root@pgserver1 ~]# yum search pgxn
Loaded plugins: langpacks, ulninfo
pgdg96 | 4.1 kB 00:00:00
(1/2): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:00
(2/2): pgdg96/7Server/x86_64/primary_db | 127 kB 00:00:00
==================================================== N/S matched: pgxn =====================================================
pgxnclient.x86_64 : Command line tool designed to interact with the PostgreSQL Extension Network
Name and summary matches only, use "search all" for everything.


[root@pgserver1 ~]# yum install pgxnclient.x86_64
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package pgxnclient.x86_64 0:1.2.1-2.rhel7 will be installed
....
....
Installed:
pgxnclient.x86_64 0:1.2.1-2.rhel7
Complete!
[root@pgserver1 ~]#

And finally we can install the extension

[root@pgserver1 ~]# pgxn install temporal_tables --pg_config=/u01/app/PostgreSQL/9.6/bin/pg_config
INFO: best version: temporal_tables 1.1.1
INFO: saving /tmp/tmpJit39m/temporal_tables-1.1.1.zip
INFO: unpacking: /tmp/tmpJit39m/temporal_tables-1.1.1.zip
INFO: building extension
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -DMAP_HUGETLB=0x40000 -fpic -I. -I./ -I/u01/app/PostgreSQL/9.6/include/postgresql/server -I/u01/app/PostgreSQL/9.6/include/postgresql/internal -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o temporal_tables.o temporal_tables.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -DMAP_HUGETLB=0x40000 -fpic -I. -I./ -I/u01/app/PostgreSQL/9.6/include/postgresql/server -I/u01/app/PostgreSQL/9.6/include/postgresql/internal -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -c -o versioning.o versioning.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -DMAP_HUGETLB=0x40000 -fpic -shared -o temporal_tables.so temporal_tables.o versioning.o -L/u01/app/PostgreSQL/9.6/lib -L/opt/local/Current/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/PostgreSQL/9.6/lib',--enable-new-dtags
INFO: installing extension
/bin/mkdir -p '/u01/app/PostgreSQL/9.6/lib/postgresql'
/bin/mkdir -p '/u01/app/PostgreSQL/9.6/share/postgresql/extension'
/bin/mkdir -p '/u01/app/PostgreSQL/9.6/share/postgresql/extension'
/bin/mkdir -p '/u01/app/PostgreSQL/9.6/doc/postgresql/extension'
/usr/bin/install -c -m 755 temporal_tables.so '/u01/app/PostgreSQL/9.6/lib/postgresql/temporal_tables.so'
/usr/bin/install -c -m 644 .//temporal_tables.control '/u01/app/PostgreSQL/9.6/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//temporal_tables--1.1.1.sql .//temporal_tables--1.0.0--1.0.1.sql .//temporal_tables--1.0.1--1.0.2.sql .//temporal_tables--1.0.2--1.1.0.sql .//temporal_tables--1.1.0--1.1.1.sql '/u01/app/PostgreSQL/9.6/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//README.md '/u01/app/PostgreSQL/9.6/doc/postgresql/extension/'
[root@pgserver1 ~]#

Once the installation done, we can load it in our database.

[postgres@pgserver1 extension]$ psql
Password:
psql.bin (9.6.1)
Type "help" for help.
postgres=# CREATE EXTENSION temporal_tables;
CREATE EXTENSION
postgres=#

We can then verify that the temporal extension is now present in our database.

postgres=# dx
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+-----------------------------------------
adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
temporal_tables | 1.1.1 | public | temporal tables
(3 rows)

postgres=# dx+ temporal_tables
Objects in extension "temporal_tables"
Object Description
----------------------------------------------------
function set_system_time(timestamp with time zone)
function versioning()
(2 rows)

For the demonstration, we create the following table Customers

CREATE TABLE Customers (
CustNo SERIAL NOT NULL,
CustName VARCHAR(30) NOT NULL,
start_date timestamp NOT NULL DEFAULT now(),
PRIMARY KEY (CustNo)
);

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

postgres=# ALTER TABLE Customers ADD COLUMN sys_period tstzrange NOT NULL;
ALTER TABLE

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

postgres=# CREATE TABLE Customers_history (LIKE Customers);
CREATE TABLE

Finally we create a trigger on our table to link it with the history table

postgres=# CREATE TRIGGER customers_hist_trigger BEFORE INSERT OR UPDATE OR DELETE ON Customers FOR EACH ROW
EXECUTE PROCEDURE versioning('sys_period', 'Customers_history', true);
CREATE TRIGGER
postgres=#

Now Let’s insert data into customers

insert into customers (custname,start_date) values ('HP','2013-08-05 00:00:00');
insert into customers (custname,start_date) values ('IBM','2014-10-10 00:00:00');
insert into customers (custname,start_date) values ('DBI','2017-01-07 00:00:00');
insert into customers (custname) values ('DHL');

We can see below rows in customers.
For example the row concerning IBM was inserted on 2017-01-26 10:48:49. Information stored in the sys_period column and represents the starting validity of the row. Note the borders [,). The lower bound is [ and thus inclusive. The upper bound is ) which means it is exclusive.
For IBM [“2017-01-26 10:48:49.768031+01”,) means
Start of validity: 2017-01-26 10:48:49.
End of validity: infinity (because there is nothing).

postgres=# table customers;
custno | custname | start_date | sys_period
--------+----------+----------------------------+------------------------------------
1 | IBM | 2014-10-10 00:00:00 | ["2017-01-26 10:48:49.768031+01",)
2 | DBI | 2017-01-07 00:00:00 | ["2017-01-26 10:48:49.778487+01",)
3 | DHL | 2017-01-26 10:48:49.841405 | ["2017-01-26 10:48:49.841405+01",)
4 | HP | 2013-08-05 00:00:00 | ["2017-01-26 10:50:21.275201+01",)
(4 rows)

The table Customers_history is empty. This is normal because no update or delete are done, just we have inserted rows.

postgres=# table customers_history;
custno | custname | start_date | sys_period
--------+----------+------------+------------
(0 rows)
postgres=#

Let’s do an update on customers, but before let’s display the current time.
postgres=# select now();
now
-------------------------------
2017-01-26 11:02:32.381634+01
(1 row)


postgres=# update customers set custname='HPSuisse' where custno=4;
UPDATE 1
postgres=#

Verifying again the customers table, we can see that the validity of row concerning HPsuisse starts at 2017-01-26 11:02:46

postgres=# table customers;
custno | custname | start_date | sys_period
--------+----------+----------------------------+------------------------------------
1 | IBM | 2014-10-10 00:00:00 | ["2017-01-26 10:48:49.768031+01",)
2 | DBI | 2017-01-07 00:00:00 | ["2017-01-26 10:48:49.778487+01",)
3 | DHL | 2017-01-26 10:48:49.841405 | ["2017-01-26 10:48:49.841405+01",)
4 | HPSuisse | 2013-08-05 00:00:00 | ["2017-01-26 11:02:46.347574+01",)
(4 rows)

If we now query the table customers_history, we can see the row updated on the table customers with the validity of the row.

postgres=# table customers_history;
custno | custname | start_date | sys_period
--------+----------+---------------------+-------------------------------------------------------------------
4 | HP | 2013-08-05 00:00:00 | ["2017-01-26 10:50:21.275201+01","2017-01-26 11:02:46.347574+01")

Let’s do a delete on the table customers

postgres=# select now();
now
-------------------------------
2017-01-26 11:32:12.229105+01
(1 row)


postgres=# delete from customers where custno=3;
DELETE 1

Below rows in table customers

postgres=# table customers;
custno | custname | start_date | sys_period
--------+----------+---------------------+------------------------------------
1 | IBM | 2014-10-10 00:00:00 | ["2017-01-26 10:48:49.768031+01",)
2 | DBI | 2017-01-07 00:00:00 | ["2017-01-26 10:48:49.778487+01",)
4 | HPSuisse | 2013-08-05 00:00:00 | ["2017-01-26 11:02:46.347574+01",)
(3 rows)

And in the history table, we can see a new row with the validity date.

postgres=# table customers_history;
custno | custname | start_date | sys_period
--------+----------+----------------------------+-------------------------------------------------------------------
4 | HP | 2013-08-05 00:00:00 | ["2017-01-26 10:50:21.275201+01","2017-01-26 11:02:46.347574+01")
3 | DHL | 2017-01-26 10:48:49.841405 | ["2017-01-26 10:48:49.841405+01","2017-01-26 11:32:15.370438+01")
(2 rows)

Conclusion
In this blog we see how temporal tables can be implemented with PostgreSQL using extention temporal_table. This feature can help for auditing, archiving,…
And the history table can be moved to lower storage.