DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=pQBRgFOjYrF6DJ/6W7ai5sA/o0vdpq3YAVlvCpup1bjOYXg9veWzuF2ZkcX6xp8Two446zrUeZw6lJ1owUmodI0iCtq3wy/MwCNheAktNWgAQw8qzWbbvWRB3niTZ2pnLyB4orYl3fYD3ejs7MIpNrPVcKRsaPT3r0T3/1kpppY=
Message-ID: <a97c77030612062258u6ecff1e1hb38ec91eb827115d@mail.gmail.com>
Date: Thu, 7 Dec 2006 12:28:37 +0530
From: "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com>
To: "asif ali" <asif_icrossing@yahoo.com>
Subject: Re: VACUUM FULL does not works.......
Cc: pgsql-performance@postgresql.org
In-Reply-To: <325721.59076.qm@web58411.mail.re3.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1165443208.3243.17.camel@state.g2switchworks.com>
	<325721.59076.qm@web58411.mail.re3.yahoo.com>

We have a view in our database.


CREATE view public.hogs AS
SELECT pg_stat_activity.procpid, pg_stat_activity.usename,
pg_stat_activity.current_query
   FROM ONLY pg_stat_activity;

Select current_query from public.hogs helps us to spot errant queries
at times.


regds
mallah.


On 12/7/06, asif ali <asif_icrossing@yahoo.com> wrote:
> Thanks Scott,
>  It worked!!!
>  We killed an old idle running transaction, now everything is fine..
>
>  Thanks Again
>  asif ali
>  icrossing inc
>
>
> Scott Marlowe <smarlowe@g2switchworks.com> wrote:
>  On Wed, 2006-12-06 at 15:53, asif ali wrote:
> > Thanks Everybody for helping me out.
> > I checked "pg_stat_activity"/pg_locks, but do not see any activity on
> > the table.
> > How to find a old running transaction...
> > I saw this link, but it did not help..
> >
> http://archives.postgresql.org/pgsql-hackers/2005-02/msg00760.php
>
> Sometimes just using top or ps will show you.
>
> on linux you can run top and then hit c for show command line and look
> for ones that are IDLE
>
> Or, try ps:
>
> ps axw|grep postgres
>
> On my machine normally:
>
>  2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
> /home/postgres/data
>  2615 ? S 0:00 postgres: stats buffer process
>  2616 ? S 0:00 postgres: stats collector process
>  2857 ? S 0:00 postgres: writer process
>  2858 ? S 0:00 postgres: stats buffer process
>  2859 ? S 0:00 postgres: stats collector process
>
> But with an idle transaction:
>
>  2408 ? S 0:00 /usr/local/pgsql/bin/postmaster -p 5432 -D
> /home/postgres/data
>  2615 ? S 0:00 postgres: stats buffer process
>  2616 ? S 0:00 postgres: stats collector process
>  2857 ? S 0:00 postgres: writer process
>  2858 ? S 0:00 postgres: stats buffer process
>  2859 ? S 0:00 postgres: stats collector process
>  8679 ? S 0:00 postgres: smarlowe test [local] idle in transaction
>
> Thar she blows!
>
> Also, you can restart the database and vacuum it then too. Of course,
> don't do that during regular business hours...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
>
>  ________________________________
> Have a burning question? Go to Yahoo! Answers and get answers from real
> people who know.
>
>