Re: Determining if a table really changed in a trigger

> On Oct 26, 2021, at 1:34 PM, Mitar <mmitar@gmail.com> wrote:
>
> Hi!
>
> On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger
> <mark.dilger@enterprisedb.com> wrote:
>> I can't tell from your post if you want the trivial update to be performed, but if not, would it work to filter
trivialupdates as: 
>
> No, I want to skip trivial updates (those which have not changed
> anything). But my trigger is per statement, not per row. So I do not
> think your approach works there? So this is why I am then making a
> more complicated check inside the trigger itself.

The trigger "my_table_trig" in the example is a per row trigger, but it exists only to demonstrate that the rule has
filteredout the appropriate rows.  You can use the rule "my_table_rule" as written and a per statement trigger, as
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$# declare
rules$#   have_rows boolean;
rules$# begin
rules$#   select true into have_rows from old_values limit 1;
rules$#   if have_rows then
rules$#     raise warning 'rows have changed';
rules$#   else
rules$#     raise warning 'no rows changed';
rules$#   end if;
rules$#   return null;
rules$# end
rules$# $$ language plpgsql;
CREATE FUNCTION
rules=# create trigger my_table_trig after update on my_table
rules-#   referencing old table as old_values
rules-#   for each statement
rules-#   execute function my_table_func();
CREATE TRIGGER
rules=# update my_table set j = '{"key":2}'::jsonb;
2021-10-26 13:51:58.139 PDT [34352] WARNING:  rows have changed
2021-10-26 13:51:58.139 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() line 7 at RAISE
WARNING:  rows have changed
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;
2021-10-26 13:51:58.143 PDT [34352] WARNING:  no rows changed
2021-10-26 13:51:58.143 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() line 9 at RAISE
WARNING:  no rows changed
UPDATE 0
rules=# update my_table set j = '{"key":3}'::jsonb;
2021-10-26 13:51:58.143 PDT [34352] WARNING:  rows have changed
2021-10-26 13:51:58.143 PDT [34352] CONTEXT:  PL/pgSQL function my_table_func() line 7 at RAISE
WARNING:  rows have changed
UPDATE 3

Note that there is a performance cost to storing the old rows using the REFERENCING clause of the trigger, so you'll
wantto think about all your various options and decide between them.  I am not in a position to make performance
recommendationsfor your schema.  However, if updates tend to be target at small sets of rows, and if the rule is used
tofurther filter out trivial updates, this might be cheap. 

Note that I used equality and inequality rather than IS DISTINCT FROM and IS NOT DISTINCT FROM in the design, but you
shouldthink about how NULL values (old, new, or both) will behave in the solution you choose. 

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