Module: PgHaMigrations::SafeStatements

Defined in:
lib/pg_ha_migrations/safe_statements.rb

Instance Method Summary collapse

Instance Method Details

#_check_postgres_adapter! ⇒ Object

Raises:



231
232
233
234
235
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 231

def _check_postgres_adapter!
  expected_adapter = "PostgreSQL"
  actual_adapter = ActiveRecord::Base.connection.adapter_name
  raise PgHaMigrations::UnsupportedAdapter, "This gem only works with the #{expected_adapter} adapter, found #{actual_adapter} instead" unless actual_adapter == expected_adapter
end

#_per_migration_caller ⇒ Object 



227
228
229
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 227

def _per_migration_caller
  @_per_migration_caller ||= Kernel.caller
end

#_type_is_enum(type) ⇒ Object 



237
238
239
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 237

def _type_is_enum(type)
  ActiveRecord::Base.connection.select_values("SELECT typname FROM pg_type JOIN pg_enum ON pg_type.oid = pg_enum.enumtypid").include?(type.to_s)
end

#adjust_lock_timeout(timeout_seconds = PgHaMigrations::LOCK_TIMEOUT_SECONDS, &block) ⇒ Object 



300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 300

def adjust_lock_timeout(timeout_seconds = PgHaMigrations::LOCK_TIMEOUT_SECONDS, &block)
  _check_postgres_adapter!
  original_timeout = ActiveRecord::Base.value_from_sql("SHOW lock_timeout").sub(/s\Z/, '').to_i * 1000
  begin
    connection.execute("SET lock_timeout = #{PG::Connection.escape_string((timeout_seconds * 1000).to_s)};")
    block.call
  ensure
    begin
      connection.execute("SET lock_timeout = #{original_timeout};")
    rescue ActiveRecord::StatementInvalid => e
      if e.message =~ /PG::InFailedSqlTransaction/
        # If we're in a failed transaction the `SET lock_timeout` will be rolled back,
        # so we don't need to worry about cleaning up, and we can't execute SQL anyway.
      else
        raise e
      end
    end
  end
end

#adjust_statement_timeout(timeout_seconds, &block) ⇒ Object 



320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 320

def adjust_statement_timeout(timeout_seconds, &block)
  _check_postgres_adapter!
  original_timeout = ActiveRecord::Base.value_from_sql("SHOW statement_timeout").sub(/s\Z/, '').to_i * 1000
  begin
    connection.execute("SET statement_timeout = #{PG::Connection.escape_string((timeout_seconds * 1000).to_s)};")
    block.call
  ensure
    begin
      connection.execute("SET statement_timeout = #{original_timeout};")
    rescue ActiveRecord::StatementInvalid => e
      if e.message =~ /PG::InFailedSqlTransaction/
        # If we're in a failed transaction the `SET lock_timeout` will be rolled back,
        # so we don't need to worry about cleaning up, and we can't execute SQL anyway.
      else
        raise e
      end
    end
  end
end

#exec_migration(conn, direction) ⇒ Object 



249
250
251
252
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 249

def exec_migration(conn, direction)
  _check_postgres_adapter!
  super(conn, direction)
end

#migrate(direction) ⇒ Object 



241
242
243
244
245
246
247
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 241

def migrate(direction)
  if respond_to?(:change)
    raise PgHaMigrations::UnsupportedMigrationError, "Tracking changes for automated rollback is not supported; use explicit #up instead."
  end

  super(direction)
end

#safe_add_column(table, column, type, options = {}) ⇒ Object 



40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 40

def safe_add_column(table, column, type, options = {})
  # Note: we don't believe we need to consider the odd case where
  # `:default => nil` or `:default => -> { null }` (or similar) is
  # passed because:
  # - It's OK to exclude that case with an "unnecessary" `raise`
  #   below as it doesn't make semantic sense anyway.
  # - If `:null => false` is also passed we are assuming Postgres's
  #   seq scan of the table (to verify the NOT NULL constraint) will
  #   short-circuit (though we have not confirmed that).
  if options.has_key?(:default)
    if ActiveRecord::Base.connection.postgresql_version < 11_00_00
      raise PgHaMigrations::UnsafeMigrationError.new(":default is NOT SAFE! Use safe_change_column_default afterwards then backfill the data to prevent locking the table")
    elsif options[:default].is_a?(Proc) || (options[:default].is_a?(String) && !([:string, :text, :binary].include?(type.to_sym) || _type_is_enum(type)))
      raise PgHaMigrations::UnsafeMigrationError.new(":default is not safe if the default value is volatile. Use safe_change_column_default afterwards then backfill the data to prevent locking the table")
    end
  elsif options[:null] == false
    raise PgHaMigrations::UnsafeMigrationError.new(":null => false is NOT SAFE if the table has data! If you _really_ want to do this, use unsafe_make_column_not_nullable")
  end

  unless options.has_key?(:default)
    self.safe_added_columns_without_default_value << [table.to_s, column.to_s]
  end

  unsafe_add_column(table, column, type, options)
