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@xxxxxxxxxxxxxxx>
To: "Rodrigo Sakai" <rodrigo.sakai@xxxxxxxxxxx>
Cc: "Michael Glaesemann" <grzm@xxxxxxxxxxxxx>;
<pgsql-general@xxxxxxxxxxxxxx>
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
