get_or_create failure with Django and Postgres (duplicate key value violates unique constraint)

Question

Thanks for taking time to read my question.

I have a django app with the following model:

class UserProfile(models.Model):
    user = models.OneToOneField(User)
    ...

class Visit(models.Model):
    profile = models.ForeignKey(UserProfile)
    date = models.DateField(auto_now_add=True, db_index=True)
    ip = models.IPAddressField()
    class Meta:
        unique_together = ('profile', 'date', 'ip')

In a view:

profile = get_object_or_404(Profile, pk = ...)
get, create = Visit.objects.get_or_create(profile=profile, date=now.date(), ip=request.META['REMOTE_ADDR'])
if create: DO SOMETHING

Everything works fine, except that the Postgres Logs are full with duplicate key errors:

2012-02-15 14:13:44 CET ERROR:  duplicate key value violates unique constraint "table_visit_profile_id_key"
2012-02-15 14:13:44 CET STATEMENT:  INSERT INTO "table_visit" ("profile_id", "date", "ip") VALUES (1111, E'2012-02-15', E'xx.xx.xxx.xxx') RETURNING "table_visit"."id"

Tried different solution e.g.

from django.db import transaction 
from django.db import IntegrityError

@transaction.commit_on_success
def my_get_or_create(prof, ip):    
    try:
        object = Visit.objects.create(profile=prof, date=datetime.now().date(), ip=ip)
    except IntegrityError:
        transaction.commit()
        object = Visit.objects.get(profile=prof, date=datetime.now().date(), ip=ip)
    return object

....

created = my_get_or_create(prof, request.META['REMOTE_ADDR'])
if created: DO SOMETHING

This only helps for MySQL? Does anyone know how to avaid the duplicate key value errors for postgres?

Answer 1

Another possible reason for these errors in get_or_create() is data type mismatch in one of the search fields - for example passing False instead of None into a nullable field. The .get() inside .get_or_create() will not find it and Django will continue with new row creation - which will fail due to PostgreSQL constraints.