Skip to content

Fix copy_indexes migration helper failing for indexes with operator classes defined for them

The copy_indexes migration helper is failing to properly copy indexes with one or more operator classes defined for them.

Examples of indexes with operator classes that we have in our database are the GIN text indexes or indexes that use the varchar_pattern_ops over a btree:

CREATE INDEX index_issues_on_description_trigram ON issues USING gin (description gin_trgm_ops);
CREATE INDEX index_merge_requests_on_description_trigram ON merge_requests USING gin (description gin_trgm_ops);
CREATE INDEX index_routes_on_path_text_pattern_ops ON routes USING btree (path varchar_pattern_ops);

The root cause of the issue was identified by @pbair and stems from a discrepancy between the indexes ActiveRecord method and the add_index one:

  1. indexes(table_name) returns the operator classes for all indexed columns through opclasses

  2. add_index(table_name, column_name, options = {}) accepts the operator classes for all indexed columns through the opclass hash key for options

    add_index(:developers, :name, using: 'gist', opclass: :gist_trgm_ops)
add_index(:developers, [:name, :city], using: 'gist', opclass: { city: :gist_trgm_ops })

In the copy_indexes migration helper, we are using for both calls opclasses, so even though we are properly fetching the operator classes, we do not set them correctly when creating the new index.