Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 3AE4C2E0247 for ; Fri, 4 Jan 2008 12:33:50 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 33531-03 for ; Fri, 4 Jan 2008 12:33:46 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by postgresql.org (Postfix) with ESMTP id 145512E00F7 for ; Fri, 4 Jan 2008 12:33:44 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so10834511waf.5 for ; Fri, 04 Jan 2008 08:33:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; 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; bh=wQ6A2FFfZP+rHeyB33r1aePKpcXnY8E07q4Ma+zOFxo=; b=NJ1Tl6r0RsentgQF/inie7mnWDnhZ0/aYAzgpNrG/oSYFPN7fUynmF1GQOsm/ly4CvcaMZuHrV0ZcpHFSExERIouis5F53Z0VXMZPm22mWPRbCqKcdwPnbK5hEim0MT3emlkJc6joHp/jPkeeSFraiEy6Xx202Fs/UZHwxiAV5c= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nCuePripTTKPVhCmpqRDhT9HRx7Ju6ouAdFIimPUPKjmsJew2+8THo6IijwwoMT1KE+7hGQ9BVEy8ycTTCZYdd6giiqFOdeNu/YgxrqzvFE1AnmWiREvceG2RnY1O0JwprS20szJv32ey/Cq9ZGWCdr8PakKSDxVFrFnpP5/wDo= Received: by 10.115.109.1 with SMTP id l1mr18785514wam.136.1199464422070; Fri, 04 Jan 2008 08:33:42 -0800 (PST) Received: by 10.114.131.4 with HTTP; Fri, 4 Jan 2008 08:33:42 -0800 (PST) Message-ID: Date: Fri, 4 Jan 2008 10:33:42 -0600 From: "Scott Marlowe" To: "Suresh Gupta VG" Subject: Re: Need some info on Postgresql Cc: "Peter Koczan" , pgsql-admin@postgresql.org In-Reply-To: <3D5445983859B84B92669C0D883EA6FB0BB35BFE@ZENMAILHQ1.ind.zensar.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3D5445983859B84B92669C0D883EA6FB0BB35BFE@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/63 X-Sequence-Number: 27837 On Jan 4, 2008 5:14 AM, Suresh Gupta VG wrote: > > Hi Scott, > > Thanks for your reply. > > I got the result for the query select * from pg_locks; as below. Can you > explain me the output please. > > relation | database | transaction | pid | mode | granted > > ----------+----------+-------------+-------+-----------------+--------- > | | 7784829 | 21222 | ExclusiveLock | t > 87441 | 87343 | | 21173 | AccessShareLock | t > 87441 | 87343 | | 21044 | AccessShareLock | t > 87441 | 87343 | | 21209 | AccessShareLock | t > | | 7782935 | 20942 | ExclusiveLock | t > | | 7784034 | 21106 | ExclusiveLock | t > | | 7784091 | 21112 | ExclusiveLock | t > 87441 | 87343 | | 21197 | AccessShareLock | t > | | 7783246 | 20989 | ExclusiveLock | t > 87441 | 87343 | | 21081 | AccessShareLock | t > | | 7783542 | 21034 | ExclusiveLock | t > | | 7782957 | 20944 | ExclusiveLock | t > | | 7785128 | 21268 | ExclusiveLock | t > 87441 | 87343 | | 21228 | AccessShareLock | t > 87441 | 87343 | | 20971 | AccessShareLock | t > | | 7783524 | 21032 | ExclusiveLock | t > | | 7784237 | 21136 | ExclusiveLock | t > 87441 | 87343 | | 21063 | AccessShareLock | t > 87441 | 87343 | | 20952 | AccessShareLock | t > | | 7784975 | 21247 | ExclusiveLock | t > 87441 | 87343 | | 21222 | AccessShareLock | t > | | 7783940 | 21092 | ExclusiveLock | t > | | 7783596 | 21044 | ExclusiveLock | t > | | 7783670 | 21052 | ExclusiveLock | t > | | 7784165 | 21124 | ExclusiveLock | t > 87441 | 87343 | | 21046 | AccessShareLock | t > 87441 | 87343 | | 21164 | AccessShareLock | t > | | 7784793 | 21218 | ExclusiveLock | t > 87441 | 87343 | | 21247 | AccessShareLock | t > 87441 | 87343 | | 21278 | AccessShareLock | t > | | 7784393 | 21158 | ExclusiveLock | t > | | 7783363 | 21008 | ExclusiveLock | t > 87441 | 87343 | | 21249 | AccessShareLock | t > 87441 | 87343 | | 21280 | AccessShareLock | t > | | 7785374 | 21303 | ExclusiveLock | t > | | 7783707 | 21061 | ExclusiveLock | t > 87441 | 87343 | | 21147 | AccessShareLock | t > 87441 | 87343 | | 21304 | AccessShareLock | t Look up those relation numbers as oid against pg_class. Note that I'm running 8.2 and haven't run 7.4 seriously in over two years, so some of this might not match up. A query like this works in 8.2: select pgc.relname, pgl2.relation from pg_locks pgl join pg_locks pgl2 on (pgl.relation is null AND pgl2.relation is not null and pgl.pid=pgl2.pid) join pg_class pgc on (pgl2.relation=pgc.oid); Like I said, no guarantees that will run on 7.4 What you're looking for is something holding an exclusive lock on the table you're trying to reindex. But it doesn't sound as if that table is bloated to me, so I'm guessing something else is wrong.