Rails: repeated ActiveRecord::RecordNotUnique when creating objects with Postgres?

Question

I'm working with a Rails 4 app that needs to create a large number of objects in response to events from another system. I am getting very frequent `ActiveRecord::RecordNotUnique` errors (caused by `PG::UniqueViolation`) on the primary key column when I call `create!` on one of my models. I found other answers on SO that suggest rescuing the exception and calling `retry`: ``` begin TableName.create!(data: 'here') rescue ActiveRecord::RecordNotUnique => e if e.message.include? '_pkey' # Only retry primary key violations log.warn "Retrying creation: #{e}" retry else raise end end ``` While this seems to help, I am still getting tons of `ActiveRecord::RecordNotUnique` errors, for sequential IDs that already exist in the database (log entries abbreviated): ``` WARN -- Retrying creation: PG::UniqueViolation: DETAIL: Key (id)=(3067) already exists. WARN -- Retrying creation: PG::UniqueViolation: DETAIL: Key (id)=(3068) already exists. WARN -- Retrying creation: PG::UniqueViolation: DETAIL:…

Accepted Answer

I'm not sure how it happened, but it turned out that the PostgreSQL sequence for the table's primary key was somehow reset or got out of sync with the table:

```
SELECT nextval('table_name_id_seq');
-- 3456

SELECT max(id) FROM table_name;
-- 95123
```

I had to restart the primary key sequence at the table's last ID:

```
ALTER SEQUENCE table_name_id_seq RESTART 95124;
```

Update: here's a Rake task to reset the ID sequence for most models on a Rails 4 with PostgreSQL project:

```
desc 'Resets Postgres auto-increment ID column sequences to fix duplicate ID errors'
task :reset_sequences => :environment do
  Rails.application.eager_load!

ActiveRecord::Base.descendants.each do |model|
    unless model.attribute_names.include?('id')
      Rails.logger.debug "Not resetting #{model}, which lacks an ID column"
      next
    end

begin
      max_id = model.maximum(:id).to_i + 1
      result = ActiveRecord::Base.connection.execute(
        "ALTER SEQUENCE #{model.table_name}_id_seq RESTART #{max_id};"
      )
      Rails.logger.info "Reset #{model} sequence to #{max_id}"
    rescue => e
      Rails.logger.error "Error resetting #{model} sequence: #{e.class.name}/#{e.message}"
    end
  end
end
```

The following references proved useful:

- https://stackoverflow.com/a/1427188

- http://apidock.com/rails/ActiveRecord/Relation/find_or_create_by

- https://stackoverflow.com/a/10712838

- https://stackoverflow.com/a/16533829

Rails: repeated ActiveRecord::RecordNotUnique when creating objects with Postgres?

Solution

Problem

Related problems