postgresql 查看系统中的锁信息与锁资源

--查看当前活动的客户端连接数
 SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();    
 
 
 --查询客户端连接的情况
 SELECT pid,case when waiting='f' then 'already get lock,sql executing' when waiting='t' then 'waiting get lock,sql waiting execute' end lock_satus,
 current_timestamp - least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_query
FROM pg_stat_activity WHERE NOT pid=pg_backend_pid() and state<>'idle' and application_name<>'pg_statsinfod' order by runtime desc






		
--查看持有锁和等待锁的一些信息：
--reltype=0代表其为索引
SELECT locker.pid,
        pc.relname,
        locker.mode,
        locker_act.application_name,
        least(query_start,xact_start) start_time,
        locker_act.state,
        CASE
    WHEN granted='f' THEN
    'wait_lock'
    WHEN granted='t' THEN
    'get_lock'
    END lock_satus,current_timestamp