Thursday, 26 January 2012

(ActiveRecord::RecordNotUnique) "PGError: ERROR: duplicate key value violates unique constraint

Today when pushed the code on production a strange notification get starts mailing that record is not unique ... the primary key is being getting duplicate which is supposed to be unique and was set to auto-increment.

Pressure keep rising as the site was live and the delete functionality was getting crash here investigation started as.

  1. Created a record from console and it get saved perfectly and get the id 225.
  2. Check the last record of the culprit table and the id was 983
  3. Here it clicks that wrapper of active model was introduced later and the table was being getting dumped through sql.
  4. Take little help from google to check how postgres handles primary key and it comes to know that postgres maintains Sequence structure for primary keys and just keep single filed ++ for the new records unique id. 
  5. So simply update the relevant tables primary key fields sequencer to the max id of the table and it starts getting smooth and no more Record Not Unique exception raised.
And this is how a strange issue of primary get resolved and came to know that the Rocket Science of DB's primary keys.

Cheer :)

No comments:

Post a Comment