Re: FW: bitemporal functionality for PostgreSQL

On Feb 1, 2008, at 10:42 AM, Luke Porter wrote:

> All
>
> Is there an interest in developing bitemporal functionality in  
> PostgreSQL
>
> Regards
>
> Luke

I can only speak for myself, but- definitely! Based on the googling I  
did on "bitemporal database", I kind of do this already with  
PostgreSQL. Some of my tables are insert-only and each row includes a  
committed time timestamp. That way, I don't need a separate audit log  
table, and "fixing" someone's mistake is as simple as copying old  
rows. The downside to this is that I need a view to represent the  
current "truth" and calculating the truth is more expensive than a  
simple table would be.

Can you explain in more detail or provide references to how  
PostgreSQL could potentially handle temporal data better?

One idea I had would be to blow the transaction ID up to 128 bits (no  
more wrapping!) and have it represent the nanoseconds since the epoch.

Cheers,
M