module OnlineMigrations::SchemaStatements

Public Instance Methods

add_check_constraint(table_name, expression, validate: true, **options) click to toggle source

Extends default method to be idempotent

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_check_constraint @note This method was added in ActiveRecord 6.1

# File lib/online_migrations/schema_statements.rb, line 767
def add_check_constraint(table_name, expression, validate: true, **options)
  constraint_name = __check_constraint_name(table_name, expression: expression, **options)

  if __check_constraint_exists?(table_name, constraint_name)
    Utils.say("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}, constraint name: #{constraint_name}")
  else
    query = "ALTER TABLE #{table_name} ADD CONSTRAINT #{constraint_name} CHECK (#{expression})"
    query += " NOT VALID" if !validate

    execute(query)
  end
end
add_column_with_default(table_name, column_name, type, **options) click to toggle source

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 extra large tables (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`).

@param table_name [String, Symbol] @param column_name [String, Symbol] @param type [Symbol] type of new column

@param 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`

@option options :default The column’s default value @option options [Boolean] :null (true) Allows or disallows NULL values in the column

@return [void]

@example

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

@example Additional column options

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

@example Additional batching options

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

@note This method should not be run within a transaction @note For PostgreSQL 11+ you can use ‘add_column` instead

# File lib/online_migrations/schema_statements.rb, line 393
def add_column_with_default(table_name, column_name, type, **options)
  default = options.fetch(:default)
  if default.is_a?(Proc) && Utils.ar_version < 5.0 # https://github.com/rails/rails/pull/20005
    raise ArgumentError, "Expressions as default are not supported"
  end

  if raw_connection.server_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.merge(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 raw_connection.server_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_foreign_key(from_table, to_table, validate: true, **options) click to toggle source

Extends default method to be idempotent and accept ‘:validate` option for ActiveRecord < 5.2.

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_foreign_key

# File lib/online_migrations/schema_statements.rb, line 711
    def add_foreign_key(from_table, to_table, validate: true, **options)
      if foreign_key_exists?(from_table, to_table, **options)
        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}".dup
        message << ", #{options.inspect}" if options.any?

        Utils.say(message)
      else
        # ActiveRecord >= 5.2 supports adding non-validated foreign keys natively
        options = options.dup
        options[:column] ||= "#{to_table.to_s.singularize}_id"
        options[:primary_key] ||= "id"
        options[:name] ||= __foreign_key_name(to_table, options[:column])

        query = <<-SQL.strip_heredoc.dup
          ALTER TABLE #{from_table}
          ADD CONSTRAINT #{options[:name]}
          FOREIGN KEY (#{options[:column]})
          REFERENCES #{to_table} (#{options[:primary_key]})
        SQL
        query << "#{__action_sql('DELETE', options[:on_delete])}\n" if options[:on_delete].present?
        query << "#{__action_sql('UPDATE', options[:on_update])}\n" if options[:on_update].present?
        query << "NOT VALID\n" if !validate

        execute(query.squish)
      end
    end
add_index(table_name, column_name, options = {}) click to toggle source

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

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index

Calls superclass method
# File lib/online_migrations/schema_statements.rb, line 644
def add_index(table_name, column_name, options = {})
  algorithm = options[:algorithm]

  __ensure_not_in_transaction! if algorithm == :concurrently

  column_names = __index_column_names(column_name || options[:column])

  index_name = options[:name]
  index_name ||= index_name(table_name, column_names)

  if index_exists?(table_name, column_name, **options)
    schema = __schema_for_table(table_name)

    if __index_valid?(index_name, schema: schema)
      Utils.say("Index 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}")
      return
    else
      Utils.say("Recreating invalid index: table_name: #{table_name}, column_name: #{column_name}")
      remove_index(table_name, column_name, name: index_name, algorithm: algorithm)
    end
  end

  disable_statement_timeout do
    # "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(table_name, column_name, **options.merge(name: index_name))
  end
end
add_not_null_constraint(table_name, column_name, name: nil, validate: true) click to toggle source

Adds a NOT NULL constraint to the column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@param validate [Boolean] whether or not the constraint should be validated

@return [void]

@example

add_not_null_constraint(:users, :email, validate: false)
# File lib/online_migrations/schema_statements.rb, line 447
def add_not_null_constraint(table_name, column_name, name: nil, validate: true)
  if __column_not_nullable?(table_name, column_name) ||
     __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 = "#{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) click to toggle source

Adds a reference to the table with minimal locking

ActiveRecord 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.

@param table_name [String, Symbol] table name @param ref_name [String, Symbol] new column name @param options [Hash] look at

https://edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_reference for available options

@return [void]

@example

add_reference_concurrently(:projects, :user)

@note This method should not be run within a transaction

# File lib/online_migrations/schema_statements.rb, line 602
def add_reference_concurrently(table_name, ref_name, **options)
  __ensure_not_in_transaction!

  column_name = "#{ref_name}_id"
  unless column_exists?(table_name, column_name)
    type = options[:type] || (Utils.ar_version >= 5.1 ? :bigint : :integer)
    allow_null = options.fetch(:null, true)
    add_column(table_name, column_name, type, null: allow_null)
  end

  # Always added by default in 5.0+
  index = options.fetch(:index) { Utils.ar_version >= 5.0 }

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

    add_index(table_name, index_columns, **index.merge(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.merge(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) click to toggle source

Adds a limit constraint to the text column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@param validate [Boolean] whether or not the constraint should be validated

@return [void]

@example

add_text_limit_constraint(:users, :bio, 255)

@note This helper must be used only with text columns

# File lib/online_migrations/schema_statements.rb, line 517
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() { || ... } click to toggle source

Disables statement timeout while executing &block

Long-running migrations may take more than the timeout allowed by the database. Disable the session’s statement timeout to ensure migrations don’t get killed prematurely.

Statement timeouts are already disabled in ‘add_index`, `remove_index`, `validate_foreign_key`, and `validate_check_constraint` helpers.

@return [void]

@example

disable_statement_timeout do
  add_index(:users, :email, unique: true, algorithm: :concurrently)
end
# File lib/online_migrations/schema_statements.rb, line 838
def disable_statement_timeout
  prev_value = select_value("SHOW statement_timeout")
  execute("SET statement_timeout TO 0")

  yield
ensure
  execute("SET statement_timeout TO #{quote(prev_value)}")
end
finalize_column_rename(table_name, column_name, new_column_name) click to toggle source

Finishes the process of column rename

@param (see initialize_column_rename) @return [void]

@example

finalize_column_rename(:users, :name, :first_name)
# File lib/online_migrations/schema_statements.rb, line 212
def finalize_column_rename(table_name, column_name, new_column_name)
  transaction do
    execute("DROP VIEW #{table_name}")
    rename_table("#{table_name}_column_rename", table_name)
    rename_column(table_name, column_name, new_column_name)
  end
end
finalize_table_rename(table_name, _new_name = nil) click to toggle source

Finishes the process of table rename

@param table_name [String, Symbol] @param _new_name [String, Symbol] table’s new name. Passing this argument will make

this change reversible in migration

@return [void]

@example

finalize_table_rename(:users, :clients)
# File lib/online_migrations/schema_statements.rb, line 314
def finalize_table_rename(table_name, _new_name = nil)
  execute("DROP VIEW IF EXISTS #{table_name}")
end
foreign_key_exists?(from_table, to_table = nil, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 758
def foreign_key_exists?(from_table, to_table = nil, **options)
  foreign_keys(from_table).any? { |fk| fk.defined_for?(to_table: to_table, **options) }
end
initialize_column_rename(table_name, column_name, new_column_name) click to toggle source

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 ActiveRecord'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. ActiveRecord heavily relies on this data, for example, to initialize new models.

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

@param table_name [String, Symbol] table name @param column_name [String, Symbol] the name of the column to be renamed @param new_column_name [String, Symbol] new new name of the column

@return [void]

@example

initialize_column_rename(:users, :name, :first_name)

@note

Prior to using this method, you need to register the database table so that
it instructs ActiveRecord 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[table_name] = { 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
# File lib/online_migrations/schema_statements.rb, line 175
def initialize_column_rename(table_name, column_name, new_column_name)
  tmp_table = "#{table_name}_column_rename"

  transaction do
    rename_table(table_name, tmp_table)
    execute("CREATE VIEW #{table_name} AS SELECT *, #{column_name} AS #{new_column_name} FROM #{tmp_table}")
  end
end
initialize_table_rename(table_name, new_name) click to toggle source

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 ActiveRecord'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. ActiveRecord heavily relies on this data, for example, to initialize new models.

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

@param table_name [String, Symbol] @param new_name [String, Symbol] table’s new name

@return [void]

@example

initialize_table_rename(:clients, :users)

@note

Prior to using this method, you need to register the database table so that
it instructs ActiveRecord 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
# File lib/online_migrations/schema_statements.rb, line 282
def initialize_table_rename(table_name, new_name)
  transaction do
    rename_table(table_name, new_name)
    execute("CREATE VIEW #{table_name} AS SELECT * FROM #{new_name}")
  end
end
remove_check_constraint(table_name, expression = nil, **options) click to toggle source

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_check_constraint @note This method was added in ActiveRecord 6.1

# File lib/online_migrations/schema_statements.rb, line 804
def remove_check_constraint(table_name, expression = nil, **options)
  constraint_name = __check_constraint_name!(table_name, expression: expression, **options)
  execute("ALTER TABLE #{table_name} DROP CONSTRAINT #{constraint_name}")
end
remove_index(table_name, column_name = nil, **options) click to toggle source

Extends default method to be idempotent and accept ‘:algorithm` option for ActiveRecord <= 4.2.

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-remove_index

Calls superclass method
# File lib/online_migrations/schema_statements.rb, line 679
def remove_index(table_name, column_name = nil, **options)
  algorithm = options[:algorithm]

  __ensure_not_in_transaction! if algorithm == :concurrently

  column_names = __index_column_names(column_name || options[:column])
  index_name = options[:name]
  index_name ||= index_name(table_name, column_names)

  if index_exists?(table_name, column_names, **options)
    disable_statement_timeout do
      # "DROP INDEX CONCURRENTLY" requires a "SHARE UPDATE EXCLUSIVE" lock.
      # It only conflicts with constraint validations, other creating/removing indexes,
      # and some "ALTER TABLE"s.

      # ActiveRecord <= 4.2 does not support removing indexes concurrently
      if Utils.ar_version <= 4.2 && algorithm == :concurrently
        execute("DROP INDEX CONCURRENTLY #{quote_table_name(index_name)}")
      else
        super(table_name, **options.merge(column: column_names))
      end
    end
  else
    Utils.say("Index was not removed because it does not exist (this may be due to an aborted migration "\
              "or similar): table_name: #{table_name}, column_name: #{column_names}")
  end
end
remove_not_null_constraint(table_name, column_name, name: nil) click to toggle source

Removes a NOT NULL constraint from the column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@return [void]

@example

remove_not_null_constraint(:users, :email)

@example Explicit name

remove_not_null_constraint(:users, :email, name: "check_users_email_null")
# File lib/online_migrations/schema_statements.rb, line 497
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) click to toggle source

Removes a limit constraint from the text column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@return [void]

@example

remove_text_limit_constraint(:users, :bio)

@example Explicit name

remove_not_null_constraint(:users, :bio, name: "check_users_bio_max_length")
# File lib/online_migrations/schema_statements.rb, line 576
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) click to toggle source

Reverts operations performed by finalize_column_rename

@param (see initialize_column_rename) @return [void]

@example

revert_finalize_column_rename(:users, :name, :first_name)
# File lib/online_migrations/schema_statements.rb, line 228
def revert_finalize_column_rename(table_name, column_name, new_column_name)
  tmp_table = "#{table_name}_column_rename"

  transaction do
    rename_column(table_name, new_column_name, column_name)
    rename_table(table_name, tmp_table)
    execute("CREATE VIEW #{table_name} AS SELECT *, #{column_name} AS #{new_column_name} FROM #{tmp_table}")
  end
end
revert_finalize_table_rename(table_name, new_name) click to toggle source

Reverts operations performed by finalize_table_rename

@param table_name [String, Symbol] @param new_name [String, Symbol] table’s new name @return [void]

@example

revert_finalize_table_rename(:users, :clients)
# File lib/online_migrations/schema_statements.rb, line 327
def revert_finalize_table_rename(table_name, new_name)
  execute("CREATE VIEW #{table_name} AS SELECT * FROM #{new_name}")
end
revert_initialize_column_rename(table_name, _column_name = nil, _new_column_name = nil) click to toggle source

Reverts operations performed by initialize_column_rename

@param table_name [String, Symbol] table name @param _column_name [String, Symbol] the name of the column to be renamed.

Passing this argument will make this change reversible in migration

@param _new_column_name [String, Symbol] new new name of the column.

Passing this argument will make this change reversible in migration

@return [void]

@example

revert_initialize_column_rename(:users, :name, :first_name)
# File lib/online_migrations/schema_statements.rb, line 197
def revert_initialize_column_rename(table_name, _column_name = nil, _new_column_name = nil)
  transaction do
    execute("DROP VIEW #{table_name}")
    rename_table("#{table_name}_column_rename", table_name)
  end
end
revert_initialize_table_rename(table_name, new_name) click to toggle source

Reverts operations performed by initialize_table_rename

@param (see initialize_table_rename) @return [void]

@example

revert_initialize_table_rename(:clients, :users)
# File lib/online_migrations/schema_statements.rb, line 297
def revert_initialize_table_rename(table_name, new_name)
  transaction do
    execute("DROP VIEW IF EXISTS #{table_name}")
    rename_table(new_name, table_name)
  end
end
swap_column_names(table_name, column1, column2) click to toggle source

Swaps two column names in a table

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

@param table_name [String, Symbol] @param column1 [String, Symbol] @param column2 [String, Symbol] @return [void]

@example

swap_column_names(:files, :size_for_type_change, :size)
# File lib/online_migrations/schema_statements.rb, line 344
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, &block) click to toggle source

