On Tue, Oct 18, 2016 at 9:39 AM, Guyren Howe <guyren@gmail.com> wrote:
> I would like to use Postgres as an immutable data store. A subject table would have a timestamp column, and that
wouldbe added to what would otherwise be the primary key.
>
> Trap updates and turn them into inserts. Have an associated _deleted table. Trap deletes and turn them into inserts
ofthe primary key into that table.
>
> Create a view that only shows the non-deleted records with the most recent timestamps.
>
> Stored procedure to do all that to a table. Event trigger to drop and re-create the view on changes to the table
columns.
>
> I think that's about it. Seems an obvious and nice general-purpose thing to do. It also seems like something someone
mighthave already done, but googling "immutable postgres" pulls up things about writing functions.
>
> Does anyone know of a project that has already done this?
The term you need is 'temporal' and searching for that you'll find
several projects providing something like that for PostgreSQL.
I've done some projects using temporal (and bitemporal) models based
on Richard Snodgrass's excellent book (which I hear is widely read at
utility companies among others), without any special library support:
http://www.cs.arizona.edu/~rts/tdbbook.pdf
His work influenced the SQL standard which I expect/hope is inspiring
those projects. SQL:2011 has a temporal feature that does this sort
of thing automatically.
--
Thomas Munro
http://www.enterprisedb.com
