My app rails app has a postgres statement timeout set to 5 seconds in production. This timeout works well for us, helps us catch errors in production, kill bad queries, etc. However, when we are deploying a change we sometimes want to run data migrations that take longer than 5 seconds and often run into the brutal PG::QueryCanceled: ERROR: canceling statement due to statement timeout. Hence, we are looking for a way to increase the statement timeouts to ~10 minutes for our migrations. What is the best way to do this?
Here are the options we went through and why we aren't happy with any of them.
1) Increase the statement timeout in every individual migration you think might be problematic. -> Often times developers forget or have poor estimates of how long this will take. If they mess up we end up with a failed deploy, cherry picks, etc.
2) Use Task.enhance for db:migrate (or other methods) to increase the statement timeout before each task. -> It's easy to increase the statement timeout using ActiveRecord::Base.connection.execute 'SET statement_timeout = 600000' but this will only apply to the existing connection. Turns out when you run rake db:migrate, ActiveRecord will actually open a new db connection which reverts to the existing default.
3) Monkey patch the actual migrate method to set the statement timeout AFTER the connection has been created. -> See code below, it's not pretty and messing around with ActiveRecord internal functions can have consequences we aren't aware of.
# See here for overriding static methods:
# https://stackoverflow.com/questions/9128515/how-to-override-static-class-method-using-module-in-ruby
# We need to override this method because immediately before this the database connection gets reset
# Override when we are running normally
# https://github.com/rails/rails/blob/master/activerecord/lib/active_record/railties/databases.rake
module OverrideNormalMigration
def self.included base
orig_method = base.method(:migrate)
base.define_singleton_method :migrate do |*args|
puts '-- SET statement_timeout = 600000'
ActiveRecord::Base.connection.execute 'SET statement_timeout = 600000' # 10 minutes should suffice, right?
orig_method.call *args
end
end
end
ActiveRecord::Tasks::DatabaseTasks.send(:include, OverrideNormalMigration)
*Note: If you use the data-migrate gem, you will also need to include the following.
# Override when we are running with the data migrate gem (which we do in prod)
# https://github.com/ilyakatz/data-migrate/blob/4db2dba7d53e73cac06aa9bfab136c10aa38367b/tasks/databases.rake#L9
module OverrideDataMigrateMigration
def self.included base
orig_method = base.method(:run)
base.define_singleton_method :run do |*args|
puts '-- SET statement_timeout = 600000'
ActiveRecord::Base.connection.execute 'SET statement_timeout = 600000' # 10 minutes should suffice, right?
orig_method.call *args
end
end
end
DataMigrate::DataMigrator.send(:include, OverrideDataMigrateMigration)
DataMigrate::SchemaMigration.send(:include, OverrideDataMigrateMigration)
