postgresql9.5查询sql或者进程是否存在锁

方法一：
select * from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where a.mode like '%ExclusiveLock%';
方法二：
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 - least(query_start,xact_start) AS runtime,
       locker_act.query
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc
WHERE locker.pid=locker_act.pid
  AND NOT locker.pid=pg_backend_pid()
  AND application_name<>'pg_statsinfod'
  AND locker.relation = pc.oid
  AND pc.reltype<>0 --and pc.relname='t'
ORDER BY  runtime desc;

select pg_cancel_backend(pid) ;
select pg_terminate_backend(pid);--杀死进程 ,pid为进程id

select 
pg_terminate_backend(pid) 
from pg_stat_activity 
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --强杀连接超过10分钟的事务 不要杀死自己的进程


select 
pg_cancel_backend(pid) 
from pg_stat_activity 
where (now() - backend_start) > '00:10:00' and pid != (select pg_backend_pid()); --杀死连接超过10分钟的事务 不要杀死自己的进程