Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id BDD3B633F01; Tue, 13 Apr 2010 10:28:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06050-04-5; Tue, 13 Apr 2010 13:28:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.microland.net (smtp.microland.net [203.90.4.139]) by mail.postgresql.org (Postfix) with ESMTP id 4F32B635E14; Tue, 13 Apr 2010 10:23:31 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.52,197,1270405800"; d="scan'208,217";a="5255131" Received: from unknown (HELO blrisbe01.MLCORP.NET) ([172.21.2.3]) by smtp.microland.net with ESMTP; 13 Apr 2010 18:38:48 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CADB0A.C9034F66" Subject: Re: [GENERAL] Query is stuck Date: Tue, 13 Apr 2010 18:41:02 +0530 Message-ID: In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F18F@XMB-BGL-41C.cisco.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [GENERAL] Query is stuck thread-index: AcrbCP0t/Bwv/Cx7SLCehFmN3ClszwAAGzWAAAATTcAAADogMA== References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com> <3A8C969225424C4D8E6BEE65ED8552DA0119F18F@XMB-BGL-41C.cisco.com> From: "Jaiswal Dhaval Sudhirkumar" To: "Satish Burnwal \(sburnwal\)" , "Plugge, Joe R." , Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/158 X-Sequence-Number: 34252 This is a multi-part message in MIME format. ------_=_NextPart_001_01CADB0A.C9034F66 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable select procpid, current_query,query_start - now(), backend_start from pg_stat_activity where current_query not like '%IDLE%' and waiting =3D 't'; =20 -- Thanks=20 Dhaval =20 From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Satish Burnwal (sburnwal) Sent: Tuesday, April 13, 2010 2:04 PM To: Plugge, Joe R.; pgsql-general@postgresql.org Cc: pgsql-admin@postgresql.org Subject: Re: [GENERAL] Query is stuck =20 controlsmartdb=3D# select * from pg_stat_activity where waiting=3D't'; ERROR: column "waiting" does not exist =20 From: Plugge, Joe R. [mailto:JRPlugge@west.com]=20 Sent: Tuesday, April 13, 2010 6:32 PM To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org Cc: pgsql-admin@postgresql.org Subject: RE: Query is stuck =20 What do you get when you run this? =20 select * from pg_stat_activity where waiting=3D't'; =20 =20 =20 From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Satish Burnwal (sburnwal) Sent: Tuesday, April 13, 2010 7:58 AM To: pgsql-general@postgresql.org Cc: pgsql-admin@postgresql.org Subject: [ADMIN] Query is stuck =20 I have a query which is not giving me the result even after 30 minutes. I want to know how to detect what is going and what's wrong ?=20 =20 EXPLAIN query - gives me the following: controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a where report_time =3D (select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1'; QUERY PLAN ------------------------------------------------------------------------ ---------------------------------------------------- Seq Scan on repcopy a (cost=3D0.00..1630178118.35 rows=3D35 width=3D133) Filter: ((report_status =3D 0) AND ((dm_user)::text =3D 'u1'::text) AND (report_time =3D (subplan))) SubPlan -> Aggregate (cost=3D8151.65..8151.66 rows=3D1 width=3D8) -> Seq Scan on repcopy b (cost=3D0.00..8151.65 rows=3D1 width=3D8) Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text = =3D (dm_user)::text) AND ((ss_key)::text <> ''::text)) (6 rows) =20 But EXPLAIN ANALYSE query hangs (is not giving me any output even after 30 minutes). =20 Pg_stat_activity shows this - SELECT procpid, usename, current_query, query_start from pg_stat_activity: 2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_os, report_time, sys_name, sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a where report_time =3D (select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30 =20 =20 In such a case what can I do ?=20 =20 The information transmitted is intended only for the person or entity to wh= ich it is addressed and may contain confidential and/or privileged material= .=20 Any review, re-transmission, dissemination or other use of or taking of any= action in reliance upon,this information by persons or entities other than= the intended recipient is prohibited.=20 If you received this in error, please contact the sender and delete the mat= erial from your computer.=20 Microland takes all reasonable steps to ensure that its electronic communic= ations are free from viruses.=20 However, given Internet accessibility, the Company cannot accept liability = for any virus introduced by this e-mail or any attachment and you are advis= ed to use up-to-date virus checking software.=20 ------_=_NextPart_001_01CADB0A.C9034F66 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

select procpid, current_query,query_start - now(= ), backend_start

from pg_stat_activity

where current_query not like '%IDLE%' and waiting =3D 't';

 

--

Thanks

Dhaval

 =

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.o= rg] On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 2:04 PM
To: Plugge, Joe R.; pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Query is stuck

 

controlsmartdb=3D# select * from pg_stat_activity where waiting= =3D't';

ERROR:  column "waiting" does not exist

 

From: Plugge, Joe R. [mailto:JRPlugge@west.com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: RE: Query is stuck

 

What do you get when you run this?

 

select * from pg_stat_activity where waiting=3D't';=

 

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] = On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: [ADMIN] Query is stuck

 

I have a query which is not giving me the result even after 30 minutes. I want to know how to detect what is going and what’s wrong = ?

 

EXPLAIN query - gives me the following:

controlsmartdb=3D# explain select report_id, dm_ip, dm_mac, dm_= user, dm_os, report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a where report_time =3D (s= elect max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_= user =3D 'u1';

          &nb= sp;            =             &nb= sp;            =          QUERY PLAN

---------------------------------------------------------------= -------------------------------------------------------------

 Seq Scan on repcopy a  (cost=3D0.00..1630178118.35 r= ows=3D35 width=3D133)

   Filter: ((report_status =3D 0) AND ((dm_user)::tex= t =3D 'u1'::text) AND (report_time =3D (subplan)))

   SubPlan

     ->  Aggregate  (cost=3D8151.65..8151.66 rows=3D1 width=3D8)

           ->  Seq Scan on repcopy b  (cost=3D0.00..8151.65 rows=3D1 widt= h=3D8)

          &nb= sp;      Filter: ((($0)::text =3D (dm_ip)::text) AND (($1)::text =3D (dm_user)::text= ) AND ((ss_key)::text <> ''::text))

(6 rows)

 

But EXPLAIN ANALYSE query hangs (is not giving me any output ev= en after 30 minutes).

 

Pg_stat_activity shows this - SELECT procpid, usename, current_query, query_start from pg_stat_activity:

2942 | postgres | select report_id, dm_ip, dm_mac, dm_user, dm_= os, report_time, sys_name,  sys_user, sys_user_domain, ss_key, login_time, role_id, new_vlan_id from repcopy as a where report_time =3D (select max(report_time) from repcopy as b where a.dm_ip =3D b.dm_ip and a.dm_user=3Db.dm_user and b.ss_key !=3D '') and report_status =3D 0 and dm_= user =3D 'u1'; | 2010-04-13 18:20:02.828623+05:30

 

 

In such a case what can I do ?

 

The information transmitted is intended only for the person or entity =
to which it is addressed and may contain confidential and/or privileged mat=
erial.=20
Any review, re-transmission, dissemination or other use of or taking of any=
 action in reliance upon,this information by persons or entities other than=
 the intended recipient is prohibited.=20
If you received this in error, please contact the sender and delete the mat=
erial from your computer.=20
Microland takes all reasonable steps to ensure that its electronic communic=
ations are free from viruses.=20
However, given Internet accessibility, the Company cannot accept liability =
for any virus introduced by this e-mail or any attachment and you are advis=
ed to use up-to-date virus checking software.=20
------_=_NextPart_001_01CADB0A.C9034F66--