Module: OnlineMigrations::ChangeColumnTypeHelpers

Included in:
SchemaStatements
Defined in:
lib/online_migrations/change_column_type_helpers.rb

Overview

To safely change the type of the column, we need to perform the following steps:

1. create a new column based on the old one (covered by `initialize_column_type_change`)
2. ensure data stays in sync (via triggers) (covered by `initialize_column_type_change`)
3. backfill data from the old column (`backfill_column_for_type_change`)
4. copy indexes, foreign keys, check constraints, NOT NULL constraint,
make new column a Primary Key if we change type of the primary key column,
swap new column in place (`finalize_column_type_change`)
5. remove copy trigger and old column (`cleanup_column_type_change`)

For example, suppose we need to change files.size column's type from integer to bigint:

  1. Create a new column and keep data in sync
  class InitializeFilesSizeTypeChangeToBigint < ActiveRecord::Migration
    def change
      initialize_column_type_change(:files, :size, :bigint)
    end
  end
  1. Backfill data
  class BackfillFilesSizeTypeChangeToBigint < ActiveRecord::Migration
    def up
      backfill_column_for_type_change(:files, :size, progress: true)
    end

    def down
      # no op
    end
  end
  1. Copy indexes, foreign keys, check constraints, NOT NULL constraint, swap new column in place
  class FinalizeFilesSizeTypeChangeToBigint < ActiveRecord::Migration
    def change
      finalize_column_type_change(:files, :size)
    end
  end
  1. Finally, if everything is working as expected, remove copy trigger and old column
  class CleanupFilesSizeTypeChangeToBigint < ActiveRecord::Migration
    def up
      cleanup_column_type_change(:files, :size)
    end

    def down
      initialize_column_type_change(:files, :size, :integer)
    end
  end

Instance Method Summary collapse

Instance Method Details

#backfill_column_for_type_change(table_name, column_name, type_cast_function: nil, **options) ⇒ void

Note:

This method should not be run within a transaction

Note:

For large tables (10/100s of millions of records) it is recommended to use backfill_column_for_type_change_in_background.

This method returns an undefined value.

Backfills data from the old column to the new column.

Examples:

backfill_column_for_type_change(:files, :size)

With type casting

backfill_column_for_type_change(:users, :settings, type_cast_function: "jsonb")
backfill_column_for_type_change(:users, :company_id, type_cast_function: Arel.sql("company_id::integer"))

Additional batch options

backfill_column_for_type_change(:files, :size, batch_size: 10_000)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • type_cast_function (String, Symbol) (defaults to: nil) —

    Some type changes require casting data to a new type. For example when changing from text to jsonb. In this case, use the type_cast_function option. You need to make sure there is no bad data and the cast will always succeed

  • options (Hash) —

    used to control the behavior of update_column_in_batches



199
200
201
202
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 199

def backfill_column_for_type_change(table_name, column_name, type_cast_function: nil, **options)
  backfill_columns_for_type_change(table_name, column_name,
      type_cast_functions: { column_name => type_cast_function }, **options)
end

#backfill_columns_for_type_change(table_name, *column_names, type_cast_functions: {}, **options) ⇒ Object

Same as backfill_column_for_type_change but for multiple columns.

Parameters:

  • type_cast_functions (Hash) (defaults to: {}) —

    if not empty, keys - column names, values - corresponding type cast functions

See Also:



211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 211

def backfill_columns_for_type_change(table_name, *column_names, type_cast_functions: {}, **options)
  conversions = column_names.map do |column_name|
    tmp_column = __change_type_column(column_name)

    old_value = begin
      # Delete after supporting only ActiveRecord >= 8.2
      Arel::Table.new(table_name)[column_name]
    rescue ArgumentError
      # https://github.com/rails/rails/commit/b1650993b02497ae7d0d8b984d40bc036e62c681
      Arel::Table.new(name: table_name)[column_name]
    end

    if (type_cast_function = type_cast_functions.with_indifferent_access[column_name])
      old_value =
        case type_cast_function
        when Arel::Nodes::SqlLiteral
          type_cast_function
        else
          Arel::Nodes::NamedFunction.new(type_cast_function.to_s, [old_value])
        end
    end

    [tmp_column, old_value]
  end

  update_columns_in_batches(table_name, conversions, **options)
end

#cleanup_column_type_change(table_name, column_name) ⇒ void

Note:

This method is not reversible by default in migrations. You need to use initialize_column_type_change in down method with the original column type to be able to revert.

This method returns an undefined value.

Finishes the process of column type change

This helper removes copy triggers and old column.

Examples:

cleanup_column_type_change(:files, :size)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol) 


365
366
367
368
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 365

def cleanup_column_type_change(table_name, column_name)
  __ensure_not_in_transaction!
  cleanup_columns_type_change(table_name, column_name)
