pg_cancel_backend()和pg_terminate_backend()区别

郑全

两个函数的官方解释：
pg_cancel_backend() 取消后台操作，回滚未提交事物
pg_terminate_backend() 中断session，回滚未提交事物

下面以PG 13.3 为例进行演示：


pg_cancel_backend()举例：
session A:

postgres=> create table t1 (a int);
CREATE TABLE
postgres=> begin;
BEGIN
postgres=*> insert into t1 select generate_series(1,100000000);


SESSION B:

postgres=# select datname,pid, query from pg_stat_activity;
datname  | pid  |                        query                        
----------+------+-----------------------------------------------------
          | 1182 |
          | 1185 |
postgres | 3587 | insert into t1 select generate_series(1,100000000);
postgres | 3598 | select datname,pid, query from pg_stat_activity;
          | 1179 |
          | 1178 |
          | 1181 |
(7 rows)


postgres=# select pg_cancel_backend(3587);
pg_cancel_backend
-------------------
t
(1 row)


SESSION A:

postgres=*> insert into t1 select generate_series(1,100000000);
ERROR:  canceling statement due to user request
postgres=!>
postgres=!>

postgres=!> commit;
ROLLBACK
postgres=>
postgres=> select * from t1;
a
---
(0 rows)



B SESSION:

postgres=# select datname,pid, query from pg_stat_activity;
datname  | pid  |                      query                       
----------+------+--------------------------------------------------
          | 1182 |
          | 1185 |
postgres | 3587 | select * from t1;
postgres | 3598 | select datname,pid, query from pg_stat_activity;
          | 1179 |
          | 1178 |
          | 1181 |
(7 rows)




-------------------
pg_terminate_backend() 举例：

session1:
postgres=>  create table t2 (a int);
CREATE TABLE
postgres=> begin;
BEGIN
postgres=*>  insert into t2 select generate_series(1,100000000);



session2:
postgres=#  select datname,pid, query from pg_stat_activity;
datname  | pid  |                        query                        
----------+------+-----------------------------------------------------
          | 1182 |
          | 1185 |
postgres | 3587 | insert into t2 select generate_series(1,100000000);
postgres | 3598 | select datname,pid, query from pg_stat_activity;
          | 1179 |
          | 1178 |
          | 1181 |
(7 rows)

postgres=# select pg_terminate_backend(3587);
pg_terminate_backend
----------------------
t
(1 row)

postgres=#


session1:

postgres=*>  insert into t2 select generate_series(1,100000000);
INSERT 0 100000000
postgres=*> commit;
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=>

postgres=> select * from t2;
a
---
(0 rows)

postgres=>


session2：
postgres=# select datname,pid, query from pg_stat_activity;              datname  | pid  |                      query                       
----------+------+--------------------------------------------------
          | 1182 |
          | 1185 |
postgres | 3766 | autovacuum: VACUUM public.t2
postgres | 3598 | select datname,pid, query from pg_stat_activity;
postgres | 3767 | select * from t2;
          | 1179 |
          | 1178 |
          | 1181 |
(8 rows)

postgres=#

在pg_cancel_backend()下，session还在，事物回退;
在pg_terminate_backend()操作后，session消失，事物回退。

如果在某些时候pg_terminate_backend()不能杀死session，那么可以在os层面，直接kill -9 pid