Opened 10 months ago
Last modified 10 months ago
#35079 closed Bug
Django Migration with OpClass and varchar_pattern_ops creates an invalid migration SQL — at Version 1
Reported by: | Andra Antariksa | Owned by: | nobody |
---|---|---|---|
Component: | Migrations | Version: | 4.2 |
Severity: | Normal | Keywords: | migration |
Cc: | Lily Foote, Hannes Ljungberg | Triage Stage: | Unreviewed |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
I have this model
class Location(models.Model): name = models.CharField(max_length=255) phone = models.CharField(max_length=255) latitude = models.FloatField() longitude = models.FloatField() address = models.TextField() address_notes = models.TextField(blank=True, default='') is_active = models.BooleanField(default=True) class Meta: indexes = [ models.Index( OpClass(Upper('name'), name='varchar_pattern_ops'), name='uppercase_name_index' ), ] def __str__(self) -> str: return self.name
And I create a migration using Django makemigration which produce the following script
# Generated by Django 4.2.7 on 2024-01-02 09:12 import django.contrib.postgres.indexes from django.db import migrations, models import django.db.models.functions.text class Migration(migrations.Migration): dependencies = [ ("locations", "0004_providerlocationcode_sub_district"), ] operations = [ migrations.AddIndex( model_name="location", index=models.Index( django.contrib.postgres.indexes.OpClass( django.db.models.functions.text.Upper("name"), name="varchar_pattern_ops", ), name="uppercase_name_index", ), ), ]
However I encounter an error when running the migration using ./manage.py migrate locations 0005
py manage.py migrate locations System check identified some issues: WARNINGS: ?: (urls.W005) URL namespace 'admin' isn't unique. You may not be able to reverse all URLs in this namespace Operations to perform: Apply all migrations: locations Running migrations: Applying locations.0005_location_uppercase_name_index...Traceback (most recent call last): File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute return self.cursor.execute(sql) psycopg2.errors.SyntaxError: syntax error at or near "varchar_pattern_ops" LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa... ^ The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/andra/Projects/fleet/manage.py", line 21, in <module> main() File "/home/andra/Projects/fleet/manage.py", line 17, in main execute_from_command_line(sys.argv) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line utility.execute() File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/__init__.py", line 436, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 412, in run_from_argv self.execute(*args, **cmd_options) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute output = self.handle(*args, **options) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 106, in wrapper res = handle_func(*args, **kwargs) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 356, in handle post_migrate_state = executor.migrate( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 135, in migrate state = self._migrate_all_forwards( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards state = self.apply_migration( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 252, in apply_migration state = migration.apply(state, schema_editor) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/migration.py", line 132, in apply operation.database_forwards( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/operations/models.py", line 894, in database_forwards schema_editor.add_index(model, self.index) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 325, in add_index self.execute( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute return super().execute(sql, params) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 201, in execute cursor.execute(sql, params) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 102, in execute return super().execute(sql, params) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/sentry_sdk/integrations/django/__init__.py", line 641, in execute return real_execute(self, sql, params) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute return self._execute_with_wrappers( File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers return executor(sql, params, many, context) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute return self.cursor.execute(sql) django.db.utils.ProgrammingError: syntax error at or near "varchar_pattern_ops" LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa...
I went investigate the generated SQL using ./manage.py sqlmigrate locations 0005
. Below are the generated SQL
-- -- Create index uppercase_name_index on OpClass(Upper(F(name)), name=varchar_pattern_ops) on model location -- CREATE INDEX "uppercase_name_index" ON "locations_location" ((UPPER("name") varchar_pattern_ops)); COMMIT;
I ran the generated SQL in my local postgres (psql (PostgreSQL) 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)
), and it indeed error
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)) Type "help" for help. postgres=# CREATE INDEX "uppercase_name_index" ON "locations_location" ((UPPER("name") varchar_pattern_ops)); ERROR: syntax error at or near "varchar_pattern_ops" LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa... ^
Although when I removed the outer parentheses of the statement of (UPPER("name") varchar_pattern_ops)
it ran successfully
harvest_st_fleet=# CREATE INDEX "uppercase_name_index" ON "locations_location" (UPPER("name") varchar_pattern_ops); CREATE INDEX