Module: OnlineMigrations::SchemaStatements

Includes:
BackgroundDataMigrations::MigrationHelpers, BackgroundSchemaMigrations::MigrationHelpers, ChangeColumnTypeHelpers
Defined in:
lib/online_migrations/schema_statements.rb

Instance Method Summary collapse

Methods included from BackgroundSchemaMigrations::MigrationHelpers

#add_index_in_background, #enqueue_background_schema_migration, #ensure_background_schema_migration_succeeded, #remove_index_in_background, #validate_constraint_in_background, #validate_foreign_key_in_background

Methods included from BackgroundDataMigrations::MigrationHelpers

#backfill_column_for_type_change_in_background, #backfill_column_in_background, #backfill_columns_for_type_change_in_background, #backfill_columns_in_background, #copy_column_in_background, #copy_columns_in_background, #delete_associated_records_in_background, #delete_orphaned_records_in_background, #enqueue_background_data_migration, #ensure_background_data_migration_succeeded, #perform_action_on_relation_in_background, #remove_background_data_migration, #reset_counters_in_background

Methods included from ChangeColumnTypeHelpers

#backfill_column_for_type_change, #backfill_columns_for_type_change, #cleanup_column_type_change, #cleanup_columns_type_change, #finalize_column_type_change, #finalize_columns_type_change, #initialize_column_type_change, #initialize_columns_type_change, #revert_finalize_column_type_change, #revert_finalize_columns_type_change, #revert_initialize_column_type_change, #revert_initialize_columns_type_change

Instance Method Details

#add_check_constraint(table_name, expression, **options) ⇒ Object

Extends default method to be idempotent

See Also:



804
805
806
807
808
809
810
811
812
813
 
# File 'lib/online_migrations/schema_statements.rb', line 804

def add_check_constraint(table_name, expression, **options)
  if check_constraint_exists?(table_name, expression: expression, **options)
    Utils.say(<<~MSG.squish)
      Check constraint was not created because it already exists (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}
    MSG
  else
    super
  end
end

#add_column_with_default(table_name, column_name, type, **options) ⇒ void

Note:

This method should not be run within a transaction

Note:

If default value is not volatile, for PostgreSQL 11+ you can use ‘add_column` instead.

This method returns an undefined value.

Adds a column with a default value without durable locks of the entire table

This method runs the following steps:

  1. Add the column allowing NULLs

  2. Change the default value of the column to the specified value

  3. Backfill all existing rows in batches

  4. Set a ‘NOT NULL` constraint on the column if desired (the default).

These steps ensure a column can be added to a large and commonly used table without locking the entire table for the duration of the table modification.

For large tables (10/100s of millions of records) you may consider implementing

the steps from this helper method yourself as a separate migrations, replacing step #3
with the help of background migrations (see `backfill_column_in_background`).

Examples:

add_column_with_default(:users, :admin, :boolean, default: false, null: false)

Additional column options

add_column_with_default(:users, :twitter, :string, default: "", limit: 64)

Additional batching options

add_column_with_default(:users, :admin, :boolean, default: false,
                        batch_size: 10_000, pause_ms: 100)

Options Hash (**options):

  • :default (Object) —

    The column’s default value

  • :null (Boolean) — default: true —

    Allows or disallows NULL values in the column



439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
 
# File 'lib/online_migrations/schema_statements.rb', line 439

def add_column_with_default(table_name, column_name, type, **options)
  default = options.fetch(:default)

  if Utils.volatile_default?(self, type, default)
    __ensure_not_in_transaction!

    batch_options = options.extract!(:batch_size, :batch_column_name, :progress, :pause_ms)

    if column_exists?(table_name, column_name)
      Utils.say("Column was not created because it already exists (this may be due to an aborted migration " \
                "or similar) table_name: #{table_name}, column_name: #{column_name}")
    else
      transaction do
        add_column(table_name, column_name, type, **options, default: nil, null: true)
        change_column_default(table_name, column_name, default)
      end
    end

    update_column_in_batches(table_name, column_name, default, **batch_options)

    allow_null = options.delete(:null) != false
    if !allow_null
      # A `NOT NULL` constraint for the column is functionally equivalent
      # to creating a CHECK constraint `CHECK (column IS NOT NULL)` for the table
      add_not_null_constraint(table_name, column_name, validate: false)
      validate_not_null_constraint(table_name, column_name)

      # In PostgreSQL 12+ it is safe to "promote" a CHECK constraint to `NOT NULL` for the column
      change_column_null(table_name, column_name, false)
      remove_not_null_constraint(table_name, column_name)
    end
  else
    add_column(table_name, column_name, type, **options)
  end
