Get value of atomic counter (increment) with Rails and Postgres

Question

I need to atomically increment a model counter and use its new value (processed by a Sidekiq job).

At the moment, I use

Group.increment_counter :tasks_count, @task.id

in my model which atomically increments the counter.

But I also need its new value to send a notification, if the counter has e.g. the value 50. Any ideas? Locking the table/row or is there an easier way?

Edit / SOLUTION

Based on mu is too short's answer and Rails's update_counters method , I implemented an instance method (tested with PostgreSQL).

def self.increment_counter_and_return_value(counter_name, id)
  quoted_column = connection.quote_column_name(counter_name)
  quoted_table = connection.quote_table_name(table_name)
  quoted_primary_key = connection.quote_column_name(primary_key)
  quoted_primary_key_value = connection.quote(id)

  sql = "UPDATE #{quoted_table} SET #{quoted_column} = COALESCE(#{quoted_column}, 0) + 1 WHERE #{quoted_table}.#{quoted_primary_key} = #{quoted_primary_key_value} RETURNING #{quoted_column}"
  connection.select_value(sql).to_i
end

Use it like:

Group.increment_counter_and_return_value(:tasks_count, @task.id)

It uses RETURNING to fetch the new value within the same query.

Answer 1

Your Group.increment_counter call sends SQL like this to the database:

update groups
set tasks_count = coalesce(tasks_counter, 0) + 1
where id = X

where X is @task.id. The SQL way to get the new tasks_counter value is to include a RETURNING clause:

update groups
set tasks_count = coalesce(tasks_counter, 0) + 1
where id = X
returning tasks_count

I don't know of any convenient Railsy way to get that SQL to the database though. The usual Rails approach would be to either do a bunch of locking and reload @task or skip the locking and hope for the best:

Group.increment_counter :tasks_count, @task.id
@task.reload
# and now look at @task.tasks_count to get the new value

You can use RETURNING like this though:

new_count = Group.connection.execute(%Q{
    update groups
    set tasks_count = coalesce(tasks_counter, 0) + 1
    where id = #{Group.connection.quote(@task.id)}
    returning tasks_count
}).first['tasks_count'].to_i

You'd probably want to hide that mess behind a method on Group so that you could say things like:

n = Group.increment_tasks_count_for(@task)
# or
n = @task.increment_tasks_count