Обсуждение: BUG #4380: Comparison of OLD and NEW columns in trigger does not always work

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:

CREATE TABLE "Business Partner"
(
  "Partner Number" serial NOT NULL,
  "First Name" character varying,
  "Middle Name" character varying,
  "Last Name" character varying,
  "Name" character varying,
  CONSTRAINT "PK Business Partner" PRIMARY KEY ("Partner Number")
)
WITH (OIDS=FALSE);

CREATE FUNCTION t_business_partner()
  RETURNS trigger AS
$BODY$
DECLARE
BEGIN
  IF (TG_OP = 'INSERT') THEN
    IF NEW."Name" IS NULL OR NEW."Name" = '' THEN
      IF NEW."Middle Name" IS NULL OR NEW."Middle Name" = '' THEN
        NEW."Name" := NEW."First Name" || ' ' || NEW."Last Name";
      ELSE
        NEW."Name" := NEW."First Name" || ' ' || NEW."Middle Name" || ' ' ||
NEW."Last Name";
      END IF;
    END IF;
    NEW."Name" = initcap(NEW."Name");
    NEW."First Name" = initcap(NEW."First Name");
    NEW."Middle Name" = initcap(NEW."Middle Name");
    NEW."Last Name" = initcap(NEW."Last Name");
    RETURN NEW;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF NEW."Name" IS NULL OR NEW."Name" = ''
    OR NEW."First Name" <> OLD."First Name"
    OR NEW."Middle Name" <> OLD."Middle Name"
    OR NEW."Last Name" <> OLD."Last Name" THEN
      IF NEW."Middle Name" IS NULL OR NEW."Middle Name" = '' THEN
        NEW."Name" = NEW."First Name" || ' ' || NEW."Last Name";
      ELSE
        NEW."Name" = NEW."First Name" || ' ' || NEW."Middle Name" || ' ' ||
NEW."Last Name";
      END IF;
      NEW."Name" = initcap(NEW."Name");
      NEW."First Name" = initcap(NEW."First Name");
      NEW."Middle Name" = initcap(NEW."Middle Name");
      NEW."Last Name" = initcap(NEW."Last Name");
    END IF;
    RETURN NEW;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

CREATE TRIGGER "TR Business Partner"
  BEFORE INSERT OR UPDATE OR DELETE
  ON "Business Partner"
  FOR EACH ROW
  EXECUTE PROCEDURE t_business_partner();

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.