'DROP INDEX' errors during database migration from 4.14.1 to 4.17 (because Postgres fails on DROP INDEX without 'IF EXISTS')

[mig5]

Describe the issue

I changed my Docker compose file from tag 4.14.1-1 to 4.17.0.3 and ran docker-compose pull followed by docker-compose up -d to recreate the container and apply database migrations.

I get an error during migration memory.0013_reindex of psycopg2.errors.UndefinedObject: index "memory_source_trgm" does not exist.

I already tried

• I've read and searched the documentation.
• I've searched for similar issues in this repository.

Steps to reproduce the behavior

Install 4.14
Upgrade to 4.17

(via Docker)

Expected behavior

Database migrations should complete successfully

Screenshots

No response

Exception traceback

Postgres 130011 is up
Starting database migration...
Operations to perform:
  Apply all migrations: accounts, addons, admin, auth, authtoken, checks, configuration, contenttypes, django_celery_beat, fonts, gitexport, glossary, lang, memory, metrics, screenshots, sessions, social_django, trans, utils, vcs, weblate_auth, wladmin
Running migrations:
  Applying accounts.0021_profile_commit_email_verifiedemail_is_deliverable... OK
  Applying accounts.0022_alter_profile_linkedin... OK
  Applying accounts.0023_cleanup_deleted_users... OK
  Applying django_celery_beat.0001_initial... OK
  Applying django_celery_beat.0002_auto_20161118_0346... OK
  Applying django_celery_beat.0003_auto_20161209_0049... OK
  Applying django_celery_beat.0004_auto_20170221_0000... OK
  Applying django_celery_beat.0005_add_solarschedule_events_choices... OK
  Applying django_celery_beat.0006_auto_20180322_0932... OK
  Applying django_celery_beat.0007_auto_20180521_0826... OK
  Applying django_celery_beat.0008_auto_20180914_1922... OK
  Applying django_celery_beat.0006_auto_20180210_1226... OK
  Applying django_celery_beat.0006_periodictask_priority... OK
  Applying django_celery_beat.0009_periodictask_headers... OK
  Applying django_celery_beat.0010_auto_20190429_0326... OK
  Applying django_celery_beat.0011_auto_20190508_0153... OK
  Applying django_celery_beat.0012_periodictask_expire_seconds... OK
  Applying django_celery_beat.0013_auto_20200609_0727... OK
  Applying django_celery_beat.0014_remove_clockedschedule_enabled... OK
  Applying django_celery_beat.0015_edit_solarschedule_events_choices... OK
  Applying django_celery_beat.0016_alter_crontabschedule_timezone... OK
  Applying django_celery_beat.0017_alter_crontabschedule_month_of_year... OK
  Applying django_celery_beat.0018_improve_crontab_helptext... OK
  Applying lang.0018_alter_plural_type... OK
  Applying lang.0019_alter_plural_type... OK
[2023-06-05 22:07:12,269: WARNING/48] Handled exception: ProgrammingError: index "memory_source_trgm" does not exist

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedObject: index "memory_source_trgm" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/weblate", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/usr/local/lib/python3.11/site-packages/weblate/runner.py", line 19, in main
    utility.execute()
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/operations/special.py", line 193, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/usr/local/lib/python3.11/site-packages/weblate/memory/migrations/0013_reindex.py", line 18, in create_index
    schema_editor.execute("DROP INDEX memory_source_trgm")
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: index "memory_source_trgm" does not exist

### How do you run Weblate?

Docker container

