class PgHaMigrations::BlockingDatabaseTransactions

Constants

LongRunningTransaction

Public Class Methods

autovacuum_regex() click to toggle source
# File lib/pg_ha_migrations/blocking_database_transactions.rb, line 23
def self.autovacuum_regex
  "^autovacuum: (?!.*to prevent wraparound)"
end
find_blocking_transactions(minimum_transaction_age = "0 seconds") click to toggle source
# File lib/pg_ha_migrations/blocking_database_transactions.rb, line 27
    def self.find_blocking_transactions(minimum_transaction_age = "0 seconds")
      postgres_version = ActiveRecord::Base.connection.postgresql_version
      pid_column, query_column = if postgres_version < 9_02_00
        ["procpid", "current_query"]
      else
        ["pid", "query"]
      end

      # In some versions of Postgres, walsenders show up here with a non-null xact_start.
      # That's been patched, so hard to test, but we should exclude them anyway.
      # https://www.postgresql.org/message-id/flat/20191209234409.exe7osmyalwkt5j4%40development
      ignore_sqlsender_sql = "psa.backend_type != 'walsender'"

      raw_query = <<~SQL
        SELECT
          psa.datname as database, -- Will only ever be one database
          psa.#{query_column} as current_query,
          psa.state,
          clock_timestamp() - psa.xact_start AS transaction_age,
          array_agg(distinct c.relname) AS tables_with_locks
        FROM pg_stat_activity psa -- Cluster wide
          LEFT JOIN pg_locks l ON (psa.#{pid_column} = l.pid)  -- Cluster wide
          LEFT JOIN pg_class c ON ( -- Database wide
            l.locktype = 'relation'
            AND l.relation = c.oid
            -- Be explicit about this being for a single database -- it's already implicit in
            -- the relations used, and if we don't restrict this we could get incorrect results
            -- with oid collisions from pg_namespace and pg_class.
            AND l.database = (SELECT d.oid FROM pg_database d WHERE d.datname = current_database())
          )
          LEFT JOIN pg_namespace ns ON (c.relnamespace = ns.oid) -- Database wide
        WHERE psa.#{pid_column} != pg_backend_pid()
          AND (
            l.locktype != 'relation'
            OR (
               ns.nspname != 'pg_catalog'
               AND c.relkind = 'r'
            )
          )
          AND psa.xact_start < clock_timestamp() - ?::interval
          AND psa.#{query_column} !~ ?
          #{postgres_version >= 10_00_00 ? "AND #{ignore_sqlsender_sql}" : ""}
        GROUP BY psa.datname, psa.#{query_column}, psa.state, psa.xact_start
      SQL

      query = ActiveRecord::Base.send(:sanitize_sql_for_conditions, [raw_query, minimum_transaction_age, autovacuum_regex])

      ActiveRecord::Base.structs_from_sql(LongRunningTransaction, query)
    end