X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0CAF39DCA24 for ; Sat, 4 Feb 2006 15:12:20 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21445-09 for ; Sat, 4 Feb 2006 15:12:19 -0400 (AST) 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 B36339DCA06 for ; Sat, 4 Feb 2006 15:12:17 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k14JC77Y020657; Sat, 4 Feb 2006 14:12:07 -0500 (EST) To: Christopher Browne cc: pgsql-admin@postgresql.org Subject: Re: autovacuum In-reply-to: References: <6.2.1.2.2.20060204110331.03dc2eb0@81.215.12.210> Comments: In-reply-to Christopher Browne message dated "Sat, 04 Feb 2006 06:54:30 -0800" Date: Sat, 04 Feb 2006 14:12:07 -0500 Message-ID: <20656.1139080327@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.104 required=5 tests=[AWL=0.104] X-Spam-Score: 0.104 X-Spam-Level: X-Archive-Number: 200602/37 X-Sequence-Number: 20458 Christopher Browne writes: >> does autovacuum locks tables while vacuuming? > Of course it does; any request to access a relation will issue one or > more locks on the relation. This is correct in general ... > VACUUM issues an AccessShareLock request against each relation that is > vacuumed, which is probably nearly the same lock request your > applications will be requesting, save for the fact that they'll also > be submitting some RowExclusiveLock requests for individual rows of > relations. ... but wrong in detail. Actually VACUUM takes ShareUpdateExclusive lock, which is a bit stronger than an ordinary reader's AccessShare lock (it does not block ordinary reads or updates, but it does block index creation as well as other attempts to VACUUM the table). Also, RowExclusiveLock is a table-level lock. Data-modifying commands such as UPDATE take RowExclusiveLock on the whole table as a means of letting other sessions know that someone is changing the table. They also take row-level locks on the specific rows they change. This is all discussed here: http://www.postgresql.org/docs/8.1/static/explicit-locking.html regards, tom lane