pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNEW.* and OLD.* inside trigger function don't seem to contain recently added columns
3+ messages / 2 participants
[nested] [flat]
* NEW.* and OLD.* inside trigger function don't seem to contain recently added columns
@ 2024-03-22 16:59 Janne Annala <janne.annala@forenom.com>
2024-03-23 00:41 ` Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Janne Annala @ 2024-03-22 16:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns
2024-03-22 16:59 NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Janne Annala <janne.annala@forenom.com>
@ 2024-03-23 00:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-25 08:15 ` Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Janne Annala <janne.annala@forenom.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-03-23 00:41 UTC (permalink / raw)
To: Janne Annala <janne.annala@forenom.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns
2024-03-22 16:59 NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Janne Annala <janne.annala@forenom.com>
2024-03-23 00:41 ` Re: NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-03-25 08:15 ` Janne Annala <janne.annala@forenom.com>
0 siblings, 0 replies; 3+ messages in thread
From: Janne Annala @ 2024-03-25 08:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-03-25 08:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-22 16:59 NEW.* and OLD.* inside trigger function don't seem to contain recently added columns Janne Annala <janne.annala@forenom.com>
2024-03-23 00:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-25 08:15 ` Janne Annala <janne.annala@forenom.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox