Opened 12 years ago
Closed 9 years ago
#19750 closed Bug (fixed)
Postgres conflict on CREATE INDEX: "relation exists"
Reported by: | Owned by: | ||
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.5-beta-1 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
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
Change History (10)
comment:1 by , 12 years ago
Has patch: | set |
---|
comment:2 by , 12 years ago
Needs tests: | set |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 by , 12 years ago
Version: | 1.4 → 1.5-beta-1 |
---|
comment:4 by , 12 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:5 by , 12 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
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
comment:6 by , 11 years ago
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.
comment:7 by , 11 years ago
New pull request with tests:
https://github.com/django/django/pull/1813
comment:8 by , 11 years ago
Needs tests: | unset |
---|---|
Patch needs improvement: | set |
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.
comment:9 by , 11 years ago
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
comment:10 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
This issue seems to be fixed using the migrations framework in Django 1.7.
https://github.com/django/django/pull/701