acts_as_list: Cannot add position uniqueness index

Hey @brendon – yeah, I’ll work on putting something together. I figured moving the callback would probably be too big of a change. It works as-is, though I do think it would be good to be able to enforce the uniqueness constraint at the DB level.

I’m running into this issue with uniqueness indices configured on Postgres, regardless of the :sequential_updates setting. I removed the indices for now. A call to item.move_higher will fail when the index is present. It works once I have removed it.

class ChecklistItemDefinition< ApplicationRecord
  belongs_to :checklist_definition
  acts_as_list scope: :checklist_definition
end

class ChecklistDefinition< ApplicationRecord
  has_many :checklist_item_definitions, -> { order(position: :asc) }
end

#The Migration
add_index :checklist_item_definitions, [:checklist_definition_id, :position], unique: true, name: :checklist_item_definition_position_unique

As @bzwei noted, I think this is due to the after_update callback. The record can’t save because it violates the unique constraint before the other position values are updated. I’d have to dig into the code a bit more, but using a before_save callback could work, but could also have unintended consequences. For now, perhaps a note on the documentation would help.

This should help:

1. Create an constraint that is DEFERRABLE

ALTER TABLE table_name
ADD CONSTRAINT index_name UNIQUE (project_id, position, user_id) DEFERRABLE INITIALLY IMMEDIATE;

1. Execute move_lower / move_higher in a transaction and set the constraint of your index to DEFERRED

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute('SET CONSTRAINTS index_name DEFERRED') 
  item.move_lower
end

https://www.postgresql.org/docs/current/sql-set-constraints.html

1. Create an constraint that is DEFERRABLE

ALTER TABLE table_name
ADD CONSTRAINT index_name UNIQUE (project_id, position, user_id) DEFERRABLE INITIALLY IMMEDIATE;

@kamilpogo Any reason not to create this constraint as DEFERRABLE INITIALLY DEFERRED ? Wouldn’t that eliminate the need to set the constraint as DEFERRED later on?

There might be some implications: Rails automatically wraps any test case in a database transaction that is rolled back after the test completes (see https://guides.rubyonrails.org/testing.html#testing-parallel-transactions ). So your test might behave differently than your production code (constraint won’t be checked in tests while it is checked in development/production) if you don’t use transactions. In tests you could use self.use_transactional_tests = false and later clean up your test database if your constraint is initially deferred.

But I didn’t have a look if this is an issue here. Maybe everything is wrapped in a transaction. Then there shouldn’t be a problem