Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E97E963265B for ; Thu, 2 Sep 2010 00:22:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 97907-06 for ; Thu, 2 Sep 2010 03:22:39 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from oproxy2-pub.bluehost.com (oproxy2-pub.bluehost.com [67.222.39.60]) by mail.postgresql.org (Postfix) with SMTP id 8F99E632460 for ; Thu, 2 Sep 2010 00:22:38 -0300 (ADT) Received: (qmail 1372 invoked by uid 0); 2 Sep 2010 03:22:36 -0000 Received: from unknown (HELO box498.bluehost.com) (66.147.242.98) by oproxy2.bluehost.com with SMTP; 2 Sep 2010 03:22:36 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=default; d=consistentstate.com; h=Received:From:Organization:To:Subject:Date:User-Agent:Cc:References:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Content-Disposition:Message-Id:X-Identified-User; b=stchABNnOl1QBystWVHTZ5viHUpgOaNcDHoGNdkoknOBdXIwcItsNsma5EjnmjpVhFRZ00aYPLxFgLjljL30x4M8GmGXfb6h2AxFngiBnTeFuhEdWd3+ji7YlADjzlKq; Received: from c-75-71-23-162.hsd1.co.comcast.net ([75.71.23.162] helo=[192.168.1.61]) by box498.bluehost.com with esmtpsa (SSLv3:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Or0No-0000vJ-KZ; Wed, 01 Sep 2010 21:22:36 -0600 From: Kevin Kempter Organization: Consistent State To: Tom Lane Subject: Re: dropping constraints Date: Wed, 1 Sep 2010 21:24:30 -0600 User-Agent: KMail/1.9.4 Cc: pgsql-admin@postgresql.org References: <201009012058.05142.cs_dba@consistentstate.com> <12512.1283397635@sss.pgh.pa.us> In-Reply-To: <12512.1283397635@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <201009012124.30922.cs_dba@consistentstate.com> X-Identified-User: {3049:box498.bluehost.com:consist3:consistentstate.com} {sentby:smtp auth 75.71.23.162 authed with kevink+consistentstate.com} X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201009/8 X-Sequence-Number: 35552 ] > Kevin Kempter writes: > > we have a few not null constraints we want to drop on very large tables. > > Unfortunately the drop's are taking hours. Is there a way to drop a not > > null constraint via the system catalogs? > > Dropping a NOT NULL constraint is a trivial operation. The problem is > probably that you are blocked waiting to get a lock on the table. > Look for idle-in-transaction sessions that are holding locks on the > target table. > > regards, tom lane thanks