Fixing PG::UniqueViolation when there isn't a unique violation

It's easy to assume that PostgreSQL is working and the problem is in your code. But this time, it isn't.

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 returns…

false

Every once in a while, I encounter a bug like this and it’s frustrating to the n-th degree.

What’s happening behind the scenes

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.

You see, 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 wasn’t 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.

Solution to the rescue

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.

Subscribe to my Rails Dev Notes Newsletter

I work on Rails every day and come across edge cases such as the one you just read. I blog about them here and solutions to problems I’m working on.

Did you enjoy reading my dev notes? If so, I’d be super happy if you subscribed to my Rails Dev Notes newsletter so we can keep in touch and exchange ideas. Until next time, take care!

Simon Chiu
@geetfun