badly formed Table Index and constraint's name when `Meta.db_table` is set with quoted string

[Carlos Leite]

2 cases where the error occur .
always because it was set a Meta.db_table quoted custom name

The same models.Model was tested twice, with index been set with field.db_index and Meta.indexes

It was tested with

Django 1.11
PostgreSQL

Model

class Publisher(models.Model):

    name = models.CharField(verbose_name='publisher name', max_length=50, null=False)

    class Meta:
        db_table = '"tbl_publisher_litle_name"'
        get_latest_by = "name"
        ordering = ['name', ]
        verbose_name = 'Publiser'
        verbose_name_plural = 'Publishers'
        indexes = [
            models.Index(fields=['name', ]),
        ]

Migration traceback

Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testapp
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying sessions.0001_initial... OK
  Applying testapp.0001_initial...Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
    utility.execute()
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/__init__.py", line 355, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/migration.py", line 129, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/operations/models.py", line 785, in database_forwards
    schema_editor.add_index(model, self.index)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 330, in add_index
    self.execute(index.create_sql(model, self))
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 119, in execute
    cursor.execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: zero-length delimited identifier at or near """"
LINE 1: CREATE INDEX ""tbl_publis_name_b0b929_idx" ON "tbl_publisher...
                     ^

(dj111) 20171202.Sat16:15:27cadu>/Volumes/p10G/prj/dj_datadictionary_testproject/testproject>

And again, when db_index is set on field ...
the migration is OK, but the error continues.

Model with no Meta.indexes but field.index=True

class Publisher(models.Model):
    """
    Book's Author - author is a Book's model supplement.
    """
    name = models.CharField(verbose_name='publisher name', max_length=50, null=False,  db_index=True)

    class Meta:
        db_table = '"tbl_publisher_litle_name"'
        get_latest_by = "name"
        ordering = ['name', ]
        verbose_name = 'Publiser'
        verbose_name_plural = 'Publishers'
        # indexes = [
        #     models.Index(fields=['name', ]),
        # ]

    def __unicode__(self):
        return self.name

Migration

migrations.CreateModel(
            name='Publisher',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('name', models.CharField(db_index=True, max_length=50, verbose_name='publisher name')),
            ],
            options={
                'get_latest_by': 'name',
                'ordering': ['name'],
                'verbose_name_plural': 'Publishers',
                'db_table': '"tbl_publisher_litle_name"',
                'verbose_name': 'Publiser',
            },
        ),
        migrations.AddField(
            model_name='book',
            name='publisher',
            field=models.ForeignKey(blank=True, null=True, on_delete=django.db.models.deletion.CASCADE, to='testapp.Publisher'),
        ),
    ]

Migration "2" traceback

$python manage.py  makemigrations
Migrations for 'testapp':
  testproject/testapp/migrations/0001_initial.py
    - Create model Author
    - Create model Book
    - Create model BookOtherTitle
    - Create model Publisher
    - Add field publisher to book
(dj111) 20171202.Sat16:45:19cadu>/Volumes/p10G/prj/dj_datadictionary_testproject/testproject>
cadu.[532]$python manage.py  migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions, testapp
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying sessions.0001_initial... OK
  Applying testapp.0001_initial...Traceback (most recent call last):
  File "manage.py", line 22, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
    utility.execute()
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/__init__.py", line 355, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 204, in handle
    fake_initial=fake_initial,
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 115, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 145, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/migrations/executor.py", line 244, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 92, in __exit__
    self.execute(sql)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/base/schema.py", line 119, in execute
    cursor.execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/Users/cadu/Envs/dj111/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: syntax error at or near "table_bo"
LINE 1: ...TRAINT "table_book_double_qu_book_id_2cd88caf_fk_"table_bo" ...
                                                             ^

[Mariusz Felisiak]

This ticket describes two issues:

• incorrect name of class-based model indexes for models with quoted db_table,
• incorrect name of foreign-key constraint for models with quoted db_table.

[Simon Charette]

Thank you for your report Carlos. Original ​@django-user thread.

[Mariusz Felisiak]

​PR for the issue with class-based model indexes.

[Mariusz Felisiak <felisiak.mariusz@…>]

In f79d9a32:

Refs #28876 -- Fixed incorrect class-based model index name generation for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In fc48047:

Refs #28876 -- Fixed incorrect foreign key constraint name for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 73ab743:

[2.0.x] Refs #28876 -- Fixed incorrect class-based model index name generation for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

Backport of f79d9a322c6008e5fada1453aebfb56afc316cc8 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 741711f8:

[2.0.x] Refs #28876 -- Fixed incorrect foreign key constraint name for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

Backport of fc48047586a8f92262f55d9d2bfb976325844b23 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3e52fd7:

[1.11.x] Refs #28876 -- Fixed incorrect class-based model index name generation for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

Backport of f79d9a322c6008e5fada1453aebfb56afc316cc8 from master

[Mariusz Felisiak <felisiak.mariusz@…>]

In 1decd019:

[1.11.x] Refs #28876 -- Fixed incorrect foreign key constraint name for models with quoted db_table.

Thanks Simon Charette and Tim Graham for the review and Carlos E. C.
Leite for the report.

Backport of fc48047586a8f92262f55d9d2bfb976325844b23 from master