Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 128E69FBF30
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Mar 2007 17:15:47 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 99173-01-3 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 29 Mar 2007 17:15:19 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.247])
	by postgresql.org (Postfix) with ESMTP id 6398B9FBBCC
	for <pgsql-sql@postgresql.org>; Thu, 29 Mar 2007 16:48:41 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c24so274469ana
	for <pgsql-sql@postgresql.org>; Thu, 29 Mar 2007 12:48:40 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=I2jLIN43HpEs82aFAQ7SKsOa+Tc7SWm046LlRFr4Hl1LZF0NUCaWq4/FAJqX0vBkSbBDSUHflul1lALFz4S8cBhspwA19pcFL+S36YxVKYqwvOhAMPtqZ7T8dTSOWI3IgiiiYaFc3zuizpSDMwbxTdmS6L8kuZoxOX8+edBOR3Q=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=craukPhF52KdpUqin5F4hDC3zzO/R9hZppcIPV0VMNUlXArbWqujP0jBzJqhcdxpWoGa04/sjq/ABFINFofluOzqcs2wIdgKnzxzw6plz3q7uMSHGoffqi7ifgoNsYGrQWVaeMwFSsqqKHcagW2Khay4hDhr+lIU9qpJobNS3CY=
Received: by 10.100.177.16 with SMTP id z16mr778838ane.1175197719723;
	Thu, 29 Mar 2007 12:48:39 -0700 (PDT)
Received: by 10.100.197.13 with HTTP; Thu, 29 Mar 2007 12:48:39 -0700 (PDT)
Message-ID: <a97c77030703291248h1c1e0ab3h37e96b9775bc16d7@mail.gmail.com>
Date: Fri, 30 Mar 2007 01:18:39 +0530
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "Andrew Sullivan" <ajs@crankycanuck.ca>
Subject: Re: Track query status
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20070328214954.GA17104@phlogiston.dyndns.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <7539aebb0703281359v5b56cc59xf9e80ad4a74e51d7@mail.gmail.com>
	<20070328214954.GA17104@phlogiston.dyndns.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/354
X-Sequence-Number: 27992

more methods:

1. select * from pg_stat_activity;
see what all running and pid of your current query also.

2. 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");

make sure that the pid in 1 is not listed as a blockee in result of
above query m, ie The the update is waiting for anything.

3. go the shell , su - postgres ,

strace -p <pid> of the backend got in 1.
observe the system calls , see if anything awkward
there shoud be a lot of writes

4. iostat ofcourse, unless the above sql is the only sql(update)
   running running in server .

my 4cents
regds
mallah.

On 3/29/07, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> On Wed, Mar 28, 2007 at 04:59:24PM -0400, Sumeet wrote:
> > Hi all,
> >
> > I have ran a update query on a dataset which has about 48 million records
> > and the query is already running for the third day....im so tempted to the
> > kill this query now....is there a way to know if the query is running??
> > here is the query i've ran
>
> 48 million records is a lot.  You oughta see activity with iostat or
> something.
>
> A
>
> --
> Andrew Sullivan  | ajs@crankycanuck.ca
> When my information changes, I alter my conclusions.  What do you do sir?
>                 --attr. John Maynard Keynes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>