What should a Django user know when moving from MySQL to PostgreSQL?

Question

Most of my experience with Django thus far has been with MySQL and mysqldb. For a new app I'm writing, I'm dipping my toe in the PostgreSQL water, now that I have seen the light.

While writing a data import script, I stumbled upon an issue with the default autocommit behavior. I would guess there are other "gotchas" that might crop up. What else should I be on the lookout for?

Answer 1

There's an inconsistency between Django's autocommit and the default PostgreSQL commit mode.

Out of the box, Django uses the default PostgreSQL mode "read committed" which combines all operations into a single transaction that ends when the db cursor goes out of scope. The problem arises when an error occurs during that series of operations. Postgres expects you to issue a rollback before continuing, and if you don't, psycopg2 throws an InternalError the next time you go to use the connection. If you're relying on the Django autocommit (the default), you're probably not going to rollback properly.

Luckily, psycopg2 has support for another mode of operation called "autocommit" wherein it doesn't set these transactions up. For those coming from MySQL (or trying to support both), this brings some sanity to the world. In 1.1 they added support to expose it. Add the following to your settings (needs to be changed for 1.2 syntax if you're on trunk)

DATABASE_OPTIONS = {
    "autocommit": True,
}

The discussion for Django ticket #3460 lays out the gritty details.

---