Re: Empty Updates, ON UPDATE triggers and Rules - Mailing list pgsql-general

On Thu, Aug 6, 2009 at 12:53 PM, Josh Trutwin<josh@trutwins.homeip.net> wrote:
> Hello,
>
> I have a simple table that has a trigger to set a last_modified column
> using the following:
>
> CREATE OR REPLACE FUNCTION set_last_modified ()
> RETURNS TRIGGER
> AS $$
>   BEGIN
>      NEW.last_modified = NOW();
>      RETURN NEW;
>   END;
> $$ LANGUAGE PLPGSQL;
>
> CREATE TRIGGER trigger_test_upd_set_last_mod
> BEFORE UPDATE ON test_upd
> FOR EACH ROW EXECUTE PROCEDURE set_last_modified();
>
> The table data:
>
>> select * from test_upd;
>  id | foo | bar |       last_modified
> ----+-----+-----+----------------------------
>  1 | foo |   1 | 2009-08-06 11:37:09.15584
>  2 | foo |   2 | 2009-08-06 11:37:12.740515
>  3 | baz |   3 | 2009-08-06 11:37:19.730894
>
> If I run the following query:
>
> UPDATE test_up SET foo = 'foo', bar = 1 WHERE id = 1;
>
> The set_last_modified() trigger is run even though the data didn't
> actually change.  Perhaps due to an application program which doesn't
> know the contents before running the UPDATE.

Triggers are supposed to fire regardless if new == old.  In fact it's
common practice to do something like:
update foo set x = x; to get trigger to fire.

> CREATE RULE no_unchanging_updates AS
>  ON UPDATE TO test_upd
>  WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*)
>  DO INSTEAD NOTHING;

in 8.3 you can also do:
WHERE old::text = new.text

in 8.4 you can (and should) do:
WHERE old = new

> This worked great - re-ran the update query and no change to
> last_modified column for row id 1.  BUT, one major issue with this -
> if I inspect the table with \d it appears the rule above was expanded
> to this:
>
> Rules:
>    no_unchanging_updates AS
>    ON UPDATE TO test_upd
>   WHERE NOT (old.id IS DISTINCT FROM new.id OR old.foo IS DISTINCT
>   FROM new.foo OR old.bar IS DISTINCT FROM new.bar OR
>   old.last_modified IS DISTINCT FROM new.last_modified) DO INSTEAD
>   NOTHING

'*' is expanded during the creation of the rule.  There's nothing you
can do about this for rules, however for functions '*' is preserved
because the function is recompiled from source when necessary.  So,
from this we conclude:

*) '*' is dangerous except in functions
*) use functions instead of rules where possible

how about:

CREATE OR REPLACE FUNCTION set_last_modified ()
RETURNS TRIGGER
AS $$
BEGIN
  IF NEW != OLD THEN  -- 8.4 syntax
    NEW.last_modified = NOW();
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;


merlin