I'm using Django 2.2 and my question is: does transaction.atomic roll back increments to a pk sequence?

Below is the background bug I wrote up that led me to this issue

I'm facing a really weird issue that I can't figure out and I'm hoping someone has faced a similar issue.

An insert using the django ORM .create() function is returning django.db.utils.IntegrityError: duplicate key value violates unique constraint "my_table_pkey" DETAIL: Key (id)=(5795) already exists.

Fine. But then I look at the table and no record with id=5795 exists!

SELECT * from my_table where id=5795; shows (0 rows)

A look at the sequence my_table_id_seq shows that it has nonetheless incremented to show last_value = 5795 as if the above record was inserted. Moreover the issue does not always occur. A successful insert with different data is inserted at id=5796. (I tried reset the pk sequence but that didn't do anything, since it doesnt seem to be the problem anyway)

I'm quite stumped by this and it has caused us a lot of issues on one specific table. Finally I realize the call is wrapped in transaction.atomic and that a particular scenario may be causing a double insert with the same pk.

So my theory is: The transaction atomic is not rolling back the increment of the

CodePudding user response：

Postgres sequences do not roll back. Every time they are touched by a statement they advance whether the statement succeeds or not. For more information see Notes section here Create Sequence.