Thread: Empty Updates, ON UPDATE triggers and Rules
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.
New Data (notice last_modified changed for row 1):
> select * from test_upd;
id | foo | bar | last_modified
----+-----+-----+----------------------------
2 | foo | 2 | 2009-08-06 11:37:12.740515
3 | baz | 3 | 2009-08-06 11:37:19.730894
1 | foo | 1 | 2009-08-06 11:37:43.045065
Doing some research on this I found this post:
http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/
Which has a Rule:
CREATE RULE no_unchanging_updates AS
ON UPDATE TO test_upd
WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*)
DO INSTEAD NOTHING;
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
Now if I add a column using:
ALTER TABLE test_upd ADD COLUMN baz TEXT;
The rule above is not updated to include the new column and running
an empty update query involving baz causes the trigger to change
last_modified.
Do I have to DROP/recreate the Rule everytime I ALTER the table or is
there a better way?
I have an application where it's possible for end users to easily
add / remove columns from their "plugin" application so I was hoping
to not have to add rule rebuilding to these operations if possible.
I noticed if I attempt to DROP column bar that I have to add CASCADE
so the rule is deleted so I'll likely have to deal with it anyway.
Postgresql 8.3.7
Thank you,
Josh
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
On Thu, 2009-08-06 at 11:53 -0500, Josh Trutwin wrote: > 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. The following doc explains the standard way to accomplish this: http://www.postgresql.org/docs/8.4/static/functions-trigger.html The document says that in most cases, you would want the above trigger to fire last. However, I think your situation is different: you probably want that trigger to fire before your "last updated" trigger. Rules happen at a much earlier stage. Expressions haven't been evaluated yet and triggers haven't been fired, etc., so the rule won't really know whether the new row and old row are really equal or not. A rule will only work in simple cases, which may or may not be acceptable for you. Regards, Jeff Davis
On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote:
> in 8.4 you can (and should) do:
> WHERE old = new
I couldn't get that to work in a rule.
> IF NEW != OLD THEN -- 8.4 syntax
Does this work correctly in the case of NULLs? It looks like it does,
but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to
NULL. Where is this documented?
Regards,
Jeff Davis
2009/8/6 Jeff Davis <pgsql@j-davis.com>: > On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote: >> in 8.4 you can (and should) do: >> WHERE old = new > > I couldn't get that to work in a rule. > >> IF NEW != OLD THEN -- 8.4 syntax > > Does this work correctly in the case of NULLs? It looks like it does, > but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to > NULL. Where is this documented? > it's not safe, I thing so correct test is IF NEW IS NOT DISTINCT FROM OLD THEN ... regards Pavel Stehule > Regards, > Jeff Davis > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Thu, Aug 6, 2009 at 1:38 PM, Jeff Davis<pgsql@j-davis.com> wrote: > On Thu, 2009-08-06 at 13:15 -0400, Merlin Moncure wrote: >> in 8.4 you can (and should) do: >> WHERE old = new > > I couldn't get that to work in a rule. it should, maybe try old::foo = new::foo >> IF NEW != OLD THEN -- 8.4 syntax > > Does this work correctly in the case of NULLs? It looks like it does, > but that seems strange, because ROW(1, NULL) = ROW(1, NULL) evaluates to > NULL. Where is this documented? If you think that's weird, check out: postgres=# select (50, 0)::foo > (50, null)::foo; ?column? ---------- f (1 row) postgres=# select (50, 0)::foo < (50, null)::foo; ?column? ---------- t (1 row) I think maybe Pavel is right and is distinct from is safer, but I'd argue against any change that disallowed comparisons of composites with nulls in them. merlin
On Thu, 6 Aug 2009 13:15:57 -0400 Merlin Moncure <mmoncure@gmail.com> wrote: > 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; Thanks - I'll try this. Since using 8.3 sounds like I need to replace above with: IF old::text != new::text ? I'll give it a go anyway.... Josh
On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure <mmoncure@gmail.com> wrote:
> 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;
Interestingly, this syntax is accepted in 8.3.7, but SELECT queries
fail:
CREATE TRIGGER trigger_test_upd_set_last_mod
BEFORE UPDATE ON test_upd
FOR EACH ROW EXECUTE PROCEDURE set_last_modified();
Then:
UPDATE test_upd SET foo = 'foo' WHERE id = 1;
ERROR: operator does not exist: test_upd <> test_upd
LINE 1: SELECT $1 != $2
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts. QUERY: SELECT $1 != $2
CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF
This seems to be working fine on 8.3 though:
> IF old::text != new::text THEN
Are there any solutions pre 8.3? We still have some 8.1 installs....
Thanks!
Josh
On Aug 6, 2009, at 15:31 , Josh Trutwin wrote: > Interestingly, this syntax is accepted in 8.3.7, but SELECT queries > fail: > > CREATE TRIGGER trigger_test_upd_set_last_mod > BEFORE UPDATE ON test_upd > FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); > > Then: > > UPDATE test_upd SET foo = 'foo' WHERE id = 1; > ERROR: operator does not exist: test_upd <> test_upd > LINE 1: SELECT $1 != $2 > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. QUERY: SELECT $1 != $2 > CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF That's not a SELECT query per se: AIUI it's how the evaluation of the NEW != OLD expression is evaluated within the PL/pgSQL function as part of the IF statement (note the "line 2 at IF" context line). It's just saying the <> operator doesn't exist for the test_upd rowtype. Michael Glaesemann grzm seespotcode net
On Thu, Aug 6, 2009 at 3:31 PM, Josh Trutwin<josh@trutwins.homeip.net> wrote: > On Thu, 6 Aug 2009 13:15:57 -0400 > Merlin Moncure <mmoncure@gmail.com> wrote: > >> 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; > > Interestingly, this syntax is accepted in 8.3.7, but SELECT queries > fail: > > CREATE TRIGGER trigger_test_upd_set_last_mod > BEFORE UPDATE ON test_upd > FOR EACH ROW EXECUTE PROCEDURE set_last_modified(); > > Then: > > UPDATE test_upd SET foo = 'foo' WHERE id = 1; > ERROR: operator does not exist: test_upd <> test_upd > LINE 1: SELECT $1 != $2 > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. QUERY: SELECT $1 != $2 > CONTEXT: PL/pgSQL function "set_last_modified_test" line 2 at IF > > This seems to be working fine on 8.3 though: > >> IF old::text != new::text THEN > > Are there any solutions pre 8.3? We still have some 8.1 installs.... yes, there is a similar, more circuitous way, that should work for 8.1 IIRC you have to calll record_out to get the text for the record (the cast is just shorthand for that). merlin
On Thu, 6 Aug 2009 16:58:02 -0400 Michael Glaesemann <grzm@seespotcode.net> wrote: > That's not a SELECT query per se: AIUI it's how the evaluation of > the NEW != OLD expression is evaluated within the PL/pgSQL function > as part of the IF statement (note the "line 2 at IF" context line). > It's just saying the <> operator doesn't exist for the test_upd > rowtype. Oops - that was a typo - meant to say UPDATE. Thx, Josh