
Row-level version control with PostgreSQL (2014) - eriknstr
http://blog.myitcv.org.uk/2014/02/25/row-level-version-control-with-postgresql.html
======
eriknstr
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.

------
eriknstr
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...](https://gist.github.com/myitcv/9212407#file-
time_travel_trigger-sql)

------
elchief
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

