KILL
功能描述
终止指定连接或该连接下执行的SQL语句。
一般结合SHOW PROCESSSLIST的查询结果Id字段使用。也可以结合select sessionid from pg_stat_activity where (过滤条件)使用。
注意事项
- 该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。
- KILL语法在非线程池模式和线程池模式下均有效。
语法格式
KILL [CONNECTION | QUERY] processlist_id
参数说明
CONNECTION
使用CONNECTION关键字修饰KILL语句时，效果等价于
KILL processlist_id，终止当前连接。QUERY
使用QUERY关键字修饰KILL语句时，终止当前连接执行的SQL语句，连接本身不受影响。
processlist_id
连接Id。
示例
1、查看当前连接。
show processlist;
返回结果为：
Id | Pid | QueryId | UniqueSqlId | User | Host | db | Command | B
ackendStart | XactStart | Time | State | Info
----------------+----------------+-----------------+-------------+----------+------+----------+------------------------+----------
---------------------+-------------------------------+-------+--------+----------------------------------------
47427372848896 | 47427372848896 | 0 | 0 | vastbase | | postgres | CfsShrinker | 2023-08-2
1 14:47:59.53251+08 | | 12849 | idle |
47427356067584 | 47427356067584 | 0 | 0 | vastbase | | postgres | TxnSnapCapturer | 2023-08-2
1 14:47:59.541665+08 | | 12849 | idle |
47427425339136 | 47427425339136 | 0 | 0 | vastbase | | postgres | Asp | 2023-08-2
1 14:47:59.558877+08 | | 1 | active |
47427389630208 | 47427389630208 | 0 | 0 | vastbase | | postgres | PercentileJob | 2023-08-2
1 14:47:59.563368+08 | | 6 | active |
47427455813376 | 47427455813376 | 0 | 0 | vastbase | | postgres | statement flush thread | 2023-08-2
1 14:47:59.565678+08 | | 12849 | idle |
47427233122048 | 47427233122048 | 0 | 0 | vastbase | | postgres | JobScheduler | 2023-08-2
1 14:47:59.567334+08 | | 0 | active |
47427626866432 | 47427626866432 | 0 | 0 | vastbase | | postgres | WorkloadMonitor | 2023-08-2
1 14:47:59.6369+08 | | 12849 | idle |
47427610085120 | 47427610085120 | 0 | 0 | vastbase | | postgres | workload | 2023-08-2
1 14:47:59.637644+08 | 2023-08-21 14:47:59.643011+08 | 12849 | active | WLM fetch collect info from data nodes
47427643647744 | 47427643647744 | 0 | 0 | vastbase | | postgres | WLMArbiter | 2023-08-2
1 14:47:59.642855+08 | | 12849 | idle |
47427738142464 | 47427738142464 | 562949953423798 | 778441007 | vastbase | -1 | vastbase | vsql | 2023-08-2
1 18:16:08.618455+08 | 2023-08-21 18:22:08.947401+08 | 0 | active | show processlist;
47427298264832 | 47427298264832 | 0 | 0 | vastbase | | postgres | ApplyLauncher | 2023-08-2
1 18:16:55.518067+08 | | 313 | idle |
(11 rows)
2、终止47427372848896连接执行的SQL语句。
kill query 47427372848896;
返回结果为：
result
--------
t
(1 row)
3、查看processlist的47427372848896连接状态，已经变为idle。
show processlist;
返回结果为：
Id | Pid | QueryId | UniqueSqlId | User | Host | db | Command | B
ackendStart | XactStart | Time | State | Info
----------------+----------------+-----------------+-------------+----------+------+----------+------------------------+----------
---------------------+-------------------------------+-------+--------+----------------------------------------
47427372848896 | 47427372848896 | 0 | 0 | vastbase | | postgres | CfsShrinker | 2023-08-2
1 14:47:59.53251+08 | | 13027 | idle |
47427356067584 | 47427356067584 | 0 | 0 | vastbase | | postgres | TxnSnapCapturer | 2023-08-2
1 14:47:59.541665+08 | | 13027 | idle |
47427425339136 | 47427425339136 | 0 | 0 | vastbase | | postgres | Asp | 2023-08-2
1 14:47:59.558877+08 | | 1 | active |
47427389630208 | 47427389630208 | 0 | 0 | vastbase | | postgres | PercentileJob | 2023-08-2
1 14:47:59.563368+08 | | 4 | active |
47427455813376 | 47427455813376 | 0 | 0 | vastbase | | postgres | statement flush thread | 2023-08-2
1 14:47:59.565678+08 | | 13027 | idle |
47427233122048 | 47427233122048 | 0 | 0 | vastbase | | postgres | JobScheduler | 2023-08-2
1 14:47:59.567334+08 | | 1 | active |
47427626866432 | 47427626866432 | 0 | 0 | vastbase | | postgres | WorkloadMonitor | 2023-08-2
1 14:47:59.6369+08 | | 13027 | idle |
47427610085120 | 47427610085120 | 0 | 0 | vastbase | | postgres | workload | 2023-08-2
1 14:47:59.637644+08 | 2023-08-21 14:47:59.643011+08 | 13027 | active | WLM fetch collect info from data nodes
47427643647744 | 47427643647744 | 0 | 0 | vastbase | | postgres | WLMArbiter | 2023-08-2
1 14:47:59.642855+08 | | 13027 | idle |
47427738142464 | 47427738142464 | 562949953423834 | 778441007 | vastbase | -1 | vastbase | vsql | 2023-08-2
1 18:16:08.618455+08 | 2023-08-21 18:25:06.789091+08 | 0 | active | show processlist;
47427298264832 | 47427298264832 | 0 | 0 | vastbase | | postgres | ApplyLauncher | 2023-08-2
1 18:16:55.518067+08 | | 491 | idle |
(11 rows)
4、终止47427372848896连接。
kill 47427372848896;
或者
kill connection 47427372848896;
返回结果均为：
result
--------
t
(1 row)
5、查看processlist中已经不存在该连接。
show processlist;
返回结果为：
Id | Pid | QueryId | UniqueSqlId | User | Host | db | Command | B
ackendStart | XactStart | Time | State | Info
----------------+----------------+-----------------+-------------+----------+------+----------+------------------------+----------
---------------------+-------------------------------+-------+--------+----------------------------------------
47427356067584 | 47427356067584 | 0 | 0 | vastbase | | postgres | TxnSnapCapturer | 2023-08-2
1 14:47:59.541665+08 | | 13146 | idle |
47427425339136 | 47427425339136 | 0 | 0 | vastbase | | postgres | Asp | 2023-08-2
1 14:47:59.558877+08 | | 1 | active |
47427389630208 | 47427389630208 | 0 | 0 | vastbase | | postgres | PercentileJob | 2023-08-2
1 14:47:59.563368+08 | | 2 | active |
47427455813376 | 47427455813376 | 0 | 0 | vastbase | | postgres | statement flush thread | 2023-08-2
1 14:47:59.565678+08 | | 13146 | idle |
47427233122048 | 47427233122048 | 0 | 0 | vastbase | | postgres | JobScheduler | 2023-08-2
1 14:47:59.567334+08 | | 0 | active |
47427626866432 | 47427626866432 | 0 | 0 | vastbase | | postgres | WorkloadMonitor | 2023-08-2
1 14:47:59.6369+08 | | 13146 | idle |
47427610085120 | 47427610085120 | 0 | 0 | vastbase | | postgres | workload | 2023-08-2
1 14:47:59.637644+08 | 2023-08-21 14:47:59.643011+08 | 13146 | active | WLM fetch collect info from data nodes
47427643647744 | 47427643647744 | 0 | 0 | vastbase | | postgres | WLMArbiter | 2023-08-2
1 14:47:59.642855+08 | | 13146 | idle |
47427738142464 | 47427738142464 | 562949953423858 | 778441007 | vastbase | -1 | vastbase | vsql | 2023-08-2
1 18:16:08.618455+08 | 2023-08-21 18:27:05.244087+08 | 0 | active | show processlist;
47427298264832 | 47427298264832 | 0 | 0 | vastbase | | postgres | ApplyLauncher | 2023-08-2
1 18:16:55.518067+08 | | 610 | idle |
47427372848896 | 47427372848896 | 0 | 0 | vastbase | | postgres | CfsShrinker | 2023-08-2
1 18:27:02.316572+08 | | 3 | idle |
(11 rows)
