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

Question

This has been set several times ( here and here , etc.).

Every time I click on a rail application on Heroku (at least the last few months, I would say), I need to reset my keys using a familiar

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

witchcraft. Otherwise, I get postgresql errors like this when I try to create new entries:

 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 in different tables, depending on what the first action was done in the application after clicking.)

As soon as I do a reset spell, it's fine until my next push to the hero ... even when my push does not include any migrations.

I am a little puzzled by why this is happening and what can be done to prevent it.

No, there is no data processing code in my deployment tasks.

Answer 1

This is because a primary key value ( id ) already exists. What for? Because the sequence of primary keys in postgres is confused. without looking at the database or knowing the scheme, it is difficult to propose a solution, but if your database can provide an idle time of 10-15 minutes. you may try

• If there is only one table, that is the problem. You can export all data to a new set of tables with new names without an identifier column.
• Delete existing tables and rename the table you just created to the name of the old tables.
• re-enable recording in our application.

But if the whole database is in a mess, then it needs something more complex, but I can’t say without looking at the diagram.