Do not use kill-9 to kill Postgresq user process __greenplum

Postgresql 8.3.3

Today the application reflects the database is very slow, some of the SQL execution day did not finish,
Check the database, found that a large number of sessions in the update the same table tbl_tmp, resulting in a large number of row lock application waiting for the waiter.

--equal to the waiting state of SQL (blocked SQL)
anpstat=# Select Datname,procpid,query_start,current_query,waiting,client_addr from pg_stat_activity where waiting= ' T ';
Datname |          Procpid |                                                           Query_start | Current_query | Waiting | Client_addr
----------+---------+-------------------------------+---------------------------------------------------------- -------------------------------------------------------------------------+---------+-------------
Sanpstat | 14044 |     2010-10-07 16:46:40.386904+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 27839 |     2010-10-07 16:49:18.022499+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 3539 |     2010-10-07 20:28:13.212075+08 | Update tbl_tmp Set feedback =$1 where Seq=$2 | T | 127.0.0.1
Sanpstat | 3894 |     2010-10-07 20:53:43.466517+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 10130 |     2010-10-08 09:37:51.253871+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 9083 |     2010-10-08 08:08:59.221976+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 10038 |     2010-10-08 09:25:55.434459+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 10241 |     2010-10-08 09:51:13.990492+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1 | T | 127.0.0.1
Sanpstat | 11147 |     2010-10-08 11:11:21.574665+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 11168 |     2010-10-08 11:17:17.158246+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 11926 |     2010-10-08 11:54:10.704641+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1
Sanpstat | 11959 |     2010-10-08 11:56:07.021072+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | T | 127.0.0.1

--The UPDATE statement being executed (blocking other SQL)
sanpstat=# Select Datname,procpid,query_start,current_query,waiting,client_addr from pg_stat_activity where waiting= ' F '
sanpstat-# and current_query like '%update tbl_tmp% ';
Datname |          Procpid |                                                           Query_start | Current_query |  Waiting | Client_addr
----------+---------+-------------------------------+---------------------------------------------------------- -------------------------------------------------------------------------+---------+---------------
Sanpstat | 2012 |     2010-10-07 16:52:55.8228+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 14157 |     2010-10-07 16:41:37.308062+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 10177 |     2010-10-08 09:43:43.297872+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 2043 |     2010-10-07 19:25:46.931806+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 3298 |     2010-10-07 19:37:53.307125+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 3322 |     2010-10-07 19:40:39.373079+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 4114 |     2010-10-08 09:25:18.701269+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 9082 |     2010-10-08 09:18:16.153882+08 | Update tbl_tmp set show=1, Gmt_modified=now (), modifier=$1 where Seq=$2 | f | 127.0.0.1
Sanpstat | 10042 |     2010-10-08 09:32:55.260732+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 10278 |     2010-10-08 11:08:55.334562+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 11810 |     2010-10-08 11:46:24.147652+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 | f | 127.0.0.1
Sanpstat | 11964 |     2010-10-08 11:58:32.831916+08 | Update tbl_tmp set Responsed=true, Feedback =$1, Gmt_modified=now (), modifier=$2 where Seq=$3 |


--Kill the process with Pg_cancel_backend ()
sanpstat=# Select Pg_cancel_backend (2012);
Pg_cancel_backend
-------------------
T
(1 row)

Kill the process with Pg_cancel_backend (), although it appears as ' t ', but the process is still on, the document explains cancel a backend ' s current query,
Guess Pg_cancel_backend () can only kill the SELECT statement, but not kill the UPDATE statement, later the experiment also confirmed this, interested friends
You can do your own experiments to verify.

--kill-9 Kill Process
Kill-9 2012
Kill-9 14157
Kill-9 10177
Kill-9 2043
Kill-9 3298
Kill-9 3322
Kill-9 4114
Kill-9 9082
Kill-9 10042
Kill-9 10278
Kill-9 11810
Kill-9 11964

This will kill the session of the Update Tbl_tmp table that is being executed.


--Database Cannot connect
[ENTERPRISEDB@SANP-RICH-DB1 ~]$ psql-h 192.168.3.27-p 1921-d postgres-u
Psql:FATAL:the database system is in recovery mode

After the process kill-9, found that the database can not connect, was, fortunately, a test library.

--The database is in recovery mode
-bash-3.2$ Ps-ef | grep post
postgres  4627     1  0 Apr12? &nb sp;       00:02:24/home/postgres/bin/postgres-d/mnt/data
postgres  4629   4627  0 Apr12?        00:00:04 postgres:logger process                
root     14195 14168  0 15:37 pts/7    00:00:00 su-postgres
Postgres 14196 14195  0 15:37 pts/7  ;   00:00:00-bash
Postgres 14332  4627-15:41 00:00:18 postgres:startup process   recovering 00000001000000da0000006e
Postgres 21495 14196  0 15:41 pts/7    00:00:00 ps-ef
Postgres 21496 14196  0 15:41 pts/7    00:00:00 grep Post

Oracle seems to be friendlier to discover that Postgresql is in a recovery state and strange that it is not allowed to connect during the Postgresql recovery process.

--then reboot the database and restore normal.

Conclusion: 1 PostgreSQL 8.3.3 version only provides system function pg_cancel_backend (PID int) kill process, but this function can only kill  Select
   &N bsp;          Query, while Updae,delete DML does not take effect, interested friends can do their own experiments validated;
         
       2 PostgreSQL 8.4 version provides functions Pg_terminate _backend (PID int), this function is more powerful than the former, you can kill
         all kinds of DML (select,update, Delete,drop) operation;
        
       3 for PostgreSQL, It's best not to use kill-9 to kill the user process, because if it's a big update,kill after Postgresql
         It takes a long time to do recovery, and in this recovery process, the database is not available, and at this point Oracle is friendlier,
         The database is still available during database recovery and does not require situations where the database is not available because of Kill-9 user processes.