[Django] #19750: Postgres conflict on CREATE INDEX: "relation exists"
Django
----------------------------------------------+--------------------
Reporter: pricco@… | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
The syncdb index creation fails on postgres when a db_table name is equal
to the union of a db_table name and a inner foreign key column name.
`<db_table>_id == <db_table>_<fk_column>_id`
{{{
class OrganizationType(models.Model):
id = models.CharField(max_length=1, primary_key=True)
class Meta:
db_table = 'organization_type'
class Organization(models.Model):
type = models.ForeignKey(OrganizationType)
class Meta:
db_table = 'organization'
}}}
`<organization_type>_id == <organization>_<type>_id`
Exception:
{{{
DatabaseError: relation "organization_type_id_like" already exists
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/19750>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: pricco@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by anonymous):
* needs_docs: => 0
* has_patch: 0 => 1
* needs_tests: => 0
* needs_better_patch: => 0
Comment:
https://github.com/django/django/pull/701
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:1>
Django
Reporter: pricco@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | Resolution:
Keywords: | Needs documentation: 0
Has patch: 1 | Patch needs improvement: 0
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by claudep):
* needs_tests: 0 => 1
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:2>
Django
Reporter: pricco@… | Owner: nobody
Type: Bug | Status: new
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by pricco):
* version: 1.4 => 1.5-beta-1
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:3>
Django
Reporter: pricco@… | Owner: marw85
Type: Bug | Status: assigned
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by marw85):
* owner: nobody => marw85
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:4>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by marw85):
* owner: marw85 =>
* status: assigned => new
Comment:
There is no bug on django 1.6 and postgresql 9.0.5
Following indexes was created:
organization_pkey
organization_type_id (on table organization)
organization_type_id_like (on table organization)
organization_type_pkey
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:5>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timo):
I can reproduce this using 1.6 and Postgres. `syncdb` with the above
models gives the error `Failed to install index for polls.Organization
model: relation "organization_type_id_like" already exists`. The patch
still lacks tests, however.
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:6>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by pricco):
New pull request with tests:
https://github.com/django/django/pull/1813
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:7>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by claudep):
* needs_better_patch: 0 => 1
* needs_tests: 1 => 0
Comment:
Thanks for the updated patch.
However, I investigated some more, and I think I found the basic issue. At
some point in time, `sql_indexes_for_field` has been customized for
PostgreSQL because it needed some special indexes (see [f1ea26dd99415d]).
Then, the common `sql_indexes_for_field` received several fixes and
improvements, namely a change in the index naming using a hash of column
names ([9d8492c169d]), without having the PostgreSQL version being
updated.
For example, while investigating this, I found a bug in that `sqlindexes`
and `sqldropindexes` do not use the same index names and are therefore
broken for PostgreSQL!
I think that the proper fix for this ticket is to somewhat reconcile
`sql_indexes_for_field` implementations. The new index naming usage will
automatically fix the current issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:8>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: new
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Comment (by claudep):
This ''might'' be a solution:
{{{
--- a/django/db/backends/postgresql_psycopg2/creation.py
+++ b/django/db/backends/postgresql_psycopg2/creation.py
@@ -65,8 +65,9 @@ class DatabaseCreation(BaseDatabaseCreation):
"(%s%s)" % (style.SQL_FIELD(qn(f.column)),
opclass) +
"%s;" % tablespace_sql)
+ index_name = "%s_%s" % (model._meta.db_table,
self._digest([f.name]))
if not f.unique:
- output = [get_index_sql('%s_%s' % (db_table, f.column))]
+ output = [get_index_sql(index_name)]
# Fields with database column types of `varchar` and `text`
need
# a second index that specifies their operator class, which
is
@@ -74,9 +75,9 @@ class DatabaseCreation(BaseDatabaseCreation):
# C locale. See #12234.
db_type = f.db_type(connection=self.connection)
if db_type.startswith('varchar'):
- output.append(get_index_sql('%s_%s_like' % (db_table,
f.column),
+ output.append(get_index_sql('%s_like' % index_name,
' varchar_pattern_ops'))
elif db_type.startswith('text'):
- output.append(get_index_sql('%s_%s_like' % (db_table,
f.column),
+ output.append(get_index_sql('%s_like' % index_name,
' text_pattern_ops'))
return output
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:9>
Django
Reporter: pricco@… | Owner:
Type: Bug | Status: closed
Component: Database layer | Version:
(models, ORM) | 1.5-beta-1
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1
Changes (by timgraham):
* status: new => closed
* resolution: => fixed
Comment:
This issue seems to be fixed using the migrations framework in Django 1.7.
--
Ticket URL: <https://code.djangoproject.com/ticket/19750#comment:10>