4

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).

0
2

The actions taken in the trigger are part of the transaction that fires it. This means that the counter increment will happen only if the whole transaction (or the subtransaction defined by a SAVEPOINT) succeeds. This way we can say that the above trigger will increment the counter atomically, from the point of view of the INSERT INTO comments statement.

If this is all you have inside the transaction (INSERT and the increment), you are safe from concurrency issues, and the default isolation level (READ COMMITTED) is enough.

Furthermore, depending on how the INSERT is issued, you might even spare the trigger, which would save you some overhead. In order to do this, the following conditions must be met:

  • you can wrap the INSERT INTO comments and UPDATE users statements into a transaction
  • there is no other way adding a comment than the transaction in the previous point (i. e. no manual editing of the data by an administrator or similar)
1
  • Thanks, didn't know that wrapping two statements inside transaction will be faster than trigger. One more question: If I will wrap INSERT (with trigger) in transaction with REPEATABLE READ/SERIALIZABLE it is possible that I will encounter could not serialize accesss... right? – user606521 Dec 22 '15 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.