14

I've seen solutions for updating a sequence when it goes out of sync with the primary key it's generating, but I don't understand how this problem occurs in the first place.

Does anyone have insight into how a primary key field, with its default defined as the nextval of a sequence, whose primary keys aren't set explicitly anywhere, can go out of sync with the sequence? I'm using Postgres, and we see this occur now and then. It results eventually in a duplicate key constraint when the sequence produces an id for an existing row.

Improve this question
2
  • 1
    After some research, in my particular case it was actually a Hibernate mapping that was generating its own IDs and ignoring the sequence. Not sure if I should delete the question in this case - it seems like others encounter the same issue. Commented Feb 16, 2012 at 17:03
  • 2
    You can just answer your own question. There's really no way for sequences to go "out of sync" except if you (or some middleware) don't use the sequence to generate primary keys for some or all rows. Commented Feb 16, 2012 at 17:53

2 Answers 2

Reset to default
13

Your application is probably occasionally setting the value for the primary key for a new row. Then postgresql has no need to get a new sequence and the sequence doesn't get updated.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is correct - a hibernate mapping was ignoring the sequence, so when a non-hibernate application inserted a row letting the sequence generate the id, the sequence was out of date.
-1

When a sequence number is allocated, it remains allocated, even if the TX that requested it is rolled back. So a number can be allocated that does not appear in the stable database. Of course, rows can also be deleted after they are created, so the maximum number found in the table need not be the maximum number ever allocated. This applies to any auto-incrementing type.

Also, depending on the technology used, separate sequences can be used with multiple tables, so a value might be missing from TableA but present in TableB. That could be because of a mistake in the use of sequence names, or it might be intentional.

Improve this answer

1 Comment

This is not an answer to this question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.