Duplicate identifier generated with primary key violation

Question

Can someone explain this? I use Populator and Faker stones to put some generated data into my database. Among other things, I generate 10,000 comments (which are taken from the "act_as_commentable" gem. This all works. However, when I go to add a new comment, I get an error that I am violating the primary key using an existing identifier. Look at my output on console below. You can see that I have 10,000 entries starting with ID 1 and ending with ID 100000. Then I try to add a new comment and it fails. This only happens with this model / table. new users, etc. d.

>> 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 due to the way the Stone Populator loads records into the database. This only happens on models / tables that I see with Populator.

Answer 1

This happens if the id column value is explicitly specified in the insert statement.

For each id column, there is a sequence in Postgres that is usually called tablename_columnname_seq, for example user_id_seq.

Please check the name in the table definition in pgadmin3, because the rails do not support sequences with other names.

You can fix the sequence with an identifier too low by doing something similar to:

SELECT setval('user_id_seq', 10000);

To find out the largest 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);

Answer 2

, , , , Populator . :

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

.

,

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

- . , Rails 3.0.1

Answer 3

This is a convenient 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;