pgsql 无法删除表 CASCADE无效

      问题发生并解决后，有一段时间了，所以问题和解决过程只记住了个大概…

问题表现

      pgsql，删除某张表，无论是用第三方工具，还是命令，都无法删除成功。因为时间有点长了，所以报的啥错我也记不清了…
      无法删除、无法访问、select 什么的都不成功。其他同事对这张表的操作一样。
      百度之后，显示最多的结果是，有依赖，解决办法也很简单：

DROP TABLE [table] CASCADE;

      但是执行后，仍然解决不了问题。

问题分析

      既然和依赖没关系，那就想其他办法。
      经过百度和分析，大概率是有一个查询的sql，因为某些原因卡住了，然后一直占住这张表了，其他的操作都无法使用这张表。
      

问题解决

      百度之后有如下办法：

select * from pg_class where relname='t_test' 
select oid from pg_class where relname='t_test' 
-- 将查出来的oid 填入下面
select * from pg_locks where relation='33635' 
-- 再将查出来的pid，调用下面的方法
select pg_terminate_backend (17789)

      因为时间过长，所以我也不确定下面的sql是干嘛的了…

select 
	*,
	pid,backend_start,application_name,
	query_start,waiting,state ,query 
from pg_stat_activity
where pid = 17789
order by query_start asc;

SELECT 
	* 
FROM pg_stat_activity 
WHERE datname='t_test' 

 

      
      

两个函数的区别

      除了pg_terminate_backend()外，还有pg_cancel_backend()。
      这里和oracle类似kill session的操作是 pg_terminate_backend()

pg_cancel_backend()

1. 只能关闭当前用户下的后台进程
2. 向后台发送SIGINT信号，用于关闭事务，此时session还在，并且事务回滚
3. 取消后台操作，回滚未提交事物

pg_terminate_backend()

1. 需要superuser权限，可以关闭所有的后台进程
2. 向后台发送SIGTERM信号，用于关闭事务、关闭Process，此时session也会被关闭，并且事务回滚
3. 中断session，回滚未提交事物

后记

      后来查了以下，出现那种删不掉，DROP TABLE [table] CASCADE也没用的情况，是因为表被锁住了。

查询被锁住的表和进程

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%';

      这里查的是排它锁，也可以精确到行排它锁或者共享锁之类的。这里有几个重要的column：a.pid是进程id，b.relname是表名、约束名或者索引名，a.mode是锁类型。
      

杀掉指定表指定锁的进程

select 
	pg_cancel_backend(a.pid) 
from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where 
	b.relname ilike '表名' 
	and a.mode like '%ExclusiveLock%';

--或者使用更加霸道的pg_terminate_backend()：
select 
	pg_terminate_backend(a.pid) 
from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where b.relname ilike '表名' 
and a.mode like '%ExclusiveLock%';

      另外需要注意的是，pg_terminate_backend()会把session也关闭，此时sessionId会失效，可能会导致系统账号退出登录，需要清除掉浏览器的缓存cookie（至少我们系统遇到的情况是这样的）。