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 6F4009FB249 for ; Thu, 14 Sep 2006 17:35:45 -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 17720-06 for ; Thu, 14 Sep 2006 20:35:35 +0000 (UTC) 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 1B8CE9FA0FD for ; Thu, 14 Sep 2006 17:35:33 -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 k8EKZTeN003025; Thu, 14 Sep 2006 16:35:29 -0400 (EDT) To: "Joshua D. Drake" cc: andy , Alvaro Herrera , pgsql-hackers@postgresql.org Subject: Re: [ADMIN] Vacuum error on database postgres In-reply-to: <45097CF4.7040409@commandprompt.com> 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> <45097CF4.7040409@commandprompt.com> Comments: In-reply-to "Joshua D. Drake" message dated "Thu, 14 Sep 2006 09:01:56 -0700" Date: Thu, 14 Sep 2006 16:35:29 -0400 Message-ID: <3024.1158266129@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/1169 X-Sequence-Number: 90698 "Joshua D. Drake" writes: >> One possible fix today would be to make ANALYZE take >> ShareUpdateExclusive lock instead, thus ensuring there is only one >> ANALYZE at a time on a table. > Why not an internal lock that people don't see? We could add another LockTagType just for ANALYZE, but that seems like rather a lot of infrastructure to support an extremely narrow corner case, namely two people doing database-wide ANALYZE at the same time inside transaction blocks. (If they do it outside a transaction block then the ANALYZE is divided into multiple xacts and so doesn't try to hold locks on multiple tables concurrently. autovacuum won't try to do that either.) There's no such animal as "an internal lock people don't see" --- if we went this way it'd propagate into user-visible entries in pg_locks, for example. ISTM it should be sufficient to use ShareUpdateExclusiveLock. The only real argument I can see against it is you couldn't ANALYZE and VACUUM a table at the same time ... but that's probably a bad idea anyway, especially if we extend ANALYZE to estimate dead-tuple statistics. regards, tom lane