end

#add_exclusion_constraint(table_name, expression, **options) ⇒ Object 



830
831
832
833
834
835
836
837
838
839
 
# File 'lib/online_migrations/schema_statements.rb', line 830

def add_exclusion_constraint(table_name, expression, **options)
  if __exclusion_constraint_exists?(table_name, expression: expression, **options)
    Utils.say(<<~MSG.squish)
      Exclusion constraint was not created because it already exists (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}
    MSG
  else
    super
  end
end

#add_foreign_key(from_table, to_table, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



772
773
774
775
776
777
778
779
780
781
782
783
 
# File 'lib/online_migrations/schema_statements.rb', line 772

def add_foreign_key(from_table, to_table, **options)
  options = foreign_key_options(from_table, to_table, options)

  if foreign_key_exists?(from_table, to_table, **options.slice(:column, :primary_key))
    Utils.say(<<~MSG.squish)
      Foreign key was not created because it already exists (this can be due to an aborted migration or similar).
      from_table: #{from_table}, to_table: #{to_table}, options: #{options.inspect}
    MSG
  else
    super
  end
end

#add_index(table_name, column_name, **options) ⇒ Object

Extends default method to be idempotent and automatically recreate invalid indexes.

See Also:



704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
 
# File 'lib/online_migrations/schema_statements.rb', line 704

def add_index(table_name, column_name, **options)
  __ensure_not_in_transaction! if options[:algorithm] == :concurrently

  index =
    if column_name.is_a?(String) && column_name.match?(/\W/)
      # Use only name to check if index exists, because it does not work for complex expressions.
      index_name = (options[:name] || index_name(table_name, column_name)).to_s
      indexes(table_name).find { |i| i.name == index_name }
    else
      indexes(table_name).find { |i| i.defined_for?(column_name, **options) }
    end

  if index
    if index.valid?
      Utils.say("Index #{index.name} was not created because it already exists.")
      return
    else
      Utils.say("Recreating invalid index: table_name: #{table_name}, column_name: #{column_name}")
      remove_index(table_name, column_name, **options)
    end
  end

  # "CREATE INDEX CONCURRENTLY" requires a "SHARE UPDATE EXCLUSIVE" lock.
  # It only conflicts with constraint validations, creating/removing indexes,
  # and some other "ALTER TABLE"s.
  super

  # Outdated statistics + a new index can hurt performance of existing queries.
  if OnlineMigrations.config.auto_analyze
    execute("ANALYZE #{table_name}")
  end
end

#add_not_null_constraint(table_name, column_name, name: nil, validate: true) ⇒ void

This method returns an undefined value.

Adds a NOT NULL constraint to the column

Examples:

add_not_null_constraint(:users, :email, validate: false)


488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
 
# File 'lib/online_migrations/schema_statements.rb', line 488

def add_not_null_constraint(table_name, column_name, name: nil, validate: true)
  column = column_for(table_name, column_name)
  if column.null == false ||
     __not_null_constraint_exists?(table_name, column_name, name: name)
    Utils.say("NOT NULL constraint was not created: column #{table_name}.#{column_name} is already defined as `NOT NULL`")
  else
    expression = "#{quote_column_name(column_name)} IS NOT NULL"
    name ||= __not_null_constraint_name(table_name, column_name)
    add_check_constraint(table_name, expression, name: name, validate: false)

    if validate
      validate_not_null_constraint(table_name, column_name, name: name)
    end
  end
end

#add_reference_concurrently(table_name, ref_name, **options) ⇒ void

Note:

This method should not be run within a transaction

This method returns an undefined value.

Adds a reference to the table with minimal locking

Active Record adds an index non-‘CONCURRENTLY` to references by default, which blocks writes. It also adds a validated foreign key by default, which blocks writes on both tables while validating existing rows.

This method makes sure that an index is added ‘CONCURRENTLY` and the foreign key creation is performed in 2 steps: addition of invalid foreign key and a separate validation.