end

#safe_add_concurrent_index(table, columns, options = {}) ⇒ Object 



147
148
149
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 147

def safe_add_concurrent_index(table, columns, options={})
  unsafe_add_index(table, columns, options.merge(:algorithm => :concurrently))
end

#safe_add_enum_value(name, value) ⇒ Object 



28
29
30
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 28

def safe_add_enum_value(name, value)
  unsafe_execute("ALTER TYPE #{PG::Connection.quote_ident(name.to_s)} ADD VALUE '#{PG::Connection.escape_string(value)}'")
end

#safe_add_unvalidated_check_constraint(table, expression, name:) ⇒ Object 



167
168
169
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 167

def safe_add_unvalidated_check_constraint(table, expression, name:)
  unsafe_add_check_constraint(table, expression, name: name, validate: false)
end

#safe_added_columns_without_default_value ⇒ Object 



2
3
4
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 2

def safe_added_columns_without_default_value
  @safe_added_columns_without_default_value ||= []
end

#safe_change_column_default(table_name, column_name, default_value) ⇒ Object 



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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 72

def safe_change_column_default(table_name, column_name, default_value)
  if PgHaMigrations.config.prefer_single_step_column_addition_with_default &&
      ActiveRecord::Base.connection.postgresql_version >= 11_00_00 &&
      self.safe_added_columns_without_default_value.include?([table_name.to_s, column_name.to_s])
    raise PgHaMigrations::BestPracticeError, "On Postgres 11+ it's safe to set a constant default value when adding a new column; please set the default value as part of the column addition"
  end

  column = connection.send(:column_for, table_name, column_name)

  # In 5.2 we have an edge whereby passing in a string literal with an expression
  # results in confusing behavior because instead of being executed in the database
  # that expression is turned into a Ruby nil before being sent to the database layer;
  # this seems to be an expected side effect of a change that was targeted at a use
  # case unrelated to migrations: https://github.com/rails/rails/commit/7b2dfdeab6e4ef096e4dc1fe313056f08ccf7dc5
  #
  # On the other hand, the behavior in 5.1 is also confusing because it quotes the
  # expression (instead of maintaining the string as-is), which results in Postgres
  # evaluating the expression once when executing the DDL and setting the default to
  # the constant result of that evaluation rather than setting the default to the
  # expression itself.
  #
  # Therefore we want to disallow passing in an expression directly as a string and
  # require the use of a Proc instead with specific quoting rules to determine exact
  # behavior. It's fairly difficult (without relying on something like the PgQuery gem
  # which requires native extensions built with the Postgres dev packages installed)
  # to determine if a string literal represent an expression or just a constant. So
  # instead of trying to parse the expression, we employ a set of heuristics:
  # - If the column is text-like or binary, then we can allow anything in the default
  #   value since a Ruby string there will always coerce directly to the equivalent
  #   text/binary value rather than being interpreted as a DDL-time expression.
  # - Custom enum types are a special case: they also are treated like strings by
  #   Rails, so we want to allow those as-is.
  # - Otherwise, disallow any Ruby string values and instead require the Ruby object
  #   type that maps to the column type.
  #
  # These heuristics eliminate (virtually?) all ambiguity. In theory there's a
  # possiblity that some custom object could be coerced-Ruby side into a SQL string
  # that does something weird here, but that seems an odd enough case that we can
  # safely ignore it.
  if default_value.present? &&
     !default_value.is_a?(Proc) &&
     (
       connection.quote_default_expression(default_value, column) == "NULL" ||
       (
         ![:string, :text, :binary, :enum].include?(column.sql_type_metadata.type) &&
         default_value.is_a?(String)
       )
     )
    raise PgHaMigrations::InvalidMigrationError, <<~ERROR
      Setting a default value to an expression using a string literal is ambiguous.

      If you want the default to be:
      * ...a constant scalar value, use the matching Ruby object type instead of a string if possible (e.g., `DateTime.new(...)`).
      * ...an expression evaluated at runtime for each row, then pass a Proc that returns the expression string (e.g., `-> { "NOW()" }`).
      * ...an expression evaluated at migration time, then pass a Proc that returns a quoted expression string (e.g., `-> { "'NOW()'" }`).
    ERROR
  end

  safely_acquire_lock_for_table(table_name) do
    unsafe_change_column_default(table_name, column_name, default_value)
  end
