-
Notifications
You must be signed in to change notification settings - Fork 5
/
query_10.sql
60 lines (60 loc) · 2.3 KB
/
query_10.sql
1
2
3
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
31
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
WITH blocks AS (
SELECT
blocking_locks.pid as pid,
blocked_locks.pid as blocked_pid,
blocking_locks.locktype as locktype,
blocking_locks.relation::regclass as relation,
blocking_locks.granted as granted
FROM
pg_catalog.pg_locks blocked_locks
JOIN
pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
WHERE NOT blocked_locks.granted
)
SELECT
procs.pid AS pid,
application_name,
datname,
usename,
backend_type,
CASE WHEN client_port=-1 THEN 'local pipe'
WHEN length(client_hostname)>0 THEN client_hostname||':'||client_port
ELSE textin(inet_out(client_addr))||':'||client_port
END AS client,
date_trunc('second', backend_start) AS backend_start,
CASE WHEN state='active' THEN date_trunc('second', query_start)::text
ELSE ''
END AS query_start,
date_trunc('second', xact_start) AS xact_start,
state,
date_trunc('second', state_change) AS state_change,
blocks.pid AS blockedby,
/* deprecated
null::text AS blocking_locks,*/
blocks.locktype AS locktype,
blocks.relation AS relation,
blocks.granted AS granted,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false
ELSE query_start < now() - '10 seconds'::interval
END AS slowquery
FROM
pg_stat_activity procs
LEFT JOIN blocks
ON blocks.blocked_pid = procs.pid
WHERE
procs.state != 'idle'
OR procs.state IS NULL
ORDER BY
pid