Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 423C32E301A for ; Thu, 3 Jan 2008 19:12:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 32358-05 for ; Thu, 3 Jan 2008 19:12:39 -0400 (AST) Received: from web34711.mail.mud.yahoo.com (web34711.mail.mud.yahoo.com [209.191.68.160]) by postgresql.org (Postfix) with SMTP id 80A912E2EFF for ; Thu, 3 Jan 2008 19:12:39 -0400 (AST) Received: (qmail 30919 invoked by uid 60001); 3 Jan 2008 23:12:37 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=itDj1r8sTCbIA0BFVt77Fwz1JYC4fBMMphGC9m6VAG2n+xohcZM/PDxipCWL79p3CiA+HvGpUlf0r0Y75xAKwH5UM0I+WvjplGEv5xnThZnGZVqwChWKY4GBE0fXWrt82gLB0kGjmKy6UNooiZUdu68yI0ddFhZYcTefBzRVRmw=; X-YMail-OSG: NCeYLnkVM1kYXHThpfT3iwEuJXUzaUTyLSdtNbQmQ02lEuC00NWULQ6_lrjn1.k2L9kqmCOsfA-- Received: from [63.251.67.35] by web34711.mail.mud.yahoo.com via HTTP; Thu, 03 Jan 2008 15:12:37 PST Date: Thu, 3 Jan 2008 15:12:37 -0800 (PST) From: Mike White Subject: Postgres 7.4 VACUUM FULL multiple AccessExclusiveLocks? To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <715193.26887.qm@web34711.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/61 X-Sequence-Number: 27835 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. 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. 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. :( =# select * from pg_locks where mode = 'AccessExclusiveLock'; relation | database | transaction | pid | mode | granted ------------+------------+-------------+-------+---------------------+--------- 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | t 2434930929 | 2434930890 | | 18356 | AccessExclusiveLock | t (2 rows) miro_stats=# VACUUM FULL VERBOSE tsdb_archive_state; INFO: vacuuming "public.tsdb_archive_state" INFO: "tsdb_archive_state": found 0 removable, 59138 nonremovable row versions in 4597419 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 60 to 60 bytes long. There were 583813020 unused item pointers. Total free space (including removable row versions) is 35231071156 bytes. 4596598 pages are or will become empty, including 0 at the end of the table. 4597196 pages containing 35231061344 free bytes are potential move destinations. CPU 99.57s/20.04u sec elapsed 3948.67 sec. INFO: index "tsdb_archive_state_pkey" now contains 59138 row versions in 132 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.02u sec elapsed 0.02 sec. (Hanging here for about 4 hours.) Thanks, Mike White