Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

why PG::UniqueViolation: ERROR: duplicate key value violates unique constraint?

I have a model Post and each time a post is created I want a new instance of Moderation to be created at the same time.

So in post.rb I use the callback after_save :create_moderation Then write a private method :

 ...  include Reportable  after_save :create_moderation   private  def create_moderation     self.create_moderation!(blog: Blog.first)  end 

But when a proposal is created I get this error :

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "moderations_reportable" DETAIL: Key (reportable_type, reportable_id)=(Post, 25) already exists. : INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

In reportable.rb I have :

  has_one :moderation, as: :reportable, foreign_key: "reportable_id", foreign_type: "reportable_type", class_name: "Moderation" 

Then few other methods for reportable object.

Note that this issue doesn't happen when I run the create method in console.

EDIT

  create_table "moderations", id: :serial, force: :cascade do |t|     t.string "reportable_type", null: false     t.string "reportable_id", null: false     t.integer "blog_id", null: false     t.datetime "created_at", null: false     t.datetime "updated_at", null: false     t.string "blog_type", null: false     t.string "upstream_moderation", default: "unmoderate"     t.index ["blog_id", "blog_type"], name: "moderations_blog"     t.index ["reportable_type", "reportable_id"], name: "moderations_reportable", unique: true   end    create_table "posts", id: :serial, force: :cascade do |t|     t.text "title", null: false     t.text "body", null: false     t.integer "feature_id", null: false     t.integer "author_id"     t.integer "scope_id"     t.datetime "created_at", null: false     t.datetime "updated_at", null: false     t.integer "post_votes_count", default: 0, null: false     t.index ["body"], name: "post_body_search"     t.index ["created_at"], name: "index_posts_on_created_at"     t.index ["author_id"], name: "index_posts_on_author_id"     t.index ["feature_id"], name: "index_posts_on_feature_id"     t.index ["proposal_votes_count"], name: "index_posts_on_post_votes_count"     t.index ["title"], name: "post_title_search"   end 
like image 564
Orsay Avatar asked Nov 30 '17 15:11

Orsay


People also ask

What does duplicate key value mean?

The duplicate occurrence value is a unique identifier, which is added to the key, which has duplicates in order to make it unique. When a new key is added, where keys of the same value exist, then the occurrence value of the last added key is incremented by one and used for the newly added key.


1 Answers

To fix the issue, we have to tell ActiveRecord to look at the sequence of the table:

ActiveRecord::Base.connection.reset_pk_sequence!('table_name') 

Now ActiveRecord should have the correct sequence value, and should be able to assign new id's properly.

To resolve error

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "moderations_reportable" DETAIL: Key (reportable_type, reportable_id)=(Post, 25) already exists. : INSERT INTO "moderations" ("blog_id", "reportable_type", "reportable_id", "created_at", "updated_at", "blog_type") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

As error occurred on 'moderations' table.

Run the following from rails console fix

ActiveRecord::Base.connection.reset_pk_sequence!('moderations') 

Thank you

like image 184
aashish Avatar answered Oct 08 '22 16:10

aashish