Opened 8 weeks ago

Last modified 4 weeks ago

#28646 new Bug

Migration calls "CREATE INDEX" when one already exists when 'unique' field attribute is added (PostgreSQL)

Reported by: Hari - 何瑞理 Owned by: nobody
Component: Migrations Version: 1.11
Severity: Normal Keywords: postgresql, migration, index
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Hari - 何瑞理)

PostgreSQL migration automatically creates an index for fields that set db_index=True. An example is SlugField, which sets this property implicitly. Thereafter when the unique=True property is added to the field the resultant migration script generates an AlterField object to apply this unique attribute. The schema editor then incorrectly detects this new unique=True attribute to indicate the need to create a like index statement on the field which causes an error as it conflicts with the already existing index.

The offending piece of code seems to be at django/db/backends/postgresql/schema.py:117.

 if ((not (old_field.db_index or old_field.unique) and new_field.db_index) or
                (not old_field.unique and new_field.unique)):
            like_index_statement = self._create_like_index_sql(model, new_field)
            if like_index_statement is not None:
                self.execute(like_index_statement)

If it's changed as:

 if (not (old_field.db_index or old_field.unique) and (new_field.db_index or new_field.unique)):
            like_index_statement = self._create_like_index_sql(model, new_field)
            if like_index_statement is not None:
                self.execute(like_index_statement)

this error won't occur.

PostgreSQL 9.5 introduces IF NOT EXISTS to the CREATE INDEX statement which if added to the schema template can also address this problem without changing the above logic.

I encountered the problem with SlugField() which implicitly sets db_index=True on PostgreSQL 9.4.

Interestingly, I only discovered this when I used django-tenant-schemas which adds a thin layer on top of the default Database router setting the schema search path before handing over the work to the default router. With a vanilla Django installation using default router, the second call to create a like index does not throw an error. However, upon reviewing the code, the logic does look incorrect. Also issuing the duplicate SQL statement in PostgreSQL console also throws an error.

I'm still investigating to see if this there's more to this than what I just described.

Change History (5)

comment:1 Changed 8 weeks ago by Hari - 何瑞理

Description: modified (diff)

comment:2 Changed 8 weeks ago by Tim Graham

That code was last touched in 9356f63a99957f01c14a9788617428a172a29fcb. Your proposal results in some tests failures. Can you write a test for tests/schema/tests.py that demonstrates this issue?

comment:3 Changed 6 weeks ago by Tim Graham

Resolution: worksforme
Status: newclosed

I couldn't reproduce this by changing SlugField() to SlugField(unique=True). Perhaps the bug is in django-tenant-schemas. Please reopen if you find that Django is at fault and add more specific steps to reproduce.

comment:4 Changed 4 weeks ago by Ariki

Resolution: worksforme
Status: closednew

Django tries to create a like index twice and fails when I try to make existing SlugField a primary key in a manually written migration. The code to reproduce:

import unittest

from django.db import connection, migrations, models
from django.db.migrations.state import ProjectState
from django.test import TestCase


class ChangePrimaryKeyTest(TestCase):

    def test_change_primary_key(self):
        # Set PostgreSQL messages locale to get error messages in English
        operation0 = migrations.RunSQL("SET lc_messages = 'C';")
        # Create a model with two fields
        operation1 = migrations.CreateModel(
            'SimpleModel',
            [
                ("field1", models.SlugField(max_length=20, primary_key=True)),
                ("field2", models.SlugField(max_length=20)),
            ],
        )
        # Drop field1 primary key constraint - this doesn't fail
        operation2 = migrations.AlterField(
            "SimpleModel",
            "field1",
            models.SlugField(max_length=20, primary_key=False),
        )
        # Add a primary key constraint to field2 - this fails
        operation3 = migrations.AlterField(
            "SimpleModel",
            "field2",
            models.SlugField(max_length=20, primary_key=True),
        )

        project_state = ProjectState()
        with connection.schema_editor() as editor:
            new_state = project_state.clone()
            operation0.database_forwards(
                "migrtest", editor, project_state, new_state)
            operation1.state_forwards("migrtest", new_state)
            operation1.database_forwards(
                "migrtest", editor, project_state, new_state)
            project_state, new_state = new_state, new_state.clone()
            operation2.state_forwards("migrtest", new_state)
            operation2.database_forwards(
                "migrtest", editor, project_state, new_state)
            project_state, new_state = new_state, new_state.clone()
            operation3.state_forwards("migrtest", new_state)
            operation3.database_forwards(
                "migrtest", editor, project_state, new_state)


Error message:

ERROR: test_change_primary_key (migrtest.tests.ChangePrimaryKeyTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/usr/lib/python3.6/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: relation "migrtest_simplemodel_field2_972171aa_like" already exists
Last edited 4 weeks ago by Ariki (previous) (diff)

comment:5 Changed 4 weeks ago by Tim Graham

Component: Database layer (models, ORM)Migrations
Summary: Migration calls "CREATE INDEX" when one already exists when 'unique' field attribute is addedMigration calls "CREATE INDEX" when one already exists when 'unique' field attribute is added (PostgreSQL)
Triage Stage: UnreviewedAccepted

I can reproduce as long as the three operations are in the same migration. The crash doesn't happen if you put the AlterField operations in a separate migration.

Note: See TracTickets for help on using tickets.
Back to Top