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.col4 OR
)
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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to