ruby-on-rails - 如何为单个迁移设置 statement_timeout？

Question

我想设置 postgres statement_timeout对于个人迁移。我似乎无法做到这一点。这是我的实验:

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

我怎样才能做到这一点？

Answer 1

我假设您的问题是关于设置 statement_timeout用于迁移中的单个语句(不适用于单个迁移)。

您可以通过使用 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

请注意，您可以使用 SET LOCAL如果你不打电话 disable_ddl_transaction! .两者结合不起作用，因为 SET LOCAL在事务块之外没有任何影响。它记录:
WARNING: SET LOCAL can only be used in transaction blocks
PS:您的电话是 disable_ddl_transaction!会提出 NoMethodError .应该在 def change之前定义。