#35180: PostgreSQL pattern ops indexes are dropped when changing between CharField and TextField -------------------------------------+------------------------------------- Reporter: Robin Ray | Owner: | timbaginski Type: Bug | Status: closed Component: Database layer | Version: dev (models, ORM) | Severity: Normal | Resolution: duplicate Keywords: index postgres | Triage Stage: migration | Unreviewed Has patch: 0 | Needs documentation: 0 Needs tests: 0 | Patch needs improvement: 0 Easy pickings: 0 | UI/UX: 0 -------------------------------------+------------------------------------- Comment (by timbaginski):
Replying to [comment:3 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: > > {{{#!python > 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 exact same thing. I think you just need to add a condition to check if the old field was type varchar or text and got changed. {{{ # 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) }}} EDIT: I'm new to open source contribution, so my apologies if my claiming of the ticket and subsequent lack of communication caused any headache -- Ticket URL: <https://code.djangoproject.com/ticket/35180#comment:7> 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 django-updates+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/0107018d9f5f7bfd-2c9ac9f6-7ad4-443a-8ac0-6043f212193e-000000%40eu-central-1.amazonses.com.