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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to