013.PGSQL-查看sql正在运行的进程、查看表是否被锁、解锁表

1.PGSQL-查看sql正在运行的进程

SELECT datname,pid,state,query FROM pg_stat_activity where state='active'

 

SELECT
    procpid,
    START,
    now() - START AS lap,
    current_query
FROM
    (
        SELECT
            backendid,
            pg_stat_get_backend_pid (S.backendid) AS procpid,
            pg_stat_get_backend_activity_start (S.backendid) AS START,
            pg_stat_get_backend_activity (S.backendid) AS current_query
        FROM
            (
                SELECT
                    pg_stat_get_backend_idset () AS backendid
            ) AS S
    ) AS S
WHERE
    current_query <> '<IDLE>'
ORDER BY
    lap DESC;

 

procpid：进程id
start：进程开始时间
lap：经过时间
current_query：执行中的sql
怎样停止正在执行的sql
SELECT pg_cancel_backend(进程id);
或者用系统函数
kill -9 进程id;

 

2.查看表是否被锁，解锁表

 

select oid from pg_class where relname='m_ss_kjcx_tech_inovate_talent' 
select pid from pg_locks where relation='4384913' 
--如果查询到了结果，表示该表被锁 则需要释放锁定 
select pg_cancel_backend(140650678843136)

 

通过命令： 
=# select pg_cancel_backend(线程id); 
来kill掉指定的SQL语句。（这个函数只能 kill Select 查询，而updae,delete DML不生效）
使用可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作= 
=# select pg_terminate_backend(pid int)

-- PGSQL
select oid from pg_class where relname='m_ss_kjcx_tech_inovate_talent' 
select pid from pg_locks where relation='4384913' 
--如果查询到了结果，表示该表被锁 则需要释放锁定 
select pg_cancel_backend(140650678843136)
--- 来查看有哪些SQL正在执行。 waiting='t'

select pid,backend_start,application_name,query_start,waiting,state ,query from pg_stat_activity
where query <>''
order by query_start asc

通过命令： 
=# select pg_cancel_backend(线程id); 
来kill掉指定的SQL语句。（这个函数只能 kill Select 查询，而updae,delete DML不生效）
使用可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作= 
=# select pg_terminate_backend(pid int)