Opened 2 years ago
Closed 12 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 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 (19)
follow-ups: 2 3 comment:1 by , 2 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 4.0 → dev |
comment:2 by , 2 years ago
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?
follow-up: 4 comment:3 by , 2 years ago
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 by , 2 years ago
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 by , 2 years ago
Has patch: | set |
---|---|
Owner: | changed from | to
Status: | new → assigned |
Triage Stage: | Accepted → Ready for checkin |
comment:7 by , 2 years ago
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?
follow-up: 9 comment:8 by , 2 years ago
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.
follow-up: 11 comment:9 by , 2 years ago
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 by , 2 years ago
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 by , 2 years ago
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.
follow-up: 15 comment:13 by , 18 months ago
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
comment:14 by , 18 months ago
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.
comment:15 by , 18 months ago
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
follow-up: 17 comment:16 by , 15 months ago
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.
comment:17 by , 15 months ago
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.
follow-up: 19 comment:18 by , 12 months ago
Resolution: | fixed |
---|---|
Status: | closed → new |
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.
comment:19 by , 12 months ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
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.
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?