Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#27151 closed Bug (invalid)

FK index created two times on PostgreSQL if referenced table PK is varchar

Reported by: Kamil Kujawiński Owned by: nobody
Component: Migrations Version: 1.8
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Django version: 1.8.14

Scenario 1. with creating index only

  1. Initial models:
class Foo(models.Model):
    guid = models.CharField(max_length=36, primary_key=True)


class Bar(models.Model):
    key = models.ForeignKey(Foo, db_index=False)
  1. Initial migration:
class Migration(migrations.Migration):

    dependencies = []

    operations = [
        migrations.CreateModel(
            name='Bar',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.CreateModel(
            name='Foo',
            fields=[
                ('guid', models.CharField(max_length=36, serialize=False, primary_key=True)),
            ],
        ),
        migrations.AddField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo', db_index=False),
        ),
    ]

  1. Changes in to model:
-    key = models.ForeignKey(Foo, db_index=False)
+    key = models.ForeignKey(Foo, db_index=True)

  1. Migration:
class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.AlterField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo'),
        ),
    ]

  1. Applying migration logs:
Operations to perform:
  Apply all migrations: myapp
Running migrations:
  Rendering model states... DONE
  Applying myapp.0002_auto_20160829_1936...[2016-08-29 19:37:55,385 pid=11084] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" DROP CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id"; (params [])
[2016-08-30 11:26:19,228 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" DROP CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid"; (params [])
[2016-08-30 11:26:19,230 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_uniq" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:26:19,230 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_uniq" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:26:19,235 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("guid") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:26:19,235 pid=25347] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_guid" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("guid") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:26:19,237 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_like" ON "myapp_bar" ("key_id" varchar_pattern_ops); (params [])
[2016-08-30 11:26:19,237 pid=25347] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_key_id_1647a22de55ad985_like" ON "myapp_bar" ("key_id" varchar_pattern_ops); (params [])
 OK


  1. Indexes created in postgres database
SELECT i.relname as indname, i.relowner as indowner, idx.indrelid::regclass, am.amname as indam, idx.indkey,
       ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) as k
             ORDER BY k) as indkey_names, idx.indexprs IS NOT NULL as indexprs, idx.indpred IS NOT NULL as indpred,
       idx.indisunique
FROM   pg_index as idx JOIN pg_class as i ON i.oid = idx.indexrelid JOIN pg_am as am ON i.relam = am.oid
WHERE  idx.indrelid::regclass='myapp_bar'::regclass;

                indname                 | indowner | indrelid  | indam | indkey | indkey_names | indexprs | indpred | indisunique 
----------------------------------------+----------+-----------+-------+--------+--------------+----------+---------+-------------
 myapp_bar_key_id_1647a22de55ad985_like |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_key_id_1647a22de55ad985_uniq |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_pkey                         |    16384 | myapp_bar | btree | 1      | {id}         | f        | f       | t


Scenario 2. indexes created in first migration

  1. Initial models:
class Foo(models.Model):
    guid = models.CharField(max_length=36, primary_key=True)


class Bar(models.Model):
    key = models.ForeignKey(Foo, db_index=True)

  1. Initial migration:
class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0033_auto_20160829_1931'),
    ]

    operations = [
        migrations.CreateModel(
            name='Bar',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.CreateModel(
            name='Foo',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ],
        ),
        migrations.AddField(
            model_name='bar',
            name='key',
            field=models.ForeignKey(to='myapp.Foo'),
        ),
    ]

  1. Applying migration logs:
Operations to perform:
  Apply all migrations: myapp
