Thread: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns

It seems that there are cases when comparing NEW.* to OLD.* using IS DISTINCT FROM incorrectly returns false. If a new column has been added to the table during the same database connection, the new column doesn't seem to be included in NEW.* and OLD.*. To workaround the issue you can apparently start a new connection, or recreate the function.

Platform: Macbook Pro M2 
OS: macOS 14.3 (23D56)
Docker image: postgres:16.2 (using all default arguments and configuration)
Docker engine: 25.0.3

Here's the minimum case to reproduce the issue:

CREATE FUNCTION x()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN
    NEW.updated = current_timestamp;
  END IF;

  RETURN NEW;
END;
$function$;

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  old_column TEXT DEFAULT NULL,
  updated TIMESTAMP NOT NULL DEFAULT current_timestamp
);

CREATE TRIGGER test_before_update_change_updated_timestamp
BEFORE UPDATE ON test
FOR EACH ROW EXECUTE FUNCTION x();

INSERT INTO test(old_column) VALUES ('a');
UPDATE test SET old_column = 'b';

-- Updated timestamp was refreshed

ALTER TABLE test ADD COLUMN new_column TEXT DEFAULT NULL;

UPDATE test SET new_column = 'b';

-- Updated timestamp was not refreshed
Janne Annala <janne.annala@forenom.com> writes:
> Here's the minimum case to reproduce the issue:

> CREATE FUNCTION x()
>  RETURNS trigger
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>   IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN

Try dropping the ROW() bit, that is just

   IF NEW.* IS DISTINCT FROM OLD.* THEN

or even

   IF NEW IS DISTINCT FROM OLD THEN

I think what is happening is that the ROW() notation is getting
expanded at parse time to

   ROW(NEW.id, NEW.old_column, NEW.updated)

and then there's no dependency on the original rowtype that would
lead to that expansion getting reconsidered.  Arguably that's a
bug, but it's not clear to me what the consequences of changing
that behavior would be.

            regards, tom lane



Hey Tom,

Thank you for taking the time to look at the issue. It appears the ROW() notation was indeed unnecessary in this situation. I tried the methods you described, and they work perfectly.

I consider the primary issue solved. I assume your team will consider whether the original behaviour is considered a bug and if it's worth fixing or not.

Thanks again
Janne Annala

From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, 23 March 2024 2.41
To: Janne Annala <janne.annala@forenom.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns
 
[You don't often get email from tgl@sss.pgh.pa.us. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ]

Janne Annala <janne.annala@forenom.com> writes:
> Here's the minimum case to reproduce the issue:

> CREATE FUNCTION x()
>  RETURNS trigger
>  LANGUAGE plpgsql
> AS $function$
> BEGIN
>   IF ROW(NEW.*) IS DISTINCT FROM ROW(OLD.*) THEN

Try dropping the ROW() bit, that is just

   IF NEW.* IS DISTINCT FROM OLD.* THEN

or even

   IF NEW IS DISTINCT FROM OLD THEN

I think what is happening is that the ROW() notation is getting
expanded at parse time to

   ROW(NEW.id, NEW.old_column, NEW.updated)

and then there's no dependency on the original rowtype that would
lead to that expansion getting reconsidered.  Arguably that's a
bug, but it's not clear to me what the consequences of changing
that behavior would be.

                        regards, tom lane