end

#safe_create_enum_type(name, values = nil) ⇒ Object 



14
15
16
17
18
19
20
21
22
23
24
25
26
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 14

def safe_create_enum_type(name, values=nil)
  case values
  when nil
    raise ArgumentError, "safe_create_enum_type expects a set of values; if you want an enum with no values please pass an empty array"
  when []
    unsafe_execute("CREATE TYPE #{PG::Connection.quote_ident(name.to_s)} AS ENUM ()")
  else
    escaped_values = values.map do |value|
      "'#{PG::Connection.escape_string(value.to_s)}'"
    end
    unsafe_execute("CREATE TYPE #{PG::Connection.quote_ident(name.to_s)} AS ENUM (#{escaped_values.join(',')})")
  end
end

#safe_create_table(table, options = {}, &block) ⇒ Object 



6
7
8
9
10
11
12
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 6

def safe_create_table(table, options={}, &block)
  if options[:force]
    raise PgHaMigrations::UnsafeMigrationError.new(":force is NOT SAFE! Explicitly call unsafe_drop_table first if you want to recreate an existing table")
  end

  unsafe_create_table(table, options, &block)
end

#safe_make_column_nullable(table, column) ⇒ Object 



135
136
137
138
139
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 135

def safe_make_column_nullable(table, column)
  safely_acquire_lock_for_table(table) do
    unsafe_execute "ALTER TABLE #{table} ALTER COLUMN #{column} DROP NOT NULL"
  end
end

#safe_remove_concurrent_index(table, options = {}) ⇒ Object 



151
152
153
154
155
156
157
158
159
160
161
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 151

def safe_remove_concurrent_index(table, options={})
  unless options.is_a?(Hash) && options.key?(:name)
    raise ArgumentError, "Expected safe_remove_concurrent_index to be called with arguments (table_name, :name => ...)"
  end
  unless ActiveRecord::Base.connection.postgresql_version >= 9_06_00
    raise PgHaMigrations::InvalidMigrationError, "Removing an index concurrently is not supported on Postgres 9.1 databases"
  end
  index_size = select_value("SELECT pg_size_pretty(pg_relation_size('#{options[:name]}'))")
  say "Preparing to drop index #{options[:name]} which is #{index_size} on disk..."
  unsafe_remove_index(table, options.merge(:algorithm => :concurrently))
end

#safe_rename_constraint(table, from:, to:) ⇒ Object

Raises:

  • (ArgumentError) 


197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 197

def safe_rename_constraint(table, from:, to:)
  raise ArgumentError, "Expected <from> to be present" unless from.present?
  raise ArgumentError, "Expected <to> to be present" unless to.present?

  quoted_table_name = connection.quote_table_name(table)
  quoted_constraint_from_name = connection.quote_table_name(from)
  quoted_constraint_to_name = connection.quote_table_name(to)
  sql = "ALTER TABLE #{quoted_table_name} RENAME CONSTRAINT #{quoted_constraint_from_name} TO #{quoted_constraint_to_name}"

  safely_acquire_lock_for_table(table) do
    say_with_time "rename_constraint(#{table.inspect}, from: #{from.inspect}, to: #{to.inspect})" do
      connection.execute(sql)
    end
  end
end

#safe_set_maintenance_work_mem_gb(gigabytes) ⇒ Object 



163
164
165
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 163

def safe_set_maintenance_work_mem_gb(gigabytes)
  unsafe_execute("SET maintenance_work_mem = '#{PG::Connection.escape_string(gigabytes.to_s)} GB'")
end

#safe_validate_check_constraint(table, name:) ⇒ Object

Raises:

  • (ArgumentError) 


185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 185

def safe_validate_check_constraint(table, name:)
  raise ArgumentError, "Expected <name> to be present" unless name.present?

  quoted_table_name = connection.quote_table_name(table)
  quoted_constraint_name = connection.quote_table_name(name)
  sql = "ALTER TABLE #{quoted_table_name} VALIDATE CONSTRAINT #{quoted_constraint_name}"

  say_with_time "validate_check_constraint(#{table.inspect}, name: #{name.inspect})" do
    connection.execute(sql)
  end
end

#safely_acquire_lock_for_table(table, &block) ⇒ Object 



254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 254