Updates the value of a column in batches.

@param table_name [String, Symbol] @param column_name [String, Symbol] @param value value for the column. It is typically a literal. To perform a computed

update, an Arel literal can be used instead

@option options [Integer] :batch_size (1000) size of the batch @option options [String, Symbol] :batch_column_name (primary key) option is for tables without primary key, in this

case another unique integer column can be used. Example: `:user_id`

@option options [Proc, Boolean] :progress (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 "." }`

@option options [Integer] :pause_ms (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

@yield [relation] a block to be called to add extra conditions to the queries being executed @yieldparam relation [ActiveRecord::Relation] an instance of ‘ActiveRecord::Relation`

to add extra conditions to

@return [void]

@example

update_column_in_batches(:users, :admin, false)

@example With extra conditions

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

@example From other column

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

@example 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

@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 extra large tables (100s of millions of records)

you may consider using `backfill_column_in_background` or `copy_column_in_background`.
# File lib/online_migrations/schema_statements.rb, line 54
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) { |batch_relation| ... } click to toggle source

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.

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

@see update_column_in_batches

# File lib/online_migrations/schema_statements.rb, line 68
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? { |e| e.is_a?(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, self)

  conditions = columns_and_values.map do |(column_name, value)|
    value = Arel.sql(value.call.to_s) if value.is_a?(Proc)
    arel_column = model.arel_table[column_name]
    arel_column.not_eq(value).or(arel_column.eq(nil))
  end

  batch_relation = model.where(conditions.inject(:and))
  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 =
      if Utils.ar_version <= 5.2
        columns_and_values.map do |(column_name, value)|
          rhs =
            # ActiveRecord <= 5.2 can't quote these - we need to handle these cases manually
            case value
            when Arel::Attributes::Attribute
              quote_column_name(value.name)
            when Arel::Nodes::SqlLiteral
              value
            when Arel::Nodes::NamedFunction
              "#{value.name}(#{quote_column_name(value.expressions.first.name)})"
            when Proc
              value.call
            else
              quote(value)
            end

          "#{quote_column_name(column_name)} = #{rhs}"
        end.join(", ")
      else
        columns_and_values.map do |(column, value)|
          value = Arel.sql(value.call.to_s) if value.is_a?(Proc)
          [column, value]
        end.to_h
      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) click to toggle source

