6

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.

Community
  • 1
  • 1
denishaskin
  • 3,305
  • 3
  • 24
  • 33
  • I wonder if it has something to do with the fact that it's a HABTM join table? – Beartech Jan 26 '15 at 06:00
  • 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 Jan 26 '15 at 16:48
  • 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 Jan 26 '15 at 22:49
  • 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 Jan 30 '15 at 00:03
  • This answer is very helpful and accepted https://stackoverflow.com/a/30639108/135160 – anka Aug 01 '18 at 08:20

1 Answers1

2

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.

CuriousMind
  • 33,537
  • 28
  • 98
  • 137
  • Well, I understand the what & why. The ActveRecord `reset_pk_sequence!` I mentioned above should fix this. What I don't really understand (and what I would like to prevent) is why this re-occurs on each new push of the app. – denishaskin Jan 26 '15 at 16:50
  • 1
    can you post a sample app that reproduces this? – CuriousMind Jan 27 '15 at 08:31
  • @CuriousMind. I'm having the same problem as the OP. Each time I try it the ID indexes by 1, so yes there is some counter that is off. But I have 130 items and have tables linked via the ID, so don't want to recreate the table. My PostgresQL (actually PostGIS) database already existed and I linked to it in my new Rails project. So does anyone have any idea where the counter lies and how to reset it? Maybe I can just try 125 more times and get to the end of the database. – Greg Jan 26 '16 at 19:07
  • Follow up: Edit: Keyboard Maestro to the rescue. And the Mac can save a bunch of pushes. So 20 right arrows at a time until I got to 131. Still would be curious about the counter. – Greg Jan 26 '16 at 19:17