Django.db.utils.IntegrityError: duplicate key value violates unique key constraint....

Using Django Using the ORM
1

Hey,

I get a weird error trying to save related objects. Basically documents related to a model. I have a Postgres backend, and seems either the postgres backend or the django orm is confused for some reason.

I used to be able to save this model fine:

class DocumentProduit(models.Model):
	fk_produit = models.ForeignKey(Produit, on_delete=models.SET_DEFAULT,related_name="documents", null=False, blank=False, default=0)

I did drop & restaured the db (using pg_dumps) to some previous version. Since then (I think) I get this error trying to save my formsets:

django.db.utils.IntegrityError: duplicate key value violates unique constraint products_documentproduit_pkey" DETAIL: Key (id)=(21) already exists.

If I try it again, I get the same error message, but with Key (id)=(22) instead (the duplicated ID increases by 1 each time).

My conclusion is that for some reason, the latest id / head of the postgres table (DocumentProduit) is wrong somewhere. Instead of just finding that the latest object in DB is id=157, it somehow attempts to save it as id=1, id=2, etc… Does django keep track of that? Or is my issue rather with postgres itself? Any idea how restauring the database may have caused this issue?

2

Yes. Postgres uses what it calls a sequence object to track the next PK to assign. If you restore the database and that restore process doesn’t include the related sequence object, you end up in exactly this situation.

You can use the ALTER SEQUENCE statement (with the RESTART parameter) in psql to reset the sequence to the desired value.

You also want to ensure that your pg_dump command is written to include all sequences in your backup to prevent this from happening in the future.

2 Likes
3

Ah, I did change the pg_dump commands & options that I was using, so this makes a lot of sense, and actually come to think of I had a separate command for the linked files because of the way they are managed in the system.

Thanks a bunch, obvious once you know, but not easy to debug otherwise.