[Django] #27151: FK index created two times if referenced table PK is varchar
Django
-----------------------------+--------------------
Reporter: kkujawinski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-----------------------------+--------------------
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)
}}}
2. 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),
),
]
}}}
3. Changes in to model:
{{{
- key = models.ForeignKey(Foo, db_index=False)
+ key = models.ForeignKey(Foo, db_index=True)
}}}
4. Migration:
{{{
class Migration(migrations.Migration):
dependencies = [
('myapp', '0001_initial'),
]
operations = [
migrations.AlterField(
model_name='bar',
name='key',
field=models.ForeignKey(to='myapp.Foo'),
),
]
}}}
5. 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
}}}
6. 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)
}}}
2. 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'),
),
]
}}}
3. 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
}}}
4. 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27151>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: kkujawinski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Could you please check if the issue still exists on Django master?
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:1>
Django
-----------------------------+------------------------------------
Reporter: kkujawinski | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* stage: Unreviewed => Accepted
Comment:
I could reproduce at d8ef5b0e6501692b8b767ebccddc936f496d77e8.
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:2>
Django
----------------------------------+------------------------------------
Reporter: Kamil Kujawiński | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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?
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:3>
Django
----------------------------------+------------------------------------
Reporter: Kamil Kujawiński | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 1.8
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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?
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:4>
Django
----------------------------------+------------------------------------
Reporter: Kamil Kujawiński | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.8
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* status: new => closed
* resolution: => invalid
Comment:
If that's what meant by "FK index created two times", yes. Closing absent
some further clarification from the reporter.
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:5>
Django
----------------------------------+------------------------------------
Reporter: Kamil Kujawiński | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.8
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:6>
Django
----------------------------------+------------------------------------
Reporter: Kamil Kujawiński | Owner: nobody
Type: Bug | Status: closed
Component: Migrations | Version: 1.8
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Kamil Kujawiński):
Replying to [comment:6 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/27151#comment:7>