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