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

Question

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.

Answer 1

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.