0

I recently had the very unpleasant experience of the postgresql sequence falling out of sync with the table id in a rails app. Until this happened, rails 'magic' meant that I had never known what a postgresql sequence was - it had always happened automagically. Even more alarmingly, I didn't even know when I had caused this 'out of sync' issue to occur (it was only when later records were created that the errors were thrown)

I managed to wade my way through the drama. But now I want to understand all the possible causes of such a severe problem as the postgresql sequence falling out of sync with the id in a rails table. I want to know this so I can avoid it in the future.

I caused it to happen by creating new records manually where I specified the id. E.g. User.create(id: 4566, name: "Jo", email: "jo@gmail.com") (importantly, creating the record without specifying the id would have avoided the issue i.e. User.create(name: "Jo", email: "jo@gmail.com")

My question: in addition to specifying ids in newly created records, what other things does a rails dev need to know will cause this issue?

stevec
  • 41,291
  • 27
  • 223
  • 311

1 Answers1

0

A pg sequence does not follow transaction semantics, it just returns its older value +1 every time it's asked to. If you open a transaction, create some records, but then rollback, the records you created will not be persisted in the DB but the sequence will still be incremented the next time it's used.

It cannot follow transaction semantics because it's supposed to keep track of a "global state counter". If two concurrent transactions were to create a new row in the same table, we want both to have different ids, for example.

My advice would be to not explicitly set id's on the DB. If you need a custom identifier, use another column.

rogercampos
  • 1,676
  • 1
  • 10
  • 8
  • "create some records, but then rollback, the records you created will not be persisted in the DB but the sequence will still be incremented the next time it's used" that's very interesting. Does that mean it won't hurt to have a sequence that is *ahead* of the table id? (obviously the opposite is not true given it could cause duplicates, which, incidentally, is how I became aware of the sequence) – stevec Jun 19 '19 at 00:28
  • "Does that mean it won't hurt to have a sequence that is ahead of the table id?" no, that's a default behavior, will always happen. – rogercampos Jun 19 '19 at 14:11