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/



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

def blocked_queries
  query = "    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\n\n  select_all(query, query_columns: [:blocked_query, :current_or_recent_query_in_blocking_process])\nend\n"

#long_running_queries ⇒ Object 



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

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

def running_queries(min_duration: nil, all: false)
  query = "    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      \#{server_version_num >= 100000 ? \"backend_type\" : \"NULL AS backend_type\"}\n    FROM\n      pg_stat_activity\n    WHERE\n      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      \#{all ? nil : \"AND query <> '<insufficient privilege>'\"}\n    ORDER BY\n      COALESCE(query_start, xact_start) DESC\n  SQL\n\n  select_all(query, query_columns: [:query])\nend\n"