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/



57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/pghero/methods/queries.rb', line 57

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

#locks ⇒ Object 



34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
# File 'lib/pghero/methods/queries.rb', line 34

def locks
  select_all "    SELECT DISTINCT ON (pid)\n      pg_stat_activity.pid,\n      pg_stat_activity.query,\n      age(now(), pg_stat_activity.query_start) AS age\n    FROM\n      pg_stat_activity\n    INNER JOIN\n      pg_locks ON pg_locks.pid = pg_stat_activity.pid\n    WHERE\n      pg_stat_activity.query <> '<insufficient privilege>'\n      AND pg_locks.mode = 'ExclusiveLock'\n      AND pg_stat_activity.pid <> pg_backend_pid()\n      AND pg_stat_activity.datname = current_database()\n    ORDER BY\n      pid,\n      query_start\n  SQL\nend\n"

#long_running_queries ⇒ Object 



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

def long_running_queries
  running_queries(min_duration: long_running_query_sec)
end

#running_queries(options = {}) ⇒ 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
 
# File 'lib/pghero/methods/queries.rb', line 4

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