Seeds.rb sets primary key value, results in duplicate key error

Question

In my Rails 4 app, which I am using on Heroku for production, I have set up a seeds.rb file for the initial set of data. As it is critical that a few records have specific primary ids, say in the 'activities' table, I have set those. All goes well.

UNTIL. Until I need to add a new activity to the activities table. Adding a new record will fail as many times as there are records in that table, while the sequencer catches up. (e.g. if I have three records, new record creation fails three times, succeeds on the fourth).

QUESTION: How can I get the primary ID counter on the Heroku postgres database to start after the highest primary id set in my seeds.rb file? If you don't know, what terms should I be googling?

Ideally, this solution would be automatic, and not something I have to open psql for each time I seed the database.

Thanks in advance.

Answer 1

The :id column is an auto-increment column. As such, you don't have control over what value it uses -- it just always uses the next id available in its internal counter. If this were me I'd find some other way to identify those few records than by :id. For example, maybe you can refer to the records by a :name or some other special attribute? (Don't forget to index the attribute you're finding by if you do go this route!)

Then, I'd use the seed_fu gem to perform idempotent seeding, as you seem to be wanting. With idempotent seeding, if a record exists already then it will be updated (if there are any updates to be performed; if not then it's a no-op). This way you don't have exceptions and you can always add new or update existing records.

Answer 2

Auto Increment

Further to pdobb's answer, you'll benefit from reading up about auto_increment - when you insert data into a table with auto_increment columns, it will just add a number to the end of the last one. In normal circumstances, you don't have to be concerned with it at all

If you need to include items in particular ids, such as if you're referencing a specific record or something, my best advice is to change your system to not deal with specific primary_keys

--

Example

We use an options table with the CMS we created, and reference role_id's from the options table (which we seed):

This is populated from our db/seeds.rb file:

Option.create({name: "role", value: "admin"})
Option.create({name: "role", value: "moderator"})
Option.create({name: "role", value: "author"})

Notice no reference to id?

This means we can reference the role as follows:

#app/models/profile.rb
belongs_to :role, -> { where(name: "role") }, class_name: "Option", foreign_key: "role"

def role
  role_name = Option.find self[:role]
  role_name.value
end

So we store the role id in our role column; and access that with a custom instance method which overrides the getter made by Rails!

So if I call

current_user.profile.role # -> in DB, will have id stored, but will output "value" of that

--

This means we're able to assign an id automatically!

This works because even if you remove the preceding items, the id of the role records will still remain the same. You don't need to worry about your auto_increment column

Answer 3

I ran into this problem with the acts_as_taggable_on gem for rails when it would throw duplicate tag error.

After I rails db:seed I run RAILS_ENV=development rake db:reset_pk_sequence so that the keys are reset.

# lib/tasks/tag_cleaner.rake
# run using 'RAILS_ENV=development rake db:reset_pk_sequence'

namespace :db do
  desc "reset the pk sequence"
  task :reset_pk_sequence => :environment do
    ActiveRecord::Base.connection.tables.each do |t|
      ActiveRecord::Base.connection.reset_pk_sequence!(t)
    end
  end
end

This seems to fix it but then I haven't tried in production as I don't use seed data for production.

Answer 4

The issue ended up being that I was running seeds.rb from

$ rake db:setup

db:setup recreates the database, and thus resets the counter. By switching to:

$ rake db:migrate
$ rake db:seed

I am able to modify my seed data in the production database without resetting the Postgres primary ID counter.

On a side note, so that this never happens accidentally, I have also created a rakefile that raises an error when rake db:setup is entered.