On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut<pete...@gmx.net> wrote: > On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: >> On Sep 3, 2009, at 7:44 AM, Peter Eisentraut <pete...@gmx.net> wrote: >> > The SQL standard specifies that a trigger is fired if the column is >> > mentioned in the UPDATE statement, independent of whether the value is >> > actually changed through the update. >> >> That is thorougly bizarre, IMO. > > Well, if you find that bizarre, consider the existing behavior: Why > should an ON UPDATE row trigger fire when none of the values of the > row's columns actually change? I think if you read > > TRIGGER ON UPDATE > > as > > TRIGER ON UPDATE OF <all columns> > > then it makes some sense.
Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
