Module: ActiveRecord::PGExtensions::PessimisticMigrations

Defined in:
lib/active_record/pg_extensions/pessimistic_migrations.rb

Overview

Changes several DDL commands to trigger background queries to warm caches prior to executing, in order to reduce the amount of time the actual DDL takes to execute (and thus how long it needs the lock)

Instance Method Summary collapse

Instance Method Details

#add_foreign_key(from_table, to_table, delay_validation: false, if_not_exists: false, **options) ⇒ Object

several improvements:

  • support if_not_exists
  • delay_validation automatically creates the FK as NOT VALID, and then immediately validates it
  • if delay_validation is used, and the index already exists but is NOT VALID, it just re-tries the validation, instead of failing


45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/active_record/pg_extensions/pessimistic_migrations.rb', line 45

def add_foreign_key(from_table, to_table, delay_validation: false, if_not_exists: false, **options)
  # pointless if we're in a transaction
  delay_validation = false if open_transactions.positive?
  options[:validate] = false if delay_validation

  options = foreign_key_options(from_table, to_table, options)

  if if_not_exists || delay_validation
    scope = quoted_scope(options[:name])
    valid = select_value(<<~SQL, "SCHEMA")
      SELECT convalidated FROM pg_constraint INNER JOIN pg_namespace ON pg_namespace.oid=connamespace WHERE conname=#{scope[:name]} AND nspname=#{scope[:schema]}
    SQL
    return if valid == true && if_not_exists
  end

  super(from_table, to_table, **options) unless valid == false
  validate_constraint(from_table, options[:name]) if delay_validation
end

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

will automatically remove a NOT VALID index before trying to add



65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/active_record/pg_extensions/pessimistic_migrations.rb', line 65

def add_index(table_name, column_name, **options)
  # catch a concurrent index add that fails because it already exists, and is invalid
  if options[:algorithm] == :concurrently && options[:if_not_exists]
    column_names = index_column_names(column_name)
    index_name = options[:name].to_s if options.key?(:name)
    index_name ||= index_name(table_name, column_names)

    index = quoted_scope(index_name)
    table = quoted_scope(table_name)
    valid = select_value(<<~SQL, "SCHEMA")
      SELECT indisvalid
      FROM pg_class t
      INNER JOIN pg_index d ON t.oid = d.indrelid
      INNER JOIN pg_class i ON d.indexrelid = i.oid
      WHERE i.relkind = 'i'
        AND i.relname = #{index[:name]}
        AND t.relname = #{table[:name]}
        AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = #{index[:schema]} )
      LIMIT 1
    SQL
    return if valid == true

    remove_index(table_name, name: index_name, algorithm: :concurrently) if valid == false
  end
  super
end

#change_check_constraint(table, from:, to:, if_exists: false, delay_validation: false, **options) ⇒ Object

Replace one check constraint with another

