Row-level version control with PostgreSQL (2014)

It's been about a day since I found this blog post. I considered using it today as I began writing a CRUD application for some data that will be modified over time and where I might want to see older revisions, but I then quickly realized that the solution in the blog post I had found will not work with a lot of common UNIQUE constraints that I imposed on my tables, so the search for row-level version control with PostgreSQL continues.

Searched Google for postgresql revision and found exactly what I was looking for; this blog post.

The blog post talks about AWS but I tested it using PostgreSQL 9.4 on Fedora 23 and it worked fine.

Briefly;

sudo dnf install postgresql-server postgresql-contrib

sudo postgresql-setup --initdb --unit postgresql

sudo -u postgres createuser -d $USER

createdb

sudo -u postgres psql -c 'create extension timetravel;'

psql

Then I pasted the function declaration [0] from the blog post into psql.

After that I pasted the SQL commands given under the heading "The functions in action: fruit trigger" in the blog post.

Next I tested with some entries

insert into fruits (name) values ('banana');

delete from fruits where name = 'banana';

insert into fruits (name) values ('apple');

update fruits set name = 'pear' where name = 'apple';

select * from fruits;

     id |  name  |          valid_from           |           valid_to            
    ----+--------+-------------------------------+-------------------------------
      1 | banana | 2016-08-24 01:14:52.395264+02 | 2016-08-24 01:15:19.6916+02
      2 | pear   | 2016-08-24 01:16:09.012415+02 | infinity
      2 | apple  | 2016-08-24 01:15:30.515809+02 | 2016-08-24 01:16:09.012415+02
    (3 rows)

The blog post mentions a couple of known limitations/problems, including the following:

>This whole process only safely works within a transaction; our version does not check that it is called within the context of a transaction

Does anyone know of a similar thing to what was described in the post, which would also be open source (preferably based on PostgreSQL, but solutions based on other open source DBs are welcome too), but which would not suffer from this problem?

[0]: https://gist.github.com/myitcv/9212407#file-time_travel_trig...

I don't understand how this would work with foreign keys.

SQL:2011 Temporal specs how this should work, and it's in DB2 and MSSQL, but nothing yet in PG