#34027: When ForeignKey target field type is changed from CharField to
IntegerField, varchar_pattern_ops index is not dropped on PostgreSQL and
migration fails
--------------------------------------+----------------------------
Reporter: Chris | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.2
Severity: Normal | Keywords: PostgreSQL
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
--------------------------------------+----------------------------
When a `ForeignKey` points to `CharField`, and the `CharField` is then
changed to e.g. an `AutoField`, the migration fails with:
{{{psycopg2.errors.DatatypeMismatch: operator class "varchar_pattern_ops"
does not accept data type bigint}}}
The reason seems to be that the original `ForeignKey` column in the
database was a `varchar`, and Django created a `varchar_pattern_ops` index
for it (e.g. one of those `..._like` indexes). Changing the column type to
`bigint` is not accepted by this index.
Manually dropping the index before attempting to run the migrations
results in the migration being performed successfully.
Performing the same set of migrations on SQLite and on MySQL does not
result in an error, it only fails on PostgreSQL.
----
Test case:
- Use models.py:
{{{
class ModelA(models.Model):
# Custom CharField primary key
id = models.CharField(max_length=10, primary_key=True)
class ModelB(models.Model):
modela = models.ForeignKey(ModelA, on_delete=models.CASCADE)
}}}
- Run `makemigrations` and `migrate` commands
- Alter `ModelA`:
{{{
class ModelA(models.Model):
# No more custom primary key
pass
}}}
- Run `makemigrations` and `migrate` commands again
- Observe error on PostgreSQL, success on SQLite and MySQL
- Manually `DROP` the `<applabel>_modelb_modela_id_<...>_like` index in
PostgreSQL
- Again run the `migrate` command
- Now the migration succeeds
----
The problem is apparently that the
`BaseDatabaseSchemaEditor._alter_field()` method simply calls
`_alter_column_type_sql()` or `_alter_column_collation_sql()` to directly
change the column type of relations pointing to the field. These methods
do not generate any additional SQL related to indexes etc as the normal
(PostgreSQL version of) `_alter_field` method would under similar
circumstances. Note that the `..._like` index for the primary key itself
_is_ dropped.
Similarly also, if the primary key field is changed in the other
direction, e.g. from `AutoField` to `CharField`, the `..._like` index is
not added to the existing `ForeignKey`, it is only added for the primary
key field, as it normally is when an integer field is changed to a text
field.
Possibly related issues: #27338 #27860
Reported for version 3.2 but also observed on 4.1.
--
Ticket URL: <https://code.djangoproject.com/ticket/34027>
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 view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018360873a1d-0ed2a052-2d37-44a6-97d2-5dec97cc4ac4-000000%40eu-central-1.amazonses.com.