Parameters:

  • table (Symbol) —

    The table name

  • from (String, Hash) —

    The check constraint to be replaced. If a Hash is provided, it must contain an :expression key with the check constraint expression. Other options are merged with options and passed through.

  • to (String, Hash) —

    The new check constraint to be added. If a Hash is provided, it must contain an :expression key with the check constraint expression. Other options are merged with options and passed through.

  • if_exists (true, false) (defaults to: false) —

    If true, the entire operation should be idempotent (only dropping/renaming the old constraint if it exists, only adding the new constraint if it doesn't exist.)

  • delay_validation (true, false) (defaults to: false) —

    If true, the new check constraint will be added as NOT VALID and validated before removing the old check constraint.



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/active_record/pg_extensions/pessimistic_migrations.rb', line 105

def change_check_constraint(table, from:, to:, if_exists: false, delay_validation: false, **options)
  delay_validation = false if open_transactions.positive?
  options[:validate] = false if delay_validation

  from_expression, from_options = split_arg(from, :expression, options)
  to_expression, to_options = split_arg(to, :expression, options)

  new_constraint_name = check_constraint_name(table, expression: to_expression, **to_options)
  # when delaying validation, we don't drop the old constraint until after the new one is validated,
  # so we need to rename it if the names are the same
  if delay_validation
    old_constraint_name = check_constraint_name(table, expression: from_expression, **from_options)
    if old_constraint_name == new_constraint_name
      new_old_constraint_name = temporary_name(old_constraint_name)
      unless check_constraint_exists?(table, name: new_old_constraint_name)
        rename_constraint(table, old_constraint_name, new_old_constraint_name, if_exists:)
      end
      old_constraint_name = new_old_constraint_name
    end
  else
    remove_check_constraint(table, from_expression, if_exists:, **from_options)
  end

  add_check_constraint(table,
                       to_expression,
                       **to_options,
                       if_not_exists: if_exists || delay_validation,
                       validate: !delay_validation)

  return unless delay_validation

  validate_constraint(table, new_constraint_name)
  remove_check_constraint(table, name: old_constraint_name, if_exists: true)
end

#change_column_null(table, column, nullness, default = nil) ⇒ Object

adds a temporary check constraint to reduce locking when changing to NOT NULL, and we're not in a transaction



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
# File 'lib/active_record/pg_extensions/pessimistic_migrations.rb', line 10

def change_column_null(table, column, nullness, default = nil)
  # no point in doing extra work to avoid locking if we're already in a transaction
  return super if nullness != false || open_transactions.positive?
  return if columns(table).find { |c| c.name == column.to_s }&.null == false

  # PG identifiers max out at 63 characters
  temp_constraint_name = "chk_rails_#{table}_#{column}_not_null"[0...63]
  scope = quoted_scope(temp_constraint_name)
  # check for temp constraint
  valid = select_value(<<~SQL, "SCHEMA")
    SELECT convalidated FROM pg_constraint INNER JOIN pg_namespace ON pg_namespace.oid=connamespace WHERE conname=#{scope[:name]} AND nspname=#{scope[:schema]}
  SQL
  if valid.nil?
    add_check_constraint(table,
                         "#{quote_column_name(column)} IS NOT NULL",
                         name: temp_constraint_name,
                         validate: false)
  end
  begin
    validate_constraint(table, temp_constraint_name)
  rescue PG::CheckViolation => e
    raise ActiveRecord::NotNullViolation.new(sql: e.sql, binds: e.binds)
  end

  transaction do
    super
    remove_check_constraint(table, name: temp_constraint_name)
  end
end

#change_index(table, from:, to:, if_exists: false, algorithm: nil, **options) ⇒ Object

Replace one index with another

Parameters:

  • table (Symbol) —

    The table name

  • from (String, Symbol, Array, Hash) —

    The index to be replaced. If a Hash is provided, it must contain a :column key with the indexed column(s). Other options are merged with options and passed through.

  • to (String, Symbol, Array, Hash) —

    The new index to be added. If a Hash is provided, it must contain a :column key with the indexed column(s). Other options are merged with options and passed through.

  • if_exists (true, false) (defaults to: false) —

    If true, the entire operation should be idempotent (only dropping/renaming the old index if it exists, only adding the new index if it doesn't exist.)

  • algorithm (Symbol, nil) (defaults to: nil) —

    If :concurrently, the new index is created concurrently and the old index is only removed after the new one exists.



153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
 
# File 'lib/active_record/pg_extensions/pessimistic_migrations.rb', line 153

def change_index(table, from:, to:, if_exists: false, algorithm: nil, **options)
  algorithm = nil if open_transactions.positive?
  concurrently = algorithm == :concurrently

  from_column, from_options = split_arg(from, :column, options)
  to_column, to_options = split_arg(to, :column, options)

  new_index_name = to_options[:name]&.to_s || index_name(table, column: to_column)
  # when creating concurrently, we don't drop the old index until after the new one is created,
  # so we need to rename it if the names are the same
  if concurrently
    old_index_name = from_options[:name]&.to_s || index_name(table, column: from_column)
    if old_index_name == new_index_name
      new_old_index_name = temporary_name(old_index_name)
      # rename_index has no if_exists option, so guard it: skip if the old index is missing
      # (only relevant when if_exists makes the whole operation idempotent) or the temp name is taken
      old_missing = if_exists && !index_name_exists?(table, old_index_name)
      unless old_missing || index_name_exists?(table, new_old_index_name)
        rename_index(table, old_index_name, new_old_index_name)
      end
      old_index_name = new_old_index_name
    end
  else
    remove_index(table, from_column, if_exists:, **from_options)
  end

  add_index(table,
            to_column,
            **to_options,
            if_not_exists: if_exists || concurrently,
            algorithm:)

  return unless concurrently

  remove_index(table, name: old_index_name, if_exists: true, algorithm: :concurrently)
end