Examples:

add_reference_concurrently(:projects, :user)


654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/online_migrations/schema_statements.rb', line 654

def add_reference_concurrently(table_name, ref_name, **options)
  __ensure_not_in_transaction!

  column_name = "#{ref_name}_id"
  type_column_name = "#{ref_name}_type"

  if !column_exists?(table_name, column_name)
    type = options[:type] || :bigint
    allow_null = options.fetch(:null, true)
    add_column(table_name, column_name, type, null: allow_null)
  end

  if options[:polymorphic] && !column_exists?(table_name, type_column_name)
    allow_null = options[:polymorphic].is_a?(Hash) ? options[:polymorphic][:null] : true
    add_column(table_name, type_column_name, :string, null: allow_null)
  end

  # Always added by default in 5.0+
  index = options.fetch(:index, true)

  if index
    index = {} if index == true
    index_columns = [column_name]

    if options[:polymorphic]
      index_columns.unshift(type_column_name)
      index[:name] ||= "index_#{table_name}_on_#{ref_name}"
    end

    add_index(table_name, index_columns, **index, algorithm: :concurrently)
  end

  foreign_key = options[:foreign_key]

  if foreign_key
    foreign_key = {} if foreign_key == true

    foreign_table_name = Utils.foreign_table_name(ref_name, foreign_key)
    add_foreign_key(table_name, foreign_table_name, **foreign_key, column: column_name, validate: false)

    if foreign_key[:validate] != false
      validate_foreign_key(table_name, foreign_table_name, **foreign_key)
    end
  end
end

#add_text_limit_constraint(table_name, column_name, limit, name: nil, validate: true) ⇒ void

Note:

This helper must be used only with text columns

This method returns an undefined value.

Adds a limit constraint to the text column

Examples:

add_text_limit_constraint(:users, :bio, 255)


569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
 
# File 'lib/online_migrations/schema_statements.rb', line 569

def add_text_limit_constraint(table_name, column_name, limit, name: nil, validate: true)
  column = column_for(table_name, column_name)
  if column.type != :text && column.type != :string
    raise "add_text_limit_constraint must be used only with :text or :string columns"
  end

  name ||= __text_limit_constraint_name(table_name, column_name)

  if __text_limit_constraint_exists?(table_name, column_name, name: name)
    Utils.say("Text limit constraint was not created: #{table_name}.#{column_name} is already has a limit")
  else
    add_check_constraint(
      table_name,
      "char_length(#{column_name}) <= #{limit}",
      name: name,
      validate: false
    )

    if validate
      validate_text_limit_constraint(table_name, column_name, name: name)
    end
  end
end

#finalize_column_rename(table_name, column_name, new_column_name) ⇒ void

This method returns an undefined value.

Finishes the process of column rename

Examples:

finalize_column_rename(:users, :name, :first_name)


231
232
233
 
# File 'lib/online_migrations/schema_statements.rb', line 231

def finalize_column_rename(table_name, column_name, new_column_name)
  finalize_columns_rename(table_name, { column_name => new_column_name })
end

#finalize_columns_rename(table_name, old_new_column_hash) ⇒ void

This method returns an undefined value.

Same as ‘finalize_column_rename` but for multiple columns.

Examples:

finalize_columns_rename(:users, { fname: :first_name, lname: :last_name })


243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/online_migrations/schema_statements.rb', line 243

def finalize_columns_rename(table_name, old_new_column_hash)
  transaction do
    execute("DROP VIEW #{quote_table_name(table_name)}")

    tmp_table = __tmp_table_name_for_column_rename(table_name)
    rename_table(tmp_table, table_name)
    old_new_column_hash.each do |column_name, new_column_name|
      rename_column(table_name, column_name, new_column_name)
    end
  end
end

#finalize_table_rename(table_name, _new_name = nil) ⇒ void

This method returns an undefined value.

Finishes the process of table rename

Examples:

finalize_table_rename(:users, :clients)


360
361
362
 
# File 'lib/online_migrations/schema_statements.rb', line 360

def finalize_table_rename(table_name, _new_name = nil)
  execute("DROP VIEW IF EXISTS #{quote_table_name(table_name)}")
end

#initialize_column_rename(table_name, column_name, new_column_name) ⇒ void

Note:

Prior to using this method, you need to register the database table so that it instructs Active Record to fetch the database table information (for SchemaCache) using the original table name (if it’s present). Otherwise, fall back to the old table name:

“‘OnlineMigrations.config.column_renames = { old_column_name => new_column_name }“`

