Thread: Data change logs

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

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

The way I do it is to create a copy of the table I want to track and add
a text column for the user name and a timestamp column.

I then set up an on update trigger on the original table that does the
following:

    insert into xxxx_log select * from xxxx where keyfield = NEW.keyfield;

I also set up an on insert trigger on the log table that adds the timestamp
and the user ID (from session_user).

The big negative is that if you add columns to the original table, you
have to fiddle with the log table to make sure you keep the username and
timestamp columns after all the columns in the original table and keep the
columns in sync with the original table as to both size and order in which
they appear, or you'll get errors.

Pulling the data out of the log table can be a bit more, because it has
the OLD data but not the NEW data.  But you know what the values were,
who changed them and when, and you can check the original table to see
what the current value is.  (If there are multiple changes, you
have to check the next one in timestamp order, of course.)

One of the nicer aspects is that because this is done at the trigger
level, the user does NOT have to have any access to the log table,
the trigger can use SECURITY DEFINER.  That way you get full control
over who can even look at the log.
--
Mike Nolan

>
>     insert into xxxx_log select * from xxxx where keyfield = NEW.keyfield;

Oops, that should be OLD.keyfield.
--
Mike Nolan