chrisspenblog

I have some basic Django code that attempts to insert a new record into a PostgreSQL database. It’s wrapped in a try/except statement in case a unique constraint is violated, in which case I’ll typically ignore it and move on.

So the general pattern is:

from django.db import IntegrityError
import models
try:
    myNewRecord = models.MyTable(data=blah)
    myNewRecord.save()
except IntegrityError:
    pass

myOtherRecord = models.MyOtherTable(data=blah)
myOtherRecord.save() # Throws an error if an IntegrityError was previously thrown.

I’d been previously using MySQL, but after switching to PostgreSQL I started getting a strange error when I tried to do any model access after an IntegrityError had been thrown, even though this access was outside the try/except:

“ProgrammingError: current transaction is aborted, commands ignored until end of transaction block”

What I eventually discovered, with a little help, is that Django’s PostgreSQL wrapper has some unusual transaction handling, so that if an error occurs then the entire connection must be closed to actually close the transaction.

The simple fix was to add connection.close() to the my except clause:

from django.db import IntegrityError, connection
import models
try:
    myNewRecord = models.MyTable(data=blah)
    myNewRecord.save()
except IntegrityError:
    connection.close() # Required to clear PostgreSQL's failed transaction.

myOtherRecord = models.MyOtherTable(data=blah)
myOtherRecord.save() # Now works correctly.

Filed under: Django, MySQL, PostgreSQL