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
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
|
-- Get blocking queries
SELECT
pid,
application_name,
sq.datname AS database,
usename AS user,
client,
relation,
mode,
locktype AS type,
duration,
CASE
WHEN sq.query = '<IDLE> in transaction (aborted)' THEN 'idle in transaction (aborted)'
WHEN sq.query = '<IDLE> in transaction' THEN 'idle in transaction'
WHEN sq.query = '<IDLE>' THEN 'idle'
ELSE 'active'
END AS state,
CASE WHEN sq.query LIKE '<IDLE>%%'
THEN NULL
ELSE sq.query
END AS query,
pg_catalog.pg_encoding_to_char(b.encoding) AS encoding,
waiting AS wait
FROM
(
-- Transaction id lock
SELECT
blocking.pid,
'<unknown>' AS application_name,
pg_stat_activity.current_query AS query,
blocking.mode,
pg_stat_activity.datname,
pg_stat_activity.datid,
pg_stat_activity.usename,
pg_stat_activity.client_addr AS client,
blocking.locktype,
EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
NULL AS state,
blocking.relation::regclass AS relation,
pg_stat_activity.waiting
FROM
pg_locks AS blocking
JOIN pg_locks AS blocked ON (blocking.transactionid = blocked.transactionid AND blocking.locktype = blocked.locktype)
JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.procpid)
WHERE
blocking.granted
AND NOT blocked.granted
AND CASE WHEN {min_duration} = 0
THEN true
ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
END
AND CASE WHEN {dbname_filter} IS NULL THEN true
ELSE datname ~* %(dbname_filter)s
END
UNION ALL
-- VirtualXid Lock
SELECT
blocking.pid,
'<unknown>' AS application_name,
pg_stat_activity.current_query AS query,
blocking.mode,
pg_stat_activity.datname,
pg_stat_activity.datid,
pg_stat_activity.usename,
pg_stat_activity.client_addr AS client,
blocking.locktype,
EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
NULL AS state,
blocking.relation::regclass AS relation,
pg_stat_activity.waiting
FROM
pg_locks AS blocking
JOIN pg_locks AS blocked ON (blocking.virtualxid = blocked.virtualxid AND blocking.locktype = blocked.locktype)
JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.procpid)
WHERE
blocking.granted
AND NOT blocked.granted
AND CASE WHEN {min_duration} = 0
THEN true
ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
END
AND CASE WHEN {dbname_filter} IS NULL THEN true
ELSE datname ~* %(dbname_filter)s
END
UNION ALL
-- Relation or tuple Lock
SELECT
blocking.pid,
'<unknown>' AS application_name,
pg_stat_activity.current_query AS query,
blocking.mode,
pg_stat_activity.datname,
pg_stat_activity.datid,
pg_stat_activity.usename,
pg_stat_activity.client_addr AS client,
blocking.locktype,
EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
NULL AS state,
blocking.relation::regclass AS relation,
pg_stat_activity.waiting
FROM
pg_locks AS blocking
JOIN pg_locks AS blocked ON (blocking.database = blocked.database AND blocking.relation = blocked.relation AND blocking.locktype = blocked.locktype)
JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.procpid)
WHERE
blocking.granted
AND NOT blocked.granted
AND blocked.relation IS NOT NULL
AND CASE WHEN {min_duration} = 0
THEN true
ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
END
AND CASE WHEN {dbname_filter} IS NULL THEN true
ELSE datname ~* %(dbname_filter)s
END
) AS sq
LEFT OUTER JOIN pg_database b ON sq.datid = b.oid
GROUP BY
pid,
application_name,
database,
usename,
client,
relation,
mode,
locktype,
duration,
state,
query,
encoding,
waiting
ORDER BY
duration DESC;
|