Deploy this change before proceeding with this helper. This is necessary to avoid errors during a zero-downtime deployment.

Note:

None of the DDL operations involving original table name can be performed until ‘finalize_column_rename` is run

This method returns an undefined value.

Renames a column without requiring downtime

The technique is built on top of database views, using the following steps:

1. Rename the table to some temporary name
2. Create a VIEW using the old table name with addition of a new column as an alias of the old one
3. Add a workaround for Active Record's schema cache

For example, to rename ‘name` column to `first_name` of the `users` table, we can run:

BEGIN;
ALTER TABLE users RENAME TO users_column_rename;
CREATE VIEW users AS SELECT *, first_name AS name FROM users;
COMMIT;

As database views do not expose the underlying table schema (default values, not null constraints, indexes, etc), further steps are needed to update the application to use the new table name. Active Record heavily relies on this data, for example, to initialize new models.

To work around this limitation, we need to tell Active Record to acquire this information from original table using the new table name (see notes).

Examples:

initialize_column_rename(:users, :name, :first_name)


163
164
165
 
# File 'lib/online_migrations/schema_statements.rb', line 163

def initialize_column_rename(table_name, column_name, new_column_name)
  initialize_columns_rename(table_name, { column_name => new_column_name })
end

#initialize_columns_rename(table_name, old_new_column_hash) ⇒ Object

Same as ‘initialize_column_rename` but for multiple columns.

This is useful to avoid multiple iterations of the safe column rename steps when renaming multiple columns.

Examples:

initialize_columns_rename(:users, { fname: :first_name, lname: :last_name })

See Also:



180
181
182
183
184
 
# File 'lib/online_migrations/schema_statements.rb', line 180

def initialize_columns_rename(table_name, old_new_column_hash)
  transaction do
    __rename_table_and_create_view(table_name, old_new_column_hash)
  end
end

#initialize_table_rename(table_name, new_name) ⇒ void

Note:

Prior to using this method, you need to register the database table so that it instructs Active Record to fetch the database table information (for SchemaCache) using the new table name (if it’s present). Otherwise, fall back to the old table name:

“‘

OnlineMigrations.config.table_renames[old_table_name] = new_table_name

“‘

Deploy this change before proceeding with this helper. This is necessary to avoid errors during a zero-downtime deployment.

Note:

None of the DDL operations involving original table name can be performed until ‘finalize_table_rename` is run

This method returns an undefined value.

Renames a table without requiring downtime

The technique is built on top of database views, using the following steps:

1. Rename the database table
2. Create a database view using the old table name by pointing to the new table name
3. Add a workaround for Active Record's schema cache

