4 ms·

Row-level version control with PostgreSQL (2014)

2 points by eriknstr 10y ago

eriknstr 10y ago
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_trigger-sql https://gist.github.com/myitcv/9212407#file-time_travel_trig...
elchief 10y ago
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
eriknstr 10y ago
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.

2 points by eriknstr 10y ago

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_trigger-sql https://gist.github.com/myitcv/9212407#file-time_travel_trig...

elchief 10y ago

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

eriknstr 10y ago

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.