http://postgres.cz/index.php?title=Monitor_logu_pro_sb%C4%9Br_dopl%C5%88kov%C3%BDch_informac%C3%AD_ohledn%C4%9B_aktivn%C3%ADch_z%C3%A1mk%C5%AF&feed=atom&action=history
Monitor logu pro sběr doplňkových informací ohledně aktivních zámků - Historie editací
2024-03-29T12:11:18Z
Historie editací této stránky
MediaWiki 1.36.0
http://postgres.cz/index.php?title=Monitor_logu_pro_sb%C4%9Br_dopl%C5%88kov%C3%BDch_informac%C3%AD_ohledn%C4%9B_aktivn%C3%ADch_z%C3%A1mk%C5%AF&diff=512&oldid=prev
imported>Pavel v 13. 10. 2011, 12:09
2011-10-13T12:09:44Z
<p></p>
<p><b>Nová stránka</b></p><div><pre><br />
REGEX='process ([0-9]+) still waiting'<br />
<br />
while read x <br />
do <br />
if [[ $x =~ $REGEX ]]<br />
then<br />
blocked_pid=${BASH_REMATCH[1]}<br />
query1="select kl.pid as blocking_pid<br />
from pg_catalog.pg_locks bl<br />
join pg_catalog.pg_stat_activity a<br />
on bl.pid = a.procpid<br />
join pg_catalog.pg_locks kl<br />
join pg_catalog.pg_stat_activity ka<br />
on kl.pid = ka.procpid<br />
on bl.transactionid = kl.transactionid and bl.pid != kl.pid<br />
where bl.pid = $blocked_pid"<br />
<br />
query2="select bl.pid as blocked_pid, a.usename as blocked_user, <br />
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement<br />
from pg_catalog.pg_locks bl<br />
join pg_catalog.pg_stat_activity a<br />
on bl.pid = a.procpid<br />
join pg_catalog.pg_locks kl<br />
join pg_catalog.pg_stat_activity ka<br />
on kl.pid = ka.procpid<br />
on bl.transactionid = kl.transactionid and bl.pid != kl.pid<br />
where not bl.granted;"<br />
<br />
statement="psql -A -t postgres -c'$query1'"<br />
<br />
blocking_pid=`su postgres -c "$statement"`<br />
if [[ "$blocking_pid" -ne "" ]]<br />
then<br />
query3="select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,<br />
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, <br />
age(now(),pg_stat_activity.query_start) as _age, pg_stat_activity.procpid <br />
from pg_stat_activity,pg_locks left <br />
outer join pg_class on (pg_locks.relation = pg_class.oid) <br />
where pg_locks.pid=pg_stat_activity.procpid and pg_locks.pid = $blocking_pid";<br />
<br />
statement="psql postgres -c'$query2'"<br />
su postgres -c "$statement"<br />
<br />
statement="psql postgres -c'$query3'"<br />
su postgres -c "$statement"<br />
<br />
fi<br />
fi<br />
done<br />
</pre><br />
Použití:<br />
<pre><br />
[root@diana pavel]# cd /usr/local/pgsql/data/<br />
[root@diana data]# tail -f serverlog | bash lockdog.sh &> /var/locks &<br />
</pre><br />
Výsledek:<br />
<pre><br />
[root@diana data]# cat /var/locks<br />
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement <br />
-------------+--------------+--------------+---------------+------------------------------------------<br />
8866 | pavel | 8864 | pavel | update ciselnik set v = 20 where id = 1;<br />
(1 row)<br />
<br />
datname | relname | transactionid | mode | granted | usename | substr | query_start | _age | procpid <br />
----------+---------------+---------------+------------------+---------+---------+-----------------------+-------------------------------+-----------------+---------<br />
postgres | ciselnik_pkey | | AccessShareLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
postgres | ciselnik | | RowShareLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
postgres | fakttab_pkey | | RowExclusiveLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
postgres | fakttab | | RowExclusiveLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
postgres | | | ExclusiveLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
postgres | | 1943 | ExclusiveLock | t | pavel | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 | 8864<br />
(6 rows)<br />
</pre></div>
imported>Pavel