Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Janne Annala
Date:
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

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

From
Tom Lane
Date:
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

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

From
Janne Annala
Date:
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