X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 5935DD1CCCD for ; Fri, 27 Feb 2004 16:56:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 42859-01 for ; Fri, 27 Feb 2004 12:56:24 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 9FC23D1B4DE for ; Fri, 27 Feb 2004 12:56:19 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1RGuE5l020163; Fri, 27 Feb 2004 11:56:14 -0500 (EST) To: Rajesh Kumar Mallah Cc: pgsql-admin@postgresql.org Subject: Re: ALTER table taking ages... In-reply-to: <403EF608.7040005@trade-india.com> References: <403EF608.7040005@trade-india.com> Comments: In-reply-to Rajesh Kumar Mallah message dated "Fri, 27 Feb 2004 13:17:20 +0530" Date: Fri, 27 Feb 2004 11:56:13 -0500 Message-ID: <20162.1077900973@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/383 X-Sequence-Number: 12556 Rajesh Kumar Mallah writes: > It takes ages to drop a constraint from one of my tables > [ table details at the end ] I cannot insert into it also. DROP CONSTRAINT in itself isn't going to take any significant amount of time. The only plausible explanation is that some other session has a lock on the table, forcing the DROP to wait. DROP is going to want access-exclusive lock on the table, so pretty much anything will block it. > tradein_clients=# SELECT * from pg_locks where mode='ExclusiveLock' and > granted is true; This is unhelpful. In the first place there are multiple kinds of lock and you've shown only one. In the second place, the entries you want to start from are the ones with granted not true, because that indicates someone waiting for a lock. Try something like select h.pid as blocker, w.pid as blockee from pg_locks h, 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); and then look in pg_stat_activity to find out what each process is doing. regards, tom lane