X-Original-To: pgsql-hackers-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 3E21B9F9FE2 for ; Thu, 14 Sep 2006 20:40:42 -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 13690-08 for ; Thu, 14 Sep 2006 20:40:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241]) by postgresql.org (Postfix) with ESMTP id 641499F9F7A for ; Thu, 14 Sep 2006 20:40:32 -0300 (ADT) Received: from [209.162.219.253] (helo=dogma.v10.wvs) by servata.com with esmtpa (Exim 4.50) id 1GO0oZ-0005U6-8P; Thu, 14 Sep 2006 16:40:15 -0700 Subject: Re: [ADMIN] Vacuum error on database postgres From: Jeff Davis To: Tom Lane Cc: andy , Alvaro Herrera , pgsql-hackers@postgreSQL.org In-Reply-To: <16508.1158272742@sss.pgh.pa.us> References: <44F82150.5000809@pnlassociates.com> <4001.1157121669@sss.pgh.pa.us> <27602.1158238299@sss.pgh.pa.us> <45095CDD.8090704@squeakycode.net> <29469.1158247230@sss.pgh.pa.us> <1158271045.29889.143.camel@dogma.v10.wvs> <16508.1158272742@sss.pgh.pa.us> Content-Type: text/plain Date: Thu, 14 Sep 2006 16:40:17 -0700 Message-Id: <1158277217.29889.146.camel@dogma.v10.wvs> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.234 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_PASS X-Spam-Level: X-Archive-Number: 200609/1198 X-Sequence-Number: 90727 On Thu, 2006-09-14 at 18:25 -0400, Tom Lane wrote: > Jeff Davis writes: > > How would creating a new lock type avoid deadlocks when an ANALYZE is > > accumulating the locks in random order? > > In itself it wouldn't. Josh Drake sketched the idea in more detail > later: if there is a lock type used *only* for ANALYZE, then you can do > ConditionalLockAcquire on it, and if you fail, skip the table on the > assumption that someone else is already doing what you came to do. > > The whole thing seems a bit too cute/complicated though; it'd open > various corner cases such as: ANALYZE, run complex query, query takes a > week because it's using out-of-date stats because previous ANALYZE-r > hadn't committed yet. I'd rather ANALYZE always analyzed than sometimes > fell through without doing anything. > Couldn't you just sort by the table names, and ANALYZE the tables in that order? Would that effectively prevent the deadlocks? Regards, Jeff Davis