526互联

1、postgres查看锁表以及释放表

发布时间 2023-05-23 15:03:23作者: 站着说话不腰疼

目录

postgres查看锁表以及释放表

1、查看锁表

select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
    select pid from pg_locks l
                        join pg_class t on l.relation = t.oid

        and t.relkind = 'r'
);

2、释放锁表

SELECT pg_cancel_backend(pid);

3、使用

SELECT
	'SELECT pg_cancel_backend(' || pid || ');',
pid,
STATE,
usename,
query,
query_start 
FROM
	pg_stat_activity 
WHERE
	pid IN ( SELECT pid FROM pg_locks l JOIN pg_class T ON l.relation = T.oid AND T.relkind = 'r' );