Thread: feature idea – automatic update tracking using date fields – feedback pls

Maybe this feature is already out there.
I guess you could write triggers to do some of this.

Often when designing a database I add a start_date and end_date column
to the table. The start_date is when the record was created and the
end_date is when the record expired. For UPDATES, I populate the
end_date and INSERT a new record. This allows you to track changes
though out time.

I do this so often I thought it would be a good idea for each table to
have the option for automatic UPDATE tracking. When the UPDATE occurs
the old record is automatically saved with the end_date populated. The
old records would not be visible unless explicitly queried using date
fields.

Advantages
----------------
Less Coding: Now I don't have update the old record's end_date and
insert a new record for a simple update. A simple UPDATE will take
care of it.

Good Form: Keeping history of all the changes is important in many
applications

Easier Queries: If this feature was implemented, multiple tables joins
could possibly be referenced by one condition in the WHERE clause,
rather than multiple conditions for each table in the join.

My .02
Feedback Please.

anony wrote:
> Maybe this feature is already out there.
> I guess you could write triggers to do some of this.
>
> Often when designing a database I add a start_date and end_date column
> to the table. The start_date is when the record was created and the
> end_date is when the record expired. For UPDATES, I populate the
> end_date and INSERT a new record. This allows you to track changes
> though out time.

You probably want to read up on "Temporal Databases" which is what
you're talking about. I believe there is a book by C.J.Date on the topic
as it relates to relational DBs.

 From a curiosity point of view, there apparently used to be a
"time-travel" feature in (really) old versions of PG. MVCC effectively
creates a new row for every update anyway, so you could "rewind" to
previous transaction IDs to see the data then.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> From a curiosity point of view, there apparently used to be a
> "time-travel" feature in (really) old versions of PG.

There still is a contrib module that supports this using triggers
(look in contrib/spi/).  It's looking a bit dated --- for instance
it uses abstime columns, which ought to get updated to timestamptz.
But it'd be a starting point.

            regards, tom lane