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: Key (id)=(3069) already exists.
WARN -- Retrying creation: PG::UniqueViolation: DETAIL: Key (id)=(3070) already exists.

The IDs it's trying are in the 3000-4000 range, even though there are over 90000 records in the table in question.

Why is ActiveRecord or PostgreSQL wasting so much time sequentially trying existing IDs?

---

The original exception (simplified/removed query string):

{
  "exception": "ActiveRecord::RecordNotUnique",
  "message": "PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint \"table_name_pkey\"\nDETAIL:  Key (id)=(3023) already exists."
}

Answer 1

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

Answer 2

You can also reset a sequence of a table 'table_name' using rails console

> ActiveRecord::Base.connection.reset_pk_sequence!('table_name')

(tested in rails 3.2, rails 5.0.1)