Monitor logu pro sběr doplňkových informací ohledně aktivních zámků

REGEX='process ([0-9]+) still waiting'

while read x 
do 
	if [[ $x =~ $REGEX ]]
	then
		blocked_pid=${BASH_REMATCH[1]}
query1="select  kl.pid as blocking_pid
 from pg_catalog.pg_locks bl
      join pg_catalog.pg_stat_activity a
      on bl.pid = a.procpid
      join pg_catalog.pg_locks kl
           join pg_catalog.pg_stat_activity ka
           on kl.pid = ka.procpid
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
  where bl.pid = $blocked_pid"

query2="select bl.pid as blocked_pid, a.usename as blocked_user,         
kl.pid as blocking_pid, ka.usename as blocking_user, a.current_query as blocked_statement
 from pg_catalog.pg_locks bl
      join pg_catalog.pg_stat_activity a
      on bl.pid = a.procpid
      join pg_catalog.pg_locks kl
           join pg_catalog.pg_stat_activity ka
           on kl.pid = ka.procpid
      on bl.transactionid = kl.transactionid and bl.pid != kl.pid
 where not bl.granted;"

		statement="psql -A -t postgres -c'$query1'"

		blocking_pid=`su postgres -c "$statement"`
		if [[ "$blocking_pid" -ne "" ]]
		then
query3="select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
     pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, 
     age(now(),pg_stat_activity.query_start) as _age, pg_stat_activity.procpid 
   from pg_stat_activity,pg_locks left 
     outer join pg_class on (pg_locks.relation = pg_class.oid)  
   where pg_locks.pid=pg_stat_activity.procpid and pg_locks.pid = $blocking_pid";
	     
		    statement="psql postgres -c'$query2'"
		    su postgres -c "$statement"

		    statement="psql postgres -c'$query3'"
		    su postgres -c "$statement"

		fi
	fi
done

Použití:

[root@diana pavel]# cd /usr/local/pgsql/data/
[root@diana data]# tail -f serverlog | bash lockdog.sh &> /var/locks &

Výsledek:

[root@diana data]# cat /var/locks
 blocked_pid | blocked_user | blocking_pid | blocking_user |            blocked_statement             
-------------+--------------+--------------+---------------+------------------------------------------
        8866 | pavel        |         8864 | pavel         | update ciselnik set v = 20 where id = 1;
(1 row)

 datname  |    relname    | transactionid |       mode       | granted | usename |        substr         |          query_start          |      _age       | procpid 
----------+---------------+---------------+------------------+---------+---------+-----------------------+-------------------------------+-----------------+---------
 postgres | ciselnik_pkey |               | AccessShareLock  | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
 postgres | ciselnik      |               | RowShareLock     | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
 postgres | fakttab_pkey  |               | RowExclusiveLock | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
 postgres | fakttab       |               | RowExclusiveLock | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
 postgres |               |               | ExclusiveLock    | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
 postgres |               |          1943 | ExclusiveLock    | t       | pavel   | <IDLE> in transaction | 2011-10-13 14:08:32.028764+02 | 00:00:09.220909 |    8864
(6 rows)