Re: BUG #4380: Comparison of OLD and NEW columns in trigger does not always work

On Wed, 27 Aug 2008, Daryl Joubert wrote:

>
> The following bug has been logged online:
>
> Bug reference: 4380
> Logged by: Daryl Joubert
> Email address: daryl(at)ftcxpress(dot)com
> PostgreSQL version: 8.3.3
> Operating system: WinXP SP2
> Description: Comparison of OLD and NEW columns in trigger does not
> always work
> Details:
>
> When comparing NEW.column_name to OLD.column_name in 'PG_UPDATE' operation
> in a trigger, I get unpredictable results. Use the following script to
> create the "Business Partner" table and its trigger, then add a few rows of
> data to "First Name", "Middle Name" and "Last Name" using pgAdmin, and watch
> how "Name" is built up for the former 3 columns.
>
> Then: change the "Middle Name" column of an existing row repeatedly and you
> should see that sometimes "Name" is updated, sometimes not. I can repeat the
> problem here within 5 to 10 changes of "Middle Name". Am I doing something
> wrong? Here is the script:

Well, the function does the wrong thing if you set a field to NULL or
change a field that was previously NULL to a non-NULL value since a <>
NULL is unknown not true. And once that happens you might need to do a
couple of updates to get out of the odd state.

Is it possible that the updates that are failing fit that pattern? If so,
using IS DISTINCT FROM rather than <> may work.