Can atomic counter in trigger lead to "could not serialize access due to concurrent update"?
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)