Code

Opened 15 months ago

Last modified 6 months ago

#19750 new Bug

Postgres conflict on CREATE INDEX: "relation exists"

Reported by: pricco@… 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

Attachments (0)

Change History (9)

comment:1 Changed 15 months ago by anonymous

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 Changed 15 months ago by claudep

  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted

comment:3 Changed 15 months ago by pricco

  • Version changed from 1.4 to 1.5-beta-1

comment:4 Changed 14 months ago by marw85

  • Owner changed from nobody to marw85
  • Status changed from new to assigned

comment:5 Changed 14 months ago by marw85

  • Owner marw85 deleted
  • Status changed from assigned to 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 Changed 8 months ago 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.

comment:7 Changed 6 months ago by pricco

New pull request with tests:
https://github.com/django/django/pull/1813

comment:8 Changed 6 months ago by claudep

  • 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 Changed 6 months ago 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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from (none) to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.