Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#25180 closed Bug (fixed)

ArrayFields should not have varchar_patterns_ops or text_patterns_ops indexes

Reported by: Fabian Büchler Owned by: Caio Ariede
Component: contrib.postgres Version: 1.8
Severity: Normal Keywords:
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

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.

Change History (7)

comment:1 Changed 5 years ago by Tim Graham

Triage Stage: UnreviewedAccepted

comment:2 Changed 5 years ago by Caio Ariede

Owner: set to Caio Ariede
Status: newassigned

comment:3 Changed 5 years ago by Caio Ariede

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

comment:4 Changed 5 years ago by Caio Ariede

Has patch: set

comment:5 Changed 5 years ago by Tim Graham

Triage Stage: AcceptedReady for checkin

Pending a few cosmetic edits.

comment:6 Changed 5 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In dad8434:

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

comment:7 Changed 5 years ago by 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

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