pg_cancel_backend 作用是退出事务（所有未提交的信息回滚），但是不退出SESSION；

pg_terminate_backend 作用是退出session（所有未提交的信息回滚）。

测试结果如下:

Server1:

mrp_url=> create table tbl_test (id int);
CREATE TABLE
mrp_url=> begin ;
BEGIN
mrp_url=> insert into tbl_test values(1);
INSERT 0 1
mrp_url=> select * from tbl_test;
id
----
1
(1 row)

mrp_url=> insert into tbl_test select generate_series(2,10000000);

Server2(Server1正在写入记录):

mrp_url=# select * from pg_stat_activity;

datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start
| xact_start | query_start | waiting |
current_query
-------+---------+---------+----------+----------+------------------+-----------------+-------------+-------------------------------
+-------------------------------+-------------------------------+---------+---------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------

16389 | mrp_url | 3253 | 16384 | mrp_url | psql | 127.0.0.1 | 23633 | 2010-10-12 14:51:01.119572+08
| 2010-10-12 14:53:21.350747+08 | 2010-10-12 14:53:30.179774+08 | f | insert into tbl_test select generate_series(2,10000000);

mrp_url=# select pg_cancel_backend(3253);
pg_cancel_backend
-------------------
t
(1 row)

ERROR: canceling statement due to user request
mrp_url=> select * from tbl_test;
ERROR: current transaction is aborted,commands ignored until end of transaction block
mrp_url=> commit;
ROLLBACK

Server2(查看Server1的SESSION并没有退出):

mrp_url=# select * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start
| xact_start | query_start | waiting |
current_query
-------+---------+---------+----------+----------+------------------+-----------------+-------------+-------------------------------
+-------------------------------+-------------------------------+---------+---------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------

16389 | mrp_url | 3253 | 16384 | mrp_url | psql | 127.0.0.1 | 23633 | 2010-10-12 14:51:01.119572+08
| 2010-10-12 14:53:21.350747+08 | 2010-10-12 14:53:30.179774+08 | f | <IDLE>

pg_terminate_backend测试:

Server1:

mrp_url=> begin;
BEGIN
mrp_url=> insert into tbl_Test values(1);
INSERT 0 1
mrp_url=> insert into tbl_test select generate_series(2,10000000);

Server2:

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

Server1():

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.
mrp_url=> select pg_total_relation_size('tbl_test')/1024/1024;
?column?
----------
249
(1 row)

mrp_url=> select * from tbl_Test;
id
----
(0 rows)

mrp_url=> vacuum full tbl_test;
VACUUM
mrp_url=> select * from tbl_Test;
id
----
(0 rows)

mrp_url=> select pg_total_relation_size('tbl_test')/1024/1024;
?column?
----------
0
(1 row)

Server2:

16389 | mrp_url | 4092 | 16384 | mrp_url | psql | 127.0.0.1 | 18986 | 2010-10-12 15:36:32.240069+08
| | 2010-10-12 15:36:57.470988+08 | f | <IDLE>

（server1被terminate后自动重连，PROCPID已经改变）

官方文档解释: