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