Postgresql after 'for each row' triggers don't actually execute after each row?

Question

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

Answer 1

Since the your INSERT and UPDATE are atomic (either all rows inserted/updated or none), you cannot catch a state when the count will not be 5 or 0. First the INSERT happens, then the trigger is fired for all the rows.

Answer 2

I guess the documentation says it: http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

Anyone know if it's possible to avoid this behavior somehow?

• Craig Ringer: Nope, but you can use another BEFORE trigger and control their ordering. Triggers are executed in name order, lexically sorted (alphabetical). So place the trigger where you want it in the execution ordering.