On Thu, Aug 6, 2009 at 12:53 PM, Josh Trutwin<josh@xxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
