#27151: FK index created two times if referenced table PK is varchar
-----------------------------+--------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.3ca84ef37fe12fc04c0abfde0154e041%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
