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

