Opened 2 years ago

Closed 2 years ago

#34027 closed Bug (fixed)

Changing CharField type referenced by ForeignKey crashes on PostgreSQL.

Reported by: Chris Owned by: David Sanders
Component: Migrations Version: 4.1
Severity: Normal Keywords: PostgreSQL
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (6)

comment:1 by David Sanders, 2 years ago

Owner: changed from nobody to David Sanders
Status: newassigned
Version: 3.24.1

comment:2 by David Sanders, 2 years ago

Version 0, edited 2 years ago by David Sanders (next)

comment:3 by David Sanders, 2 years ago

Has patch: set

comment:4 by Mariusz Felisiak, 2 years ago

Summary: When ForeignKey target field type is changed from CharField to IntegerField, varchar_pattern_ops index is not dropped on PostgreSQL and migration failsChanging CharField type referenced by ForeignKey crashes on PostgreSQL.
Triage Stage: UnreviewedAccepted

comment:5 by Mariusz Felisiak, 2 years ago

Triage Stage: AcceptedReady for checkin

comment:6 by Mariusz Felisiak <felisiak.mariusz@…>, 2 years ago

Resolution: fixed
Status: assignedclosed

In 9f8c9948:

Fixed #34027 -- Fixed migrations crash when altering type of char/text fields referenced by foreign key on PostgreSQL.

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