Re: Immutable datastore library?

On Tue, Oct 18, 2016 at 9:39 AM, Guyren Howe <guyren(at)gmail(dot)com> wrote:
> I would like to use Postgres as an immutable data store. A subject table would have a timestamp column, and that would be 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 of the 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 might have 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