This is a funky error, and I have fixed it manually, but want to understand the root behavior of why it happens. Sorry it's a bit long, it's kind of hard to explain, I'm doing my best.
I have a database table called RentalItems which has records created when a user submits a RentalRequest. Everything is going along smoothly, but one day I run a rake db:reset accidentally in production and wipe the database clean. But thanks to PGBackup, able to get all but 4 records back. Until we used PGBackup however, we had users submitting requests created on a clean database. So, when the PGBackup was restored, I manually put in those 4 records, along with the 4 requests that were created on the clean database (that I overwrote with the backup).
Visually speaking, here's what happened:
Database IDs @ point of rake db:reset
...
79 (backed up)
80 (backed up)
81 (not backed up)
82 (not backed up)
83 (not backed up)
84 (not backed up)
Database IDs after rake db:reset was obviously blank. At the moment when we were ready to do the restore, however, there had been some new entries filled in:
1
2
3
4
What we did was manually write down the info for these 4 records so we could recreate them AFTER the backup happened. So database IDs after the PGBackup restore looked like:
...
79
80
81 (manually created from the original DB, matching record #81)
82 (manually created from the original DB, matching record #82)
83 (manually created from the original DB, matching record #83)
84 (manually created from the original DB, matching record #84)
85 (manually created from the new DB, this would be record #1)
86 (manually created from the new DB, this would be record #2)
87 (manually created from the new DB, this would be record #3)
88 (manually created from the new DB, this would be record #4)
Yay! At this point, I'd expect any new RentalItems that the controller creates to pick up at ID = 89. So a user today tried to create something and was thrown an error message. Looking at the logs, here's what happened:
PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
ActiveRecord::RecordNotUnique (PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
DETAIL: Key (id)=(84) already exists.
Hmm... rather than starting at ID = 89, it started at 84! Now I didn't have time to investigate because I needed to solve the user's issue right then and there. How I solved it was by going into the production console and doing this:
a = RentalItem.new(name = "blah")
a.save(validate:false)
# console output
: INSERT INTO "rental_items" ("created_at", "itemname_id", "updated_at") VALUES ($1, $2, $3) RETURNING "id"
ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
DETAIL: Key (id)=(85) already exists.
a = RentalItem.new(name = "blah")
a.save(validate:false)
# console output
: INSERT INTO "rental_items" ("created_at", "itemname_id", "updated_at") VALUES ($1, $2, $3) RETURNING "id"
ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
DETAIL: Key (id)=(86) already exists.
a = RentalItem.new(name = "blah")
a.save(validate:false)
# console output
: INSERT INTO "rental_items" ("created_at", "itemname_id", "updated_at") VALUES ($1, $2, $3) RETURNING "id"
ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
DETAIL: Key (id)=(87) already exists.
a = RentalItem.new(name = "blah")
a.save(validate:false)
# console output
: INSERT INTO "rental_items" ("created_at", "itemname_id", "updated_at") VALUES ($1, $2, $3) RETURNING "id"
ActiveRecord::RecordNotUnique: PG::Error: ERROR: duplicate key value violates unique constraint "rental_items_pkey"
DETAIL: Key (id)=(88) already exists.
As you can see, every time I tried to save a new record, it incremented the ID up, inching toward an actual "free" row. The last time it did work:
a = RentalItem.new(name = "blah")
a.save(validate:false)
# console output
=> true
RentalItem.last
=> #<RentalItem id:89, name: "blah"...>
Can anyone explain why this was happening for future reference? I have a theory that I can't quite put my finger on... something about how when I run:
RentalItem.find(89).destroy
a = RentalItem.new(name = "blah")
a.save(validate:false)
a.id = 90
In other words, after being destroyed, the record of 89 is not rewritten by new create actions, instead the next record skips ahead to ID of 90 (i.e., the list of IDs would look like 87, 88, 90). So in this case, somehow ActiveRecord knows where it's at, but probably in my manual tampering with the database I did something to mess this up.
