Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C396D9FA103 for ; Wed, 6 Dec 2006 18:13:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 66160-06 for ; Wed, 6 Dec 2006 18:13:31 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 48C949F9C9E for ; Wed, 6 Dec 2006 18:13:35 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 6 Dec 2006 22:13:27 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 06 Dec 2006 16:13:29 -0600 Subject: Re: VACUUM FULL does not works....... From: Scott Marlowe To: asif ali Cc: pgsql-performance@postgresql.org In-Reply-To: <20061206215301.67204.qmail@web58407.mail.re3.yahoo.com> References: <20061206215301.67204.qmail@web58407.mail.re3.yahoo.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1165443208.3243.17.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 06 Dec 2006 16:13:29 -0600 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/156 X-Sequence-Number: 22212 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...