django.db.utils.IntegrityError: duplicate key value violates unique constraint "spirit_category_category_pkey"

Question

The Error

I'm using django and spirit to build a website. In a test, when I was inserting new data into a table called spirit_category_category, I got the following error:

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

Note that there were already two other records in the table with ids of 1 and 2. So inserting Key(id)=(1) wouldn't work, of course. But the sql executed didn't include id field. That is, Key (id)=(1) was automatically generated by postgresql, why did it generate an already existed id?

The Reason

To find out the reason, I ran the following command in postgresql:

test_spiritdb=# select start_value, last_value, max_value from spirit_category_category_id_seq;
 start_value | last_value |      max_value      
-------------+------------+---------------------
           1 |          1 | 9223372036854775807
(1 row)

So basically, last_value is 1, so postgresql will generate Key (id)=(1) every time, I tried to alter it to 3, and everything is fine.

test_spiritdb=# alter sequence spirit_category_category_id_seq restart with 3;

I don't know how to fix it for a test

The test passed. But it is a test, so it is meaningless to alter a test table because the test database will be deleted and created again for every test, so the next time the test will fail again because last_value will still be generated as 1. So I wondered why django/postgresql would generate such an abnormal value for last_value? How to fix it? The models and migrations for category are as follows, if it helps.

models.py

# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from django.db import models
from django.utils.translation import ugettext_lazy as _
from django.core.urlresolvers import reverse
from django.conf import settings

from .managers import CategoryQuerySet
from ..core.utils.models import AutoSlugField

class Category(models.Model):

    parent = models.ForeignKey('self', verbose_name=_("category parent"), null=True, blank=True)

    title = models.CharField(_("title"), max_length=75)
    slug = AutoSlugField(populate_from="title", db_index=False, blank=True)
    description = models.CharField(_("description"), max_length=255, blank=True)
    is_global = models.BooleanField(_("global"), default=True,
                                    help_text=_('Designates whether the topics will be'
                                                'displayed in the all-categories list.'))
    is_closed = models.BooleanField(_("closed"), default=False)
    is_removed = models.BooleanField(_("removed"), default=False)
    is_private = models.BooleanField(_("private"), default=False)

    # topic_count = models.PositiveIntegerField(_("topic count"), default=0)

    objects = CategoryQuerySet.as_manager()

    class Meta:
        ordering = ['title', 'pk']
        verbose_name = _("category")
        verbose_name_plural = _("categories")

    def get_absolute_url(self):
        if self.pk == settings.ST_TOPIC_PRIVATE_CATEGORY_PK:
            return reverse('spirit:topic:private:index')
        else:
            return reverse('spirit:category:detail', kwargs={'pk': str(self.id), 'slug': self.slug})

    @property
    def is_subcategory(self):
        if self.parent_id:
            return True
        else:
            return False

0001_initial.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
import spirit.core.utils.models


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Category',
            fields=[
                ('id', models.AutoField(primary_key=True, verbose_name='ID', serialize=True, auto_created=True)),
                ('title', models.CharField(verbose_name='title', max_length=75)),
                ('slug', spirit.core.utils.models.AutoSlugField(db_index=False, populate_from='title', blank=True)),
                ('description', models.CharField(verbose_name='description', max_length=255, blank=True)),
                ('is_closed', models.BooleanField(verbose_name='closed', default=False)),
                ('is_removed', models.BooleanField(verbose_name='removed', default=False)),
                ('is_private', models.BooleanField(verbose_name='private', default=False)),
                ('parent', models.ForeignKey(null=True, verbose_name='category parent', to='spirit_category.Category', blank=True)),
            ],
            options={
                'ordering': ['title', 'pk'],
                'verbose_name': 'category',
                'verbose_name_plural': 'categories',
            },
        ),
    ]

0002_auto_20150728_0442.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
from django.conf import settings


def default_categories(apps, schema_editor):
    Category = apps.get_model("spirit_category", "Category")

    if not Category.objects.filter(pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK).exists():
        Category.objects.create(
            pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
            title="Private",
            slug="private",
            is_private=True
        )

    if not Category.objects.filter(pk=settings.ST_UNCATEGORIZED_CATEGORY_PK).exists():
        Category.objects.create(
            pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
            title="Uncategorized",
            slug="uncategorized"
        )


class Migration(migrations.Migration):

    dependencies = [
        ('spirit_category', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(default_categories),
    ]

0003_category_is_global.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
        ('spirit_category', '0002_auto_20150728_0442'),
    ]

    operations = [
        migrations.AddField(
            model_name='category',
            name='is_global',
            field=models.BooleanField(default=True, help_text='Designates whether the topics will bedisplayed in the all-categories list.', verbose_name='global'),
        ),
    ]

Solution

After lots of debugging, I finally found the solution. The reason is that I was trying to insert two another categories with specified ids, which would cause postgresql stop increasing the last_value of the relative sequence. Just as follows:

0002_auto_20150728_0442.py

if not Category.objects.filter(pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK).exists():
    Category.objects.create(
        pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
        title="Private",
        slug="private",
        is_private=True
    )

if not Category.objects.filter(pk=settings.ST_UNCATEGORIZED_CATEGORY_PK).exists():
    Category.objects.create(
        pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
        title="Uncategorized",
        slug="uncategorized"
    )

The way to fix this is simple, either change the last_value manually in django, or just don't specify the id, i.e. remove the following lines:

....
pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
....
pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
....

I guess if you let django undertake the task of managing id, it may not be a good idea to specify the id yourself when inserting new data.

Comments

[Searene]

I was inserting the same id into the table, just as you say. I removed the id part and everything was fine again. Thank you!