### Weblate versions

 * Weblate: 4.17
 * Django: 4.2.1
 * siphashc: 2.1
 * translate-toolkit: 3.8.6
 * lxml: 4.9.2
 * Pillow: 9.5.0
 * nh3: 0.2.12
 * python-dateutil: 2.8.2
 * social-auth-core: 4.4.2
 * social-auth-app-django: 5.2.0
 * django-crispy-forms: 2.0
 * oauthlib: 3.2.2
 * django-compressor: 4.3.1
 * djangorestframework: 3.14.0
 * django-filter: 23.1
 * django-appconf: 1.0.5
 * user-agents: 2.2.0
 * filelock: 3.11.0
 * rapidfuzz: 3.0.0
 * openpyxl: 3.1.2
 * celery: 5.2.7
 * django-celery-beat: 2.5.0
 * kombu: 5.3.0
 * translation-finder: 2.15
 * weblate-language-data: 2023.4
 * html2text: 2020.1.16
 * pycairo: 1.23.0
 * pygobject: 3.44.1
 * diff-match-patch: 20200713
 * requests: 2.28.2
 * django-redis: 5.2.0
 * hiredis: 2.2.3
 * sentry_sdk: 1.25.0
 * Cython: 0.29.35
 * misaka: 2.1.1
 * GitPython: 3.1.31
 * borgbackup: 1.2.4
 * pyparsing: 3.0.9
 * pyahocorasick: 2.0.0
 * python-redis-lock: 4.0.0
 * charset-normalizer: 3.1.0
 * Python: 3.11.3
 * Git: 2.30.2
 * psycopg2: 2.9.6
 * psycopg2-binary: 2.9.6
 * phply: 1.2.6
 * ruamel.yaml: 0.17.31
 * tesserocr: 2.6.0
 * boto3: 1.26.146
 * zeep: 4.2.1
 * aeidon: 1.12
 * iniparse: 0.5
 * mysqlclient: 2.1.1
 * Mercurial: 6.4.3
 * git-svn: 2.30.2
 * git-review: 2.3.1
 * Redis server: 4.0.14
 * PostgreSQL server: 13.11
 * Database backends: django.db.backends.postgresql
 * Cache backends: default:RedisCache, avatar:FileBasedCache
 * Email setup: django.core.mail.backends.smtp.EmailBackend: smtp.mailgun.org
 * OS encoding: filesystem=utf-8, default=utf-8
 * Celery: redis://localhost:6379/1, redis://localhost:6379/1, regular
 * Platform: Linux 5.10.0-23-amd64 (x86_64)

### Weblate deploy checks

_No response_

### Additional context

I am running redis:4-alpine docker image, but the Postgres database runs on the host machine itself (not via Docker), and is 13.11 on Debian 11 (Bullseye).

[mig5]

based on the migration code in question, I seem to have worked around it by running this directly against the Postgres DB:

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE INDEX memory_source_trgm ON memory_memory USING GIN (source gin_trgm_ops, target_language_id, source_language_id);

Now since the index exists, the migration was able to DROP it. I guess your migration should've accounted for whether the index didn't already exist somehow before trying to drop it. (Postgres requires you to put IF EXISTS if you don't want a fatal error here...)

But I am now having the same problem on later migrations:

Postgres 130011 is up
Starting database migration...
Operations to perform:
  Apply all migrations: accounts, addons, admin, auth, authtoken, checks, configuration, contenttypes, django_celery_beat, fonts, gitexport, glossary, lang, memory, metrics, screenshots, sessions, social_django, trans, utils, vcs, weblate_auth, wladmin
Running migrations:
[2023-06-05 22:39:33,332: WARNING/48] Handled exception: ProgrammingError: index "unit_source_fulltext" does not exist

Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UndefinedObject: index "unit_source_fulltext" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/weblate", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/usr/local/lib/python3.11/site-packages/weblate/runner.py", line 19, in main
    utility.execute()
  File "/usr/local/lib/python3.11/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.11/site-packages/django/db/migrations/operations/special.py", line 193, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/usr/local/lib/python3.11/site-packages/weblate/trans/migrations/0163_update_indexes.py", line 33, in create_index
    schema_editor.execute(PG_DROP.format(table, field))
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: index "unit_source_fulltext" does not exist

  Applying trans.0163_update_indexes...

Please advise, what indexes should I add to workaround this? Thanks...

[mig5]

I have manually added some indexes to allow the migration to drop them and recreate them, and now my instance is back online.

I'll rename this issue to say that you need to use 'IF EXISTS' with a DROP INDEX for postgres compatibility.

[nijel]

The unit_source_fulltext index is supposed to be there since the 4.0 release (introduced in fabe5a3). Similarly, with the memory index, it was created by one of the migrations on earlier releases. Did you do some manual changes to the database?

[mig5]

@nijel I don't know, unfortunately (I inherited the app when it was already 4.6, and the people that used to manage it are no longer with the organization). This was a staging app, but I can see the production app is the same. Here are the indexes on the trans_unit table on the production db right now (I haven't tried to upgrade it yet from 4.14.1)

