Re: BUG #4380: Comparison of OLD and NEW columns in trigger does not always work - Mailing list pgsql-bugs

From Stephan Szabo
Subject Re: BUG #4380: Comparison of OLD and NEW columns in trigger does not always work
Date
Msg-id 20080827100033.G59818@megazone.bigpanda.com
Whole thread Raw
In response to BUG #4380: Comparison of OLD and NEW columns in trigger does not always work  ("Daryl Joubert" <daryl@ftcxpress.com>)
List pgsql-bugs
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@ftcxpress.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.

pgsql-bugs by date:

Previous
From: Tom Wright
Date:
Subject: Re: BUG #4381: Postgresql daemon won't stay in the foreground
Next
From: Alvaro Herrera
Date:
Subject: win32 installer page in pgfoundry is wrong