Database: Random jumps in the primary key: Duplicate key value violates unique constraint "attendance_final_pkey"

[venkat.b.mmbay]

• Goal: We have an app that is used to store attendance data. It is backed by a table in the database, named attendance. We have a front end that accepts values to be entered into the database table. We were hit with the error Duplicate key value violates unique constraint "attendance_final_pkey" recently.

On debugging and looking at the logs, we realized the primary key of the table, was being incremented randomly, (ie. not just by 1 on every insert. It would have random jumps)

In the screenshot 1 below, we entered the data on Sept 13th, notice the jump from 5 to 842 - I'm not sure why it chose to jump to 842.

In screenshot2 below, on Oct 5th 9:24AM when the attendance was taken, it decided to start incrementing from 838 .. eventually hitting the collision with 842, which started giving us the duplicate pkey error

around 1:14 we reset the sequence val to pick max, it it starts from 1050

This is the set of commands we ran to reset the sequence val:
CREATE SEQUENCE attendance_id_seq;

ALTER TABLE attendance ALTER COLUMN id SET DEFAULT nextval('attendance_id_seq');

SELECT setval('attendance_id_seq', (SELECT MAX(id) FROM attendance));

Now after we reset the sequence it seems to be ok, but we aren't 100% sure if we will hit that error again.

So my question is, what typically causes that error? We don't manually touch the primary keys. We don't manually enter records into the table.

Is there a way to figure out what kind of auto increment Primary key policy we set when the table was created?

[Darren]

Hi @venkat.b.mmbay! Welcome to the community.

Creating a new table in RetoolDB automatically configures an id column of type integer that is tagged as the PRIMARY KEY and that auto-increments using a corresponding sequence:

Note that it is possible to overwrite this default configuration when creating the table, but it doesn't look like it's possible to go back and see if that was actually done in this case.

Generally speaking, it's natural to see gaps in the ids assigned by any sort of auto-incrementing sequence because of deletions and transactions that get rolled back. Systems with super high concurrency can also cause this, but typically only if the cache is configured to a value greater than 1.

A primary key conflict is almost always the result of manual insertions or unintended changes to the underlying sequence. One thing that might be interesting is to manually connect to your backing database with psql using the connection info found here:

Once you've connected, you can run the command \d to see all relations and \d relation_name to see detailed information about a particular one. See if you can figure out which sequence, if any, that the table might have used originally!