Re: temporal support patch

On 08/21/2012 01:52 PM, Jeff Davis wrote:
> On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote:
>> Personally, I would prefer a tool which just made it simpler to build my
>> own triggers, and made it automatic for the history table to track
>> changes in the live table. I think anything we build which controls
>> what goes into the history table, etc., will only narrow the user base.
> That sounds like a good way to start. Actually, even before the tool,
> how about just some really good examples of triggers for specific kinds
> of audit logs, and some ways to run queries on them? I think that might
> settle a lot of these details.
>
>
Here is the example of triggers we use in our applications. This is the
test implementation, the production one uses similar triggers written in C.
http://softus.org/?page_id=63

1. There are 3 tables: test contains only current data, test_history
contains only historical data and test_audit contains all data.
2. There must be a field in an audited table system_time for a period of
validity of the row.
3. Optional fields are: txid_modified, user_modified for txid/user that
inserts or updated the row, txid_deleted, user_deleted for txid/user
that deleted the row. There may be other information in the audit table
that was omitted in the example (client IP, host name, etc.)
3. We do not use txid_current() as transaction ID because backup/restore
resets it.
4. User is set by the application (audit.current_user() is just a dummy).
5. There is no exclusion constraint on (primary key, system_time) in
history table, integrity is maintained by triggers (however the user can
damage the historical data by modifying test_history table).
6. It's important to understand that when audit triggers are enabled
some modifications can fail because the same row may be concurrently
modified by another transaction CURRENT_TIMESTAMP of which is lower or
the same as the current one.