Class: PgHaMigrations::BlockingDatabaseTransactions

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_ha_migrations/blocking_database_transactions.rb

Defined Under Namespace

Classes: LongRunningTransaction

Class Method Summary collapse

Class Method Details

.autovacuum_regex ⇒ Object 



28
29
30
 
# File 'lib/pg_ha_migrations/blocking_database_transactions.rb', line 28

def self.autovacuum_regex
  "^autovacuum: (?!.*to prevent wraparound)"
end

.find_blocking_transactions(minimum_transaction_age = "0 seconds") ⇒ Object 



32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
# File 'lib/pg_ha_migrations/blocking_database_transactions.rb', line 32

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 array[c.relname, ns.nspname, l.mode]) 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
      )
      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 IN ('r', 'p') -- 'r' is a standard table; 'p' is a partition parent
        )
      )
      AND psa.xact_start < clock_timestamp() - ?::interval
      AND psa.#{query_column} !~ ?
      -- 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 psa.datname = current_database()
      #{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