Postgres Sequence Out Of Sync

Symptom

Insert operations start failing with duplicate-key or unique-constraint errors even though the application is not intentionally reusing the same id values. The original January 25, 2026 incident surfaced this pattern during OMR result writes, but the database failure mode is generic.

Likely Causes

• a past script inserted ids manually and never advanced the sequence;
• a bulk import or restore loaded rows without resetting sequence state;
• one table switched from manual ids to auto-increment while older rows still held larger values.

Diagnosis

Compare the sequence state with the actual maximum row id.

Representative SQL:

SELECT pg_get_serial_sequence('exam_results', 'examResultId');

SELECT last_value
FROM public."exam_results_examResultId_seq";

SELECT MAX("examResultId")
FROM exam_results;

If last_value is below the table's real maximum id, the next generated values will eventually collide with existing rows.

Fix

Reset the sequence to the current maximum row id.

Representative SQL:

SELECT setval(
  'public."exam_results_examResultId_seq"',
  (SELECT MAX("examResultId") FROM exam_results)
);

Apply the same check to related child tables that use their own sequences.

Prevention

• Treat setval(..., max(id)) as mandatory follow-up after manual id inserts, data loads, or restore jobs.
• Keep one id-generation strategy per table. Mixing manual ids with sequence-backed ids creates delayed failures.
• When the same unique-id error appears across many rows at once, inspect the sequence before debugging application payloads.

Source Incident Detail

The January 25, 2026 source incident happened in the OMR grading result path. A previous Python-script workflow inserted examResultId manually into JSON/imported rows. Later, the FastAPI/Prisma path stopped specifying ids and relied on the Postgres sequence.

Detail	Value
Affected table	exam_results
User-visible symptom	OMR grading could not finish because result inserts failed
Error	unique constraint failed on examResultId
Historical cause	manual ids inserted through the earlier Python/JSON upload path
Immediate repair	setval() sequence reset to the table maximum

The useful diagnosis signal was that the same unique-id error appeared for many students, not one malformed row. That pointed to the id generator rather than the payload.

The source record compared old and new id ranges:

Observation	Meaning
older rows had ids around 2909-2920	manual inserts had advanced row ids
newer sequence-backed rows were around 159-173, then 635-661	sequence had not caught up
collision began as the sequence reached existing historical ids	delayed failure from mixed id strategies

Apply the same reasoning after data restores, manual imports, table copies, or any migration that writes ids explicitly.