This article was originally published in 2020 and was imported from my old Substack newsletter.

The Problem

The other day, I was trying to attach an ActiveStorage attachment to an ActiveRecord model. But the darn thing kept returning PG::UniqueViolation.

Here it was:

ActiveRecord::RecordNotUnique (PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "index_active_storage_attachments_uniqueness")

DETAIL:  Key (record_type, record_id, name, blob_id)=(Inbox::Message, 334, embedded_attachments, 415) already exists.

So I checked it out. I went into Rails console and typed in my query:

ActiveStorage::Attachment.where(record_type: “Inbox::Message”, record_id: 334, name: “embedded_attachments”, blob_id: 415).any?

And it returned... false

Every once in a while, I encounter a bug like this and it can be quite frustrating trying to find the root cause of the issue.

The Research

The issue is that somewhere in Postgres, it believes that the record already exists. Something is off, and it looks like the index is the culprit.

The ActiveStorage::Attachment table migrations added an index like this:

index ["record_type", "record_id", "name", "blob_id"], name: "index_active_storage_attachments_uniqueness", unique: true

This is to ensure that duplicate attachments aren’t added — and this makes sense.

Unfortunately here, something went wrong and Postgres was not indexing things properly. I have my theories as to why this happened (a bug in the database, lack of memory, etc).

Whatever the case may be, the answer was found in this very helpful StackOverflow post.

The Solution

The pkey sequence of the database needed to be fixed, and it can be done like so:

ActiveRecord::Base.connection.tables.each do |table_name| 
  ActiveRecord::Base.connection.reset_pk_sequence!(table_name)
end

I’m not sure how memory intensive this is, especially on a large database. Regardless, it worked and I was able to attach my attachment without an issue.