postgresql - Why do SQL id sequences go out of sync (specifically using Postgres)?

Question

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.

Answer 1

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.