Handling PostgreSQL Integrity Errors in Django
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) # Throws an error if an IntegrityError was previously thrown. myOtherRecord.save()
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: # Required to clear PostgreSQL's failed transaction. connection.close() myOtherRecord = models.MyOtherTable(data=blah) # Now works correctly. myOtherRecord.save()
Filed under: Django, MySQL, PostgreSQL
Thanks, this helped me!
This would have helped and saved me a lot of time
I discovered this solution independently and I tried with every combination of transaction, connection, ecc: this was the only workaround that worked.
even after quitting and restarting the python shell… I was still unable to execute a query.. but this cleared it up..
django 1.2 dev
I was thinking about setting the PostgreSQL variable ON_ERROR_ROLLBACK to ‘on’, but this is a much better solution!
Thanks Chris.
Thanks for the post. I’ve been thinking and googling all afternoon for this.
A colleague pointed me at transaction.rollback(), which works the same, but feels less dirty.
Also see: https://docs.djangoproject.com/en/dev/topics/db/transactions/#transaction-rollback
@JH, True, transaction.rollback() will work in code that’s under transaction management, but will fail otherwise (e.g. in a shell). My solution will work in all cases that I’m currently aware of.
The connection.close() trick solved a problem I had when using the sqlite3 backend: My Django app runs as a win32 service and locked the sqlite database whenever an IntegrityError occurred, so that other non-service apps could no longer access the database.
Since google couldn’t find any solution for this, I just thought to share it here - and to thank you for your post, of course