Menu
I recently migrated my rails app to PostgreSQL in order to take advantage of fulltext search.
Since the migration coincided with moving to a new webhost, the steps for migration were:
The application is running successfully but the issue comes when trying to add new content to the database. For example, when I run the rake task to update my twitter feed:
PG::Error: ERROR: duplicate key value violates unique constraint "twitter_feeds_pkey" DETAIL: Key (id)=(3) already exists.
This also happens for all other models, creating new articles, users etc. In development I can see that posting the insert statement n+1 times will successfully save the record without error.
My question is: How do I tell PostgreSQL to start adding indexes sequentially from the existing data?
I've read the REINDEX page but don't think that is really the operation I'm looking for.
876
The “duplicate key violates unique constraint” error notifies the caller that a retry is needed. This seems like an intuitive approach, but relying on this optimistic insert can quickly have a negative performance impact on your database.
Duplicate key values occur when the value of an indexed column is identical for multiple rows. For example, suppose that the third and fourth leaf nodes of a B-tree structure contain the key value Smith .
The syntax for creating a unique constraint using an ALTER TABLE statement in PostgreSQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
The PostgreSQL UNIQUE constraint ensures that the uniqueness of the values entered into a column or a field of a table. The UNIQUE constraint in PostgreSQL can be applied as a column constraint or a group of column constraint or a table constraint.
In Rails you can use the command
ActiveRecord::Base.connection.reset_pk_sequence!('users')
to bring the primary key index for the User table in sync again.
68
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
