今天在 postgresql 上执行一个 DDL 的时候卡死,其实就是一个 alter table add column 的语句
那么,首先可以用这个 SQL 来看有哪些执行中的查询
select query, * from pg_stat_activity where query != '' order by query_start asc;
但是从这里有两个问题,一是只能看到自己名下的,别人的 SQL 看不到具体 query,二是通过这个结果看不出到底是啥 SQL 阻塞了自己的 DDL
Google 一下,看到这里, https://stackoverflow.com/ques…
select pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;
但是可惜,这种方法只能支持到 postgresql 9.6 以上的,而我们是 9.5,但是没关系,下面又给出了另外一种方式
SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item, now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid, blockeda.query as blocked_query, blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid, blockinga.query as blocking_query, blockingl.mode as blocking_mode FROM pg_catalog.pg_locks blockedl JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid JOIN pg_catalog.pg_locks blockingl ON( ( (blockingl.transactionid=blockedl.transactionid) OR (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype) ) AND blockedl.pid != blockingl.pid) JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid WHERE NOT blockedl.granted AND blockinga.datname = current_database()
通过这个的结果,可以清楚的看到是哪个 pid 造成了阻塞
locked_item waiting_duration blocked_pid blocked_query blocked_mode blocking_pid blocking_query blocking_mode ... 00:02:21.268013 8131 alter table add column ; AccessExclusiveLock 5227 <insufficient privilege> AccessShareLock
接下来,就是处理了,看了一下,那个是个 select,那就不客气了
杀掉有两种方式
select pg_cancel_backend(<pid of the process>)If the process cannot be killed, try:
select pg_terminate_backend(<pid of the process>)