Empty Updates, ON UPDATE triggers and Rules

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

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