Thread: Temporal Databases

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.

Hope this helps, forgive my ignorance if if doesn't.
  bkw

There is also a description of an implementation for timetravel
at http://www.varlena.com/GeneralBits/122.php

If you would like to discuss this further, please let me know.
I've know postgres's and illustra's timetravel implementations.

--elein
elein@varlena.com

On Thu, Feb 23, 2006 at 10:52:13PM +0100, Bernhard Weisshuhn wrote:
> 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.
>
> Hope this helps, forgive my ignorance if if doesn't.
>   bkw
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On Feb 24, 2006, at 3:34 , Rodrigo Sakai 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!

Nice to meet you, Rodrigo. This is an area that I hope to do some
work on as well.

Another resource that you may find useful is Date, Darwen, and
Lorentzos' "Temporal Data and the Relational Model" which starts from
temporal data in particular and proposes a more generalized
"interval"  data type constructor. Lorentzos has also published some
articles on IXSQL[2] which you may find interesting in its extensions
to SQL in particular, rather than the relational model.

You may also be interested to know there is an no-longer documented
tinterval type in Postgres. I don't believe it's even been tested in
recent years, but you may want to look at its implementation.

Michael Glaesemann
grzm myrealbox com

[1](http://www.amazon.com/gp/product/1558608559/)
[2] Nikos A. Lorentzos and Yannis G. Mitsopoulos, "SQL Extension for
Interval Data",  IEEE Transactions On Knowledge And Data
Engineering,  Vol.  9,  No.  3,  May/June  1997

  Thanks for all people. And Michael, I think your researches are very close
to my researches. I'm looking for partnership (I hope this is the correct
word).
  Although, this area is very old (about 20 years), there is not much
implementation. I saw the architecture of BTPGSQL, and is a usual
implementation where it was developed a API to do the interface between the
aplication and the data. 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.
  And thanks Michael, I will have a look on the Date's article.

----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: "Rodrigo Sakai" <rodrigo.sakai@poli.usp.br>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, February 23, 2006 10:43 PM
Subject: Re: [GENERAL] Temporal Databases


>
> On Feb 24, 2006, at 3:34 , Rodrigo Sakai 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!
>
> Nice to meet you, Rodrigo. This is an area that I hope to do some  work on
> as well.
>
> Another resource that you may find useful is Date, Darwen, and  Lorentzos'
> "Temporal Data and the Relational Model" which starts from  temporal data
> in particular and proposes a more generalized  "interval"  data type
> constructor. Lorentzos has also published some  articles on IXSQL[2] which
> you may find interesting in its extensions  to SQL in particular, rather
> than the relational model.
>
> You may also be interested to know there is an no-longer documented
> tinterval type in Postgres. I don't believe it's even been tested in
> recent years, but you may want to look at its implementation.
>
> Michael Glaesemann
> grzm myrealbox com
>
> [1](http://www.amazon.com/gp/product/1558608559/)
> [2] Nikos A. Lorentzos and Yannis G. Mitsopoulos, "SQL Extension for
> Interval Data",  IEEE Transactions On Knowledge And Data  Engineering,
> Vol.  9,  No.  3,  May/June  1997
>
>

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

Simon Riggs wrote:

>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.
>
>
>

We're currently prototyping a system (still very much in it's infancy)
that uses the Slony-I shipping mechanism to build an off line temporal
system for point in time reporting purposes.  The idea being that the
log shipping files will contain only the committed inserts, updates and
deletes.  Those log files are then applied to an off line system which
has a  trigger defined on each table that re-write the statements, based
on the type of statement, into a temporally sensitive format.

If you want to get an exact point in time snapshot with this approach,
you are going to have to have timestamps on all table in your source
database that contain the exact time of the statement table.  Otherwise,
a best guess (based on the time the slony sync was generated) is the
closest that you will be able to come.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

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/>

On Fri, Feb 24, 2006 at 11:56:58AM -0500, Brad Nicholson wrote:
> Simon Riggs wrote:
>
> >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.
>
> We're currently prototyping a system (still very much in it's infancy)
> that uses the Slony-I shipping mechanism to build an off line temporal
> system for point in time reporting purposes.  The idea being that the
> log shipping files will contain only the committed inserts, updates and
> deletes.  Those log files are then applied to an off line system which
> has a  trigger defined on each table that re-write the statements, based
> on the type of statement, into a temporally sensitive format.
>
> If you want to get an exact point in time snapshot with this approach,
> you are going to have to have timestamps on all table in your source
> database that contain the exact time of the statement table.  Otherwise,
> a best guess (based on the time the slony sync was generated) is the
> closest that you will be able to come.

Have you looked at using timelines in PITR for stuff like this?
Depending on your needs, it might be less work to do it this way.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

A long time ago, in a galaxy far, far away, jnasby@pervasive.com ("Jim C. Nasby") wrote:
> On Fri, Feb 24, 2006 at 11:56:58AM -0500, Brad Nicholson wrote:
>> Simon Riggs wrote:
>>
>> >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.
>>
>> We're currently prototyping a system (still very much in it's infancy)
>> that uses the Slony-I shipping mechanism to build an off line temporal
>> system for point in time reporting purposes.  The idea being that the
>> log shipping files will contain only the committed inserts, updates and
>> deletes.  Those log files are then applied to an off line system which
>> has a  trigger defined on each table that re-write the statements, based
>> on the type of statement, into a temporally sensitive format.
>>
>> If you want to get an exact point in time snapshot with this approach,
>> you are going to have to have timestamps on all table in your source
>> database that contain the exact time of the statement table.  Otherwise,
>> a best guess (based on the time the slony sync was generated) is the
>> closest that you will be able to come.
>
> Have you looked at using timelines in PITR for stuff like this?
> Depending on your needs, it might be less work to do it this way.

