0

In a Rails 5 app I create items and increment their position:

class ListItem < ActiveRecord::Base
  belongs_to :list

  before_validation :ensure_position

  private
  def ensure_position
    self.position ||= self.class.where(list_id: list_id).maximum(:position).to_i + 1
  end
end

The index responsible for the duplicate violation is:

 t.index ["list_id", "position"]

I get a PG duplicate key violation on the :position column. Whenever multiple create are fired simultaneously, the maximum function returns the same position for multiple records.

I am new to thread safety and postgres locking in ruby on rails. Is there a way to lock or create a mutex on save action of the list item record so that the position gets incremented safely?

stefano_cdn
  • 1,362
  • 2
  • 15
  • 29

1 Answers1

0

You want a composite primary_key which is not supported by Rails.

However, there is a composite_primary_keys gem and a blog post about using the gem.

Aurel Branzeanu
  • 535
  • 1
  • 7
  • 18
  • Thank you for the suggestion. I am currently checking the gem and the blog post is interesting, but don't you think a whole gem is kind of an overkill for such a small part? In case you think about a workaround, it would be very helpful. – stefano_cdn Sep 23 '16 at 19:25
  • Well, Sami already have suggested a sequence like in http://stackoverflow.com/a/36720571/4812102 "nothing prevents you from having what you think of a a composite primary key as an alternative key and have the Rails default PK (postgres: pseudotype 'serial' which draws its values from a sequence). Just make sure that you add a unique index for those columns that make up your key." – Aurel Branzeanu Sep 23 '16 at 20:18