Recently I ran into a problem, which bugged me for days. I’m using django and spirit to build a website, there’s a model called category in spirit, which is like this
The only thing you need to know is that, there’s no such thing as an
id in the model, which means django and postgresql will take care of the primary key. It seems fine, right? Until I ran a test, which created a category:
This is a very simple test,
create_category is only responsible for creating a brand new category. Everything should work fine. Then I got an error:
django.db.utils.IntegrityError: duplicate key value violates unique constraint "spirit_category_category_pkey"
Is there an already existing
Key (id)=(1)? I leafed through the migration file, and found this:
if not Category.objects.filter(pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK).exists():
So django will create two default categories due to the migration file. OK, that’s fine. But why did postgresql insert a record with a
Key (id)=(1) instead of
Key (id)=(3)? Maybe it’s django’s fault? Maybe django was trying to insert a specified record with
Key (id)=(1)? To find out the reason, I debugged all the way to this part:
def execute(self, sql, params=None):
This was where the insert happened. I checked the sql and params, which was:
sql = 'INSERT INTO "spirit_category_category" ("parent_id", "title", "slug", "description", "is_global", "is_closed", "is_removed", "is_private") VALUES (%s, %s, %s, %s, %s, %s, %s, %s) RETURNING "spirit_category_category"."id"'
So django didn’t include the
id part, but why didn’t postgresql auto increment the
id? After searching online for a long time, I found this website. I followed its process and ran the following code to find out the
last_value of the
id sequence(the primary key is usually a sequence in postgresql if you use django to generate the tables automatically)
SELECT last_value from spirit_category_category_id_seq;
And the answer I got was
1. It’s ONE! What does it mean? It means the next
id to be generated will be
1. This is so not what we want. This is why the error happened.
Let’s alter it to
alter sequence spirit_category_category_id_seq restart with 3
Continue the test, and you will pass it. Wait, this is a test, right? So the next time you run the test, it will create all kinds of brand new tables again in order to start a fresh test. So it’s meaningless to alter the sequence, because it would be flushed. Don’t believe it? Run the test again, and it will fail.
So how to fix this? Just remove the line which
id was specified in the migration file, i.e. let postgresql handle the primary key for us. Don’t insert the primary key manually, don’t try to calculate the next avaible id number, you will mess it up. In my case, I just need to remove the following two lines in file
Run the test again, it will pass.
It’s a long story, I’ve debugged for days to find out the reason, but it’s totally fine. Though the solution is quite simple, I have to fight all the way to find. During the process, I read a lot of django source code, I learned a lot of python features. I learned how postgresql’s sequence worked. It’s very helpful to me.