Running migrations:
  Rendering model states... DONE
  Applying myapp.0034_auto_20160830_1136...[2016-08-30 11:37:00,682 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_bar" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,682 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_bar" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,704 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_foo" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,704 pid=28406] DEBUG    | django.db.backends.schema | CREATE TABLE "myapp_foo" ("id" serial NOT NULL PRIMARY KEY); (params None)
[2016-08-30 11:37:00,749 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD COLUMN "key_id" integer NOT NULL; (params [])
[2016-08-30 11:37:00,749 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD COLUMN "key_id" integer NOT NULL; (params [])
[2016-08-30 11:37:00,750 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ALTER COLUMN "key_id" DROP DEFAULT; (params [])
[2016-08-30 11:37:00,750 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ALTER COLUMN "key_id" DROP DEFAULT; (params [])
[2016-08-30 11:37:00,751 pid=28406] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_30f69126" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:37:00,751 pid=28406] DEBUG    | django.db.backends.schema | CREATE INDEX "myapp_bar_30f69126" ON "myapp_bar" ("key_id"); (params [])
[2016-08-30 11:37:00,759 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED; (params [])
[2016-08-30 11:37:00,759 pid=28406] DEBUG    | django.db.backends.schema | ALTER TABLE "myapp_bar" ADD CONSTRAINT "myapp_bar_key_id_1647a22de55ad985_fk_myapp_foo_id" FOREIGN KEY ("key_id") REFERENCES "myapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED; (params [])
 OK
  1. Indexes created in postgres database


SELECT i.relname as indname, i.relowner as indowner, idx.indrelid::regclass, am.amname as indam, idx.indkey,
       ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM generate_subscripts(idx.indkey, 1) as k
             ORDER BY k) as indkey_names, idx.indexprs IS NOT NULL as indexprs, idx.indpred IS NOT NULL as indpred,
       idx.indisunique
FROM   pg_index as idx JOIN pg_class as i ON i.oid = idx.indexrelid JOIN pg_am as am ON i.relam = am.oid
WHERE  idx.indrelid::regclass='myapp_bar'::regclass;

                indname                 | indowner | indrelid  | indam | indkey | indkey_names | indexprs | indpred | indisunique 
----------------------------------------+----------+-----------+-------+--------+--------------+----------+---------+-------------
 myapp_bar_30f69126                     |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_key_id_1647a22de55ad985_like |    16384 | myapp_bar | btree | 2      | {key_id}     | f        | f       | f
 myapp_bar_pkey                         |    16384 | myapp_bar | btree | 1      | {id}         | f        | f       | t
(3 rows)

Note: I've edited manually in logs app name and migration numbers. I hope it didn't intefere this test case.

Change History (7)

comment:1 Changed 3 years ago by Tim Graham

Could you please check if the issue still exists on Django master?

comment:2 Changed 3 years ago by Tim Graham

Summary: FK index created two times if referenced table PK is varcharFK index created two times on PostgreSQL if referenced table PK is varchar
Triage Stage: UnreviewedAccepted

comment:3 Changed 3 years ago by Tim Graham

I'm not sure what I did 6 weeks ago when I confirmed the issue. I seem to remember that maybe it was the duplicated queries that appear in logging but I see that all queries are duplicated, so that seems to be caused by your logging setup. What do you mean by "FK index created two times"? There are a couple extra indexes on CharField due to #12234 -- are those the indexes that you're referring to?

comment:4 Changed 3 years ago by Maciej Gol

Isn't this just how indexes on varchar/text columns work? The regular index is used for exact match queries, whilst the _like index is supporting the LIKE operator?

comment:5 Changed 3 years ago by Tim Graham

Resolution: invalid
Status: newclosed

If that's what meant by "FK index created two times", yes. Closing absent some further clarification from the reporter.

comment:6 Changed 3 years ago by Markus Holtermann

Exactly what Maciej Gol said. VARCHAR fields with an db_index=True have 2 indexes. One for exact matches, one for LIKE.

comment:7 in reply to:  6 Changed 3 years ago by Kamil Kujawiński

Replying to Markus Holtermann:

Exactly what Maciej Gol said. VARCHAR fields with an db_index=True have 2 indexes. One for exact matches, one for LIKE.

Agree.

I thought that those are same indexes with only different name. My pg query doesn't show any differences between them. I found in Django code that _like index is created with varchar_pattern_ops option.

So problem is with my pg query, not with Django.

Sorry for bother.

Note: See TracTickets for help on using tickets.
Back to Top