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