pg_cancel_backend() 与pg_terminate_backend()

来源：互联网发布：ubuntu 只能游客登录编辑：程序博客网时间：2024/04/30 03:20

先看下两个函数的官方解释：

pg_cancel_backend() 与pg_terminate_backend() - dazuiba_008 - 魂醉的一亩二分地

pg_cancel_backend() 取消后台操作，回滚未提交事物

pg_terminate_backend() 中断session，回滚未提交事物

这里和oracle类似kill session的操作是pg_terminate_backend()

pg_cancel_backend() 举例：

session A:
postgres=# create table tb1 (a int);CREATE TABLEpostgres=# begin;
postgres=# insert into tb1 select generate_series(1,100000000);
session B：
postgres=#  select * from pg_stat_activity; datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |                    current_query                     -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres |   10305 |       10 | postgres | psql             |             |                 |          -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:12:17.773935+08 | 2013-12-03 15:14:31.454816+08 | f       | insert into tb1 select generate_series(1,100000000);
postgres=# select pg_cancel_backend(10305); pg_cancel_backend ------------------- t
session A：ERROR:  canceling statement due to user requestpostgres=# commit;ROLLBACKpostgres=# select * from tb1; a ---(0 rows)
session B:
postgres=# select * from pg_stat_activity; datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |          current_query          -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+--------------------------------- 12699 | postgres |   10305 |       10 | postgres | psql             |             |                 |          -1 | 2013-12-03 14:38:05.887116+08 |

pg_terminate_backend() 举例：

session A：
postgres=# create table tb2 (a int);CREATE TABLEpostgres=# begin;BEGINpostgres=# insert into tb2 select generate_series(1,100000000);
session B：
postgres=#  select * from pg_stat_activity; datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |                    current_query                     -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+------------------------------------------------------ 12699 | postgres |   10305 |       10 | postgres | psql             |             |                 |          -1 | 2013-12-03 14:38:05.887116+08 | 2013-12-03 15:24:22.45073+08  | 2013-12-03 15:24:33.362185+08 | f       | insert into tb2 select generate_series(1,100000000);
postgres=# select pg_terminate_backend(10305); pg_terminate_backend ---------------------- t(1 row)
session A：
postgres=# insert into tb2 select generate_series(1,100000000);FATAL:  terminating connection due to administrator commandFATAL:  terminating connection due to administrator commandThe connection to the server was lost. Attempting reset: Succeeded.postgres=# commit;WARNING:  there is no transaction in progressCOMMITpostgres=# select * from tb2; a ---(0 rows)
postgres=# select * from pg_stat_activity;
 datid | datname  | procpid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          | waiting |          current_query          -------+----------+---------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+--------------------------------- 12699 | postgres |   10918 |       10 | postgres | psql             |             |                 |          -1 | 2013-12-03 15:25:12.613672+08 | 2013-12-03 15:30:29.544088+08 | 2013-12-03 15:30:29.544088+08 | f       | select * from pg_stat_activity;

通过以上实验理解起来应该很简单了，procpid=10305在pg_cancel_backend()下，session还在，事物回退，在pg_terminate_backend()操作后，session消失，事物回退。如果在某些时候pg_terminate_backend()不能杀死session，那么可以在os层面，直接kill -9 pid