Issue with postgres gin indexes

Using Django Using the ORM
1

I’m using Django 4.2.latest, and i’m running into an issue with this model:

from django.contrib.postgres.indexes import GinIndex, OpClass

class Model(models.Model):
    ...
    class Meta:
        indexes = [
            GinIndex(OpClass(Lower('first_name'), 'gin_trgm_ops'), name='user_first_name_gin_unac_idx'),
            GinIndex(OpClass(Lower('last_name'), 'gin_trgm_ops'), name='user_last_name_gin_unac_idx'),
        ]

$ ./manage.py migrate
...
Traceback (most recent call last):
  File "/app/./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.10/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/operations/models.py", line 894, in database_forwards
    schema_editor.add_index(model, self.index)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 325, in add_index
    self.execute(
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: syntax error at or near "gin_trgm_ops"
LINE 1: ...user" USING gin ((LOWER("first_name") gin_trgm_o...
                                                             ^

From what I can piece together the sql generated should be:

CREATE INDEX "user_first_name_gin_unac_idx" ON "model" USING gin (LOWER("first_name") gin_trgm_ops)

and not what it’s currently doing:
CREATE INDEX "user_first_name_gin_unac_idx" ON "model" USING gin ((LOWER("first_name") gin_trgm_ops))

Is this an issue with my code? Or is this a bug?

2

While I wait, i have a hack/workaround to get me over the issue:

from django.contrib.postgres.indexes import GinIndex as GinIndexBase

class GinIndex(GinIndexBase):
    def create_sql(self, model, schema_editor, using="", **kwargs):
        sql = 'CREATE INDEX %(name)s ON %(table)s%(using)s %(columns)s%(include)s%(extra)s%(condition)s'
        kwargs['sql'] = sql
        return super().create_sql(model, schema_editor, using=using, **kwargs)

The original implementation has an extra set of parentheses around %(columns)s

3

Not sure which version of Postgres you are using but I can’t reproduce with 4.2 and main against Postgres 14 (oldest supported Postgres version) with the following test

diff --git a/tests/postgres_tests/test_indexes.py b/tests/postgres_tests/test_indexes.py
index d063ac64a2..695c33c9ef 100644
--- a/tests/postgres_tests/test_indexes.py
+++ b/tests/postgres_tests/test_indexes.py
@@ -365,6 +365,22 @@ def test_trigram_op_class_gin_index(self):
             editor.remove_index(Scene, index)
         self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table))

+    def test_trigram_op_class_gin_index_expr(self):
+        index_name = "trigram_op_class_gin_lower"
+        index = GinIndex(OpClass(Lower("scene"), name="gin_trgm_ops"), name=index_name)
+        with connection.schema_editor() as editor:
+            editor.add_index(Scene, index)
+        with editor.connection.cursor() as cursor:
+            cursor.execute(self.get_opclass_query, [index_name])
+            self.assertCountEqual(cursor.fetchall(), [("gin_trgm_ops", index_name)])
+        constraints = self.get_constraints(Scene._meta.db_table)
+        self.assertIn(index_name, constraints)
+        self.assertIn(constraints[index_name]["type"], GinIndex.suffix)
+        with connection.schema_editor() as editor:
+            editor.remove_index(Scene, index)
+        self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table))
+
     def test_cast_search_vector_gin_index(self):
         index_name = "cast_search_vector_gin"
         index = GinIndex(Cast("field", SearchVectorField()), name=index_name)

The generated SQL also differs from what you are experiencing

CREATE INDEX "trigram_op_class_gin_lower"
ON "postgres_tests_scene" USING gin ((LOWER("scene")) gin_trgm_ops)
4

I’m using Postgres 15, i just swapped over to 14, and i get the same issue.

django-1  |     return self.cursor.execute(sql)
django-1  | django.db.utils.ProgrammingError: syntax error at or near "gin_trgm_ops"
django-1  | LINE 1: ...ON "model" USING gin ((UPPER("first_name") gin_trgm_o...

Unrelated, i swapped LOWER → UPPER so it would match the __icontains logic and match my index.

5

Interesting, it seems that the SQL generated when using add_index during tests and when calling migrate isn’t the same as I can also get a crash against 4.2 by going the full route of defining models and associated migrations (haven’t been able to test against main).

If you can reproduce against main I would definitely file a bug for it.

7

Right because 4.2 is not in active support, only security support.. and this is not security…

I have a long way to get to 5.2/main.

Thanks

8

I figured it out. You must make sure that django.contrib.postgres is part of your INSTALLED_APPS which will work on Django 4.2 as well.

It is documented

To use it, you need to add 'django.contrib.postgres' in your INSTALLED_APPS.

But it’s quite easy to miss.

Doing so ensures that this code gets registered.

I’d appreciate if you could chime in on this ticket about the exact same problem to support the idea of failing more loudly when such a mis-configuration is detected. There’s a clear solution on how to implement it but having someone else testimony could be valuable.

9

Confirmed, I was missing 'django.contrib.postgres' in my INSTALLED_APPS. Thank you so much for your help!