From: Jaiswal Dhaval Sudhirkumar <JaiswalDS@microland.com>
To: Satish Burnwal \(sburnwal\) <sburnwal@cisco.com>
To: Plugge, Joe R. <JRPlugge@west.com>
To: pgsql-general@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] Query is stuck
Date: Tue, 13 Apr 2010 18:41:02 +0530
Message-ID: <F260108AB230D44CBAA660B47CC4110A07F71160@blrisbe01.MLCORP.NET> (raw)
In-Reply-To: <3A8C969225424C4D8E6BEE65ED8552DA0119F18F@XMB-BGL-41C.cisco.com>
References: <3A8C969225424C4D8E6BEE65ED8552DA0119F188@XMB-BGL-41C.cisco.com>
<BD69807DAE0CE44CA00A8338D0FDD08302CF97EA2F@oma00cexmbx03.corp.westworlds.com>
<3A8C969225424C4D8E6BEE65ED8552DA0119F18F@XMB-BGL-41C.cisco.com>
select procpid, current_query,query_start - now(), backend_start
from pg_stat_activity
where current_query not like '%IDLE%' and waiting = 't';
--
Thanks
Dhaval
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
controlsmartdb=# select * from pg_stat_activity where waiting='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='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=# 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 =
(select max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = 'u1';
QUERY PLAN
------------------------------------------------------------------------
----------------------------------------------------
Seq Scan on repcopy a (cost=0.00..1630178118.35 rows=35 width=133)
Filter: ((report_status = 0) AND ((dm_user)::text = 'u1'::text) AND
(report_time = (subplan)))
SubPlan
-> Aggregate (cost=8151.65..8151.66 rows=1 width=8)
-> Seq Scan on repcopy b (cost=0.00..8151.65 rows=1
width=8)
Filter: ((($0)::text = (dm_ip)::text) AND (($1)::text =
(dm_user)::text) AND ((ss_key)::text <> ''::text))
(6 rows)
But EXPLAIN ANALYSE query hangs (is not giving me any output even 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 = (select
max(report_time) from repcopy as b where a.dm_ip = b.dm_ip and
a.dm_user=b.dm_user and b.ss_key != '') and report_status = 0 and
dm_user = '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 material.
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.
If you received this in error, please contact the sender and delete the material from your computer.
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses.
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: JaiswalDS@microland.com, sburnwal@cisco.com, JRPlugge@west.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query is stuck
In-Reply-To: <F260108AB230D44CBAA660B47CC4110A07F71160@blrisbe01.MLCORP.NET>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox