Data change logs

[Brendan Jurd <blakjak@blakjak.sytes.net>]

Hello all,

I have a particular application sitting on postgres that requires a 
complete log of all inserts, updates and deletes, along with the 
application user who made the change.  There is a functional solution in 
place, but it is a bit ugly.  I was hoping that some of the 
pgsql-general readers would have encountered this problem before and 
have some insight to offer. 

Here's how the current solution works.  We have five tables for logging, 
with the following basic structure:
  
   insert_log ( id, userid, time, tablename, number )
   insert_log_col ( id, insert_log, col, value )

   update_log ( id, userid, time, tablename, number )
   update_log_col ( id, update_log, col, old, new )

   delete_log ( id, userid, time, tablename, number )

So, as you can see, each "insert log" has many "insert log columns", 
which document the values inserted into each column.  "update log" works 
in much the same way, as well as recording the value which was replaced 
by the update.

Whenever the frontend of the app (in PHP) needs to do an insert, update, 
or delete, it passes the tablename, primary key number, and field / 
value pairs (except for deletes) to a wrapper function.  The wrapper 
function performs whatever validations are necessary, assembles the SQL 
command to carry out the action, and if the action was successful, then 
inserts the information into the log tables.

Because the wrapper functions reside on the front-end, this is a fairly 
costly process.  The database is growing rapidly, and will continue to 
do so, and thus performance is an increasingly serious issue.  Moving 
the wrappers to PL/pgSQL functions is something we're looking into right 
now, but I'm open to the possibility that this entire solution is a poor 
approach.

Bear in mind the overal purpose of the logging is for traceability - we 
want to be able to track down who made what changes and when, for any 
and all data in the system.  Point-in-time recovery, while it would be 
cool, is not a serious concern.

So, if anyone out there has an effective alternative, I would love to 
hear about it.

Regards,

Brendan Jurd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faqs/FAQ.html