def safely_acquire_lock_for_table(table, &block)
  _check_postgres_adapter!
  table = table.to_s
  quoted_table_name = connection.quote_table_name(table)

  successfully_acquired_lock = false

  until successfully_acquired_lock
    while (
      blocking_transactions = PgHaMigrations::BlockingDatabaseTransactions.find_blocking_transactions("#{PgHaMigrations::LOCK_TIMEOUT_SECONDS} seconds")
      blocking_transactions.any? { |query| query.tables_with_locks.include?(table) }
    )
      say "Waiting on blocking transactions:"
      blocking_transactions.each do |blocking_transaction|
        say blocking_transaction.description
      end
      sleep(PgHaMigrations::LOCK_TIMEOUT_SECONDS)
    end

    connection.transaction do
      adjust_timeout_method = connection.postgresql_version >= 9_03_00 ? :adjust_lock_timeout : :adjust_statement_timeout
      begin
        method(adjust_timeout_method).call(PgHaMigrations::LOCK_TIMEOUT_SECONDS) do
          connection.execute("LOCK #{quoted_table_name};")
        end
        successfully_acquired_lock = true
      rescue ActiveRecord::StatementInvalid => e
        if e.message =~ /PG::LockNotAvailable.+ lock timeout/ || e.message =~ /PG::QueryCanceled.+ statement timeout/
          sleep_seconds = PgHaMigrations::LOCK_FAILURE_RETRY_DELAY_MULTLIPLIER * PgHaMigrations::LOCK_TIMEOUT_SECONDS
          say "Timed out trying to acquire an exclusive lock on the #{quoted_table_name} table."
          say "Sleeping for #{sleep_seconds}s to allow potentially queued up queries to finish before continuing."
          sleep(sleep_seconds)

          raise ActiveRecord::Rollback
        else
          raise e
        end
      end

      if successfully_acquired_lock
        block.call
      end
    end
  end
end

#unsafe_add_check_constraint(table, expression, name:, validate: true) ⇒ Object

Raises:

  • (ArgumentError) 


171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 171

def unsafe_add_check_constraint(table, expression, name:, validate: true)
  raise ArgumentError, "Expected <name> to be present" unless name.present?

  quoted_table_name = connection.quote_table_name(table)
  quoted_constraint_name = connection.quote_table_name(name)
  sql = "ALTER TABLE #{quoted_table_name} ADD CONSTRAINT #{quoted_constraint_name} CHECK (#{expression}) #{validate ? "" : "NOT VALID"}"

  safely_acquire_lock_for_table(table) do
    say_with_time "add_check_constraint(#{table.inspect}, #{expression.inspect}, name: #{name.inspect}, validate: #{validate.inspect})" do
      connection.execute(sql)
    end
  end
end

#unsafe_add_column(table, column, type, options = {}) ⇒ Object 



66
67
68
69
70
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 66

def unsafe_add_column(table, column, type, options = {})
  safely_acquire_lock_for_table(table) do
    super(table, column, type, options)
  end
end

#unsafe_make_column_not_nullable(table, column, options = {}) ⇒ Object

options arg is only present for backwards compatiblity



141
142
143
144
145
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 141

def unsafe_make_column_not_nullable(table, column, options={}) # options arg is only present for backwards compatiblity
  safely_acquire_lock_for_table(table) do
    unsafe_execute "ALTER TABLE #{table} ALTER COLUMN #{column} SET NOT NULL"
  end
end

#unsafe_remove_constraint(table, name:) ⇒ Object

Raises:

  • (ArgumentError) 


213
214
215
216
217
218
219
220
221
222
223
224
225
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 213

def unsafe_remove_constraint(table, name:)
  raise ArgumentError, "Expected <name> to be present" unless name.present?

  quoted_table_name = connection.quote_table_name(table)
  quoted_constraint_name = connection.quote_table_name(name)
  sql = "ALTER TABLE #{quoted_table_name} DROP CONSTRAINT #{quoted_constraint_name}"

  safely_acquire_lock_for_table(table) do
    say_with_time "remove_constraint(#{table.inspect}, name: #{name.inspect})" do
      connection.execute(sql)
    end
  end
end

#unsafe_rename_enum_value(name, old_value, new_value) ⇒ Object 



32
33
34
35
36
37
38
 
# File 'lib/pg_ha_migrations/safe_statements.rb', line 32

def unsafe_rename_enum_value(name, old_value, new_value)
  if ActiveRecord::Base.connection.postgresql_version < 10_00_00
    raise PgHaMigrations::InvalidMigrationError, "Renaming an enum value is not supported on Postgres databases before version 10"
  end

  unsafe_execute("ALTER TYPE #{PG::Connection.quote_ident(name.to_s)} RENAME VALUE '#{PG::Connection.escape_string(old_value)}' TO '#{PG::Connection.escape_string(new_value)}'")
end