On Tue, Oct 18, 2016 at 9:39 AM, Guyren Howe <guyren@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
