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
