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

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