arkhipov / temporal_tables

Temporal Tables PostgreSQL Extension
BSD 2-Clause "Simplified" License
937 stars 47 forks source link

Ability to populate history table with insert #22

Open sheerun opened 8 years ago

sheerun commented 8 years ago

Hey,

I'd like history table to contain all the data, not only modified rows. Would it be possible to add another parameter to temporal_tables, so even an INSERT to original table creates a row in history table?

sheerun commented 8 years ago

Actually I don' even want to add sys_period to original table. I just want history table to automatically track any changes in the original table.

arkhipov commented 8 years ago

Yes, it is possible. Actually, that was also my first idea of how to implement system versioning, but not how the people who wrote the SQL standard decided it should be implemented. I will see if I can come up with something over this weekend.

sheerun commented 8 years ago

You could take a look at chronomodel that uses similar approach: https://github.com/ifad/chronomodel They just version whole schema, not only a table, and it's not as integrated with postgres as temporal_tables

arkhipov commented 8 years ago

This turned out to be more difficult than I expected, so I gave it up after some time. The worst thing about this approach is that when you insert a new record into the table and then update that record in the same transaction, you have to find the history record you inserted before in order to update it. So, you must have a unique constraint in the original table and the corresponding index (which includes the system_period column in addition to the columns of the unique constraint of the original table).

mlt commented 7 years ago

I might be late for the party, but doesn't table inheritance as described in readme solve the original problem? You'll get all records including current ones.

arkhipov commented 7 years ago

I think the main point was to get rid of the sys_period column in the original table. Unfortunately, this approach comes at the price of performance loss since you will have to add two additional indices to the original and history tables and check the existence of a record any time you update it.