Re: Determining if a table really changed in a trigger

> On Oct 26, 2021, at 12:05 AM, Mitar <mmitar(at)gmail(dot)com> wrote:
>
> Hi!
>
> I have a trigger like:
>
> CREATE TRIGGER update_trigger AFTER UPDATE ON my_table REFERENCING NEW
> TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE
> FUNCTION trigger_function;
>
> I would like to test inside trigger_function if the table really
> changed. I have tried to do:
>
> PERFORM * FROM ((TABLE old_table EXCEPT TABLE new_table) UNION ALL
> (TABLE new_table EXCEPT TABLE old_table)) AS differences LIMIT 1;
> IF FOUND THEN
> ... changed ...
> END IF;
>
> But this fails if the table contains a JSON field with the error:
>
> could not identify an equality operator for type json
>
> The table has an unique index column, if that helps.

I can't tell from your post if you want the trivial update to be performed, but if not, would it work to filter trivial updates as:

CREATE RULE filter_trivial_updates AS ON UPDATE TO my_table WHERE new.i = old.i AND new.j = old.j AND ... DO INSTEAD NOTHING;

You could replace the i, j, ... above with whichever columns you have, and specify the casts and equality operators you want for the json column (such as a cast to jsonb and equality.)

The advantage here, if you do it right, is that the trigger doesn't have to check whether the row has changed, because the trigger will only fire when a change has occurred. You might try it and compare the performance against other solutions. The general idea is shown here:

rules=# create table my_table (i integer, j json);
CREATE TABLE
rules=# insert into my_table
rules-# select gs::integer, '{"key":1}'::json
rules-# from generate_series(1,3) gs;
INSERT 0 3
rules=# create function my_table_func () returns trigger as $$
rules$# begin
rules$# raise warning '[old.i=%, old.j=%] => [new.i=%, new.j=%]',
rules$# old.i, old.j, new.i, new.j;
rules$# return new;
rules$# end
rules$# $$ language plpgsql;
CREATE FUNCTION
rules=# create trigger my_table_trig before update on my_table
rules-# for each row execute function my_table_func();
CREATE TRIGGER
rules=# update my_table set j = '{"key":2}'::jsonb;
WARNING: [old.i=1, old.j={"key":1}] => [new.i=1, new.j={"key": 2}]
WARNING: [old.i=2, old.j={"key":1}] => [new.i=2, new.j={"key": 2}]
WARNING: [old.i=3, old.j={"key":1}] => [new.i=3, new.j={"key": 2}]
UPDATE 3
rules=# create rule filter_trivial_updates as on update to my_table
rules-# where new.i = old.i
rules-# and new.j::jsonb = old.j::jsonb
rules-# do instead nothing;
CREATE RULE
rules=# update my_table set j = '{"key":2}'::jsonb;
UPDATE 0
rules=# update my_table set j = '{"key":3}'::jsonb;
WARNING: [old.i=1, old.j={"key": 2}] => [new.i=1, new.j={"key": 3}]
WARNING: [old.i=2, old.j={"key": 2}] => [new.i=2, new.j={"key": 3}]
WARNING: [old.i=3, old.j={"key": 2}] => [new.i=3, new.j={"key": 3}]
UPDATE 3

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company