PostgreSQL pattern ops indexes are dropped when changing between CharField and TextField

[Robin Ray]

When converting an indexed CharField to a TextField (and vice versa) on PostgreSQL, Django drops the existing pattern ops _like index for the column but does not recreate it with the new pattern ops. When reversing the migration, Django does not recreate the initial _like index.

I have been able to reproduce this behavior in Django 3.2 and 5.0.

Reproduction

1. Given a model with a CharField that has db_index=True on a PostgreSQL database
2. Inspect the database indexes or migration SQL and see that the indexed CharField has two indexes, one of which ends in _like and uses varchar_pattern_ops
3. Change the CharField to a TextField and generate a migration
4. Run the migration or inspect the SQL with the sqlmigrate manage command
5. Inspect the database indexes or migration SQL and see that the indexed TextField does not have a _like index
6. Reverse the migration
7. Inspect the database indexes or migration SQL and see that the indexed CharField no longer has a _like index

Here is an example project that demonstrates the issue: ​https://github.com/robin-ray/alter_text_index_repro

[Natalia Bidart]

Hello Robin Ray, thank you for your report and for helping making Django better.

As far as I can see, this is a valid issue. When I first read the description, I was sure there were already similar reports so I searched a bit for possible duplicates. I couldn't find an exact duplicate but there are related issues reported (and fixed in some cases) in #25412, #34505, and a few others involving collations.

It seems that the code at fault is the _alter_field in django/db/backends/postgresql/schema.py that skips creating the index because old_field.db_index and new_field.db_index are both True. I'm not an expert in this area so I have added some people as cc in this ticket, but I think this guard needs to also consider whether the column being altered is also changing its type:

diff --git a/django/db/backends/postgresql/schema.py b/django/db/backends/postgresql/schema.py
index 842830be30..975a9f1e93 100644
--- a/django/db/backends/postgresql/schema.py
+++ b/django/db/backends/postgresql/schema.py
@@ -295,10 +295,12 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
             new_db_params,
             strict,
         )
+        type_changed = old_type != new_type
+        old_db_index = old_field.db_index or old_field.unique
+        new_db_index = new_field.db_index or new_field.unique
+        needs_index = (not old_db_index and new_db_index) or (type_changed and new_db_index)
         # Added an index? Create any PostgreSQL-specific indexes.
-        if (not (old_field.db_index or old_field.unique) and new_field.db_index) or (
-            not old_field.unique and new_field.unique
-        ):
+        if needs_index:
             like_index_statement = self._create_like_index_sql(model, new_field)
             if like_index_statement is not None:
                 self.execute(like_index_statement)

NOTE: please do not consider this diff as suitable for a patch without further discussion.

[Natalia Bidart]

EDIT: in my previous comment I mentioned that no test was failing with my proposed patch but I needed to run a wider set of tests. In fact there are failures=3, errors=6 so we should discuss the proper fix in more depth.

[Mariusz Felisiak]

As far as I'm aware, it is a ​duplicate of #28646 and changing a data type is not crucial for this issue. It's important that the altered field has db_index set to True, in such cases all indexes are deleted in the BaseDatabaseSchemaEditor._alter_field() but in some cases we miss recreating _like indexes.

[timbaginski]

Replying to Natalia Bidart:

Hello Robin Ray, thank you for your report and for helping making Django better.

As far as I can see, this is a valid issue. When I first read the description, I was sure there were already similar reports so I searched a bit for possible duplicates. I couldn't find an exact duplicate but there are related issues reported (and fixed in some cases) in #25412, #34505, and a few others involving collations.

It seems that the code at fault is the _alter_field in django/db/backends/postgresql/schema.py that skips creating the index because old_field.db_index and new_field.db_index are both True. I'm not an expert in this area so I have added some people as cc in this ticket, but I think this guard needs to also consider whether the column being altered is also changing its type:

diff --git a/django/db/backends/postgresql/schema.py b/django/db/backends/postgresql/schema.py
index 842830be30..975a9f1e93 100644
--- a/django/db/backends/postgresql/schema.py
+++ b/django/db/backends/postgresql/schema.py
@@ -295,10 +295,12 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
             new_db_params,
             strict,
         )
+        type_changed = old_type != new_type
+        old_db_index = old_field.db_index or old_field.unique
+        new_db_index = new_field.db_index or new_field.unique
+        needs_index = (not old_db_index and new_db_index) or (type_changed and new_db_index)
         # Added an index? Create any PostgreSQL-specific indexes.
-        if (not (old_field.db_index or old_field.unique) and new_field.db_index) or (
-            not old_field.unique and new_field.unique
-        ):
+        if needs_index:
             like_index_statement = self._create_like_index_sql(model, new_field)
             if like_index_statement is not None:
                 self.execute(like_index_statement)

NOTE: please do not consider this diff as suitable for a patch without further discussion.

I've been looking at it and noticed the same thing, though my code is less elegant. I'm new to open source contribution, so my apologies if my claiming of the ticket and subsequent lack of communication caused any headache

        # Added an index or deleted index due to type change? 
        # Create any PostgreSQL-specific indexes.
        if (not (old_field.db_index or old_field.unique) and new_field.db_index) or (
            not old_field.unique and new_field.unique) or ((old_field.db_index or old_field.unique) and (
            (old_type.startswith("varchar") and not new_type.startswith("varchar"))
            or (old_type.startswith("text") and not new_type.startswith("text")))
        ):
            like_index_statement = self._create_like_index_sql(model, new_field)
            if like_index_statement is not None:
                self.execute(like_index_statement)