Module: PgHero::Methods::Queries

Included in:
Database
Defined in:
lib/pghero/methods/queries.rb

Instance Method Summary collapse

Instance Method Details

#blocked_queries ⇒ Object

from wiki.postgresql.org/wiki/Lock_Monitoring and big-elephants.com/2013-09/exploring-query-locks-in-postgres/



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
 
# File 'lib/pghero/methods/queries.rb', line 37

def blocked_queries
  query = <<-SQL
    SELECT
      COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
      blockeda.pid AS blocked_pid,
      blockeda.usename AS blocked_user,
      blockeda.query as blocked_query,
      age(now(), blockeda.query_start) AS blocked_duration,
      blockedl.mode as blocked_mode,
      blockinga.pid AS blocking_pid,
      blockinga.usename AS blocking_user,
      blockinga.state AS state_of_blocking_process,
      blockinga.query AS current_or_recent_query_in_blocking_process,
      age(now(), blockinga.query_start) AS blocking_duration,
      blockingl.mode as blocking_mode
    FROM
      pg_catalog.pg_locks blockedl
    LEFT JOIN
      pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
    LEFT JOIN
      pg_catalog.pg_locks blockingl ON blockedl.pid != blockingl.pid AND (
        blockingl.transactionid = blockedl.transactionid
        OR (blockingl.relation = blockedl.relation AND blockingl.locktype = blockedl.locktype)
      )
    LEFT JOIN
      pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid
    WHERE
      NOT blockedl.granted
      AND blockeda.query <> '<insufficient privilege>'
      AND blockeda.datname = current_database()
    ORDER BY
      blocked_duration DESC
  SQL

  select_all(query, query_columns: [:blocked_query, :current_or_recent_query_in_blocking_process])
end

#long_running_queries ⇒ Object 



31
32
33
 
# File 'lib/pghero/methods/queries.rb', line 31

def long_running_queries
  running_queries(min_duration: long_running_query_sec)
end

#running_queries(min_duration: nil, all: false) ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
# File 'lib/pghero/methods/queries.rb', line 4

def running_queries(min_duration: nil, all: false)
  query = <<-SQL
    SELECT
      pid,
      state,
      application_name AS source,
      age(NOW(), COALESCE(query_start, xact_start)) AS duration,
      #{server_version_num >= 90600 ? "(wait_event IS NOT NULL) AS waiting" : "waiting"},
      query,
      COALESCE(query_start, xact_start) AS started_at,
      EXTRACT(EPOCH FROM NOW() - COALESCE(query_start, xact_start)) * 1000.0 AS duration_ms,
      usename AS user
    FROM
      pg_stat_activity
    WHERE
      state <> 'idle'
      AND pid <> pg_backend_pid()
      AND datname = current_database()
      #{min_duration ? "AND NOW() - COALESCE(query_start, xact_start) > interval '#{min_duration.to_i} seconds'" : nil}
      #{all ? nil : "AND query <> '<insufficient privilege>'"}
    ORDER BY
      COALESCE(query_start, xact_start) DESC
  SQL

  select_all(query, query_columns: [:query])
end