Re: Temporal Databases

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461