pg_cancel_backend()和pg_terminate_backend()

pg_cancel_backend()和pg_terminate_backend()

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

pg_cancel_backend()举例：
session A:
postgres=# create table t1 (a int);
CREATE TABLE
postgres=# begin;
postgres=# insert into t1 select generate_series(1,100000000);

session B：
tina=# select datname,pid, query from pg_stat_activity;
datname  | pid  |                        query                       
----------+------+-----------------------------------------------------
postgres | 1923 | insert into t2 select generate_series(1,100000000);
tina     | 1922 | select datname,pid, query from pg_stat_activity;
(2 rows)

tina=# select pg_cancel_backend(1923);
pg_cancel_backend
-------------------
t
(1 row)

session A：
STATEMENT:  insert into t2 select generate_series(1,100000000);
ERROR:  canceling statement due to user request
postgres=# commit;
ROLLBACK
postgres=# select * from t2;
a
---
(0 rows)

session B:
tina=#  select datname,pid, query from pg_stat_activity;
datname  | pid  |                      query                      
----------+------+--------------------------------------------------
postgres | 1923 | commit;
tina     | 1922 | select datname,pid, query from pg_stat_activity;


pg_terminate_backend() 举例：
session A：
postgres=# create table t2 (a int);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into t2 select generate_series(1,100000000);

session B：
tina=# select datname,pid, query from pg_stat_activity;
datname  | pid  |  query                       
---------+------+-------------
postgres | 1874 | insert into t2 select generate_series(1,100000000);
tina     | 1914 | select * from pg_stat_activity;
(2 rows)

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


session A：
FATAL:  terminating connection due to administrator command
STATEMENT:  insert into t2 select generate_series(1,100000000);
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=# commit;
WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
COMMIT
postgres=# select * from t2;
a
---
(0 rows)

session B：
tina=# select datname,pid, query from pg_stat_activity;
datname  | pid  |  query                       
---------+------+-------------
tina     | 1914 | select * from pg_stat_activity;
(1 rows)


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

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