Re: Temporal features in PostgreSQL

On 2/13/13 10:06 PM, Vlad Arkhipov wrote:
>> - I don't need to deal with update conflicts, because I use clock_timestamp() instead of current_timestamp.
> You can still come across a conflict even with clock_timestamp(). What if clocks go back during the time synchronization? Even if you have absolutely precious clocks, there are may be clock skew on different CPUs, low system clock time resolution, etc.

Sorry for the late reply, catching up on email...

If you want to track the history of something, "measured time" is absolutely NOT the way to do it. I use the term "measured time" to differentiate from the real-world concept of time that is forever flowing forward from one instant to the next. The problem with measured time is that it's incredibly easy to screw up. Clocks out of sync, clocks running backwards, etc. Heck, it's not even clear what time you should actually use: transaction start, wallclock, or transaction end.

For any kind of history tracking to actually be robust you have no choice but to link one history record to another so that you can actually walk down a chain. Of course you might want to capture a timestamp as part of your history metadata, but you better be ready to deal with a history record with a timestamp that is *earlier* than the prior history record.

BTW, we've been working on a generic history implementation at work; hopefully we'll be able to release it relatively soon.