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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
