Opened 6 years ago

Closed 6 years ago

#25317 closed Bug (fixed)

Altering a 1to1 field to FK on Postgres drops uniqueness but does not create index

Reported by: synotna Owned by: nobody
Component: Migrations Version: 1.8
Severity: Normal Keywords:
Cc: a@…, Shai Berger Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Changing a OneToOneField to a ForeignKey does not trigger an index to be created by the migration

        migrations.AlterField(
            model_name='contracttwitchchannelrevenueadsbycountry',
            name='revenue_data',
            field=models.ForeignKey(to='twitchrevenue.TwitchRevenueDataAdsByCountry'),
            preserve_default=True,
        ),

SQL for the migration:

 ./manage.py sqlmigrate partners 0021

BEGIN;
ALTER TABLE "partners_contracttwitchchannelrevenueadsbycountry" DROP CONSTRAINT "a45496b32304e658c2a94795584a1889";
ALTER TABLE "partners_contracttwitchchannelrevenueadsbycountry" ADD CONSTRAINT "D321aaa5509069df3ee6b51c0eb97103" FOREIGN KEY ("revenue_data_id") REFERENCES "twitchrevenue_twitchrevenuedataadsbycountry" ("id") DEFERRABLE INITIALLY DEFERRED;

COMMIT;

Also, explicitly stating db_index=True does not cause a new migration to be created

Change History (9)

comment:1 Changed 6 years ago by Shai Berger

Resolution: invalid
Status: newclosed

Why should one be created? If it was a OneToOneField, it should already have an index, and I see nothing there to drop it.

I suppose you came to the conclusion that something was wrong because something failed for you; it would be more useful to describe the problem you encountered and the process that led you to the conclusion that the behavior was wrong (but, the proper place to do so would be a users forum like the django-users group).

I am closing the ticket as invalid based on the info given; feel free to reopen (and explain) if I've misunderstood, or to open a separate ticket if appropriate.

comment:2 Changed 6 years ago by synotna

Sorry, you are correct: I did not make the connection that the OneToOneField should have already created the index beforehand

Here is the migration that created the model including the OneToOneField, and the output from sqlmigrate which shows that index creation was missing:

        migrations.CreateModel(
            name='ContractTwitchChannelRevenueAdsByCountry',
            fields=[
                ('id', models.AutoField(primary_key=True, serialize=False, auto_created=True, verbose_name='ID')),
                ('partner_revenue', models.DecimalField(decimal_places=7, max_digits=12)),
                ('contract_twitch_channel', models.ForeignKey(to='partners.ContractTwitchChannel')),
                ('revenue_data', models.OneToOneField(to='twitchrevenue.TwitchRevenueDataAdsByCountry')),
            ],
            options={
            },
            bases=(models.Model,),
        ),
./manage.py sqlmigrate partners 0007

BEGIN;
CREATE TABLE "partners_contracttwitchchannelrevenueadsbycountry" ("id" serial NOT NULL PRIMARY KEY, "partner_revenue" numeric(12, 7) NOT NULL, "contract_twitch_channel_id" integer NOT NULL, "revenue_data_id" integer NOT NULL UNIQUE);
ALTER TABLE "partners_contracttwitchchannelrevenueadsbycountry" ADD CONSTRAINT "ccbec8ea2680b0e44e335452dd041304" FOREIGN KEY ("contract_twitch_channel_id") REFERENCES "partners_contracttwitchchannel" ("id") DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE "partners_contracttwitchchannelrevenueadsbycountry" ADD CONSTRAINT "a1dfe1f83ea9a2dbb28dc2ec53ef0136" FOREIGN KEY ("revenue_data_id") REFERENCES "twitchrevenue_twitchrevenuedataadsbycountry" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "partners_contracttwitchchannelrevenueadsbycountry_71b8390c" ON "partners_contracttwitchchannelrevenueadsbycountry" ("contract_twitch_channel_id");

COMMIT;

Am I correct in thinking that is a bug? If so then I'll edit (or open) a new bug ticket correctly

comment:3 Changed 6 years ago by synotna

Cc: a@… added

comment:4 Changed 6 years ago by Tim Graham

My understanding is that an index shouldn't be created in this case since OneToOneField is unique=True: "Note that when unique is True, you don’t need to specify db_index, because unique implies the creation of an index." (In which case, the original report might be valid.)

Last edited 6 years ago by Tim Graham (previous) (diff)

comment:5 Changed 6 years ago by Shai Berger

Cc: Shai Berger added
Resolution: invalid
Status: closednew
Summary: Altering a field to ForeignKey does not create indexAltering a 1to1 field to FK on Postgres drops uniqueness but does not create index
Triage Stage: UnreviewedAccepted

Ok, I see now. The PG backend relies on the unique constraint to create an index (correctly) for a 1-1 field; which means, if the field is no longer unique but still a FK, it needs an explicit index.

(you can probably work around this by adding a single-field index_together entry to your Meta)

comment:6 Changed 6 years ago by Claude Paroz

Component: UncategorizedMigrations

comment:7 Changed 6 years ago by Tim Graham

See also the opposite case of ForeignKey to OneToOneField in #26090.

comment:9 Changed 6 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: newclosed

In 9356f63a:

Fixed #25317, #26090 -- Fixed swapping combinations of unique and db_index during migrations.

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