Indexes:
    "trans_unit_pkey" PRIMARY KEY, btree (id)
    "trans_unit_shaping_id_b90861a2" btree (variant_id)
    "trans_unit_source_unit_id_7a735f87" btree (source_unit_id)
    "trans_unit_translation_id_513bb910" btree (translation_id)
    "trans_unit_translation_id_id_hash_ab945589_uniq" UNIQUE CONSTRAINT, btree (translation_id, id_hash)
    "unit_explanation_fulltext" gin (explanation gin_trgm_ops)

and on memory_memory:

Indexes:
    "memory_memory_pkey" PRIMARY KEY, btree (id)
    "memory_memory_project_id_f26cda43" btree (project_id)
    "memory_memory_source_language_id_c53ef4ff" btree (source_language_id)
    "memory_memory_target_language_id_c44dca3c" btree (target_language_id)
    "memory_memory_user_id_1f05b3bb" btree (user_id)
    "memory_origin_index" hash (origin)
    "memory_source_index" hash (source)
    "memory_target_index" hash (target)

Either case, can I recommend to add IF EXISTS on postgres DBs for DROP INDEX activities. Whatever the situation and what might cause it, for such a small change it makes the app much more resilient. https://www.postgresql.org/docs/current/sql-dropindex.html

[nijel]

Accepting random changes to the database schema will not make the application more resilient, it will just silently accept breakage.

On the memory_memory table, you lack at least all the gin_trgm_ops indexes, while you have some extra hash indexes, which should have been replaced by these in the 4.1 migrations.

It appears that something went wrong while upgrading to 4.1 where all the trgm indexes should have been created.

[mig5]

@nijel , thanks for the insight about the possible 4.1 upgrade issue.

I do find your response very peculiar though.

Accepting random changes to the database schema

This seems a bit over an overstatement to call adding an IF EXISTS when dropping an index, a 'random change'. Come on.

will not make the application more resilient

It will make the migration more resilient.

it will just silently accept breakage

You are dropping an index and creating a new one. Who cares if it was already broken? You can at least keep in the forefront of your mind that you can help the users of your software by ensuring that your migration succeeds, why care about whether the index should have been there if you are only going to drop it anyway.

It appears that something went wrong

Finally we agree: things can go wrong. And you have a perfect opportunity to guard against such surprises that neither of us expected. Sorry that this represents a 'random change' that means you feel the need to double down on.

What a shame! I guess I'll try my luck with the production upgrade and hope my workarounds work there too. Thanks anyway.

[github-actions]

The issue you have reported is now resolved. If you don’t feel it’s right, please follow its labels to get a clue for further steps.

• In case you see a similar problem, please open a separate issue.
• If you are happy with the outcome, don’t hesitate to support Weblate by making a donation.

[nijel]

Finally we agree: things can go wrong. And you have a perfect opportunity to guard against such surprises that neither of us expected. Sorry that this represents a 'random change' that means you feel the need to double down on.

Sorry, but it will never be safe to skip parts of the database migrations. Adding IF EXISTS would address only one part of the problem (not created indexes), but other issues would remain hidden (not removed indexes). In the end, this will cause performance issues because of the additional indexes in the database. That's why I think it's better to see the breakage instead of hiding it by ignoring errors via IF EXISTS.

[mig5]

I don't think I understand what you mean.

DROP INDEX IF EXISTS instead of DROP INDEX can't ever cause 'not removed indexes' or a situation of 'additional indexes'.

Your migration already knows it wants to drop an existing index - all you'd be doing is ensuring you only drop if it really does exist.

Nothing about adding 'IF EXISTS" to a DROP statement could cause extra things to remain. It's literally just turning a fatal error into a warning if the thing didn't exist in the first place before trying to remove it.

But that's ok, I can see I can't convince you otherwise, that's why I closed it out. Thanks anyway.

[nijel]

There are many other migrations that will fail if something in the database has been manually tinkered with. This is not a supported scenario.