X-Original-To: pgsql-hackers-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 97D459FB1D7 for ; Thu, 14 Sep 2006 23:15:04 -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 30773-02 for ; Fri, 15 Sep 2006 02:14:53 +0000 (UTC) X-Greylist: delayed 00:29:33.071221 by SQLgrey- Received: from marajade.camavision.com (unknown [63.228.164.252]) by postgresql.org (Postfix) with ESMTP id 3416C9F9F66 for ; Thu, 14 Sep 2006 23:14:53 -0300 (ADT) Received: from [192.168.1.4] (unknown [12.206.226.59]) (Authenticated sender: andy) by marajade.camavision.com (Postfix) with ESMTP id E2E45F4039; Thu, 14 Sep 2006 20:45:25 -0500 (CDT) Message-ID: <450A059D.4090400@squeakycode.net> Date: Thu, 14 Sep 2006 20:45:01 -0500 From: andy User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: Tom Lane CC: Jeff Davis , Alvaro Herrera , pgsql-hackers@postgreSQL.org Subject: Re: [ADMIN] Vacuum error on database postgres 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> <1158277217.29889.146.camel@dogma.v10.wvs> <17022.1158277858@sss.pgh.pa.us> In-Reply-To: <17022.1158277858@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/1346 X-Sequence-Number: 90875 Tom Lane wrote: > Jeff Davis writes: >> Couldn't you just sort by the table names, and ANALYZE the tables in >> that order? Would that effectively prevent the deadlocks? > > That'd work too, I think (I suggested the variant of ordering by OID, > which is simpler and more reliable). Not sure if it's really worth the > trouble though --- how many people do you think are doing concurrent > whole-database ANALYZEs inside transaction blocks? > > As-is the code will do the analyzes in pg_class physical row order, > which is almost good enough --- only if someone did a schema change that > forced a pg_class row update between the starts of the two ANALYZE runs > would it possibly fail. So the use-case for a fix is really kinda narrow. > > regards, tom lane Honestly, its not that big a problem, and if there were some doc's, faq's, etc (and people on the newsgroups) I dont think you should even worry about it. It makes sense to me, and if Tom had come back and said, yeah, here is why, cuz you run autovacuum and at then end of the script you did a vacuum... they are conflicting... dont do that. I'd be cool with that. As soon as its common knowledge I think it could be avoided. Really, isn't it just bulk loads anyway where a person might do this? -Andy