X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5DECE9FB274 for ; Tue, 15 Aug 2006 14:27:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 21558-08 for ; Tue, 15 Aug 2006 17:27:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by postgresql.org (Postfix) with ESMTP id 32B739FB1FB for ; Tue, 15 Aug 2006 14:27:29 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1GD2hL-00021r-00; Tue, 15 Aug 2006 13:27:27 -0400 To: PostgreSQL Admin Discussion Subject: dropping partitioned table waits forever From: Gregory Stark Organization: EnterpriseDB Date: 15 Aug 2006 13:27:26 -0400 Message-ID: <87u04dzz7l.fsf@stark.xeocode.com> Lines: 43 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.384 tagged_above=0 required=5 tests=SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200608/165 X-Sequence-Number: 22708 Hm, in my experiments what's happening is that dropping a table needs an AccessExclusiveLock on the table and any open transactions that have read from that table will have at least an AccessShareLock on that table which blocks the schema change. But I don't think merely having a trigger will hold a lock. Will it? Anyone? I think you need an actual open transaction that has accessed the table. Is it possible you have some transactions that are live for an extended period? Either large queries that are still running or just some non-autocommit transaction sitting waiting for user input before committing? You could investigate by doing select * from pg_lock where not granted; Pick out the one looking for an AccessExclusiveLock, its pid should match the pid of the backend where you're doing the DROP. Look at the relation column and do select * from pg_lock where relation = NNN where NNN is the relation of the nongranted lock. Look at what type of lock is being held by that's blocking the partition drop and what the pid of those backends are. You can see what command they're executing by using a tool like pg_admin to look at the stats command buffer or just by looking at ps usually. 8.2 will allow you to move a partition out of an inheritance structure instead of dropping it entirely. Sadly it takes the same lock so it won't help you here. I wonder if it really needs such a strong lock. If it dropped the inheritance structure without locking it would open up some strange cases though. Like, you wouldn't know just because you had committed that other queries weren't still running that think your table is still a partition in the partitioned table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com