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 !

[Petter Friberg]

Not sure if there should be a new ticket here or not but I'm still seeing this on 4.2 when having a relation that points to a unique field with a non-deterministic collation.

For instance, consider if we run the following migration operation:

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'))
    ]
)

Running manage.py sqlmigrate for that operation displays the generated _like index

BEGIN;
--
-- Create model ExtendedUser
--
CREATE TABLE "myapp_extendeduser" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "user_id" varchar(30) COLLATE "ci" NOT NULL UNIQUE);
ALTER TABLE "myapp_extendeduser" ADD CONSTRAINT "myapp_extendeduser_user_id_<chars>_fk_myapp_user_username" FOREIGN KEY ("user_id") REFERENCES "myapp_user" ("username") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "myapp_extendeduser_user_id_<chars>_like" ON "myapp_extendeduser" ("user_id" varchar_pattern_ops);
COMMIT;

(Not sure if there's more to it but) I think the provided patch could be extended with something like below to resolve at least OneToOneField

            # Non-deterministic collations on Postgresql don't support indexes
            # for operator classes varchar_pattern_ops/text_pattern_ops.
            if getattr(field, "db_collation", None) or (field.is_relation and getattr(field.target_field, "db_collation", None)):
                return None

[Mariusz Felisiak]

Petter, thanks for the report. If you believe it's an issue in Django, then please create a new ticket in Trac and follow our ​bug reporting guidelines.

[Ed Chai]

Replying to Petter Friberg:

Not sure if there should be a new ticket here or not but I'm still seeing this on 4.2 when having a relation that points to a unique field with a non-deterministic collation.

For instance, consider if we run the following migration operation:

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'))
    ]
)

Running manage.py sqlmigrate for that operation displays the generated _like index

BEGIN;
--
-- Create model ExtendedUser
--
CREATE TABLE "myapp_extendeduser" ("id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, "user_id" varchar(30) COLLATE "ci" NOT NULL UNIQUE);
ALTER TABLE "myapp_extendeduser" ADD CONSTRAINT "myapp_extendeduser_user_id_<chars>_fk_myapp_user_username" FOREIGN KEY ("user_id") REFERENCES "myapp_user" ("username") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "myapp_extendeduser_user_id_<chars>_like" ON "myapp_extendeduser" ("user_id" varchar_pattern_ops);
COMMIT;

(Not sure if there's more to it but) I think the provided patch could be extended with something like below to resolve at least OneToOneField

            # Non-deterministic collations on Postgresql don't support indexes
            # for operator classes varchar_pattern_ops/text_pattern_ops.
            if getattr(field, "db_collation", None) or (field.is_relation and getattr(field.target_field, "db_collation", None)):
                return None

I think your code is not related to this issue. This ticket only addresses errors that occur when both db_collation and unique are applied on Postgres. CREATE INDEX is the expected operation when you create foreign keys

[alfonsrv]

Despite this ticket being closed, I still get an error when attempting to run the SQL migration. Running Django 4.2.3 and Postgres 15.x (also tried 13.x)

I created the collation required collation and attempt to run a migration like this

    operations = [
        CreateCollation(
            "case_insensitive",
            provider="icu",
            locale="und-u-ks-level2",
            deterministic=False,
        ),
    ]

migrations.AlterField(
    model_name='user',
    name='email',
    field=models.EmailField(db_collation='case_insensitive', max_length=254, unique=True, verbose_name='email address'),
),

This results in the following SQL:

BEGIN;
--
-- Alter field email on user
--
ALTER TABLE "core_user" ALTER COLUMN "email" TYPE varchar(254) COLLATE "case_insensitive";
COMMIT;

And the same error when attempting to run python manage.py migrate: django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops". I found that altering the existing field doesn't work, but instead it has to be recreated completely.

[Ed Chai]

Replying to alfonsrv:
The issue is likely caused by a "varchar_pattern_ops" that already existed on your Postgres DB, you can run select * from pg_indexes where tablename='tablename'; in psql terminal to check if it was created. This fix indeed only solves the problem when you first time creating model fields with collation and prevents pattern ops index created. I'd be happy to submit another PR for the migration.

Despite this ticket being closed, I still get an error when attempting to run the SQL migration. Running Django 4.2.3 and Postgres 15.x (also tried 13.x)

I created the collation required collation and attempt to run a migration like this

    operations = [
        CreateCollation(
            "case_insensitive",
            provider="icu",
            locale="und-u-ks-level2",
            deterministic=False,
        ),
    ]

migrations.AlterField(
    model_name='user',
    name='email',
    field=models.EmailField(db_collation='case_insensitive', max_length=254, unique=True, verbose_name='email address'),
),

This results in the following SQL:

BEGIN;
--
-- Alter field email on user
--
ALTER TABLE "core_user" ALTER COLUMN "email" TYPE varchar(254) COLLATE "case_insensitive";
COMMIT;

And the same error when attempting to run python manage.py migrate: django.db.utils.NotSupportedError: nondeterministic collations are not supported for operator class "varchar_pattern_ops". I found that altering the existing field doesn't work, but instead it has to be recreated completely.

[Adam]

the issues can be recreated as follows:

I have an old migration that sets the field with unique=true

I have a new migration that sets the field with unique=true, db_collation="utf8_unicode_ci"

The old migration runs first, and creates a LIKE index for the field. The new migration attempts to run:

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

My work around is writing a DROP INDEX of the LIKE index that is created by the first migration

        migrations.RunSQL(
            sql="DROP INDEX IF EXISTS appname_modelname_fieldname_073c92ac_like;",
            reverse_sql=migrations.RunSQL.noop,
        ),
        migrations.AlterField(
            model_name="modelname",
            name="fieldname",
            field=models.CharField(
                blank=True, db_collation="utf8_unicode_ci", max_length=255, null=True, unique=True
            ),
        ),

But this is quite messy.

[Mariusz Felisiak]

Replying to Adam:

the issues can be recreated as follows:

I have an old migration that sets the field with unique=true

I have a new migration that sets the field with unique=true, db_collation="utf8_unicode_ci"

The old migration runs first, and creates a LIKE index for the field. The new migration attempts to run:

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

My work around is writing a DROP INDEX of the LIKE index that is created by the first migration

        migrations.RunSQL(
            sql="DROP INDEX IF EXISTS appname_modelname_fieldname_073c92ac_like;",
            reverse_sql=migrations.RunSQL.noop,
        ),
        migrations.AlterField(
            model_name="modelname",
            name="fieldname",
            field=models.CharField(
                blank=True, db_collation="utf8_unicode_ci", max_length=255, null=True, unique=True
            ),
        ),

But this is quite messy.

Thanks for the report. In the future, please open new tickets for linked bug reports and don't reopen already closed. I've created #34898.