New columns can't be updated if a trigger BEFORE UPDATE is triggered-CodePudding

I came across a strange behavior (at least for me) with PostgreSQL and a trigger BEFORE UPDATE.

I have a table witch has an updated_at column witch is set by a BEFORE UPDATE trigger. I need to add new columns to this table and set their values with an UPDATE query (not with DEFAULT).

It works just fine excepts when i do an UPDATE juste before adding those columns. Here's an example :

ALTER TABLE my_schema.my_table ADD COLUMN new_column varchar(50);
UPDATE my_schema.my_table SET new_column = 'new_column_update' WHERE id = xxxxxx;

This script works fine. But if i do an UPDATE before :

UPDATE my_schema.my_table SET other_column = 'other_column_update' WHERE id = xxxxxx; -- the TRIGGER is triggered
ALTER TABLE my_schema.my_table ADD COLUMN new_column varchar(50);
UPDATE my_schema.my_table SET new_column = 'new_column_update' WHERE id = xxxxxx; -- this UPDATE does't do anything

It doesn't works anymore.

After a few (a lot) hours, i found that the trigger BEFORE UPDATE is reponsible. But i can't find why.

I found a workaround by temporary disabling the trigger

ALTER TABLE my_table DISABLE TRIGGER update_date;

Here is a dbfiddle, just run it to see this behaviour : dbfiddle

Have you ever encountered this behavior ?

CodePudding user response：

It's something to do with the (unnecessary) wrapping of NEW/OLD with a ROW(...) constructor:

            BEGIN
               IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
               -- IF NEW IS DISTINCT FROM OLD THEN
                  NEW.updated_at = now();
               ELSE
                   RAISE EXCEPTION $$NOT DISTINCT: % / %$$, NEW, OLD;
               END IF;
               RETURN NEW;
            END;

I've also moved the RETURN NEW to the end. If you try your version you should see the exceptions. If you replace it out with the commented-out one below then it works.

Now, as to why this is failing when you compare rows I'm not sure and it's too hot and late on a Friday afternoon where I am to figure it out I'm afraid.

CodePudding user response：

I am going to say this is a caching problem. I modified the function to see what is going on:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (
  other_column varchar(50),
  updated_at timestamp
);

CREATE OR REPLACE FUNCTION public.update_date()
 RETURNS trigger
 LANGUAGE plpgsql
 COST 1
AS $function$
            BEGIN
              RAISE NOTICE 'New row %', ROW(NEW.*);
              RAISE NOTICE 'Old row%', ROW(OLD.*);
              RAISE NOTICE 'New.* %', (NEW.*)::text;
              RAISE NOTICE 'Old.* %', (OLD.*)::text;
              IF NEW.* IS DISTINCT FROM OLD.* THEN
                  NEW.updated_at = now();
                  RETURN NEW;
               ELSE
                  RETURN OLD;
               END IF;
            END;
$function$;


CREATE TRIGGER update_date BEFORE
UPDATE
    ON
    my_table FOR EACH ROW EXECUTE PROCEDURE update_date();


INSERT INTO my_table VALUES ('other_column_insert');
UPDATE my_table SET other_column = 'other_column_update';

NOTICE:  New row (other_column_update,)
NOTICE:  Old row(other_column_insert,)
NOTICE:  New.* (other_column_update,)
NOTICE:  Old.* (other_column_insert,)


ALTER TABLE my_table ADD COLUMN new_colum varchar(50);
UPDATE my_table SET new_colum = 'new_colum_update'; 

NOTICE:  New row (other_column_update,"2022-08-12 10:38:54.815831")
NOTICE:  Old row(other_column_update,"2022-08-12 10:38:54.815831")
NOTICE:  New.* (other_column_update,"2022-08-12 10:38:54.815831",new_colum_update)
NOTICE:  Old.* (other_column_update,"2022-08-12 10:38:54.815831",)