Re: Temporal Databases - Mailing list pgsql-general

Hello Simon, sorry for the late answer! What I really need is a temporal
database that can check some temporal constraints, like, imagine you have
two tables:



employee (emp_id, name, address, start_date, end_date)



where [start_date, end_date] is the period that the employee worked or still
works in the company!



the second table keeps all salary that this employee have along the time!



salary (emp_id, salary, start_date, end_date)



where [start_date, end_date] is the period that the salary was valid for
this employee.



So, is necessary to check if the period of salary is inside (exists) in
employee. Almost like foreign keys, where you have to verify if
salary.emp_id exists in employee.emp_id.



So, I was thinking in extend the triggers that check the RI constraints. And
do others modifications.



I know that oracle's flachback functionality is for recovery, but it
implements a kind of temporal functionality because it keeps track the exact
time (transaction time) that a transaction commited.



            Thanks for your help!


----- Original Message -----
From: "Simon Riggs" <simon@2ndquadrant.com>
To: "Rodrigo Sakai" <rodrigo.sakai@poli.usp.br>
Cc: "Michael Glaesemann" <grzm@myrealbox.com>;
<pgsql-general@postgresql.org>
Sent: Friday, February 24, 2006 8:41 AM
Subject: Re: [GENERAL] 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
>
>
>
>