Module: OnlineMigrations::SchemaStatements

Includes:
BackgroundMigrations::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, #create_background_schema_migration, #enqueue_background_schema_migration, #ensure_background_schema_migration_succeeded, #remove_index_in_background

Methods included from BackgroundMigrations::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, #create_background_data_migration, #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

#__set_statement_timeout(timeout) ⇒ Object 



944
945
946
947
948
 
# File 'lib/online_migrations/schema_statements.rb', line 944

def __set_statement_timeout(timeout)
  # use ceil to prevent no timeout for values under 1 ms
  timeout = (timeout.to_f * 1000).ceil if !timeout.is_a?(String)
  execute("SET statement_timeout TO #{quote(timeout)}")
end

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

Extends default method to be idempotent

See Also:



851
852
853
854
855
856
857
858
859
860
 
# File 'lib/online_migrations/schema_statements.rb', line 851

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:

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)

Parameters:

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

    type of new column

  • options (Hash) —

    ‘:batch_size`, `:batch_column_name`, `:progress`, and `:pause_ms` are directly passed to `update_column_in_batches` to control the backfilling process. Additional options (like `:limit`, etc) are forwarded to `add_column`

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
474
475
 
# 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 database_version >= 11_00_00 && !Utils.volatile_default?(self, type, default)
    add_column(table_name, column_name, type, **options)
  else
    __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)

      if database_version >= 12_00_00
        # 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
    end
  end
end

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



892
893
894
895
896
897
898
899
900
901
 
# File 'lib/online_migrations/schema_statements.rb', line 892

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:



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

def add_foreign_key(from_table, to_table, **options)
  # Do not consider validation for idempotency.
  if foreign_key_exists?(from_table, to_table, **options.except(:validate))
    message = +"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}"
    message << ", #{options.inspect}" if options.any?

    Utils.say(message)
  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:



696
697
698
699
700
701
702
703
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
736
737
738
739
 
# File 'lib/online_migrations/schema_statements.rb', line 696

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
      # Rewrite this with `IndexDefinition#defined_for?` when Active Record >= 7.1 is supported.
      # See https://github.com/rails/rails/pull/45160.
      indexes(table_name).find { |i| __index_defined_for?(i, column_name, **options) }
    end

  if index
    schema = __schema_for_table(table_name)

    if __index_valid?(index.name, schema: schema)
      Utils.say("Index 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

  if OnlineMigrations.config.statement_timeout
    # "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
  else
    OnlineMigrations.deprecator.warn(<<~MSG)
      Running `add_index` without a statement timeout is deprecated.
      Configure an explicit statement timeout in the initializer file via `config.statement_timeout`
      or the default database statement timeout will be used.
      Example, `config.statement_timeout = 1.hour`.
    MSG

    disable_statement_timeout do
      super
    end
  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)

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`

  • validate (Boolean) (defaults to: true) —

    whether or not the constraint should be validated



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

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)

Parameters:



646
647
648
649
650
651
652
653
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
 
# File 'lib/online_migrations/schema_statements.rb', line 646

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)

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`

  • validate (Boolean) (defaults to: true) —

    whether or not the constraint should be validated



561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
 
# File 'lib/online_migrations/schema_statements.rb', line 561

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
    raise "add_text_limit_constraint must be used only with :text 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

#disable_statement_timeout ⇒ Object 



929
930
931
932
933
934
935
936
937
938
939
940
941
 
# File 'lib/online_migrations/schema_statements.rb', line 929

def disable_statement_timeout
  OnlineMigrations.deprecator.warn(<<~MSG)
    `disable_statement_timeout` is deprecated and will be removed. Configure an explicit
    statement timeout in the initializer file via `config.statement_timeout` or the default
    database statement timeout will be used. Example, `config.statement_timeout = 1.hour`.
  MSG

  prev_value = select_value("SHOW statement_timeout")
  __set_statement_timeout(0)
  yield
ensure
  __set_statement_timeout(prev_value)
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)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



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 })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns



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)

Parameters:

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

    table’s new name. Passing this argument will make this change reversible in migration



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

#index_name(table_name, options) ⇒ Object

From ActiveRecord. Will not be needed for ActiveRecord >= 7.1.



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

def index_name(table_name, options)
  if options.is_a?(Hash)
    if options[:column]
      Utils.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, column: options)
  end
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)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



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 })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns

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)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table’s new name



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 



905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
 
# File 'lib/online_migrations/schema_statements.rb', line 905

def pk_and_sequence_for(table)
  views = self.views

  table_renames = OnlineMigrations.config.table_renames
  renamed_tables = table_renames.select do |old_name, _|
    views.include?(old_name)
  end

  column_renames = OnlineMigrations.config.column_renames
  renamed_columns = column_renames.select do |table_name, _|
    views.include?(table_name)
  end

  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_index(table_name, column_name = nil, **options) ⇒ Object

