[Gnumed-devel] audit trail question

[Karsten Hilbert]

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'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.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346