How can I set statement_timeout for an individual migration?

user_168143 0
Viewed 3007

I'd like to set postgres statement_timeout for an individual migration. I can't seem to be able to do this. Here's my experiment:

def change
  execute <<~SQL
    SET LOCAL statement_timeout = 1; -- ms

    -- this does not cause a timeout which is expected, because pg
    -- only applies the timeout to the next protocol message / statement,
    -- and rails sends everthing inside execute in the same statement
    select pg_sleep(1); -- seconds
  SQL

  # if uncommented, this DOES cause a timeout, which is expected
  # execute <<~SQL
  #   select pg_sleep(1); -- seconds
  # SQL

  # this does not cause a timeout, which is unexpected
  remove_column :foos, :bar

  # we do get here, which is unexpected
  raise "we finished"
end

How can I do this?

database-migration postgresql ruby-on-rails
3 Answers

I assume your question is about setting statement_timeout for an individual statement within your migration (not for an individual migration).

You can achieve this by using Postgres’s SET instead of SET LOCAL.

class SomeMigration < ActiveRecord::Migration[6.0]
  # Disables BEGIN/COMMIT around migration. SET LOCAL will have no effect.
  disable_ddl_transaction!

  def change
    # Applies to the current database session, until changed or reset.
    execute <<~SQL
      SET statement_timeout = '1s';
    SQL

    # Will raise `ActiveRecord::QueryCanceled`.
    execute <<~SQL
      SELECT pg_sleep(2);
    SQL

    # Resets (disables) timeout.
    execute <<~SQL
      SET statement_timeout = DEFAULT;
    SQL

    # Will not raise.
    execute <<~SQL
      SELECT pg_sleep(2);
    SQL
  end
end

Note that you could use SET LOCAL if you don’t call disable_ddl_transaction!. Combining both doesn’t work because SET LOCAL outside a transaction block has no effect. It logs:

WARNING: SET LOCAL can only be used in transaction blocks

PS: Your call to disable_ddl_transaction! would raise NoMethodError. It should be before the def change definition.

user_3659690

You could try using the lower level exec_query or go even lower with execute_and_clear or lower still with exec_no_cache.

user_82719390

After turning on log_duration and doing more investigation, I think I am coming to the conclusion that postgres just doesn't consider droping the column to take more than 1ms, even when something like this is logged:

statement: ALTER TABLE "foos" DROP COLUMN "bar"
duration: 1.171 ms
duration: 0.068 ms
duration: 0.328 ms

But with the below recipe,

  1. I am able to get another type of query to fail due to statement timeout
  2. I am able to get drop column to fail due to lock timeout (while holding lock in a psql session)
class TimeoutTest < ActiveRecord::Migration[5.2]

  def change
    execute <<~SQL
      SET statement_timeout = 1; -- ms
      SET lock_timeout = 1; -- ms
    SQL

    # I can't get this to fail due to a statement timeout, but does fail due to lock timeout
    # interestingly, if both timeouts are set, it will be statement timeout which happens first. not
    # sure if this is a pg bug, or if calculating the lock timeout somehow increases the overall statement
    # overhead
    remove_column :foos, :bar

    # With 100k of rows of test data, this does fail the statement timeout
    Foo.unscoped.update_all(bar: 5)
  end
end
user_1681430
Related