Re: temporal support patch

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