So,
I have a question, either on how to best use session variables from triggers, or
on how to best solve my actual problem, of effective auditing.
I'm working on a general record auditing system for a new database, that is like
the common approach of having automatic datestamp fields plus auditing tables
that track IUD, but with these 2 main features/differences:
1. Most redundancy is avoided by only inserting a record into an audit
("history") table when a main table record is updated or deleted. A plain
insert into the main table does not result in an history table insertion (an
insert-only table has no corresponding history table at all). Or to be more
accurate, a SQL UPDATE is treated logically as a DELETE+INSERT, and so the
history table just contains a copy of the old version of a record from the main,
not a current one. A main table just has an "inserted when" timestamp while a
history table has that plus a "deleted when" timestamp.
2. I have factored out "insert" and "delete" timestamps into a separate
"changeset" table, such that any other metadata for the insertion or deletion is
described in one place and a single changeset_id is used in all the regular
tables instead of the various metadata. A primary benefit of this approach is
that it is much easier to see which of any arbitrary number of data changes,
across multiple tables, were conceptually performed all together as a unit,
because they share a changeset_id value, which is more reliable than looking for
changes that share say a timestamp. But there's also avoiding meta-redundancy.
Now, its easy enough to manage all of this manually, that is, whenever I would
explicitly UPDATE/DELETE a main record, I also explicitly INSERT a history
record with its old value in the same database transaction. Done manually, the
first step is to insert a "changeset" record, then reference its pk in all the
other normal records. So manual is my fallback option.
However, I would like to have triggers to create the history records, which
should be as simple as cloning the old main table records but adding an extra
field referring to the "changeset" record related to the record that caused the
trigger. So then the information somehow needs to be available to the trigger
when a UPDATE or DELETE happens on a main table, so it can add it to the
corresponding history table insert. With UPDATE thats easy enough, as we just
copy the inserted-when value for the NEW record; but with plain DELETE there is
no such thing.
In the simpler or perhaps most common scenario, where the main table changes are
all happening right after I inserted the "changeset" record, and all in the same
database transaction or session, I presumably could just call currval() in the
triggers for the sequence generator of the "changeset" pk.
But if I wanted to possibly make db changes in multiple transactions or sessions
and attribute them to the same "changeset", I would have to explicitly pass a
changeset id to the triggers somehow. So then a question might be what is a
best practice for doing this? Or alternately, perhaps this situation is more
just hypothetical and I haven't thought of a specific time this may happen, and
so I don't need to worry about it.
So I ask ...
Has anyone here used an auditing methodology similar to what I described above?
Or do you have any thoughts on whether I seem to be on the right track or
whether I should do something different for effective auditing instead? Or did
this even give you ideas for something to try yourself?
Or is what I said here not understandable?
Thank you in advance for your feedback.
-- Darren Duncan
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
