Thread

1. table logging

   Jeff Janes <jeff.janes@gmail.com> — 2012-10-28T19:25:33Z

   I am looking for some very simple table logging.  I am not trying to
   do auditing in a hostile environment, just simple logging.
   
   I found two candidates, tablelog from pgfoundry, and
   http://wiki.postgresql.org/wiki/Audit_trigger_91plus
   
   The first has the advantage of being simple, but hasn't been
   maintained in 5 years which is a little worrisome.
   
   The latter is very flexible, but is rather complex as you have to fish
   out the data with queries against hstore object, instead of tables
   with the same structure as the tables being logged.  On the other hand
   it does deal with columns being added to the underlying tables more
   gracefully.
   
   Are there widely used/supported options other than these two, and of
   course rolling my own?
   
   Cheers,
   
   Jeff
   
   
   

2. Re: table logging

   Julian <tempura@internode.on.net> — 2012-10-28T23:01:16Z

   On 29/10/12 06:25, Jeff Janes wrote:
   > I am looking for some very simple table logging.  I am not trying to
   > do auditing in a hostile environment, just simple logging.
   >
   > I found two candidates, tablelog from pgfoundry, and
   > http://wiki.postgresql.org/wiki/Audit_trigger_91plus
   >
   > The first has the advantage of being simple, but hasn't been
   > maintained in 5 years which is a little worrisome.
   >
   > The latter is very flexible, but is rather complex as you have to fish
   > out the data with queries against hstore object, instead of tables
   > with the same structure as the tables being logged.  On the other hand
   > it does deal with columns being added to the underlying tables more
   > gracefully.
   >
   > Are there widely used/supported options other than these two, and of
   > course rolling my own?
   >
   > Cheers,
   >
   > Jeff
   >
   >
   Hi Jeff, It does really depend on the requirements of your app and what
   you want to log.
   I have used tablelog and also rolled my own, both work fine.
   Theres also the idea of "revision" logging (such as a web page
   you wish to revert back to/review).
   These are "insert" tables, there are never any actual "DELETE"s or
   "UPDATE"s just "INSERT"s there is no log table per se.
   
   After a brief look at Audit Trigger it seems like a simple solution to 
   table
   auditing, perhaps you can hack at that to not use hstore and log what 
   you want?
   
   I usually avoid full blown "audits" on a table if I can and go with the 
   "revision"
   style of logging (such as change of address, email, etc) if its required.
   
   This solution can lead to bloat if only the data in 1 column differs
   to the original. But bloat is also hard to avoid if one letter in a 
   large web page
   (stored in 1 column) differs to the original, overcoming that can lead 
   to complexities.
   
   So this all depends on the requirements of your app.
   
   It can be fair to say that all kinds of logging leads to bloat that you'll
   have to deal with later. (partitioning, purging etc).
   
   Julian.
   
   
   
   

3. Re: table logging

   Richard Huxton <dev@archonet.com> — 2012-10-29T10:03:01Z

   On 28/10/12 19:25, Jeff Janes wrote:
   > I am looking for some very simple table logging.  I am not trying to
   > do auditing in a hostile environment, just simple logging.
   >
   > I found two candidates, tablelog from pgfoundry, and
   > http://wiki.postgresql.org/wiki/Audit_trigger_91plus
   >
   > The first has the advantage of being simple, but hasn't been
   > maintained in 5 years which is a little worrisome.
   
   I've got tablelog in use on one of my projects. I had to make one small 
   fix when I upgraded the db to 9.1 - something to do with quote escaping. 
   Can't remember the details I'm afraid.
   
   Other than that, it seems to work fine.
   
   
   -- 
      Richard Huxton
      Archonet Ltd