Обсуждение: feature idea automatic update tracking using date fields feedback pls
feature idea automatic update tracking using date fields feedback pls
От
anony@dslextreme.com (anony)
Дата:
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