1-- Get blocking queries >= 9.2
2SELECT
3      pid,
4      application_name,
5      datname AS database,
6      usename AS user,
7      client,
8      relation,
9      mode,
10      locktype AS type,
11      duration,
12      state,
13      query,
14      waiting as wait
15  FROM
16      (
17      SELECT
18            blocking.pid,
19            pg_stat_activity.application_name,
20            pg_stat_activity.query,
21            blocking.mode,
22            pg_stat_activity.datname,
23            pg_stat_activity.usename,
24            CASE WHEN pg_stat_activity.client_addr IS NULL
25                THEN 'local'
26                ELSE pg_stat_activity.client_addr::TEXT
27            END AS client,
28            blocking.locktype,
29            EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
30            pg_stat_activity.state as state,
31            blocking.relation::regclass AS relation,
32            pg_stat_activity.waiting
33        FROM
34            pg_locks AS blocking
35            JOIN (
36                SELECT
37                      transactionid
38                  FROM
39                      pg_locks
40                 WHERE
41                      NOT granted
42            ) AS blocked ON (blocking.transactionid = blocked.transactionid)
43            JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.pid)
44       WHERE
45            blocking.granted
46        AND CASE WHEN %(min_duration)s = 0
47                THEN true
48                ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
49            END
50      UNION ALL
51      SELECT
52            blocking.pid,
53            pg_stat_activity.application_name,
54            pg_stat_activity.query,
55            blocking.mode,
56            pg_stat_activity.datname,
57            pg_stat_activity.usename,
58            CASE WHEN pg_stat_activity.client_addr IS NULL
59                THEN 'local'
60                ELSE pg_stat_activity.client_addr::TEXT
61            END AS client,
62            blocking.locktype,
63            EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
64            pg_stat_activity.state as state,
65            blocking.relation::regclass AS relation,
66            pg_stat_activity.waiting
67        FROM
68            pg_locks AS blocking
69            JOIN (
70                SELECT
71                      virtualxid
72                  FROM
73                      pg_locks
74                 WHERE
75                      NOT granted
76            ) AS blocked ON (blocking.virtualxid = blocked.virtualxid)
77            JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.pid)
78       WHERE
79            blocking.granted
80        AND CASE WHEN %(min_duration)s = 0
81                THEN true
82                ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
83            END
84      UNION ALL
85      SELECT
86            blocking.pid,
87            pg_stat_activity.application_name,
88            pg_stat_activity.query,
89            blocking.mode,
90            pg_stat_activity.datname,
91            pg_stat_activity.usename,
92            CASE WHEN pg_stat_activity.client_addr IS NULL
93                THEN 'local'
94                ELSE pg_stat_activity.client_addr::TEXT
95            END AS client,
96            blocking.locktype,
97            EXTRACT(epoch FROM (NOW() - pg_stat_activity.{duration_column})) AS duration,
98            pg_stat_activity.state as state,
99            blocking.relation::regclass AS relation,
100            pg_stat_activity.waiting
101        FROM
102            pg_locks AS blocking
103            JOIN (
104                SELECT
105                      database,
106                      relation,
107                      mode
108                  FROM
109                      pg_locks
110                 WHERE
111                      NOT granted
112                  AND relation IS NOT NULL
113            ) AS blocked ON (blocking.database = blocked.database AND blocking.relation = blocked.relation)
114            JOIN pg_stat_activity ON (blocking.pid = pg_stat_activity.pid)
115       WHERE
116            blocking.granted
117        AND CASE WHEN %(min_duration)s = 0
118                THEN true
119                ELSE extract(epoch from now() - {duration_column}) > %(min_duration)s
120            END
121      ) AS sq
122GROUP BY
123      pid,
124      application_name,
125      query,
126      mode,
127      locktype,
128      duration,
129      datname,
130      usename,
131      client,
132      state,
133      relation,
134      wait
135ORDER BY
136      duration DESC;
137