Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id DBD8E9FB38B for ; Fri, 29 Sep 2006 11:45:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 54480-06 for ; Fri, 29 Sep 2006 11:45:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 3BEE59FB387 for ; Fri, 29 Sep 2006 11:45:10 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8TEj707015997; Fri, 29 Sep 2006 10:45:07 -0400 (EDT) To: "Sriram Dandapani" cc: "Matthew T. O'Connor" , pgsql-admin@postgresql.org Subject: Re: autovacuum ignore tables In-reply-to: <6992E470F12A444BB787B5C937B9D4DF060E5B49@ca-mail1.cis.local> References: <6992E470F12A444BB787B5C937B9D4DF060E5B49@ca-mail1.cis.local> Comments: In-reply-to "Sriram Dandapani" message dated "Thu, 28 Sep 2006 23:40:35 -0700" Date: Fri, 29 Sep 2006 10:45:07 -0400 Message-ID: <15996.1159541107@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.096 tagged_above=0 required=5 tests=AWL, SPF_HELO_PASS, SPF_PASS X-Spam-Level: X-Archive-Number: 200609/365 X-Sequence-Number: 23279 "Sriram Dandapani" writes: > The only issue I have with autovacuum is the fact that I have to briefly > stop/restart postgres every couple of days, which kills autovacuum and > it has no memory of previous work done. As already stated, this isn't true as of 8.1. > I work with several databases > with partitioned tables having high daily volume. Dropping partitioned > tables locks out jdbc inserts and the drop command itself goes into a > WAIT state. Hence, I have to stop postgres,update pg_hba.conf to prevent > access,restart postgres,drop tables and update pg_hba to allow > access(Crazy, but I have no choice because Postgres deadlocks on drop > child tables while inserts happen on the parent) It sounds to me like you have a problem with failing to commit transactions promptly. The DROP will wait for existing transactions to release their locks on the doomed table, but its lock request will block any incoming transactions that try to acquire new locks on the table. So basically you should see a hiccup of length equal to the longest normal transaction using that table. In a well-designed concurrent system that should not be a problem. It might be worth doing the DROP as begin; lock table parent_table; drop table child_table; commit; so that SELECTs on the parent do simply block and don't risk getting errors from trying to access a just-dropped child table. regards, tom lane