Rails on heroku: after push, get “PG::UniqueViolation: ERROR: duplicate key value violates unique constraint”

6
votes

This has been asked several times before (here and here, and more).

Every time I push my rails app to Heroku (for at least the last few months, I'd say), I have to reset my keys using the familiar

ActiveRecord::Base.connection.tables.each { |t|    ActiveRecord::Base.connection.reset_pk_sequence!(t) }

incantation. Otherwise I get postgresql failures like this when I try to create new records:

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "users_clients_pkey" DETAIL: Key (id)=(401) already exists. : INSERT INTO "users_clients" ("user_id", "client_id") VALUES (20, 46) RETURNING "id"

(This is an example; it happens on various tables, depending on what the first action is that's done on the app after a push.)

Once I do the reset-keys incantation, it's fine until my next push to heroku... even when my push does not include any migrations.

I'm a little baffled as to why this is happening and what can be done to prevent it.

No, there's no datatable manipulation code in my deployment tasks.

1
ruby-on-railsrubypostgresqlheroku
I wonder if it has something to do with the fact that it's a HABTM join table? – Beartech
I've updated the question to try and make it more clear that it doesn't happen only on this table; that was an example. I'm pretty sure it has happened on non-intersection tables as well, but I will watch more closely next time it happens. – denishaskin
The only I can think of to help diagnose is to watch the Heroku logs at the same time you push the update up until you get the error. Do a heroku logs --tail in another window before the push and watch it live. Compare it to the log in your local dev environment. – Beartech
I was pushing my Rails app to Heroku from a Mac. Then, I pushed it from a Windows machine and experienced this error. Are you also pushing from a Windows computer? – programmingfun11
This answer is very helpful and accepted stackoverflow.com/a/30639108/135160 – anka

1 Answers

2
votes

Its happening because the primary key(id) value already exists. Why? Because the primary key sequence in postgres is messed up. without looking at the database or knowing the schema, it difficult to suggest a solution but if your database can affort a downtime of 10-15mins. you can try

  1. If there is just one table which is problem. you can Export all data into new set of table with new names without ID column.
  2. drop existing tables and rename the newly created table to old tables's name.
  3. enable writes to our app again.

But if entire DB is in a mess, then it need something more elaborate but I can't tell without looking the schema.