CODEWITHSUNDEEP
djangopostgresqldjango-testingdjango-postgresql
Mario César
Mario César

Reputation: 3737

Django creates the test database for Postgresql with incorrect sequences start values

Using pyscopg2, as Django database default backend, and with very simple case for a test. It fails trying to create a user, with a IntegrityError exception

$ python manage.py test project.core.tests
nosetests --verbosity 1 project.core.tests --rednose
Creating test database for alias 'default'...
X.
-----------------------------------------------------------------------------
1) ERROR: Tests that a user with a sha1 hashed password is change to newer hash method

   Traceback (most recent call last):
    crowddeals/core/tests.py line 7 in setUp
      self.user1 = User.objects.create_user('user1', '[email protected]')
    env/lib/python2.7/site-packages/django/contrib/auth/models.py line 160 in create_user
      user.save(using=self._db)
    env/lib/python2.7/site-packages/django/db/models/base.py line 477 in save
      force_update=force_update, update_fields=update_fields)
    env/lib/python2.7/site-packages/django/db/models/base.py line 572 in save_base
      result = manager._insert([self], fields=fields, return_id=update_pk, using=using, raw=raw)
    env/lib/python2.7/site-packages/django/db/models/manager.py line 203 in _insert
      return insert_query(self.model, objs, fields, **kwargs)
    env/lib/python2.7/site-packages/django/db/models/query.py line 1588 in insert_query
      return query.get_compiler(using=using).execute_sql(return_id)
    env/lib/python2.7/site-packages/django/db/models/sql/compiler.py line 911 in execute_sql
      cursor.execute(sql, params)
    env/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py line 52 in execute
      return self.cursor.execute(query, args)
   IntegrityError: duplicate key value violates unique constraint "auth_user_pkey"
   DETAIL:  Key (id)=(1) already exists.

The default database is working, and is possible to add users. The tests fails just on the test database.

Looking at the test database, I see that the auto increment sequences for the primary keys for all tables has wrong start values, it takes the last used value from the default database as the start values on the test database.

For the default database, the User.id use the auto increment sequence.

CREATE SEQUENCE auth_user_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

And for the test database creates 

CREATE SEQUENCE auth_user_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 2
  CACHE 1;

This is obviously wrong, this happens also on other tables, as permissions.

CREATE SEQUENCE auth_permission_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 191
  CACHE 1;

I can't guess why this is happening, it has to create the sequences on the new test database starting from 1.

If I change the database engine backend to sqlite3, the exception don't happen. This just happens in postgresql.

How can I fix this? so my tests can start working again.

Upvotes: 4

Views: 1646

Answers (1)

pkoch
pkoch

Reputation: 2722

If you don't recreate the db with every test, then you've have to reset the sequences yourself.

Also, I think that the sequence definition you've posted were found when inspecting the db after the tests run with a db tool. In that case, I think your db tool is presenting you the SQL to create the sequence as it is: after it's been used.

The fact that it doesn't happen on sqlite has to do with it using a in-memory db by default. Therefore, it's being re-created in every test run.

Upvotes: 1

Related Questions