I have written one approach to audit tables, available from
https://github.com/akaariai/pgsql_shadow_tables
The approach is that every table is backed by a similar audit table +
some meta information. The tables and triggers to update the audit
tables are managed by plpgsql procedures.
While the approach isn't likely that interesting itself there is one
interesting aspects. Views similar to the original tables are created
automatically in the shadow schema. The views use a session variable for
wanted "snapshot" time. The reason is that one can use this to query the
database at wanted time:
set search_path = 'shadow_public, public';
set test_session_variable.view_time = 'wanted view timestamp'; -- for
example '2012-05-06 22:08:00'
And now you can use exactly the same queries you use normally to
retrieve data from wanted view timestamp. This is very useful if you
happen to use an ORM.
In addition the "known limitations" mentioned in the README are likely
something the temporal support patch needs to tackle.
- Anssi