Re: Query is stuck

From: "Plugge, Joe R(dot)" <JRPlugge(at)west(dot)com>
To: "Satish Burnwal (sburnwal)" <sburnwal(at)cisco(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Query is stuck
Date: 2010-04-13 13:06:37
Message-ID: BD69807DAE0CE44CA00A8338D0FDD08302CF97EA35@oma00cexmbx03.corp.westworlds.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

What version of postgres are you on?

From: Satish Burnwal (sburnwal) [mailto:sburnwal(at)cisco(dot)com]
Sent: Tuesday, April 13, 2010 8:04 AM
To: Plugge, Joe R.; pgsql-general(at)postgresql(dot)org
Cc: pgsql-admin(at)postgresql(dot)org
Subject: RE: Query is stuck

controlsmartdb=# select * from pg_stat_activity where waiting='t';
ERROR: column "waiting" does not exist

From: Plugge, Joe R. [mailto:JRPlugge(at)west(dot)com]
Sent: Tuesday, April 13, 2010 6:32 PM
To: Satish Burnwal (sburnwal); pgsql-general(at)postgresql(dot)org
Cc: pgsql-admin(at)postgresql(dot)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(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Satish Burnwal (sburnwal)
Sent: Tuesday, April 13, 2010 7:58 AM
To: pgsql-general(at)postgresql(dot)org
Cc: pgsql-admin(at)postgresql(dot)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 ?

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Ray Stell 2010-04-13 13:08:01 Re: Query is stuck
Previous Message Satish Burnwal (sburnwal) 2010-04-13 13:03:43 Re: Query is stuck

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2010-04-13 13:07:26 Re: Email address column verification foraddresslist
Previous Message Satish Burnwal (sburnwal) 2010-04-13 13:03:43 Re: Query is stuck