Non-deterministic collations doesn't work with unique related fields on PostgreSQL

[Petter Friberg]

Creating a relation to a column with a non-deterministic collation raises:

django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops"

Error is raised due to creation of a _like index.

This is kind of a continuation of #33901, but instead regarding relations.

Here's some code to reproduce:

class Migration(migrations.Migration):

    initial = True

    dependencies = [
    ]

    operations = [
        CreateCollation(
            'ci',
            provider='icu',
            locale='und-u-ks-level2',
            deterministic=False
        ),
        migrations.CreateModel(
            name='User',
            fields=[
                ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('password', models.CharField(max_length=128, verbose_name='password')),
                ('username', models.CharField(db_collation='ci', max_length=30, unique=True, verbose_name='username')),
            ],
            options={
                'abstract': False,
            },
        ),
        migrations.CreateModel(
            name='ExtendedUser',
            fields=[
                ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('user', models.OneToOneField(on_delete=django.db.models.deletion.CASCADE, to=settings.AUTH_USER_MODEL, to_field='username')),
            ],
        ),
    ]

Adding a test case similar to what can be found in f3f9d03, but adjusted to include a OneToOneField, fails on main

    @isolate_apps("schema")
    @unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL specific")
    @skipUnlessDBFeature(
        "supports_collation_on_charfield",
        "supports_non_deterministic_collations",
    )
    def test_unique_relation_to_collation_charfield(self):
        ci_collation = "case_insensitive"

        def drop_collation():
            with connection.cursor() as cursor:
                cursor.execute(f"DROP COLLATION IF EXISTS {ci_collation}")

        self.addCleanup(drop_collation)

        with connection.cursor() as cursor:
            cursor.execute(
                f"CREATE COLLATION IF NOT EXISTS {ci_collation} (provider = icu, "
                f"locale = 'und-u-ks-level2', deterministic = false)"
            )

        class CiCharModel(Model):
            field = CharField(max_length=16, db_collation=ci_collation, unique=True)

            class Meta:
                app_label = "schema"

        class RelationModel(Model):
            field = OneToOneField(CiCharModel, on_delete=CASCADE, to_field="field")

            class Meta:
                app_label = "schema"

        # Create the table.
        with connection.schema_editor() as editor:
            editor.create_model(CiCharModel)
            editor.create_model(RelationModel)

        self.isolated_local_models = [CiCharModel, RelationModel]
        self.assertEqual(
            self.get_column_collation(RelationModel._meta.db_table, "field_id"),
            ci_collation,
        )
        self.assertIn("field_id", self.get_uniques(RelationModel._meta.db_table))

[Mariusz Felisiak]

Thanks for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 8ed25d6:

Fixed #34505 -- Skipped varchar_pattern_ops/text_pattern_ops index creation when db_collation is set in related field.