python - Django 无法迁移 PostgreSQL : constraint X of relation Y does not exist

Question

我正在尝试在 PostgreSQL 9.6.5 数据库上运行 Django 1.11 迁移，但出现奇怪的错误:

  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

迁移正在将唯一契约(Contract)从包含一列更改为两列。很简单。它需要在创建新索引之前销毁旧索引“idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq”。然而，它失败了，因为它认为旧的不存在。

所以我用 pgAdminIII 连接到数据库并检查了表，与错误消息相反，表确实有一个名为 idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq 的索引。

我想也许 Django 使用的连接参数略有不同，并且正在连接到不同的数据库？让我们尝试从 Django dbshel​​l 内部检查它。所以我启动了 manage.py dbshel​​l 并运行:

SELECT *
FROM pg_stat_all_indexes     
WHERE indexrelname='idx_32269_myapp_mymodel_title_333195ae82ac2107_uniq';

它返回了一行。

为什么 Django 在迁移过程中看不到这个索引，即使该索引肯定存在于数据库中？

Answer 1

问题原来是我使用工具 pgloader 将数据库从 MySQL 转换为 PostgreSQL，并且该工具通过在 PostgreSQL 中将它们创建为索引来转换约束，而 Django PG 后端将它们创建为约束。因此，当迁移运行时，它只会查找约束而不会找到任何约束。

我通过删除索引并将其重新创建为真正的约束来修复此问题:

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);

之后，Django 迁移正确运行。