I have the following code:
create table users (id serial primary key, comments_counter integer default 0); create table comments (id serial primary key, user_id integer references users(id) not null) create or replace function users_comments_counter() returns trigger as $$ begin update users set comments_counter = comments_counter + 1 where id = NEW.user_id; return null; end; $$ language plpgsql; create trigger users_comments_counter_trg after insert on comments for each row execute procedure users_comments_counter();
Is it possible that inserting a row to the
comments table will throw
could not serialize access due to concurrent update?
Or maybe in other words: is it possible to use such counter (with ANY of transaction isolation levels) while avoiding situation where "retry" (due transaction serialization error) is needed, and data consistency is guaranteed (counter will behave as expected - will be incremented only if comment row will be successfuly inserted).