Re: Temporal Databases - Mailing list pgsql-general

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.