Changing a CharField to a ForeignKey crashes when migrating in PostgreSQL

[Daniel Quinn]

If I have a model that looks like this:

class MyModel(models.Model):
    my_field = models.CharField(max_length=128, db_index=True)

makemigrations will create a migration that looks like this:

        migrations.CreateModel(
            name='MyModel',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('my_field', models.CharField(db_index=True, max_length=128)),
            ],
        ),

However, if I later change the field to a ForeignKey:

class MyOtherModel(models.Model):
    stuff = models.CharField(max_length=128)

class MyModel(models.Model):
    my_field = models.ForeignKey("alpha.MyOtherModel", blank=True)

makemigrations will create this:

        migrations.AlterField(
            model_name='mymodel',
            name='my_field',
            field=models.ForeignKey(blank=True, on_delete=django.db.models.deletion.CASCADE, to='alpha.MyOtherModel'),
        ),

...which explodes in PostgreSQL (but not SQLite or MySQL) with this:

psycopg2.ProgrammingError: operator class "varchar_pattern_ops" does not accept data type integer

The fix (at least for my case) was to manually break up the AlterField into separate RemoveField and AddField steps like this:

        migrations.RemoveField(
            model_name='mymodel',
            name='my_field',
        ),
        migrations.AddField(
            model_name='mymodel',
            name='my_field',
            field=models.ForeignKey(blank=True, on_delete=django.db.models.deletion.CASCADE, to='alpha.MyOtherModel'),
        ),

I ran into this on my own GPL project, and the issue history wherein we found and fixed the problem is here: ​https://github.com/danielquinn/paperless/issues/183

[Tim Graham]

I couldn't reproduce a problem with these final models:

class CharPK(models.Model):
    id = models.CharField(primary_key=True, max_length=128)

class MyModel(models.Model):
    my_field = models.ForeignKey(CharPK, max_length=128, on_delete=models.CASCADE)

Does "alpha.MyOtherModel" have a CharField primary key?

I'm not sure what the proper resolution would be here, considering that automatically generating RemoveField / AddField would be data loss operations.

[Daniel Quinn]

alpha.MyOtherModel is using a default numeric key. Sorry, I should have included that in the ticket and so I've modified it to include the MyOtherModel class.

The problem is that migrations is doing an alter for a field that changed not just from a CharField to a ForeignKeyField but rather to a foreign key of a different type. SQLite and MySQL somehow didn't have a problem with this, but PostgreSQL exploded.

Perhaps a solution would be a warning stage not unlike the prompt you get when trying to add a field with no default= value? Something like:

It looks like you've changed a CharField to a ForeignKey of a different type. This will generate an AddField and RemoveField. Is that cool with you?

[Tim Graham]

It looks like the varchar_pattern_ops index needs to be dropped before altering the field.

[Mariusz Felisiak]

​PR

[Tim Graham <timograham@…>]

In 6b47431:

Refs #27860 -- Simplified deleting indexes on PostgreSQL using "IF EXISTS".

[GitHub <noreply@…>]

In 91b2bc3:

Fixed #27860 -- Dropped varchar_pattern_ops/text_pattern_ops index before altering char/text field in PostgreSQL.

Thanks Tim Graham for the review.