I encountered the following interesting bug at work:
- When user has restored a database backup and logs into our product (website), the website crashes with an error indicating the primary key of data objects is duplicated and violates the unique key constraint.
Until then, I haven’t worked on database backup and restore script before and I was not sure what happened much. It seemed super interesting though as I am interested in database and was curious why there would be the duplication of primary keys.
Fortunately, our staff developer kindly offered help to look at the issue together and I’m so thankful that learned so much from this:
- In PostgreSQL (or database in general), there is a table called sequence table which the database system uses in order to track the primary key of each data object and to keep the uniqueness of primary keys by incrementing accordingly.
- In our database restore script, PostgreSQL was supposed to use the
last_valueof the sequence table to increment by 1 for the primary key of a newly created database object. However, for some reason, PostgreSQL was using thelast_valueas the new primary key of the new object. As a result, the violation of unique key constraint occurred and the website crashes with the error. - As a solution, we updated our database restore script to be incremented by 1 so that the
last_valuewould be the correct new primary key for the next new object.
What still remains unclear to me is why this error suddenly occurred this time. I wonder if this might be related to the new PostgreSQL version update or some changes added to our codebase. I’d like to look into PostgreSQL more for this.
Commands
CREATE SEQUENCE:
creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name name. The generator will be owned by the user issuing the command.
After a sequence is created, you use the functions
nextval,currval, andsetvalto operate on the sequence.
Although you cannot update a sequence directly, you can use a query like:
SELECT * FROM name;to examine the parameters and current state of a sequence. In particular, thelast_valuefield of the sequence shows thelast valueallocated by any session. (Of course, this value might be obsolete by the time it’s printed, if other sessions are actively doingnextvalcalls.)