[Django] #34027: When ForeignKey target field type is changed from CharField to IntegerField, varchar_pattern_ops index is not dropped on PostgreSQL and migration fails

[Django]

#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.

[Django]

#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: David Sanders
Type: Bug | Status: assigned
Component: Migrations | Version: 4.1
Severity: Normal | Resolution:

Keywords: PostgreSQL | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

----------------------------+-----------------------------------------
Changes (by David Sanders):

* owner: nobody => David Sanders
* status: new => assigned
* version: 3.2 => 4.1

--
Ticket URL: <https://code.djangoproject.com/ticket/34027#comment:1>

[Django]

#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: David Sanders
Type: Bug | Status: assigned
Component: Migrations | Version: 4.1
Severity: Normal | Resolution:
Keywords: PostgreSQL | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------+-----------------------------------------

Comment (by David Sanders):

Draft PR with failing test: https://github.com/django/django/pull/16087

--
Ticket URL: <https://code.djangoproject.com/ticket/34027#comment:2>

[Django]

#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: David Sanders
Type: Bug | Status: assigned
Component: Migrations | Version: 4.1
Severity: Normal | Resolution:
Keywords: PostgreSQL | Triage Stage: Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------+-----------------------------------------

Changes (by David Sanders):

* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/34027#comment:3>