migrate --syncdb and TEST_MIGRATE break for models with fields requiring extensions, and custom collation or types on Postgres

[wadhah mahrouk]

When running tests with pytest --no-migration, postgres extensions required by model indexes are not automatically enabled in the test database. This causes migration to fail when models contain indexes that depend on postgres extensions like pg_trgm.

to reproduce it's enough to run pytest --no-migrations
and have a model like

from django.db import models
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.operations import OpClass
from django.db.models.functions import Lower

class User(models.Model):
    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)
    email = models.EmailField(unique=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        indexes = [
            GinIndex(
                OpClass(
                    Lower("first_name"),
                    name="gin_trgm_ops",
                ),
                OpClass(
                    Lower("last_name"),
                    name="gin_trgm_ops",
                ),
                name="user_name_trgm_idx",
            ),
        ]

this will produce

    def _execute(self, sql, params, *ignored_wrapper_args):
        self.db.validate_no_broken_transaction()
        with self.db.wrap_database_errors:
            if params is None:
                # params default might be backend specific.
>               return self.cursor.execute(sql)
E               django.db.utils.ProgrammingError: operator class "gin_trgm_ops" does not exist for access method "gin"

The easy fix and the most expected by user is for create_test_db to copy existing and enabled extensions
I am willing to contribute if maintainer agree on the fix

[Simon Charette]

I believe this is a problem with migrate --syncdb and not test database creation per se. Test database creation is meant to do one thing only; create an empty database with the proper name. It then delegates everything else to migrate.

The existence of extensions, which is tracked by database migration operations, is not different from other Postgres feature that the schema might require to create indices and fields such as collations and custom types.

The problem here is that migrate --run-syncdb, which is relied upon by ​test database creation, has absolutely no knowledge of database migration operations that don't relate directly to models by design. All it does is ​create models.

To confirm the issue is not specific to TEST_MIGRATE=False try settings MIGRATION_MODULES entries to None for each of your INSTALLED_APPS and running migrate --run-syncdb against a fresh database, you should see the exact same failure.

Even if we wanted to only solve the problem for TEST_MIGRATE=False the proposed solution seems brittle as it requires the migrations to be fully applied on the non-test database (for extensions to exists and be introspected) which might the case for local development setups but definitely not the case on CI setups where only the test database is created.

The best available options at this time for Postgres users running into this problem is to use the TEST_TEMPLATE ​database setting to point it at a pre-configured database with extensions already installed on it.

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "USER": "user",
        "NAME": "database",
        "TEST": {
            "TEMPLATE": "testtemplate",  # testtemplate is meant to be a blank database with extensions already installed
        },
    },
}