search

arkhipov / temporal_tables

Temporal Tables PostgreSQL Extension
BSD 2-Clause "Simplified" License
927 stars 46 forks source link

Support for other range types #26

Open lifepillar opened 7 years ago

lifepillar commented 7 years ago

Any plan to support other range types, like int4range?

arkhipov commented 7 years ago

Hello @lifepillar, Could you please clarify your idea?

lifepillar commented 7 years ago

The idea is to use integers for versioning, where every transaction creates a new version (i.e., increases the current version number by one). Something similar to what is described in this paper (except that I am not interested in tracking schema changes, just data). One motivation is that integers would be monotonically increasing, while timestamps may be affected by errors.

Actually, I haven't thought too hard about it: it may be that it's something more easily said than done. I was mainly curious to know if that is an option that you have already considered (and, possibly, ruled out).

arkhipov commented 7 years ago

I have seen it implemented in some ORMs (Hibernate Envers is one the examples). The main issue of this approach is the version table. It may become a bottleneck, so you often have to divide your table graph in a set of independent strongly connected components which complicates the system design.

I do not think that possible time errors are a real problem. Even when using the version table, you still may run into situations when due to NTP corrections or CPU clock skew the next timestamp turns out to be less than the previous one. You just move the problem from the history tables to the revision table. If the extension runs into such a situation, it just uses the next available timestamp as the 'now' value.

And what is more, you still cannot get a valid database snapshot at some specific point of time (because you still cannot put different records with the same revision on a single timeline). Neither does the temporal tables extension allow you to get such a snapshot.