Fixture interoperability problem between 'postgresql_psycopg2' and 'sqlite3'

[alexm]

I stumbled across a data migration problem while developing a project with django.
I use Django 1.1 (release on python 2.5) on OS X with a 'sqlite3' database backend. After finishing the code I set-up the default data for the application which includes a few entries for some models I have, which need to be initialized in the production environment. I intended to start off with a fresh database (deleting the DB file and run a dbsync afterwards, in order to have clean slate) and added all the necessary default data via the django admin interface. Everything went fine and the app even got through staging tests (still using the sqlite database backend).
Deploying the project on the live environment proved to be difficult (Gentoo Linux, Python 2.5, django 1.1 release, PostgreSQL backend).

After inconclusive error messages at the database initialization and some digging around the in the sources of django, I found the code lines in my project which caused two problems:

models.py in entity app dir

class Entity(models.Model):
    .... model code

class EntityPermissions(models.Model):
    entity = models.ForeignKey('entity.Entity', verbose_name = _("Entity"))
    .... other datafield definitions

views.py of some app directory

...
import myproject.entity.models import Entity

try:
    DEFAULT_ENTITY = Entity.objects.get(short_name = 'NSA')
except:
    DEFAULT_ENTITY = None

... view code 

1. manage.py syncdb ... comes up with a exception

The exception happens in the postgresql database backend and just explains that something the SQL command chain went wrong and dbsync therefore fails. syncdb also writes suddenly (in red) that the models are inconclusive now, it states that my declaration of the ForeignKey Field 'EntityPermissions' does not find Entity anymore. Changing from the indirect reference to a direct reference of the model Entity made no difference.

It turns out that the try / except line for DEFAULT_ENTITY causes this. After commenting out the try / except and just adding a 'DEFAULT_ENTITY = None' the dbsync is successful. This only happens with the postgresql backend and is not an issue with the sqlite3 backend at all.

2. After fixing the problem with the workaround, I was not able to load my fixture made from the sqlite3 database backend.

Even after the changing back my DEFAULT_ENTITY try / except stuff, the loaddata for my sqlite3 fixture failed with an database error. Taking a close look into the freshly syncdb'd (and empty) databases showed that the written data in the table 'django_content_type' had nearly similar entries. The way syncdb initialized this table under sqlite3 (no code change on my side) and on postgresql (with the workaround) is different. Up to a certain row id everything was identical (as it should be) but the row for my model Entity moved from id 28 to id 32 and the following models changed order as well. This unexpected order change made loaddata fail. After rearranging the order in the postgresql database by hand my fixture successfully loaded.

So either it is not allowed to have default objects loaded at startup of the app (and I entirely missed that in the documentation of django) or the postgresql database backend seems to be broken somehow because it behaves differently on a syncdb command.

[Russell Keith-Magee]

I suspect that this is a variant of #7052.