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
