local postgres db keeps giving error duplicate key value violates unique constraint

4
votes

I don't understand why postgres is raising: 

duplicate key value violates unique constraint

I went to check the table in pgadmin to see if the table really did have a duplicate and see:

Running VACUUM recommended

The estimated rowcount on the table deviates significantly from the actual rowcount.

Why is this happening? Luckily it doesn't seem to happen in production on heroku. It's a rails app.

Update:

Here is the sql log:

SQL (2.6ms) INSERT INTO "favorites" ("artist_id", "author_id", "created_at", "post_id", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [["artist_id", 17], ["author_id", nil], ["created_at", Sun, 18 Mar 2012 03:48:37 UTC +00:00], ["post_id", 62], ["updated_at", Sun, 18 Mar 2012 03:48:37 UTC +00:00]] PG::Error: ERROR: duplicate key value violates unique constraint "index_favorites_on_artist_id_and_post_id" DETAIL: Key (artist_id, post_id)=(17, 62) already exists.

But in the actual table there is no such record with artist_id = 17 and post_id = 62. But postgres believes that there is.

5
ruby-on-railspostgresqlherokuduplicates
Could you be a little more specific about when this error is occurring. Duplicate key violations wouldn't leave a duplicate in the table but it can cause an insert or update to fail. The vaccume thing is unrelated you should look into auto-vacuum or (depending on version) set a scheduled task to vacuum automatically. – Philip Couling
I updated question with some example. – Homan
Usually you would expect one key to exist afterwords but as sketchfemme indicates in his answer, simultaneous statements may be a problem. OR something attempting to add the same key twice in the same transaction will cause the whole transaction to roll back and neither row will stay in the table. Is there a reason why post_id is not being assigned by a sequence? – Philip Couling

5 Answers

9
votes

You need to run ANALYZE to get the row count in sync. In pgAdmin right click the table and choose "Maintenance" for that. Then press F5 on the table.

Doesn't have anything to do with the unique key violation, though. That means a value you try to enter in a column with a UNIQUE or PRIMARY KEY constraint is already present in another row.

4
votes

In case of PostgreSQL unique key violation error messages, ActiveRecord::Base.connection.reset_pk_sequence!('table_name') might help to bring the key back in sync.

2
votes

A common cause of this is that you've put data into a table with a primary key that's normally provided by a serial type, but the sequence behind the default() isn't in sync with the table.

0
votes

actually i think the problem is not related to postgres at all. It was a combination of simultaneous duplicate ajax calls creating what would be a duplicate record, then neither are inserted.

0
votes

I faced this problem in my development environment. This worked for me phppgadmin->admin->reindex. My solution is dangerous in some situations as it updates whole database.I suggest to look other solutions as well when working in production environment. However, Analyse is a good way to start as suggested by @Erwin Brandstetter