Get atom counter value (increment) using Rails and Postgres

Question

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

I'm currently using

Group.increment_counter :tasks_count, @task.id 

in my model, which atomically increments the counter.

But I also need a new value to send a notification if the counter has, for example. value 50 . Any ideas? Table / row locking or is there an easier way?

Change / SOLVE

Based on mu, the answer is too short and the Rails update_counters method, I applied the instance method (tested using 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 as:

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

It uses RETURNING to retrieve a new value within the same query.

Answer 1

Your call to Group.increment_counter 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 the RETURNING clause:

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

I do not know what a convenient way Railsy get SQL in the database. The usual Rails approach would be to do a bunch of locking and reloading @task or skip the lock 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:

 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 probably want to hide this clutter behind a method on Group so you can say things like:

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