From mallah@tradeindia.com Wed Jan 18 07:12:14 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 975239DC9BC for ; Wed, 18 Jan 2006 03:12:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56698-06 for ; Wed, 18 Jan 2006 03:12:13 -0400 (AST) X-Greylist: delayed 00:06:51.023857 by SQLgrey- Received: from tradeindia.com (server3.trade-india.com [216.247.238.132]) by postgresql.org (Postfix) with SMTP id 915869DC9B3 for ; Wed, 18 Jan 2006 03:12:11 -0400 (AST) Received: (qmail 354 invoked from network); 18 Jan 2006 06:43:00 -0000 Received: from infocom-82-154-16-del.trade-india.com (HELO tradeindia.com) (61.16.154.82) by server3.trade-india.com with SMTP; 18 Jan 2006 06:43:00 -0000 Received: (qmail 12986 invoked from network); 18 Jan 2006 06:41:51 -0000 Received: from unknown (HELO ?10.100.100.100?) (10.100.100.100) by 10.100.100.66 with SMTP; 18 Jan 2006 06:41:51 -0000 Message-ID: <43CDE33D.3070702@tradeindia.com> Date: Wed, 18 Jan 2006 12:12:05 +0530 From: Rajesh Kumar Mallah User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres Admin List Subject: killing a query safely Content-Type: multipart/alternative; boundary="------------070706060706030109050608" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/224 X-Sequence-Number: 20312 This is a multi-part message in MIME format. --------------070706060706030109050608 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, many a times the backend are stubborn. A backend is currently executing a Query , the query usually finishes quickly < 5 secs. But this query is on for past many minutes. I want to debug why it is hung if it is hung. I have also tried to kill this query using following methods: SQL> SELECT pg_cancel_backend(27649); # does not kill # kill 27649 # does not kill # kill -TERM 27649 # does not kill # ps auxwww| grep 27649 postgres 27649 0.0 1.9 421060 81576 ? *S* 10:41 0:00 postgres: tradein tradein_clients 216.247.238.131(59261) SELECT this query does not block any other tradein_clients=# SELECT * from blockers; +---------+---------+ | blocker | blockee | +---------+---------+ +---------+---------+ (0 rows) \d blockers View "public.blockers" +---------+---------+-----------+ | Column | Type | Modifiers | +---------+---------+-----------+ | blocker | integer | | | blockee | integer | | +---------+---------+-----------+ View definition:*(Courtesy: Tom Lane)* SELECT h.pid AS blocker, w.pid AS blockee FROM ONLY pg_locks h, ONLY pg_locks w WHERE h."granted" AND NOT w."granted" AND (h.relation = w.relation AND h."database" = w."database" OR h."transaction" = w."transaction"); Can anyone please guide what should be done in such situations. Regds mallah. -- Best Regards, regds Mallah. Rajesh Kumar Mallah +---------------------------------------------------+ | Tradeindia.com (3,97,300) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---------------------------------------------------+ --------------070706060706030109050608 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,

many a times the backend are stubborn. A backend is currently
executing a Query , the query usually finishes quickly < 5 secs.

But this query is on for past many minutes. I want to debug why
it is hung if it is hung.

I have also tried to kill this query using following methods:

SQL> SELECT  pg_cancel_backend(27649); # does not kill

# kill
27649                           # does not kill
# kill -TERM
27649                     # does not kill

# ps auxwww| grep  27649
postgres 27649  0.0  1.9 421060 81576 ?      S    10:41   0:00 postgres: tradein tradein_clients 216.247.238.131(59261) SELECT


this query does not block any other

tradein_clients=# SELECT * from blockers;
+---------+---------+
| blocker | blockee |
+---------+---------+
+---------+---------+
(0 rows)

\d blockers
     View "public.blockers"
+---------+---------+-----------+
| Column  |  Type   | Modifiers |
+---------+---------+-----------+
| blocker | integer |           |
| blockee | integer |           |
+---------+---------+-----------+
View definition:
(Courtesy: Tom Lane)
 SELECT h.pid AS blocker, w.pid AS blockee
   FROM ONLY pg_locks h, ONLY pg_locks w
  WHERE h."granted" AND NOT w."granted" AND (h.relation = w.relation AND h."database" = w."database" OR h."transaction" = w."transaction");



Can anyone please guide what should be done in such situations.

Regds
mallah.
-- 

Best Regards,



regds
Mallah.

Rajesh Kumar Mallah
+---------------------------------------------------+
| Tradeindia.com  (3,97,300) Registered Users 	    | 
| Indias' Leading B2B eMarketPlace                  |
| http://www.tradeindia.com/			    |
+---------------------------------------------------+
--------------070706060706030109050608-- From tgl@sss.pgh.pa.us Thu Jan 19 20:42:48 2006 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C3AFE9DCAEE for ; Thu, 19 Jan 2006 16:42:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 98067-05 for ; Thu, 19 Jan 2006 16:42:49 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 01E019DCACB for ; Thu, 19 Jan 2006 16:42:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0JKglQU008801; Thu, 19 Jan 2006 15:42:47 -0500 (EST) To: Rajesh Kumar Mallah cc: Postgres Admin List Subject: Re: killing a query safely In-reply-to: <43CDE33D.3070702@tradeindia.com> References: <43CDE33D.3070702@tradeindia.com> Comments: In-reply-to Rajesh Kumar Mallah message dated "Wed, 18 Jan 2006 12:12:05 +0530" Date: Thu, 19 Jan 2006 15:42:47 -0500 Message-ID: <8800.1137703367@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.093 required=5 tests=[AWL=0.093] X-Spam-Score: 0.093 X-Spam-Level: X-Archive-Number: 200601/228 X-Sequence-Number: 20316 Rajesh Kumar Mallah writes: > many a times the backend are stubborn. A backend is currently > executing a Query , the query usually finishes quickly < 5 secs. > But this query is on for past many minutes. I want to debug why > it is hung if it is hung. Attach to the backend process with gdb and get a stack trace --- that would at least give us some information to work with. regards, tom lane