Postgresql after 'for each row' triggers don't actually execute after each row? 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
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.