Extends default method with disabled statement timeout while validation is run

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements.html#method-i-validate_check_constraint @note This method was added in ActiveRecord 6.1

# File lib/online_migrations/schema_statements.rb, line 786
def validate_check_constraint(table_name, **options)
  constraint_name = __check_constraint_name!(table_name, **options)

  # Skip costly operation if already validated.
  return if __constraint_validated?(table_name, constraint_name, type: :check)

  disable_statement_timeout do
    # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with other validations, creating/removing indexes,
    # and some other "ALTER TABLE"s.
    execute("ALTER TABLE #{table_name} VALIDATE CONSTRAINT #{constraint_name}")
  end
end
validate_foreign_key(from_table, to_table = nil, **options) click to toggle source

Extends default method with disabled statement timeout while validation is run

@see edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/PostgreSQL/SchemaStatements.html#method-i-validate_foreign_key @note This method was added in ActiveRecord 5.2

# File lib/online_migrations/schema_statements.rb, line 744
def validate_foreign_key(from_table, to_table = nil, **options)
  fk_name_to_validate = __foreign_key_for!(from_table, to_table: to_table, **options).name

  # Skip costly operation if already validated.
  return if __constraint_validated?(from_table, fk_name_to_validate, type: :foreign_key)

  disable_statement_timeout do
    # "VALIDATE CONSTRAINT" requires a "SHARE UPDATE EXCLUSIVE" lock.
    # It only conflicts with other validations, creating/removing indexes,
    # and some other "ALTER TABLE"s.
    execute("ALTER TABLE #{from_table} VALIDATE CONSTRAINT #{fk_name_to_validate}")
  end