For example, to rename ‘clients` table name to `users`, we can run:

BEGIN;
ALTER TABLE clients RENAME TO users;
CREATE VIEW clients AS SELECT * FROM users;
COMMIT;

As database views do not expose the underlying table schema (default values, not null constraints, indexes, etc), further steps are needed to update the application to use the new table name. Active Record heavily relies on this data, for example, to initialize new models.

To work around this limitation, we need to tell Active Record to acquire this information from original table using the new table name (see notes).

Examples:

initialize_table_rename(:clients, :users)


328
329
330
331
332
333
 
# File 'lib/online_migrations/schema_statements.rb', line 328

def initialize_table_rename(table_name, new_name)
  transaction do
    rename_table(table_name, new_name)
    execute("CREATE VIEW #{quote_table_name(table_name)} AS SELECT * FROM #{quote_table_name(new_name)}")
  end
end

#pk_and_sequence_for(table) ⇒ Object 



842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
 
# File 'lib/online_migrations/schema_statements.rb', line 842

def pk_and_sequence_for(table)
  views = self.views

  table_renames = OnlineMigrations.config.table_renames
  renamed_tables = table_renames.slice(*views)

  column_renames = OnlineMigrations.config.column_renames
  renamed_columns = column_renames.slice(*views)

  if renamed_tables.key?(table)
    super(renamed_tables[table])
  elsif renamed_columns.key?(table)
    tmp_table = __tmp_table_name_for_column_rename(table)
    super(tmp_table)
  else
    super
  end
end

#remove_check_constraint(table_name, expression = nil, **options) ⇒ Object

Extends default method to be idempotent

See Also:



819
820
821
822
823
824
825
826
827
828
 
# File 'lib/online_migrations/schema_statements.rb', line 819

def remove_check_constraint(table_name, expression = nil, **options)
  if check_constraint_exists?(table_name, expression: expression, **options)
    super
  else
    Utils.say(<<~MSG.squish)
      Check constraint was not removed because it does not exist (this may be due to an aborted migration or similar).
      table_name: #{table_name}, expression: #{expression}, options: #{options.inspect}
    MSG
  end
end

#remove_foreign_key(from_table, to_table = nil, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



789
790
791
792
793
794
795
796
797
798
 
# File 'lib/online_migrations/schema_statements.rb', line 789

def remove_foreign_key(from_table, to_table = nil, **options)
  if foreign_key_exists?(from_table, to_table, **options.slice(:name, :to_table, :column))
    super
  else
    Utils.say(<<~MSG.squish)
      Foreign key was not removed because it does not exist (this may be due to an aborted migration or similar).
      from_table: #{from_table}, to_table: #{to_table}, options: #{options.inspect}
    MSG
  end
end

#remove_index(table_name, column_name = nil, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
 
# File 'lib/online_migrations/schema_statements.rb', line 741

def remove_index(table_name, column_name = nil, **options)
  if column_name.blank? && options[:column].blank? && options[:name].blank?
    raise ArgumentError, "No name or columns specified"
  end

  __ensure_not_in_transaction! if options[:algorithm] == :concurrently

  column = column_name || options[:column]
  index_exists =
    if column.is_a?(String) && column.match?(/\W/)
      # Use only name to check if index exists, because it does not work for complex expressions.
      index_name = options[:name] || index_name(table_name, column)
      index_name_exists?(table_name, index_name)
    else
      index_exists?(table_name, column_name, **options)
    end

  if index_exists
    # "DROP INDEX CONCURRENTLY" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with constraint validations, other creating/removing indexes,
    # and some "ALTER TABLE"s.
    super
  else
    Utils.say("Index was not removed because it does not exist.")
  end
end

#remove_not_null_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Removes a NOT NULL constraint from the column

Examples:

remove_not_null_constraint(:users, :email)

Explicit name

remove_not_null_constraint(:users, :email, name: "check_users_email_null")


549
550
551
552
 
# File 'lib/online_migrations/schema_statements.rb', line 549

def remove_not_null_constraint(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  remove_check_constraint(table_name, name: name)
end

#remove_text_limit_constraint(table_name, column_name, _limit = nil, name: nil) ⇒ void

This method returns an undefined value.

Removes a limit constraint from the text column

Examples:

remove_text_limit_constraint(:users, :bio)

Explicit name

remove_not_null_constraint(:users, :bio, name: "check_users_bio_max_length")


628
629
630
631
 
# File 'lib/online_migrations/schema_statements.rb', line 628

def remove_text_limit_constraint(table_name, column_name, _limit = nil, name: nil)
  name ||= __text_limit_constraint_name(table_name, column_name)
  remove_check_constraint(table_name, name: name)
end

#revert_finalize_column_rename(table_name, column_name, new_column_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by finalize_column_rename

Examples:

revert_finalize_column_rename(:users, :name, :first_name)


263
264
265
 
# File 'lib/online_migrations/schema_statements.rb', line 263

def revert_finalize_column_rename(table_name, column_name, new_column_name)
  revert_finalize_columns_rename(table_name, { column_name => new_column_name })
end

#revert_finalize_columns_rename(table_name, old_new_column_hash) ⇒ void

This method returns an undefined value.

Same as ‘revert_finalize_column_rename` but for multiple columns.

Examples:

revert_finalize_columns_rename(:users, { fname: :first_name, lname: :last_name })


275
276
277
278
279
280
281
282
 
# File 'lib/online_migrations/schema_statements.rb', line 275

def revert_finalize_columns_rename(table_name, old_new_column_hash)
  transaction do
    old_new_column_hash.each do |column_name, new_column_name|
      rename_column(table_name, new_column_name, column_name)
    end
    __rename_table_and_create_view(table_name, old_new_column_hash)
  end
