Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 66A6C2E3425 for ; Tue, 8 Jan 2008 11:25:17 -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 35970-07 for ; Tue, 8 Jan 2008 11:25:01 -0400 (AST) Received: from ro-out-1112.google.com (ro-out-1112.google.com [72.14.202.181]) by postgresql.org (Postfix) with ESMTP id 257B62E33CF for ; Tue, 8 Jan 2008 11:25:10 -0400 (AST) Received: by ro-out-1112.google.com with SMTP id p4so2425522roc.5 for ; Tue, 08 Jan 2008 07:25:08 -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=+eQdt+dGkeNRcoWDw2ewhNU+hmFUBwa2nmMAwRe4G34=; b=YB1aGtUyBAazfoA5kxzaR1dsYTjZMpLCiJjtvbJsqIPk3GHdVn0/GWQtJeI0VZP1mJtpIah52BXMHE9GfBTWV3Zs7qELe/TY/SK4+SQ0Xle1LRqZSEejJbiq0t3WSBlDKBc8+OE64Dgs4rWLERkF053oDLa6T51/90PzWyNtwv4= 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=DhSWecbz/sWIHXK30Er+n/lP0Pt0bz8ji5hAKqFiVnEVNF6PJo6CC+39c0QNaQzKxtapF8AdLb6YR2S/2HGFSHy6CxrmKabfELubhMe0f7YRCkH19rq+NBxaXa/WPGiUpoPmR3OaSTu373IjoFc5IKk1/Z+VQ07TbuWhjvHg8xg= Received: by 10.114.167.2 with SMTP id p2mr6660543wae.78.1199805907580; Tue, 08 Jan 2008 07:25:07 -0800 (PST) Received: by 10.114.131.4 with HTTP; Tue, 8 Jan 2008 07:25:07 -0800 (PST) Message-ID: Date: Tue, 8 Jan 2008 09:25:07 -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: <3D5445983859B84B92669C0D883EA6FB0BB94E30@ZENMAILHQ1.ind.zensar.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <3D5445983859B84B92669C0D883EA6FB0BB94E30@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/82 X-Sequence-Number: 27856 On Jan 8, 2008 1:39 AM, Suresh Gupta VG wrote: > > Thanks Scott for your reply. > > Actually, our application will extract all the data from currency table and > never uses that table later at all. I don't know which process is locking > that table. Pls find the attached file for the list of locks available on > our Pgsql. Our database is restarted recently last 2 days back. No fruitful > result has come. Then whatever is causing your problems is happening right after you turn it back on. > Pls advice us why it is happening. And our database is taking more than 5 > minutes to execute a simple query directly on the database at the peak > timings say (9:00 Am to 11:00 AM). It's your database it's happening in, so the why is hidden away in there. All I can do is help you find it, as I'm not looking at it myself... :) > Pls advice and suggest us what to do. Try this query: select pgc.relname, pgl2.relation, pgl.mode 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); and look for modes that are ExclusiveLock and the like. Shared stuff isn't bad. So, I'm guessing that something you're doing with your database is taking out these locks. DDL in a long running transaction, that kind of thing.