Skip to content

For CREATE INDEX CONCURRENTLY, drop existing one first

Nikolay Samokhvalov requested to merge nik/create_index_concurrently into master

What does this MR do?

  • Inside add_concurrent_index perform DROP INDEX CONCURRENTLY first, because it might exist already (if the previous attempt of running migration failed for som.e reason, leaving the index in INVALID state, making impossible to re-run DB migration with manual corrections). This can be improved in the future if we drop only those indexes that are in INVALID state, and skip valid ones.
  • Improve error messages related to disable_ddl_transaction!.

Closes https://gitlab.com/gitlab-org/gitlab-ce/issues/50162

A use case

Assume that we have a DB migration that consists of multiple single-statement transactions (disable_ddl_transaction! is used), each one is CREATE INDEX CONCURRENTLY over some large table(s). Assume we have 3 such CREATE INDEX CONCURRENTLY in the migration and during applying it, the second call fails due to some reason. After such a failure, we have the following picture:

  • the first index is built successfully and exists in the database,
  • the second index exists as well but in INVALID state,
  • the third one doesn't exist yet.

For such cases, before fixes presented in this MR, it was impossible to proceed without manual interventions. Two possibilities here:

  • if the migration had index_exists? check and tends to skip indexes already presented in the database, the migration will "succeed", but the second index will remain in INVALID state;
  • if migration doesn't have this explicit index_exists? check, the migration itself will simply fail, saying the index already exists.

Both cases are unacceptable.

See also https://www.postgresql.org/docs/9.6/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY, one of the possibilities to have failed CREATE UNIQUE INDEX CONCURRENTLY failure:

If a problem arises while scanning the table, such as a deadlock or a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however, it will still consume update overhead. The psql \d command will report such an index as INVALID: ... The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. Another caveat when building a unique index concurrently is that the uniqueness constraint is already being enforced against other transactions when the second table scan begins. This means that constraint violations could be reported in other queries prior to the index becoming available for use, or even in cases where the index build eventually fails.

Todo:

Edited by Nikolay Samokhvalov

Merge request reports