#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 theunique=True
is also set on the field.Since unique implies the creation of an index despite settingdb_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 follow-ups: 2 3 Changed 8 months ago by
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 4.0 → dev |
comment:2 Changed 8 months ago by
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 deprecatedcontrib.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 customdb_collation
is specified until we get a consensus on #24082?
comment:3 follow-up: 4 Changed 8 months ago by
Replying to Simon Charette:
Could we have the Postgres schema editor not create these
opclass
indexes when a customdb_collation
is specified until we get a consensus on #24082?
Sounds good to me. Ed, would you like to prepare a patch?
comment:4 Changed 8 months ago by
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 customdb_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
Has patch: | set |
---|---|
Owner: | changed from nobody to Ed Chai |
Status: | new → assigned |
Triage Stage: | Accepted → Ready for checkin |
comment:7 Changed 5 months ago by
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 follow-up: 9 Changed 5 months ago by
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 follow-up: 11 Changed 5 months ago by
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
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 Changed 5 months ago by
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.
Closely related if not a duplicate of #24082 but since
db_collation
is the documented way off the now deprecatedcontrib.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 customdb_collation
is specified until we get a consensus on #24082?