Rails 7.1 handles long auto-generated index names with a limit
While adding a composite index that is made up of columns with long names, the index name auto-generated by Rails can grow too long. That leads to the annoying error
Index name index_table_name_on_column1_and_column2_and_column3 on table ‘table_name’ is too long
Before
After we see this error, the fix is to add a name option for adding index like so:
1
add_index :opportunities, %i(manager_id operational_countries_id hospital_id opportunity_type), name: "idx_opps_on_mid_ocid_hid_otype"
This works great, but could be better if Rails handled that auto-magically.
Rails 7.1
This PR added a limit of 62 bytes on the auto generated index names. This is safe for MySQL, Postgres and SQLite index name limits.
If the generated index name is over the limit, the naming would be done as per a shorter format.
Example
1
2
3
index_table_name_on_column1_and_column2_and_column3 => Long format
ix_on_column1_and_column2_and_column3_584cb5f07a => Shorter format
The shorter format includes a hash created from the long format name to ensure uniqueness across the database.
Primary format
index_{table_name}_on_#{columns.join('_and_')}
New fallback - shorter format
ix_on_#{columns.join('_')}_#{OpenSSL::Digest::SHA256.hexdigest(long_name).first(10)}
Digging into Mike’s PR
I’m trying something new here. I’ll go over some key aspects of the PR and how this OSS contribution fixes this problem. The aim is to simplify the solution.
Changing the index name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb
def index_name(table_name, options) # :nodoc:
if Hash === options
if options[:column]
- "index_#{table_name}_on_#{Array(options[:column]) * '_and_'}"
+ generate_index_name(table_name, options[:column])
elsif options[:name]
options[:name]
else
raise ArgumentError, "You must specify the index name"
end
else
index_name(table_name, index_name_options(options))
end
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def generate_index_name(table_name, column)
name = "index_#{table_name}_on_#{Array(column) * '_and_'}"
# Array(column) * '_and_' is similar to Array(column).join('_and_')
# Example: index_students_on_name_and_age_and_city
return name if name.bytesize <= max_index_name_size
# If generated `name` is within limit, return that.
hashed_identifier = "_" + OpenSSL::Digest::SHA256.hexdigest(name).first(10)
name = "ix_on_#{Array(column) * '_'}"
# Example: ix_on_name_age_city_0c06491783
short_limit = max_index_name_size - hashed_identifier.bytesize
# Calculates the size left after counting size of the hash that has to be added at the end of the name.
# In this case 62 - 11 = 51
short_name = name.mb_chars.limit(short_limit).to_s
# Limit the new short format name to the limit we have left.
"#{short_name}#{hashed_identifier}"
# The final name is the calculated limited short_name with the hash identifier appended to it, which would always be within the limit.
end
def max_index_name_size
62
end
Adding migration compatibility
Any modification to Rails migration functionality must maintain compatibility with older migrations, ensuring they run the same way, just as they did when originally created in their respective Rails versions.
In this case, the author has added the below override for the add_index method for the Rails version immediately preceding 7.1. That is 7.0.
1
2
3
4
def add_index(table_name, column_name, **options)
options[:name] = legacy_index_name(table_name, column_name) if options[:name].nil?
super
end
Simply put, if name option is provided, the change in Rails 7.1 doesn’t do anything differently, so we don’t do anything and call super.
If the name has to be generated, author has implemented a compatible method legacy_index_name.
Let’s dive in:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
def legacy_index_name(table_name, options)
if Hash === options
# In this instance, think of it as, is `options` a Hash?
if options[:column]
# If column names are provided use them to create the name as before.
"index_#{table_name}_on_#{Array(options[:column]) * '_and_'}"
elsif options[:name]
# If name was provided, use that instead.
options[:name]
else
raise ArgumentError, "You must specify the index name"
end
else
# If options isn't a Hash, call itself again with standardized options via index_name_options method
legacy_index_name(table_name, index_name_options(options))
end
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
def index_name_options(column_names)
if expression_column_name?(column_names)
# If column_names has any character except letters, numbers or underscores,
# replace them with an underscore
column_names = column_names.scan(/\w+/).join("_")
end
# Return the column_names in an expected format for legacy_index_name
{ column: column_names }
end
def expression_column_name?(column_name)
column_name.is_a?(String) && /\W/.match?(column_name)
end
Conclusion
This was a minor pain point from the beginning and I liked when this was fixed. The deep dive is something I haven’t done before in my blogs, I’d appreciate the feedback in the comments below.
