ArrayFields should not have varchar_patterns_ops or text_patterns_ops indexes

[Fabian Büchler]

I believe I bumped into a bug using the new PostgreSQL ArrayField from 1.8 in combination with db_index=True:

This seems to be related to Django ticket #12234 which introduced creating secondary
varchar_patterns_ops or text_patterns_ops indexes for varchar or text fields
respectively, so that PostgreSQL could use them for the contains filter
(e.g. MyModel.object.filter(name__contains='abc')).

However, for the array fields introduced with Django 1.8 this does not seem to
work when having db_index activated. When syncdb or migrate try to
create the secondary index, PostgreSQL raises an error like this:

django.db.utils.ProgrammingError: operator class "varchar_pattern_ops" does not accept data type character varying[]

The executed SQL statement is something like this:

CREATE INDEX "shop_product_package_product_numbers_2ebd72fe1541fbd4_like" ON "shop_product" ("package_product_numbers" varchar_pattern_ops)

The important parts of the model defintion are:

class Product(models.Model):

    package_product_numbers = ArrayField(
        models.CharField(max_length=10, blank=True),
        verbose_name="Package product numbers",
        null=True, default=None, db_index=True)

I'm currently using a workaroud to fix this. I created a custom DB backend that inherits from the postgresql_psycopg2 one and overwrites the DatabaseCreation.sql_indexes_for_field and DatabaseSchemaEditor._model_indexes_sql methods to check for ArrayFields when creating those secondary indexes. I can post this code here, if you need it, however, I don't think that this is the right solution, since the PostgreSQL database backend should not really need to know anything about the django.contrib.postgresql stuff.

Please let me know if I can help to reproduce this.

[Caio Ariede]

Seems to happen only when the table is created. Adding the field later does not trigger the error.

[Caio Ariede]

Pull request: ​https://github.com/django/django/pull/5118

[Tim Graham]

Pending a few cosmetic edits.

[Tim Graham <timograham@…>]

In dad8434:

Fixed #25180 -- Prevented varchar_patterns_ops and text_patterns_ops indexes for ArrayField.

[Tim Graham <timograham@…>]

In 29fa1b58:

[1.8.x] Fixed #25180 -- Prevented varchar_patterns_ops and text_patterns_ops indexes for ArrayField.

Backport of dad8434d6ff5da10959672726dc9b397296d380b from master