end

#cleanup_columns_type_change(table_name, *column_names) ⇒ Object

Same as cleanup_column_type_change but for multiple columns

See Also:



373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 373

def cleanup_columns_type_change(table_name, *column_names)
  __ensure_not_in_transaction!

  tmp_column_names = column_names.map { |column_name| __change_type_column(column_name) }

  # Safely remove existing indexes and foreign keys first, if any.
  tmp_column_names.each do |column_name|
    __indexes_for(table_name, column_name).each do |index|
      remove_index(table_name, name: index.name, algorithm: :concurrently)
    end

    __foreign_keys_for(table_name, column_name).each do |fk|
      remove_foreign_key(table_name, name: fk.name)
    end
  end

  transaction do
    __remove_copy_triggers(table_name, column_names, tmp_column_names)
    remove_columns(table_name, *tmp_column_names)
  end
end

#finalize_column_type_change(table_name, column_name) ⇒ Object

Note:

This method should not be run within a transaction

Copies NOT NULL constraint, indexes, foreign key, and check constraints from the old column to the new column

Note: If a column contains one or more indexes that don't contain the name of the original column, this procedure will fail. In that case, you'll first need to rename these indexes.

Examples:

finalize_column_type_change(:files, :size)


250
251
252
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 250

def finalize_column_type_change(table_name, column_name)
  finalize_columns_type_change(table_name, column_name)
end

#finalize_columns_type_change(table_name, *column_names) ⇒ Object

Same as finalize_column_type_change but for multiple columns

See Also:



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 257

def finalize_columns_type_change(table_name, *column_names)
  __ensure_not_in_transaction!

  conversions = column_names.to_h do |column_name|
    [column_name.to_s, __change_type_column(column_name)]
  end

  primary_key = primary_key(table_name)

  conversions.each do |column_name, tmp_column_name|
    old_column = column_for(table_name, column_name)
    column = column_for(table_name, tmp_column_name)

    # We already set default and NOT NULL for to-be-PK columns
    # for PG >= 11, so can skip this case
    if !old_column.null && column.null
      add_not_null_constraint(table_name, tmp_column_name, validate: false)
      validate_not_null_constraint(table_name, tmp_column_name)

      # At this point we are sure there are no NULLs in this column
      transaction do
        change_column_null(table_name, tmp_column_name, false)
        remove_not_null_constraint(table_name, tmp_column_name)
      end
    end

    __copy_indexes(table_name, column_name, tmp_column_name)
    __copy_foreign_keys(table_name, column_name, tmp_column_name)
    __copy_check_constraints(table_name, column_name, tmp_column_name)
    __copy_exclusion_constraints(table_name, column_name, tmp_column_name)

    if column_name == primary_key
      __finalize_primary_key_type_change(table_name, column_name, column_names)
    end
  end

  # Swap all non-PK columns at once, because otherwise when this helper possibly
  # will have a need to be rerun, it will be impossible to know which columns
  # already were swapped and which were not.
  transaction do
    conversions
      .reject { |column_name, _tmp_column_name| column_name == primary_key }
      .each do |column_name, tmp_column_name|
        swap_column_names(table_name, column_name, tmp_column_name)
      end

    __reset_trigger_function(table_name, column_names)
  end
end

#initialize_column_type_change(table_name, column_name, new_type, **options) ⇒ void

This method returns an undefined value.

Initialize the process of changing column type. Creates a new column from the old one and ensures that data stays in sync.

Examples:

initialize_column_type_change(:files, :size, :bigint)

With additional column options

initialize_column_type_change(:users, :name, :string, limit: 64)

With type casting

initialize_column_type_change(:users, :settings, :jsonb, type_cast_function: "jsonb")
initialize_column_type_change(:users, :company_id, :integer, type_cast_function: Arel.sql("company_id::integer"))

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • new_type (String, Symbol)
  • options (Hash) —

    additional options that apply to a new type, :limit for example



79
80
81
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 79

def initialize_column_type_change(table_name, column_name, new_type, **options)
  initialize_columns_type_change(table_name, [[column_name, new_type]], column_name => options)
end

#initialize_columns_type_change(table_name, columns_and_types, **options) ⇒ Object

Same as initialize_column_type_change but for multiple columns at once

This is useful to avoid multiple costly disk rewrites of large tables when changing type of each column separately.

Parameters:

  • table_name (String, Symbol)
  • columns_and_types (Array<Array<(Symbol, Symbol)>>) —

    columns and new types, represented as nested arrays. Example: [[:id, :bigint], [:name, :string]]

  • options (Hash) —

    keys - column names, values - options for specific columns (additional options that apply to a new type, :limit for example)

See Also:



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 96

