Django unable to migrate PostgreSQL: constraint X of relation Y does not exist

Question

I'm trying to run a Django 1.11 migration on a PostgreSQL 9.6.5 database, and I'm getting the odd error:

  Applying myapp.0011_auto_20171130_1807...Traceback (most recent call last):
  File "manage.py", line 9, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 364, in execute_from_command_line
    utility.execute()
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 356, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/migrations/operations/models.py", line 536, in database_forwards
    getattr(new_model._meta, self.option_name, set()),
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 349, in alter_unique_together
    self._delete_composed_index(model, fields, {'unique': True}, self.sql_delete_unique)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 380, in _delete_composed_index
    self.execute(self._delete_constraint_sql(sql, model, constraint_names[0]))
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 120, in execute
    cursor.execute(sql, params)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/myproject/.env/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: constraint "idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq" of relation "myapp_mymodel" does not exist

The migration is changing a unique contract from including one column to two. Pretty simple. It needs to destroy the old index, "idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq", before creating the new one. However, it fails because it doesn't think the old one exists.

So I connected to the database with pgAdminIII and inspected the table, and contrary to the error message, the table does have an index called idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq.

I thought maybe Django is using slightly different connection parameters, and is connecting to a different database? Let's try inspecting it from inside a Django dbshell. So I started manage.py dbshell and ran:

SELECT *
FROM pg_stat_all_indexes     
WHERE indexrelname='idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq';

and it returned one row.

Why is Django unable to see this index during a migration, even though the index definitely exists in the database?

Answer 1

The problem turned out to be that I converted the database to PostgreSQL from MySQL using the tool pgloader, and this tool converts constraints by creating them as indexes in PostgreSQL, whereas the Django PG backend creates them as constraints. So when the migration runs, it only looks for constraints and doesn't find any.

I fixed this by dropping the index and re-creating it as a true constraint with:

DROP INDEX idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq;
ALTER TABLE public.myapp_mymodel ADD CONSTRAINT idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq UNIQUE(title);

After that, the Django migration ran correctly.