On 08/21/2012 12:52 PM, Jeff Davis wrote:
> On Mon, 2012-08-20 at 16:32 -0700, Josh Berkus wrote:
>> This is sounding like a completely runaway spec on what should be a
>> simple feature.
>
> My feeling as well. However, we will eventually want to coalesce around
> some best practices and make it easy and robust for "typical" cases.
>
>> Personally, I would prefer a tool which just made it simpler to build my
>> own triggers, and made it automatic for the history table to track
>> changes in the live table. I think anything we build which controls
>> what goes into the history table, etc., will only narrow the user base.
>
> That sounds like a good way to start. Actually, even before the tool,
> how about just some really good examples of triggers for specific kinds
> of audit logs
That reminds me: The single biggest improvement I can see for audit
triggers would be to provide an _easy_ and _efficient_ way to test
whether any fields have changed between OLD and NEW *except* for one or
more ignored fields.
Right now if I have a four-column table and I want to ignore UPDATEs to
col2 for audit purposes, I have to write:
CREATE TRIGGER tablename_audit_insert_delete
AFTER INSERT OR DELETE ON sometable FOR EACH ROW
EXECUTE PROCEDURE audit_func();
CREATE TRIGGER tablename_audit_update_selective
AFTER UPDATE ON sometable FOR EACH ROW
WHEN ( OLD.col1 IS DISTINCT FROM NEW.col1 OR OLD.col3 IS DISTINCT FROM NEW.col3 OR OLD.col4 IS DISTINCT FROM
NEW.col4OR
)
EXECUTE PROCEDURE audit.if_modified_func();
... which is horrible for all sorts of reasons:
- If a column is added the audit trigger also needs an update to test
for it, otherwise it'll be ignored;
- It isn't explicit that "col2" is ignored; and
- I have to repeat my trigger definitions twice.
An alternative is to create hstores from OLD and NEW, delete the field
of interest, and compare them. That's pretty slow though, and may
duplicate work done by the already-expensive audit trigger.
What I'm imagining is something like a:
row_equals_ignorecols(OLD, NEW, 'col2')
... which would solve half the problem, and is simple enough I could
implement it with a little C function.
A way to avoid splitting the trigger function definition and a built-in
"compare rows except columns" would be great, though.
--
Craig Ringer