end

#revert_finalize_table_rename(table_name, new_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by finalize_table_rename

Examples:

revert_finalize_table_rename(:users, :clients)


373
374
375
 
# File 'lib/online_migrations/schema_statements.rb', line 373

def revert_finalize_table_rename(table_name, new_name)
  execute("CREATE VIEW #{quote_table_name(table_name)} AS SELECT * FROM #{quote_table_name(new_name)}")
end

#revert_initialize_column_rename(table_name, column_name = nil, new_column_name = nil) ⇒ void

This method returns an undefined value.

Reverts operations performed by initialize_column_rename

Examples:

revert_initialize_column_rename(:users, :name, :first_name)


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

def revert_initialize_column_rename(table_name, column_name = nil, new_column_name = nil)
  revert_initialize_columns_rename(table_name, { column_name => new_column_name })
end

#revert_initialize_columns_rename(table_name, _old_new_column_hash = nil) ⇒ void

This method returns an undefined value.

Same as ‘revert_initialize_column_rename` but for multiple columns.

Examples:

revert_initialize_columns_rename(:users, { fname: :first_name, lname: :last_name })


214
215
216
217
218
219
220
221
 
# File 'lib/online_migrations/schema_statements.rb', line 214

def revert_initialize_columns_rename(table_name, _old_new_column_hash = nil)
  transaction do
    execute("DROP VIEW #{quote_table_name(table_name)}")

    tmp_table = __tmp_table_name_for_column_rename(table_name)
    rename_table(tmp_table, table_name)
  end
end

#revert_initialize_table_rename(table_name, new_name) ⇒ void

This method returns an undefined value.

Reverts operations performed by initialize_table_rename

Examples:

revert_initialize_table_rename(:clients, :users)


343
344
345
346
347
348
 
# File 'lib/online_migrations/schema_statements.rb', line 343

def revert_initialize_table_rename(table_name, new_name)
  transaction do
    execute("DROP VIEW IF EXISTS #{quote_table_name(table_name)}")
    rename_table(new_name, table_name)
  end
end

#swap_column_names(table_name, column1, column2) ⇒ void

This method returns an undefined value.

Swaps two column names in a table

This method is mostly intended for use as one of the steps for concurrent column type change

Examples:

swap_column_names(:files, :size_for_type_change, :size)


390
391
392
393
394
395
396
 
# File 'lib/online_migrations/schema_statements.rb', line 390

def swap_column_names(table_name, column1, column2)
  transaction do
    rename_column(table_name, column1, "#{column1}_tmp")
    rename_column(table_name, column2, column1)
    rename_column(table_name, "#{column1}_tmp", column2)
  end
end

#update_column_in_batches(table_name, column_name, value, **options) {|relation| ... } ⇒ void

Note:

This method should not be run within a transaction

Note:

Consider ‘update_columns_in_batches` when updating multiple columns to avoid rewriting the table multiple times.

Note:

For large tables (10/100s of millions of records) you may consider using ‘backfill_column_in_background` or `copy_column_in_background`.

This method returns an undefined value.

Updates the value of a column in batches.

Examples:

update_column_in_batches(:users, :admin, false)

With extra conditions

update_column_in_batches(:users, :name, "Guest") do |relation|
  relation.where(name: nil)
end

From other column

update_column_in_batches(:users, :name_for_type_change, Arel.sql("name"))

With computed value

truncated_name = Arel.sql("substring(name from 1 for 64)")
update_column_in_batches(:users, :name, truncated_name) do |relation|
  relation.where("length(name) > 64")
end

Options Hash (**options):

  • :batch_size (Integer) — default: 1_000 —

    size of the batch

  • :batch_column_name (String, Symbol) — default: primary key —

    option is for tables without primary key, in this case another unique integer column can be used. Example: ‘:user_id`

  • :progress (Proc, Boolean) — default: false —

    whether to show progress while running.

    • when ‘true` - will show progress (prints “.” for each batch)

    • when ‘false` - will not show progress

    • when ‘Proc` - will call the proc on each iteration with the batched relation as argument. Example: `proc { |_relation| print “.” }`

  • :pause_ms (Integer) — default: 50 —

    The number of milliseconds to sleep between each batch execution. This helps to reduce database pressure while running updates and gives time to do maintenance tasks

Yields:

  • (relation) —

    a block to be called to add extra conditions to the queries being executed

Yield Parameters:

  • relation (ActiveRecord::Relation) —

    an instance of ‘ActiveRecord::Relation` to add extra conditions to