No.

That would require taking a whole lot of PITR snapshots in order to
get answers for previous days and such.

We've got applications that are "pretty stateful," where the point of
"temporalizing" is that this allows achieving  detailed history that
transforms the data into a fundamentally richer form.

The point of the exercise isn't to occasionally be able to look at how
things were ten minutes ago.  For online systems running 24x7, it's
implausible that we'd get questions within ten minutes such that such
a slightly-old transaction could be of any use.

It is, instead, to be able to much more comprehensively look at all
[available] historical states over a fairly substantial period of
time.
--
output = ("cbbrowne" "@" "gmail.com")
http://cbbrowne.com/info/postgresql.html
ASSEMBLER  is a  language. Any  language  that can  take a  half-dozen
keystrokes and compile it down to one  byte of code is all right in my
books.  Though  for the  REAL  programmer,  assembler  is a  waste  of
time.  Why use  a  compiler when  you  can code  directly into  memory
through a front panel.

hello,

I have a somehow related question to this topic: is it possible to know
(in postgresql) if an update on a column is absolute (set col = 3) or
relative to it's previous value (set col = col + 3)
in a trigger one have access to OLD row values and NEW row values, but
no correlation between the two. is this type of information available
somewhere in postgresql ?

thanks,
Razvan Radu

Rodrigo Sakai wrote:
>   Hi everyone,
>
>   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!
>
>   Thanks!!!
>

In an attempt to throw the authorities off his trail, pgsql-general@list.coretech.ro ("pgsql-general@list.coretech.ro")
transmitted:
> I have a somehow related question to this topic: is it possible to
> know (in postgresql) if an update on a column is absolute (set col =
> 3) or relative to it's previous value (set col = col + 3)
> in a trigger one have access to OLD row values and NEW row values, but
> no correlation between the two. is this type of information available
> somewhere in postgresql ?

No, that's not available in any direct fashion.

I understand that in some replication systems (Sybase has been the
name bandied about in this context), you can mark particular table
columns as being ones where "deltas" should be computed.

In effect, you note down somewhere that certain columns represent
"balances," which implies that changes should always be regarded as
"deltas."

It seems like it ought to work...
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://cbbrowne.com/info/x.html
If you're not part of the solution, you're part of the precipitate.

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
>
>
>
>

Ok, but actually I'm not concerned about logging old values. I'm concerned
about checking temporal constraints. Entity Integrity (PK) and Referential
Integrity (FK).



 For example, if you have the salary table:



Salary (employee_id, salary, start_date, end_date)



Where [star_date, end_date] is an interval. Means that the salary is (was)
valid in that period of time.



I have to avoid this occurrence:



      001
      1000
      2005-20-01
      2005-20-12

      001
      2000
      2005-20-06
      2006-20-04




So, is needed to compare intervals, not only atomic values. If you want to
know which was the salary on 2005-25-07, is not possible. It is
inconsistent!!!



Of course I can develop some functions and triggers to accomplish this work.
But the idea is to keep simple for the developers, just simple as declare a
primary key!



Thanks for your attention!!



----- Original Message -----
From: "Brad Nicholson" <bnichols@ca.afilias.info>
To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: "Rodrigo Sakai" <rodrigo.sakai@poli.usp.br>; "Michael Glaesemann"
<grzm@myrealbox.com>; <pgsql-general@postgresql.org>
Sent: Friday, February 24, 2006 1:56 PM
Subject: Re: [GENERAL] Temporal Databases


> Simon Riggs wrote:
>
>>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.
>>
>>
>
> We're currently prototyping a system (still very much in it's infancy)
> that uses the Slony-I shipping mechanism to build an off line temporal
> system for point in time reporting purposes.  The idea being that the log
> shipping files will contain only the committed inserts, updates and
> deletes.  Those log files are then applied to an off line system which has
> a  trigger defined on each table that re-write the statements, based on
> the type of statement, into a temporally sensitive format.
>
> If you want to get an exact point in time snapshot with this approach, you
> are going to have to have timestamps on all table in your source database
> that contain the exact time of the statement table.  Otherwise, a best
> guess (based on the time the slony sync was generated) is the closest that
> you will be able to come.
>
> --
> Brad Nicholson  416-673-4106   Database Administrator, Afilias Canada
> Corp.
>
>
>

On Wed, Mar 08, 2006 at 12:56:38 -0300,
  Rodrigo Sakai <rodrigo.sakai@poli.usp.br> wrote:
> Ok, but actually I'm not concerned about logging old values. I'm concerned
> about checking temporal constraints. Entity Integrity (PK) and Referential
> Integrity (FK).

Did you see the reference to 'Developing Time-Oriented Database Applications
in SQL' (http://www.cs.arizona.edu/people/rts/tdbbook.pdf) in a recent thread?
That should give you some trigger code you can use to do this kind of thing.
It isn't postgres specific, but shouldn't need too much work.