non-deterministic collations doesn't work with Unique=True on Postgres13.3

[Ed Chai]

Creating a non-deterministic collation used for case insensitive fields to replace the old approach, ​CIText, would raise django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops" when the unique=True is also set on the field.Since unique implies the creation of an index despite setting db_index=False explicitly.

here is a sample code to reproduce:

class Migration(migrations.Migration):

    initial = True

    dependencies = [
        migrations.swappable_dependency(settings.AUTH_USER_MODEL),
    ]

    operations = [
        CreateCollation(
            'ci',
            provider='icu',
            locale='und-u-ks-level2',
            deterministic=False
        ),
        migrations.CreateModel(
            name='TestgUser',
            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={
                'verbose_name': 'Test User',
                'verbose_name_plural': 'Test Users',
                'abstract': False,
            },
        ),
    ]

[Simon Charette]

Closely related if not a duplicate of #24082 but since db_collation is the documented way off the now deprecated contrib.postgres.CIText I think it's worth keeping the two issue distinct for now.

Could we have the Postgres schema editor not create these opclass indexes when a custom db_collation is specified until we get a consensus on #24082?

[Ed Chai]

Replying to Simon Charette: I found #24082 but filed the issue for the same reason, CIText is deprecated and db_collation is documented way to do this job without any conflicts. Removing the duplicate index is an optimal solution and unique only for constraint seems semantic to me. Although I know some others still need LIKE and regex expression for regular case sensitive CharFeild and Textfield.

Closely related if not a duplicate of #24082 but since db_collation is the documented way off the now deprecated contrib.postgres.CIText I think it's worth keeping the two issue distinct for now.

Could we have the Postgres schema editor not create these opclass indexes when a custom db_collation is specified until we get a consensus on #24082?

[Mariusz Felisiak]

Replying to Simon Charette:

Could we have the Postgres schema editor not create these opclass indexes when a custom db_collation is specified until we get a consensus on #24082?

Sounds good to me. Ed, would you like to prepare a patch?

[Ed Chai]

Replying to Mariusz Felisiak: Sure, I think i can make a pull request this week.

Replying to Simon Charette:

Could we have the Postgres schema editor not create these opclass indexes when a custom db_collation is specified until we get a consensus on #24082?

Sounds good to me. Ed, would you like to prepare a patch?

[Mariusz Felisiak]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In f3f9d03:

Fixed #33901 -- Skipped varchar_pattern_ops/text_pattern_ops index creation when db_collation is set.

[Ismael ABBO]

Hello everyone!
First thing first, I am a newbie, using 3.2 LTS (latest 3.2.16) version and Postgres 14.4!
While this has been closed, I am still facing the same issue unless it is just a patch for Django 4.2?

Please help on how to fix it!

Here is the error:

...
Applying auth.0012_alter_user_first_name_max_length... OK
  Applying person.0001_initial...Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.FeatureNotSupported: nondeterministic collations are not supported for operator class "varchar_pattern_ops"


The above exception was the direct cause of the following exception:
...
 File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops"

There is FeatureNotSupported error on psycopg2, I noticed but, if it is the main reason, how was it managed before? Any workaround?

[Tim Graham]

Yes, the fix will only be applied for Django 4.2. Since the bug isn't a regression from a previous release or a major bug in a new feature it won't be backported. See our ​supported versions policy.

[Ismael ABBO]

Replying to Tim Graham: Thanks for that information!
Then, is there any workaround for 3.2.x versions?

Yes, the fix will only be applied for Django 4.2. Since the bug isn't a regression from a previous release or a major bug in a new feature it won't be backported. See our ​supported versions policy.

[Simon Charette]

Ismael, the only way to work around the issue on Django 3.2 would to be subclass the postgres SchemaEditor to override its _create_like_index_sql method and then use the adjusted backend in your DATABASES entries.

You can refer to the ​documentation on the subject.

[Ed Chai]

Replying to Ismael ABBO:

Replying to Tim Graham: Thanks for that information!
Then, is there any workaround for 3.2.x versions?

Yes, the fix will only be applied for Django 4.2. Since the bug isn't a regression from a previous release or a major bug in a new feature it won't be backported. See our ​supported versions policy.

You can keep using CITEXT, or set unique constraints directly via psql terminal.

[Ismael ABBO]

Thanks for help !