Re: [Gnumed-devel] audit trail question

[engelbert . gruber]

On Thu, 8 May 2003, Karsten Hilbert wrote:

> Have been reading up on the various ways of doing audit
> trailing (by which I mean who changed what when in the DB).
>
> There's three main approaches with regards to PostgreSQL:
>
> 1 keeping backups of entire rows upon change
> 2 keeping backups of the changed columns only
> 3 logging the queries leading to said changes
>
> 1 allows for much easier access to a revisional history of the
>   records, it can be implemented with procedural languages
>   alone but it always keeps the entire column, this is
>   available, needs an audit table per audited table
>
> 2 access to a revisional history of the record is much more
>   involved, this is often faster, it does not store as much
>   redundant data as 1 but it does require a backend loadable
>   module (in C, usually) to do so, this is also available, can
>   audit all data to one audit table
>
> 3 this is not readily available, PostgreSQL would have to be
>   patched to provide this handily or a logging gate-ware would
>   have to be written which logs incoming queries, it may be
>   faster (due to parallelism with the actual query if
>   implemented as gate-ware)

i did 3 for a bill managment system years ago, there was one execute_sql
function and this one checked if it is an insert update or delete
and if puts it into the changelog table (which therefore grew
fast and sometimes blew up the indexes).

but on occasion i took a setup the system from a backup and reexecuted
the queries from the changelog.

> I'd be interested in arguments pro-con the various approaches.
>
> Audit trailing is the one big issue that needs to be solved to
> allow for more functionality to be declared safe to actually use.

1 and 2 might allow to show what has changed aside the current entry.

3 is only for rollback, or one would need to execute the queries.

-- 
 BINGO: Das lassen wir aussen vor
 --- Engelbert Gruber -------+
  SSG Fintl,Gruber,Lassnig  /
  A6170 Zirl   Innweg 5b   /
  Tel. ++43-5238-93535 ---+