I wrote such an audit system and am using it production. It works reasonably well. It was quite a bit of work to develop, and still has some rough edges.Hi all. I'm new with PostgreSQL and this is my first post, so easy on me... :)I'm thinking of using the native procedural language and triggers to keep an audit trail. For editing changes, we only keep a log of the modified fields and we create a record for each modified value. The audit table record holds information like user, date/time, table_name, field_name, old_value, new_value, type(delete, new, edit). I have a couple of questions:
I had this problem, and as Michael Fuhr mentioned you can't resolve it in PL/PGSQL. I ended up using PL/TCL because it was stable under 7.4 and it does the field dereferencing you need. As of 8.0 and later PL/PERL is also stable and I believe it does field dereferencing as well.Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records.
I looked into that as well, and it's pretty hard. Most applications that use only one database user but have multiple application-level users are three-tier, and the apps tend to do logging themselves, often using a separate loggin mechanism like log4j and friends. So for that part I'd either have your app write the user action into the appropriate table, or look into retrieving the PK of your audit/history table row, passing it back to your application and having your application log the user after writing the row history table. Otherwise you're at the mercy of when and how your database connection is opened (i.e., how long a session lasts).Another issue is how to keep track of the audit user since we share the same postgres user and our application keeps track of the actual current user locally. Is there some kind of way we can set the current user so that we're able to read it from the trigger event? Other suggestions?