end
validate_not_null_constraint(table_name, column_name, name: nil) click to toggle source

Validates a NOT NULL constraint on the column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@return [void]

@example

validate_not_null_constraint(:users, :email)

@example Explicit name

validate_not_null_constraint(:users, :email, name: "check_users_email_null")
# File lib/online_migrations/schema_statements.rb, line 477
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) click to toggle source

Validates a limit constraint on the text column

@param table_name [String, Symbol] @param column_name [String, Symbol] @param name [String, Symbol] the constraint name.

Defaults to `chk_rails_<identifier>`

@return [void]

@example

validate_text_limit_constraint(:users, :bio)

@example Explicit name

validate_text_limit_constraint(:users, :bio, name: "check_users_bio_max_length")
# File lib/online_migrations/schema_statements.rb, line 556
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
views() click to toggle source

@private

# File lib/online_migrations/schema_statements.rb, line 812
      def views
        select_values(<<-SQL, "SCHEMA")
          SELECT c.relname
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE c.relkind IN ('v','m') -- (v)iew, (m)aterialized view
          AND n.nspname = ANY (current_schemas(false))
        SQL
      end
with_lock_retries(&block) click to toggle source

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

# File lib/online_migrations/schema_statements.rb, line 851
def with_lock_retries(&block)
  __ensure_not_in_transaction!

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

