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