Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id D26A72E2E7B for ; Fri, 4 Jan 2008 12:56:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40769-03 for ; Fri, 4 Jan 2008 12:56:46 -0400 (AST) Received: from ro-out-1112.google.com (ro-out-1112.google.com [72.14.202.181]) by postgresql.org (Postfix) with ESMTP id BF5412E31FE for ; Fri, 4 Jan 2008 12:56:45 -0400 (AST) Received: by ro-out-1112.google.com with SMTP id p4so1471622roc.5 for ; Fri, 04 Jan 2008 08:56:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=cZ/7hXbTV+HuioPHTWGWITYybAtion+NpLGh7jTsano=; b=i65AuVPa+PQZByNMOqi7rweGlP3rjn8tzz/MM88erZmd+BYNQzjkdCZm7GkHQiTRzVboTy2BvHqsKG/sDayjc2W2rc1jINQ2M37vkgoFAesTarbsPP6Aerp32f+ZMztqDTGLrYVzPKxBbEcX82YPGItITy9k4M1YhIHcSo4HRTs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=SJZMqsZveuWs+UdJAZacfR1egeU1aInD+zoHMEtnCbvZb4pJ0DpmyFIqjVwdAd2Fr85omkEtrsNVZyVEdviGbLlCgkTGyiZKAAQ1Rm895lGSeW9V1fq19nmqqF/hZ9/hdMiN/Sr8CpIZ/CfNEb94urp1ES7y730u/7gGDM/oqIc= Received: by 10.114.157.1 with SMTP id f1mr2291873wae.13.1199465803730; Fri, 04 Jan 2008 08:56:43 -0800 (PST) Received: by 10.114.131.4 with HTTP; Fri, 4 Jan 2008 08:56:43 -0800 (PST) Message-ID: Date: Fri, 4 Jan 2008 10:56:43 -0600 From: "Scott Marlowe" To: "Mike White" Subject: Re: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks? Cc: pgsql-admin@postgresql.org In-Reply-To: <715193.26887.qm@web34711.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <715193.26887.qm@web34711.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/64 X-Sequence-Number: 27838 On Jan 3, 2008 5:12 PM, Mike White wrote: > I'm performing a VACUUM FULL on a 7.4.7 db table, one that is not huge > at any given time, but which has a lot of churn. Just the kind of > usage pattern that 7.4 loves. :) This is something that we have to do > every 6 months or so, and it always takes a long time. I wonder why you have to do it every 6 months... Is this one of those things where slowly the table bloats because fsm is too small or vacuum is too infrequent? Or is this a case of some kind of fragmentation? I've got plenty of heavily updated tables that USED to live in a 7.4 and I never needed to run vacuum full on it, because autovacuum (with slightly more aggressive settings than default) was plenty on that db to keep the table clean. So I'm wondering if there's some preventative maintenance that would stop you needing to do this. > Today, however, > I've noticed that the pg_lock table shows two AccessExclusiveLock > entries that are completely identical. Is this a sign of internal > deadlock, or is this behavior expected during VACUUM FULL operation? > Also, please don't tell me to upgrade. OK. I will tell you to update. 7.4.7 had many data eating bugs that were fixed by 7.4.18. > We're happily running 8.2 on > some production machines, but I am tied to 7.4 on this particular box > for reasons beyond my control. :( Been there, done that, got the t-shirt :-( > > =# select * from pg_locks where mode = 'AccessExclusiveLock'; > relation | database | transaction | pid | mode | > granted > ------------+------------+-------------+-------+---------------------+--------- > 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | > t > 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | > t I'm guessing that the richer view that exists in 8.2 might show differences, but the simpler view in 7.4 doesn't. You might try adapting parts of the 8.2 view into 7.4 and seeing if that helps.