Opened 8 months ago

Closed 8 months ago

Last modified 5 months ago

#33901 closed Bug (fixed)

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

Reported by: Ed Chai Owned by: Ed Chai
Component: Migrations Version: dev
Severity: Normal Keywords: collation unique citext
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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,
            },
        ),
    ]

Change History (12)

comment:1 Changed 8 months ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 4.0dev

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?

comment:2 in reply to:  1 Changed 8 months ago by 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?

comment:3 in reply to:  1 ; Changed 8 months ago by 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?

comment:4 in reply to:  3 Changed 8 months ago by 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?

comment:5 Changed 8 months ago by Mariusz Felisiak

Has patch: set
Owner: changed from nobody to Ed Chai
Status: newassigned
Triage Stage: AcceptedReady for checkin

comment:6 Changed 8 months ago by Mariusz Felisiak <felisiak.mariusz@…>

Resolution: fixed
Status: assignedclosed

In f3f9d03:

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

comment:7 Changed 5 months ago by 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?

comment:8 Changed 5 months ago by 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.

comment:9 in reply to:  8 ; Changed 5 months ago by 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.

comment:10 Changed 5 months ago by 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.

comment:11 in reply to:  9 Changed 5 months ago by 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.

comment:12 Changed 5 months ago by Ismael ABBO

Thanks for help !

Note: See TracTickets for help on using tickets.
Back to Top