Subject Re: [IB-Architect] Super-transactions and Incremental Backup
Author Doug Chamberlin
At 6/20/00 07:31 PM (Tuesday), Jason Wharton wrote:
>The thought comes to mind, how expensive would it be to make available the
>ability to query up records along with their committed timestamp and USER
>who committed it. Kind of like DB_KEY is a system provided mechanism it
>might be very useful to take advantage of this. I suppose what would end up
>happening is the transaction data that is generated would have to remain a
>permanent record in order for this information to be accessed. Please don't
>flame, it's just a thought...

Here's an idea which I guess is a solution looking for a problem. (Sorry,
Jim!) Nothing happens to records without being associated with a
transaction, right? Each transaction is associated with a user, right? Each
record version has a transaction number associated with it (stored in the
record I think). So to determine who did what when we need to make the
transaction number associated with each record available (like DB_KEY is).
We also have to create a new system table which accumulates records
containing transaction number, user, and timestamp when the transaction

Now you can join the tracking table to any other table using the
transaction number field and obtain any record in any table which was
committed after a certain time. You can also find any record which was
modified or added by any user within any time interval.

Some problems I see right off: 1) Deletes are not tracked. Maybe they do
not need to be. 2) Are this tracking table's records inserted within the
transaction context or outside of it? If within then the transaction hasn't
committed until after the tracking record is written. How do you get an
accurate timestamp into the record if it is written before the timestamp
value is available? Maybe the timestamp value at the time the tracking
record is inserted is good enough. 3) Do we really want to accumulate a
record, even a short one, for every transaction? 4) Transaction numbers do
not have a lifetime across backup/restore cycles do they? Maybe it doesn't
matter since the timestamp+transaction number combo forms the primary key
of the tracking table. You'd have trouble if the clock was reset, though!

Anyway, food for thought.