Update Trigger Inconsistency with 7.1?

We recently upgraded our development server to 7.1 and I believe I've
noticed an inconsistency with how update triggers behave on version 7.1
versus 7.0. Since I'm not sure which should be the expected behavior I have
no idea if it is a bug or not. Here is the situation:

I have a table in which on field (two actually) *needs* to be updated every
time, *even* if the new value is the same as the old value. For example:
UPDATE Foo SET UpdateOptional='something', UpdateRequired='updated' WHERE
Other='value' would work, but UPDATE Foo SET UpdateOptional='something'
WHERE Other='value' would not work, because no value was explicitly given
for UpdateRequired.

To do this I created a trigger that would raise an exception "IF
new.UpdateRequired ISNULL". In 7.0 this would work because
new.UpdateRequired seemed to be NULL unless it was specified (I cannot test
this any longer because I don't have a 7.0 server available). In 7.1 this
behavior seemed to change: new.UpdateRequired is now equal to the
old.UpdateRequired value if no new value is specified.

Which behavior should I expect... and how else can I tell if a value has
been given by an UPDATE statement (keeping in mind that the value may or may
not change)?

Greg

"Gregory Wood" <gregw(at)com-stock(dot)com> writes:
> To do this I created a trigger that would raise an exception "IF
> new.UpdateRequired ISNULL". In 7.0 this would work because
> new.UpdateRequired seemed to be NULL unless it was specified (I cannot test
> this any longer because I don't have a 7.0 server available).

I find that very hard to believe. The NEW record contains the proposed
new tuple, which will include the old value of any fields that weren't
specified in the UPDATE statement.

You can detect whether a field is actually being *changed* by comparing
NEW.field and OLD.field. You cannot distinguish the case where the
UPDATE didn't mention a field from the case where it did but assigned
the same value that was already there (eg UPDATE foo SET x = x).
This behavior was not different in 7.0.

regards, tom lane

> You can detect whether a field is actually being *changed* by comparing
> NEW.field and OLD.field. You cannot distinguish the case where the
> UPDATE didn't mention a field from the case where it did but assigned
> the same value that was already there (eg UPDATE foo SET x = x).
> This behavior was not different in 7.0.

I had thought that I tested by trying update statements where the field
wasn't specified. I suppose I probably am overlooking some other fact that
was causing it to fail, but I could've sworn...

I suppose I'll have to NULL out the field on INSERTs and UPDATEs, or push it
into yet another field, then check for NULLness every time to verify that
it's updated. Sigh.

Greg