Extends default method to be idempotent.

See Also:



745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
 
# File 'lib/online_migrations/schema_statements.rb', line 745

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
    if OnlineMigrations.config.statement_timeout
      # "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
      OnlineMigrations.deprecator.warn(<<~MSG)
        Running `remove_index` without a statement timeout is deprecated.
        Configure an explicit statement timeout in the initializer file via `config.statement_timeout`
        or the default database statement timeout will be used.
        Example, `config.statement_timeout = 1.hour`.
      MSG

      disable_statement_timeout do
        super
      end
    end
  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")

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`



541
542
543
544
 
# File 'lib/online_migrations/schema_statements.rb', line 541

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")

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`



620
621
622
623
 
# File 'lib/online_migrations/schema_statements.rb', line 620

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)

Parameters:

  • table_name (String, Symbol) —

    table name

  • column_name (String, Symbol) —

    the name of the column to be renamed

  • new_column_name (String, Symbol) —

    new new name of the column



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 })

Parameters:

  • table_name (String, Symbol) —

    table name

  • old_new_column_hash (Hash) —

    the hash of old and new columns



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)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table’s new name



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)

Parameters:

  • table_name (String, Symbol) —

    table name

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

    the name of the column to be renamed. Passing this argument will make this change reversible in migration

  • new_column_name (String, Symbol) (defaults to: nil) —

    new new name of the column. Passing this argument will make this change reversible in migration



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 })

Parameters:

  • table_name (String, Symbol) —

    table name

  • _old_new_column_hash (Hash) (defaults to: nil) —

    the hash of old and new columns Passing this argument will make this change reversible in migration



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)

Parameters:

  • table_name (String, Symbol)
  • new_name (String, Symbol) —

    table’s new name



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)

Parameters:

  • table_name (String, Symbol)
  • column1 (String, Symbol)
  • column2 (String, Symbol) 


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

Parameters:

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

    value for the column. It is typically a literal. To perform a computed update, an Arel literal can be used instead

  • options (Hash) —

    a customizable set of options

Options Hash (**options):

  • :batch_size (Integer) — default: 1000 —

    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)

Parameters:

  • columns_and_values

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_check_constraint(table_name, **options) ⇒ Object

Extends default method with disabled statement timeout while validation is run

See Also:



866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
 
# File 'lib/online_migrations/schema_statements.rb', line 866

def validate_check_constraint(table_name, **options)
  check_constraint = check_constraint_for!(table_name, **options)

  # Skip costly operation if already validated.
  return if check_constraint.validated?

  if OnlineMigrations.config.statement_timeout
    # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with other validations, creating/removing indexes,
    # and some other "ALTER TABLE"s.
    super
  else
    OnlineMigrations.deprecator.warn(<<~MSG)
      Running `validate_check_constraint` without a statement timeout is deprecated.
      Configure an explicit statement timeout in the initializer file via `config.statement_timeout`
      or the default database statement timeout will be used.
      Example, `config.statement_timeout = 1.hour`.
    MSG

    disable_statement_timeout do
      super
    end
  end
end

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

Extends default method with disabled statement timeout while validation is run

See Also:



822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
 
# File 'lib/online_migrations/schema_statements.rb', line 822

def validate_foreign_key(from_table, to_table = nil, **options)
  foreign_key = foreign_key_for!(from_table, to_table: to_table, **options)

  # Skip costly operation if already validated.
  return if foreign_key.validated?

  if OnlineMigrations.config.statement_timeout
    # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with other validations, creating/removing indexes,
    # and some other "ALTER TABLE"s.
    super
  else
    OnlineMigrations.deprecator.warn(<<~MSG)
      Running `validate_foreign_key` without a statement timeout is deprecated.
      Configure an explicit statement timeout in the initializer file via `config.statement_timeout`
      or the default database statement timeout will be used.
      Example, `config.statement_timeout = 1.hour`.
    MSG

    disable_statement_timeout do
      super
    end
  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")

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`



521
522
523
524
 
# File 'lib/online_migrations/schema_statements.rb', line 521

def validate_not_null_constraint(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  validate_check_constraint(table_name, name: name)
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")

Parameters:

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

    the constraint name. Defaults to ‘chk_rails_<identifier>`



600
601
602
603
 
# File 'lib/online_migrations/schema_statements.rb', line 600

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.



954
955
956
957
958
959
960
 
# File 'lib/online_migrations/schema_statements.rb', line 954

def with_lock_retries(&block)
  __ensure_not_in_transaction!

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