If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted or updated.
Why is that? And is there a way to prevent it from happening?
begin;
create table foos (state text not null);
create function f() returns trigger as $$ begin
-- This seems to be ran after all the rows are inserted or updated?
-- expected it to run once for each row as it was inserted or updated.
raise notice '%', count(*) from foos where state = 'initial';
return null;
end $$ language plpgsql;
create trigger f after insert or update on foos for each row execute procedure f();
insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';
Expected Output:
psql:/tmp/t.sql:14: NOTICE: 1
psql:/tmp/t.sql:14: NOTICE: 2
psql:/tmp/t.sql:14: NOTICE: 3
psql:/tmp/t.sql:14: NOTICE: 4
psql:/tmp/t.sql:14: NOTICE: 5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE: 4
psql:/tmp/t.sql:15: NOTICE: 3
psql:/tmp/t.sql:15: NOTICE: 2
psql:/tmp/t.sql:15: NOTICE: 1
psql:/tmp/t.sql:15: NOTICE: 0
UPDATE 5
Actual Output:
psql:/tmp/t.sql:14: NOTICE: 5
psql:/tmp/t.sql:14: NOTICE: 5
psql:/tmp/t.sql:14: NOTICE: 5
psql:/tmp/t.sql:14: NOTICE: 5
psql:/tmp/t.sql:14: NOTICE: 5
INSERT 0 5
psql:/tmp/t.sql:15: NOTICE: 0
psql:/tmp/t.sql:15: NOTICE: 0
psql:/tmp/t.sql:15: NOTICE: 0
psql:/tmp/t.sql:15: NOTICE: 0
psql:/tmp/t.sql:15: NOTICE: 0
UPDATE 5
Edit:
Using a before trigger behaves differently (and how I would expect it to behave):
begin;
create table foos (state text not null);
create function f() returns trigger as $$ begin
raise notice '%', count(*) from foos where state = 'initial';
return new;
end $$ language plpgsql;
create trigger f before insert or update on foos for each row execute procedure f();
insert into foos select 'initial' from generate_series(1, 5);
update foos set state = 'canceled';
Output:
psql:/private/tmp/t.sql:14: NOTICE: 0
psql:/private/tmp/t.sql:14: NOTICE: 1
psql:/private/tmp/t.sql:14: NOTICE: 2
psql:/private/tmp/t.sql:14: NOTICE: 3
psql:/private/tmp/t.sql:14: NOTICE: 4
INSERT 0 5
psql:/private/tmp/t.sql:15: NOTICE: 5
psql:/private/tmp/t.sql:15: NOTICE: 4
psql:/private/tmp/t.sql:15: NOTICE: 3
psql:/private/tmp/t.sql:15: NOTICE: 2
psql:/private/tmp/t.sql:15: NOTICE: 1
UPDATE 5