Private Instance Methods

__action_sql(action, dependency) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 936
      def __action_sql(action, dependency)
        case dependency
        when :nullify then "ON #{action} SET NULL"
        when :cascade  then "ON #{action} CASCADE"
        when :restrict then "ON #{action} RESTRICT"
        else
          raise ArgumentError, <<-MSG.strip_heredoc
            '#{dependency}' is not supported for :on_update or :on_delete.
            Supported values are: :nullify, :cascade, :restrict
          MSG
        end
      end
__check_constraint_exists?(table_name, constraint_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 1026
      def __check_constraint_exists?(table_name, constraint_name)
        schema = __schema_for_table(table_name)

        check_sql = <<-SQL.strip_heredoc
          SELECT COUNT(*)
          FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_class cl
              ON cl.oid = con.conrelid
            INNER JOIN pg_catalog.pg_namespace nsp
              ON nsp.oid = con.connamespace
          WHERE con.contype = 'c'
            AND con.conname = #{quote(constraint_name)}
            AND cl.relname = #{quote(table_name)}
            AND nsp.nspname = #{schema}
        SQL

        select_value(check_sql).to_i > 0
      end
__check_constraint_name(table_name, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 1016
def __check_constraint_name(table_name, **options)
  options.fetch(:name) do
    expression = options.fetch(:expression)
    identifier = "#{table_name}_#{expression}_chk"
    hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10)

    "chk_rails_#{hashed_identifier}"
  end
end
__check_constraint_name!(table_name, expression: nil, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 1006
def __check_constraint_name!(table_name, expression: nil, **options)
  constraint_name = __check_constraint_name(table_name, expression: expression, **options)

  if __check_constraint_exists?(table_name, constraint_name)
    constraint_name
  else
    raise(ArgumentError, "Table '#{table_name}' has no check constraint for #{expression || options}")
  end
end
__column_for(table_name, column_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 929
def __column_for(table_name, column_name)
  column_name = column_name.to_s

  columns(table_name).find { |c| c.name == column_name } ||
    raise("No such column: #{table_name}.#{column_name}")
end
__column_not_nullable?(table_name, column_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 873
      def __column_not_nullable?(table_name, column_name)
        schema = __schema_for_table(table_name)

        query = <<-SQL.strip_heredoc
          SELECT is_nullable
          FROM information_schema.columns
          WHERE table_schema = #{schema}
            AND table_name = #{quote(table_name)}
            AND column_name = #{quote(column_name)}
        SQL

        select_value(query) == "NO"
      end
__constraint_validated?(table_name, name, type:) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 988
      def __constraint_validated?(table_name, name, type:)
        schema = __schema_for_table(table_name)
        contype = type == :check ? "c" : "f"

        validated = select_value(<<-SQL.strip_heredoc)
          SELECT convalidated
          FROM pg_catalog.pg_constraint con
            INNER JOIN pg_catalog.pg_namespace nsp
              ON nsp.oid = con.connamespace
          WHERE con.conrelid = #{quote(table_name)}::regclass
            AND con.conname = #{quote(name)}
            AND con.contype = '#{contype}'
            AND nsp.nspname = #{schema}
        SQL

        Utils.to_bool(validated)
      end
__copy_foreign_key(fk, to_column, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 949
def __copy_foreign_key(fk, to_column, **options)
  fkey_options = {
    column: to_column,
    primary_key: options[:primary_key] || fk.primary_key,
    on_delete: fk.on_delete,
    on_update: fk.on_update,
    validate: false,
  }
  fkey_options[:name] = options[:name] if options[:name]

  add_foreign_key(
    fk.from_table,
    fk.to_table,
    **fkey_options
  )

  if !fk.respond_to?(:validated?) || fk.validated?
    validate_foreign_key(fk.from_table, fk.to_table, column: to_column, **options)
  end
end
__ensure_not_in_transaction!(method_name = caller[0]) click to toggle source

Private methods are prefixed with ‘__` to avoid clashes with existing or future ActiveRecord methods

# File lib/online_migrations/schema_statements.rb, line 862
      def __ensure_not_in_transaction!(method_name = caller[0])
        if transaction_open?
          raise <<-MSG.strip_heredoc
            `#{method_name}` cannot run inside a transaction block.

            You can remove transaction block by calling `disable_ddl_transaction!` in the body of
            your migration class.
          MSG
        end
      end
__foreign_key_for!(from_table, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 983
def __foreign_key_for!(from_table, **options)
  foreign_key_for(from_table, **options) ||
    raise(ArgumentError, "Table '#{from_table}' has no foreign key for #{options[:to_table] || options}")
end
__foreign_key_name(table_name, column_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 970
def __foreign_key_name(table_name, column_name)
  identifier = "#{table_name}_#{column_name}_fk"
  hashed_identifier = Digest::SHA256.hexdigest(identifier).first(10)

  "fk_rails_#{hashed_identifier}"
end
__index_column_names(column_names) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 905
def __index_column_names(column_names)
  if column_names.is_a?(String) && /\W/.match(column_names)
    column_names
  else
    Array(column_names)
  end
end
__index_valid?(index_name, schema:) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 913
      def __index_valid?(index_name, schema:)
        # ActiveRecord <= 4.2 returns a string, instead of automatically casting to boolean
        valid = select_value <<-SQL.strip_heredoc
          SELECT indisvalid
          FROM pg_index i
          JOIN pg_class c
            ON i.indexrelid = c.oid
          JOIN pg_namespace n
            ON c.relnamespace = n.oid
          WHERE n.nspname = #{schema}
            AND c.relname = #{quote(index_name)}
        SQL

        Utils.to_bool(valid)
      end
__not_null_constraint_exists?(table_name, column_name, name: nil) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 887
def __not_null_constraint_exists?(table_name, column_name, name: nil)
  name ||= __not_null_constraint_name(table_name, column_name)
  __check_constraint_exists?(table_name, name)
end
__not_null_constraint_name(table_name, column_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 892
def __not_null_constraint_name(table_name, column_name)
  __check_constraint_name(table_name, expression: "#{column_name}_not_null")
end
__schema_for_table(table_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 1045
def __schema_for_table(table_name)
  _, schema = table_name.to_s.split(".").reverse
  schema ? quote(schema) : "current_schema()"
end
__text_limit_constraint_exists?(table_name, column_name, name: nil) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 900
def __text_limit_constraint_exists?(table_name, column_name, name: nil)
  name ||= __text_limit_constraint_name(table_name, column_name)
  __check_constraint_exists?(table_name, name)
end
__text_limit_constraint_name(table_name, column_name) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 896
def __text_limit_constraint_name(table_name, column_name)
  __check_constraint_name(table_name, expression: "#{column_name}_max_length")
end
foreign_key_for(from_table, **options) click to toggle source
# File lib/online_migrations/schema_statements.rb, line 978
def foreign_key_for(from_table, **options)
  foreign_keys(from_table).detect { |fk| fk.defined_for?(**options) }
end