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."
}

score 18 · Accepted Answer · edited May 23 '17 at 12:34

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:

Any thoughts of how the IDs and sequence can get out of sync? This happened to me too, and the fix is easy, but I would like to know how the problem could have originally occurred... — Kevin Bullaughey, Aug 05 '15 at 14:00
In my case the sequences got out of sync due to a bulk data import that set IDs directly, but did not update the sequences. *Maybe* it can also happen if concurrent processes are creating lots of records at the same time? — nitrogen, Aug 17 '15 at 06:29

score 12 · Answer 2 · edited Dec 26 '18 at 06:45

12

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)

edited Dec 26 '18 at 06:45

Obromios

15,408
15
72
127

answered Nov 27 '15 at 18:28

adc

771
4
12

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

2 Answers2