def initialize_columns_type_change(table_name, columns_and_types, **options)
  if !columns_and_types.is_a?(Array) || !columns_and_types.all?(Array)
    raise ArgumentError, "columns_and_types must be an array of arrays"
  end

  conversions = columns_and_types.to_h do |(column_name, _new_type)|
    [column_name, __change_type_column(column_name)]
  end

  if (extra_keys = (options.keys - conversions.keys)).any?
    raise ArgumentError, "Options has unknown keys: #{extra_keys.map(&:inspect).join(', ')}. " \
                         "Can contain only column names: #{conversions.keys.map(&:inspect).join(', ')}."
  end

  transaction do
    type_cast_functions = {}.with_indifferent_access

    columns_and_types.each do |(column_name, new_type)|
      old_col = column_for(table_name, column_name)
      old_col_options = __options_from_column(old_col, [:collation, :comment])
      column_options = options[column_name] || {}
      type_cast_function = column_options.delete(:type_cast_function)
      type_cast_functions[column_name] = type_cast_function if type_cast_function
      tmp_column_name = conversions[column_name]

      if primary_key(table_name) == column_name.to_s && old_col.type == :integer
        # For PG < 11 and Primary Key conversions, setting a column as the PK
        # converts even check constraints to NOT NULL column constraints
        # and forces an inline re-verification of the whole table.
        # To avoid this, we instead set it to `NOT NULL DEFAULT 0` and we'll
        # copy the correct values when backfilling.
        add_column(table_name, tmp_column_name, new_type,
          **old_col_options, **column_options, default: old_col.default || 0, null: false)
      else
        if !old_col.default.nil?
          old_col_options = old_col_options.merge(default: old_col.default, null: old_col.null)
        end
        add_column(table_name, tmp_column_name, new_type, **old_col_options, **column_options)
      end
    end

    __create_copy_triggers(table_name, conversions.keys, conversions.values, type_cast_functions: type_cast_functions)
  end
end

#revert_finalize_column_type_change(table_name, column_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by finalize_column_type_change

Examples:

revert_finalize_column_type_change(:files, :size)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol) 


316
317
318
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 316

def revert_finalize_column_type_change(table_name, column_name)
  revert_finalize_columns_type_change(table_name, column_name)
end

#revert_finalize_columns_type_change(table_name, *column_names) ⇒ Object

Same as revert_finalize_column_type_change but for multiple columns

See Also:



323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 323

def revert_finalize_columns_type_change(table_name, *column_names)
  __ensure_not_in_transaction!

  conversions = column_names.to_h do |column_name|
    [column_name.to_s, __change_type_column(column_name)]
  end

  primary_key = primary_key(table_name)
  primary_key_conversion = conversions.delete(primary_key)

  # No need to remove indexes, foreign keys etc, because it  can take a significant amount
  # of time and will be automatically removed if decided to remove the column itself.
  if conversions.any?
    transaction do
      conversions.each do |column_name, tmp_column_name|
        swap_column_names(table_name, column_name, tmp_column_name)
      end

      __reset_trigger_function(table_name, column_names)
    end
  end

  if primary_key_conversion
    __finalize_primary_key_type_change(table_name, primary_key, column_names)
  end
end

#revert_initialize_column_type_change(table_name, column_name, _new_type = nil, **_options) ⇒ void

This method returns an undefined value.

Reverts operations performed by initialize_column_type_change

Examples:

revert_initialize_column_type_change(:files, :size)

Parameters:

  • table_name (String, Symbol)
  • column_name (String, Symbol)
  • _new_type (String, Symbol) (defaults to: nil) —

    Passing this argument will make this change reversible in migration

  • _options (Hash) —

    additional options that apply to a new type. Passing this argument will make this change reversible in migration



154
155
156
157
158
159
160
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 154

def revert_initialize_column_type_change(table_name, column_name, _new_type = nil, **_options)
  tmp_column_name = __change_type_column(column_name)
  transaction do
    __remove_copy_triggers(table_name, column_name, tmp_column_name)
    remove_column(table_name, tmp_column_name)
  end
end

#revert_initialize_columns_type_change(table_name, columns_and_types, **_options) ⇒ Object

Same as revert_initialize_column_type_change but for multiple columns.

See Also:



165
166
167
168
169
170
171
172
173
 
# File 'lib/online_migrations/change_column_type_helpers.rb', line 165

def revert_initialize_columns_type_change(table_name, columns_and_types, **_options)
  column_names = columns_and_types.map(&:first)
  tmp_column_names = column_names.map { |column_name| __change_type_column(column_name) }

  transaction do
    __remove_copy_triggers(table_name, column_names, tmp_column_names)
    remove_columns(table_name, *tmp_column_names)
  end
end