55
56
57
 
# File 'lib/online_migrations/schema_statements.rb', line 55

def update_column_in_batches(table_name, column_name, value, **options, &block)
  update_columns_in_batches(table_name, [[column_name, value]], **options, &block)
end

#update_columns_in_batches(table_name, columns_and_values, batch_size: 1000, batch_column_name: primary_key(table_name), progress: false, pause_ms: 50) ⇒ Object

Same as ‘update_column_in_batches`, but for multiple columns.

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

columns_and_values is an array of arrays (first item is a column name, second - new value)

See Also:



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/online_migrations/schema_statements.rb', line 69

def update_columns_in_batches(table_name, columns_and_values,
                              batch_size: 1000, batch_column_name: primary_key(table_name), progress: false, pause_ms: 50)
  __ensure_not_in_transaction!

  if !columns_and_values.is_a?(Array) || !columns_and_values.all?(Array)
    raise ArgumentError, "columns_and_values must be an array of arrays"
  end

  if progress
    if progress == true
      progress = ->(_) { print(".") }
    elsif !progress.respond_to?(:call)
      raise ArgumentError, "The progress body needs to be a callable."
    end
  end

  model = Utils.define_model(table_name)

  conditions = columns_and_values.filter_map do |(column_name, value)|
    value = Arel.sql(value.call.to_s) if value.is_a?(Proc)

    # Ignore subqueries in conditions
    if !value.is_a?(Arel::Nodes::SqlLiteral) || !value.to_s.match?(/select\s+/i)
      arel_column = model.arel_table[column_name]
      if value.nil?
        arel_column.not_eq(nil)
      else
        arel_column.not_eq(value).or(arel_column.eq(nil))
      end
    end
  end

  batch_relation = model.where(conditions.inject(:or))
  batch_relation = yield batch_relation if block_given?

  iterator = BatchIterator.new(batch_relation)
  iterator.each_batch(of: batch_size, column: batch_column_name) do |relation|
    updates =
      columns_and_values.to_h do |(column, value)|
        value = Arel.sql(value.call.to_s) if value.is_a?(Proc)
        [column, value]
      end

    relation.update_all(updates)

    progress.call(relation) if progress

    sleep(pause_ms * 0.001) if pause_ms > 0
  end
end

#validate_not_null_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Validates a NOT NULL constraint on the column

Examples:

validate_not_null_constraint(:users, :email)

Explicit name

validate_not_null_constraint(:users, :email, name: "check_users_email_null")


519
520
521
522
523
524
525
526
527
528
529
530
531
532
 
# File 'lib/online_migrations/schema_statements.rb', line 519

def validate_not_null_constraint(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  column = column_for(table_name, column_name)

  if column.null == false &&
     !__not_null_constraint_exists?(table_name, column_name, name: name)
    Utils.say(<<~MSG.squish)
      NOT NULL constraint was not validated: it does not exist and
      column #{table_name}.#{column_name} is already defined as `NOT NULL`
    MSG
  else
    validate_check_constraint(table_name, name: name)
  end
end

#validate_text_limit_constraint(table_name, column_name, name: nil) ⇒ void

This method returns an undefined value.

Validates a limit constraint on the text column

Examples:

validate_text_limit_constraint(:users, :bio)

Explicit name

validate_text_limit_constraint(:users, :bio, name: "check_users_bio_max_length")


608
609
610
611
 
# File 'lib/online_migrations/schema_statements.rb', line 608

def validate_text_limit_constraint(table_name, column_name, name: nil)
  name ||= __text_limit_constraint_name(table_name, column_name)
  validate_check_constraint(table_name, name: name)
end

#with_lock_retries(&block) ⇒ Object

Executes the block with a retry mechanism that alters the ‘lock_timeout` and sleep time between attempts.



865
866
867
868
869
870
 
# File 'lib/online_migrations/schema_statements.rb', line 865

def with_lock_retries(&block)
  __ensure_not_in_transaction!

  retrier = OnlineMigrations.config.lock_retrier
  retrier.with_lock_retries(self, &block)
end