Duplicate ID's being generated violating primary key contraints

Question

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.

Oops. Bad title! Sorry about that. Not sure how that fragment got there. Not very descriptive of the problem, is it? :) — NJ., Nov 13 '10 at 12:55

score 5 · Accepted Answer · answered Nov 13 '10 at 14:59

5

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);

answered Nov 13 '10 at 14:59

Hendrik Brummermann

8,242
3
31
55

You can't use MAX while many users use the database. Many users will see the same number, all try to use this number +1 and their queries fail: duplicate key. Use a sequence or lock the table. – Frank Heikens Nov 13 '10 at 15:07
Yes, sorry, i assumed that his tests don't use multiple threads. SELECT...FOR UPDATE is the easiest way to make it thread save, assuming that transactions are used. – Hendrik Brummermann Nov 13 '10 at 15:08
I am very sure this is it. Problem is, it is a problem in the Populator gem so I have had to stop using it. But this is useful information! – NJ. Nov 13 '10 at 21:48

score 0 · Answer 2 · answered Dec 04 '13 at 14:25

This is a handy PostreSQL script to fix sequences for all tables at once

SELECT  'SELECT SETVAL(' ||quote_literal(quote_ident(S.relname))|| ', MAX(' ||quote_ident(C.attname)|| ') ) FROM ' ||quote_ident(T.relname)|| ';'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
ORDER BY S.relname;

score 0 · Answer 3 · answered Nov 13 '10 at 14:24

I do not know what the actual problem is, but it is certainly linked to using the Populator gem to add records. Generating data by using:

Populator.sentences(1..3) # makes 3 sentences

is fine.

However, generating records like

User.populate 5000 do |user| # makes 5000 users in batches of 1000
   user.name = Populator.words(1)
   ...
end

Something in there is causing my problem. note that I am using Rails 3.0.1

Duplicate ID's being generated violating primary key contraints

3 Answers3