pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
NEW.* 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>
  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-23 00:41  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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-25 08:15  Janne Annala <janne.annala@forenom.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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