KILL

    • KILL语法在非线程池模式和线程池模式下均有效。
    • 一般结合SHOW PROCESSSLIST的查询结果Id字段使用。
    • 也可以结合select sessionid from pg_stat_activity where (过滤条件) 使用

    • CONNECTION

    • QUERY

    • processlist_id

    2. openGauss=# show processlist;
    3.        Id        |       Pid       |      QueryId      | UniqueSqlId |   User    | Host |    db    |        Command         |
    4.       BackendStart          |           XactStart           |  Time  | State  |                  Info
    5. -----------------+-----------------+-------------------+-------------+-----------+------+----------+------------------------+---
    6. ----------------------------+-------------------------------+--------+--------+----------------------------------------
    7.  139653370304256 | 139653370304256 |                 0 |           0 | opengauss |      | postgres | ApplyLauncher          | 20
    8. 22-06-21 16:46:19.656076+08 |                               |        |        |
    9.  139653319255808 | 139653319255808 |                 0 |           0 | opengauss |      | postgres | Asp                    | 20
    10. 22-06-21 16:46:19.728521+08 |                               |      1 | active |
    11.  139653336483584 | 139653336483584 |                 0 |           0 | opengauss |      | postgres | PercentileJob          | 20
    12. 22-06-21 16:46:19.728527+08 |                               |      8 | active |
    13.  139653302175488 | 139653302175488 |                 0 |           0 | opengauss |      | postgres | statement flush thread | 20
    14. 22-06-21 16:46:19.728558+08 |                               | 508507 | idle   |
    15.  139653198239488 | 139653198239488 |                 0 |           0 | opengauss |      | postgres | WorkloadMonitor        | 20
    16. 22-06-21 16:46:19.750133+08 |                               |        |        |
    17.  139653181298432 | 139653181298432 |                 0 |           0 | opengauss |      | postgres | WLMArbiter             | 20
    18. 22-06-21 16:46:19.750976+08 |                               |        |        |
    19.  139653215110912 | 139653215110912 |                 0 |           0 | opengauss |      | postgres | workload               | 20
    20. 22-06-21 16:46:19.754504+08 | 2022-06-21 16:46:19.769585+08 | 508507 | active | WLM fetch collect info from data nodes
    21.  139653421840128 | 139653421840128 |                 0 |           0 | opengauss |      | postgres | JobScheduler           | 20
    22. 22-06-27 10:00:54.754007+08 |                               |      0 | active |
    23.  139653044328192 | 139653044328192 | 48976645947655327 |  1772643515 | opengauss | -1   | dolphin  | gsql                   | 20
    24. 22-06-27 14:00:53.163338+08 | 2022-06-27 14:01:26.794658+08 |      0 | active | show processlist;
    25.  139653027546880 | 139653027546880 | 48976645947655326 |  1775585557 | opengauss | -1   | postgres | gsql                   | 20
    26. 22-06-27 14:01:03.969962+08 | 2022-06-27 14:01:19.967521+08 |      7 | active | select pg_sleep(100);
    28. --终止139653027546880连接执行的SQL语句
    29. openGauss=# kill query 139653027546880;
    30.  result
    31. --------
    32.  t
    33. (1 row)
    35. --查看processlist的139653027546880连接状态，已经变为idle
    36. openGauss=# show processlist;
    37.        Id        |       Pid       |      QueryId      | UniqueSqlId |   User    | Host |    db    |        Command         |
    38.       BackendStart          |           XactStart           |  Time  | State  |                  Info
    39. -----------------+-----------------+-------------------+-------------+-----------+------+----------+------------------------+---
    40. ----------------------------+-------------------------------+--------+--------+----------------------------------------
    41.  139653370304256 | 139653370304256 |                 0 |           0 | opengauss |      | postgres | ApplyLauncher          | 20
    42. 22-06-21 16:46:19.656076+08 |                               |        |        |
    43.  139653319255808 | 139653319255808 |                 0 |           0 | opengauss |      | postgres | Asp                    | 20
    44. 22-06-21 16:46:19.728521+08 |                               |      0 | active |
    45.  139653336483584 | 139653336483584 |                 0 |           0 | opengauss |      | postgres | PercentileJob          | 20
    46. 22-06-21 16:46:19.728527+08 |                               |      5 | active |
    47.  139653302175488 | 139653302175488 |                 0 |           0 | opengauss |      | postgres | statement flush thread | 20
    48. 22-06-21 16:46:19.728558+08 |                               | 508573 | idle   |
    49.  139653198239488 | 139653198239488 |                 0 |           0 | opengauss |      | postgres | WorkloadMonitor        | 20
    50. 22-06-21 16:46:19.750133+08 |                               |        |        |
    51.  139653181298432 | 139653181298432 |                 0 |           0 | opengauss |      | postgres | WLMArbiter             | 20
    52. 22-06-21 16:46:19.750976+08 |                               |        |        |
    53.  139653215110912 | 139653215110912 |                 0 |           0 | opengauss |      | postgres | workload               | 20
    54. 22-06-21 16:46:19.754504+08 | 2022-06-21 16:46:19.769585+08 | 508573 | active | WLM fetch collect info from data nodes
    55.  139653421840128 | 139653421840128 |                 0 |           0 | opengauss |      | postgres | JobScheduler           | 20
    56. 22-06-27 10:00:54.754007+08 |                               |      1 | active |
    57.  139653044328192 | 139653044328192 | 48976645947655329 |  1772643515 | opengauss | -1   | dolphin  | gsql                   | 20
    58. 22-06-27 14:00:53.163338+08 | 2022-06-27 14:02:33.180256+08 |      0 | active | show processlist;
    59.  139653027546880 | 139653027546880 |                 0 |           0 | opengauss | -1   | postgres | gsql                   | 20
    60. 22-06-27 14:01:03.969962+08 |                               |     11 | idle   | select pg_sleep(100);
    61. (10 rows)
    63. --终止139653027546880连接
    64.  result
    65. --------
    66.  t
    69. --或
    71. openGauss=# kill connection 139653027546880;
    72.  result
    73. --------
    74.  t
    75. (1 row)
    77. --查看processlist中已经不存在该连接
    78. openGauss=# show processlist;
    79.        Id        |       Pid       |      QueryId      | UniqueSqlId |   User    | Host |    db    |        Command         |
    80.       BackendStart          |           XactStart           |  Time  | State  |                  Info
    81. -----------------+-----------------+-------------------+-------------+-----------+------+----------+------------------------+---
    82. ----------------------------+-------------------------------+--------+--------+----------------------------------------
    83.  139653370304256 | 139653370304256 |                 0 |           0 | opengauss |      | postgres | ApplyLauncher          | 20
    84. 22-06-21 16:46:19.656076+08 |                               |        |        |
    85.  139653319255808 | 139653319255808 |                 0 |           0 | opengauss |      | postgres | Asp                    | 20
    86. 22-06-21 16:46:19.728521+08 |                               |      1 | active |
    87.  139653336483584 | 139653336483584 |                 0 |           0 | opengauss |      | postgres | PercentileJob          | 20
    88. 22-06-21 16:46:19.728527+08 |                               |      7 | active |
    89.  139653302175488 | 139653302175488 |                 0 |           0 | opengauss |      | postgres | statement flush thread | 20
    90. 22-06-21 16:46:19.728558+08 |                               | 508696 | idle   |
    91.  139653198239488 | 139653198239488 |                 0 |           0 | opengauss |      | postgres | WorkloadMonitor        | 20
    92. 22-06-21 16:46:19.750133+08 |                               |        |        |
    93.  139653181298432 | 139653181298432 |                 0 |           0 | opengauss |      | postgres | WLMArbiter             | 20
    94. 22-06-21 16:46:19.750976+08 |                               |        |        |
    95.  139653215110912 | 139653215110912 |                 0 |           0 | opengauss |      | postgres | workload               | 20
    96. 22-06-21 16:46:19.754504+08 | 2022-06-21 16:46:19.769585+08 | 508696 | active | WLM fetch collect info from data nodes
    97.  139653421840128 | 139653421840128 |                 0 |           0 | opengauss |      | postgres | JobScheduler           | 20
    98. 22-06-27 10:00:54.754007+08 |                               |      1 | active |
    99.  139653044328192 | 139653044328192 | 48976645947655331 |  1772643515 | opengauss | -1   | dolphin  | gsql                   | 20
    100. 22-06-27 14:00:53.163338+08 | 2022-06-27 14:04:35.418518+08 |      0 | active | show processlist;