Re: Determining if a table really changed in a trigger

Hi!

On Wed, Oct 27, 2021 at 3:56 AM Michael Lewis <mlewis@entrata.com> wrote:
> If you end up with no rows changing from an insert or delete, something seems awry. Unless you mean 0 rows affected.

Isn't this the same? Isn't the number of rows affected the same as the
number of rows changing? For example:

DELETE FROM my_table where i=100;

would not change anything in your example. But probably this is just
terminology I have used badly.

> Do after statement triggers still execute? I suppose they very well might.

I have run the following and it seems statement triggers still execute
even if nothing changes:

postgres=# create table my_table (i integer, j json);
CREATE TABLE
postgres=# insert into my_table
  select gs::integer, '{"key":1}'::json
    from generate_series(1,3) gs;
INSERT 0 3
postgres=# create function my_table_func () returns trigger as $$
declare
  have_rows boolean;
begin
  raise warning 'trigger called';
  if (tg_op = 'INSERT') then
    select true into have_rows from new_values limit 1;
    if have_rows then
      raise warning 'rows have changed';
    end if;
  elsif (tg_op = 'UPDATE' or tg_op = 'DELETE') then
    select true into have_rows from old_values limit 1;
    if have_rows then
      raise warning 'rows have changed';
    end if;
  end if;
  return null;
end
$$ language plpgsql;
CREATE FUNCTION
postgres=# create trigger my_table_trig_insert after insert on my_table
  referencing new table as new_values
  for each statement
  execute function my_table_func();
CREATE TRIGGER
postgres=# create trigger my_table_trig_update after update on my_table
  referencing old table as old_values
  for each statement
  execute function my_table_func();
CREATE TRIGGER
postgres=# create trigger my_table_trig_delete after delete on my_table
  referencing old table as old_values
  for each statement
  execute function my_table_func();
CREATE TRIGGER
postgres=# update my_table set j = '{"key":2}'::jsonb;
WARNING:  trigger called
WARNING:  rows have changed
UPDATE 3
postgres=# update my_table set j = '{"key":2}'::jsonb;
WARNING:  trigger called
WARNING:  rows have changed
UPDATE 3
postgres=# create trigger z_min_update
  before update on my_table
  for each row execute function suppress_redundant_updates_trigger();
CREATE TRIGGER
postgres=# update my_table set j = '{"key":2}'::jsonb;
WARNING:  trigger called
UPDATE 0
postgres=# update my_table set j = '{"key":3}'::jsonb;
WARNING:  trigger called
WARNING:  rows have changed
UPDATE 3
postgres=# delete from my_table where i = 100;
WARNING:  trigger called
DELETE 0
postgres=# insert into my_table select * from my_table where i = 100;
WARNING:  trigger called
INSERT 0 0

> Would the statement even execute if no rows get updated and that is prevented with before update? I would assume null
isbeing returned rather than old if the trigger finds the row to be identical. 

It looks like a statement trigger is always called, but checking
REFERENCING matches affected rows as returned by the psql shell. Also
notice how the number of affected rows is non-zero for trivial update
before the use of suppress_redundant_updates_trigger, both through
REFERENCING and through the psql shell.

That matches also documentation:

> ..., a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many
rowsit modifies (in particular, an operation that modifies zero rows will still result in the execution of any
applicableFOR EACH STATEMENT triggers). 

So it would be really cool to be able to access the number of affected
rows inside a trigger without the use of REFERENCING. Given that WHEN
condition of a statement trigger is currently mostly useless (because
the condition cannot refer to any values in the table) maybe providing
something like AFFECTED variable in there would be the way to go? So
one could write:

CREATE TRIGGER my_trigger AFTER UPDATE ON my_table FOR EACH STATEMENT
WHEN AFFECTED <> 0 EXECUTE FUNCTION my_table_func();


Mitar

--
http://mitar.tnode.com/
https://twitter.com/mitar_m