Can anyone help explain this? I am using the Populator and Faker gems to put some generated data into my database. Among other things, I generate 10,000 comments (which are from the 'acts_as_commentable' gem. All this works. However, when I go to add a new comment, I get an error saying that I am violating the primary key by using an existing id. Look at my console output below. You can see I have 10,000 records starting with ID 1 and ending with ID 100000. I then try to add a new comment and it fails. This is only happening with this model/table. I can add new users, etc.
>> Comment.first(:order => 'id').id
=> 1
>> Comment.last(:order => 'id').id
=> 10000
>> Comment.count
=> 10000
>> Comment.create(:title => 'wtf is up?')
ActiveRecord::RecordNotUnique: PGError: ERROR: duplicate key value violates unique constraint "comments_pkey"
DETAIL: Key (id)=(1) already exists.
I suspect this is related to how the Populator gem is batching the records into the database. It is only happening on models/tables that I see with Populator.
This happens if the value of the id column is explicitly set in an insert statement.
For every id-column there is a sequence in Postgres, which is usually named tablename_columnname_seq, for example user_id_seq.
Please check the name in the table definition in pgadmin3 as rails does not support sequences with other names.
You can fix a sequence with a too low id by executing something similar to:
SELECT setval('user_id_seq', 10000);
To learn the highest number: SELECT max(id) FROM users;
SELECT max(x) FROM
(SELECT max(id) As x FROM users
UNION SELECT last_value As x FROM user_id_seq As y);