Re: Temporal Databases - Mailing list pgsql-general

bkw@weisshuhn.de (Bernhard Weisshuhn) writes:
> On Thu, Feb 23, 2006 at 03:34:36PM -0300, Rodrigo Sakai <rodrigo.sakai@poli.usp.br> wrote:
>> I'm focus on temporal databases (not temporary), and I want to know
>> if anyone here is studying this tecnologies too. So, we can
>> exchange knowlegment. Specifically, anyone who is trying to
>> implement on postgresql the aspect of time (temporal).  These
>> researches are lead by Richard Snodgrass. So, anyone who have
>> something to share, please contact me!
>
> Not sure if I understand the problem correctly, but the
> contrib/spi/timetravel module does something which I think may be
> what you are talking about.
>
>   http://developer.postgresql.org/cvsweb.cgi/~checkout~/pgsql/contrib/spi/README.timetravel
>
> The docs are a bit cryptic but you should be able to grasp if it
> suits your needs. Basically you can go back to any point in tabe for
> a timetravel table and make date based comparisons.

If I recall properly, the last time one of our folks looked at the
timetravel module, they found that it hadn't been updated to be
compatible with modern versions of PostgreSQL.

FYI, we're interested in this too; one of the neato new features in
Slony-I 1.1 was "log shipping," which had a number of alterations made
to it to ensure it would provide useful information for constructing
temporal databases.

Notably, log shipping includes the timestamp of the time of each SYNC
on the source system, which gives at least *approximate* temporal
information as to when updates took place.

The intent of that is to allow loading "log shipping" data into a
modified database schema where two changes take place:

 - Tables are augmented with start/end dates

 - Three triggers affect the three operations, populating those dates:

    - INSERT sets start = time of SYNC, end = infinity
    - UPDATE alters the last record to change the end date to time of SYNC,
      and inserts the new row with start = time of SYNC, end = infinity
    - DELETE alters the last record to change the end date to time of SYNC

That maps a "stateful" database onto a temporal form.

It doesn't provide a way to address making retroactive changes, but
seeing as how this is being fed by "operational/production" style
systems, retroactivity normally isn't something "online" systems cope
with terribly much anyways.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/linux.html
Rules of  the Evil Overlord #76.  "If the hero  runs up to my  roof, I
will not run up after him and  struggle with him in an attempt to push
him  over the  edge. I  will also  not  engage him  at the  edge of  a
cliff. (In the middle of a  rope-bridge over a river of molten lava is
not even worth considering.)" <http://www.eviloverlord.com/>