Re: Temporal Databases

On Fri, 2006-02-24 at 00:20 -0300, Rodrigo Sakai wrote:
> It's a good solution, but not what I'm looking for.
> I'm looking for something implemented inside the database, like the
> flashback functionality of oracle 10g.

I think you need to be clear about why you want this: do you want this
as a recovery mechanism or to satisfy general temporal queries?

You also need to read much recent work on the use of temporal results in
BI applications, starting with Kimball et al's books. BI applications
already frequently address these issues via specific design patterns,
rather than requiring a specific implementation within the dbms.

IMHO this is the primary reason why no mainstream dbms provides an
in-dbms solution to this problem area for general temporal queries and
why flashback functionality is essentially a data recovery technique.

To support this you would need
- a transaction time table - inserted into by each commit (only), so you
can work out which transactions have committed and which haven't at any
point in history
- a mechanism to avoid using the clog and subtrans, since those caches
are updated in real time, so they would not give temporal results as
they currently stand, plus a mechanism to override the individual commit
hint bits that are stored on each row in the database - probably via a
new kind of Snapshot with its own local Xid result cache
- a mechanism to track the xmin up to which a table has been VACUUMed
(which is probably going to exist for 8.2ish), so you can throw an error
to say "no longer possible to answer query for time T"
- potentially a mechanism to control which xmin was selected by VACUUM,
so that you could maintain explicit control over how much history was
kept

...but it would not be easily accepted into the main line, I would
guess, without some careful planning to ensure low-zero impact for
non-users.

A much easier way is to start a serialized transaction every 10 minutes
and leave the transaction idle-in-transaction. If you decide you really
need to you can start requesting data through that transaction, since it
can "see back in time" and you already know what the snapshot time is
(if you record it). As time moves on you abort and start new
transactions... but be careful that this can effect performance in other
ways.

Best Regards, Simon Riggs