#28646: Migration calls "CREATE INDEX" when one already exists when 'unique'
field
attribute is added
-------------------------------------+-------------------------------------
Reporter: Hari - 何瑞理 | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
postgresql,migration,index | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Hari - 何瑞理:
Old description:
> 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.
>
> I'm still investigating to see if this there's more to this than what I
> just described.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/28646#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/066.2347a87d19f6f339174ae8d4d375c97a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
