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