Date: Fri, 3 Apr 2015 17:59:05 +0000 (UTC)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
To: John Scalia <jayknowsunix@gmail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Message-ID: <1905275951.4626814.1428083945794.JavaMail.yahoo@mail.yahoo.com>
In-Reply-To: <551EB8D4.7050406@gmail.com>
References: <551EB8D4.7050406@gmail.com>
Subject: Re: vacuum and table locking
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Length: 1696
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

John Scalia <jayknowsunix@gmail.com> wrote:

> VACUUM (VACUUM ANALYZE) - shouldn't need to lock the table(?).
> I'm looking at the Douglas' book "PostgreSQL" on page 804 where
> it claims the operation does not require exclusive access.

Without the FULL keyword, it only requires a SHARE UPDATE EXCLUSIVE
lock, which does not conflict with ordinary DML.

http://www.postgresql.org/docs/9.2/interactive/explicit-locking.html#LOCKING-TABLES

> VACUUM FULL (VACUUM FULL ANALYZE) - will need to lock the table,

Yes, in this case with an ACCESS EXCLUSIVE lock, which conflicts
with all other locks.

> but I'm thinking this may only take a couple of milliseconds as I
> think this builds a temporary table from the one
> being vacuumed and then when complete, drops the old table and
> renames the temporary. So the lock should only be during this
> move, or does it lock the table for the entirety?

If it only locked while after the data was scanned, how could it
deal with DML affecting portions it had already scanned?  If that
were the only problem it could take a SHARE lock, which would allow
concurrent reads, but then we would have the issue of escalating
the lock to swap in the new heap and indexes -- and lock escalation
carries a risk of deadlock.

> And how does it then resolve say another deletion in the table
> that occurs while the VACUUM is in operation?

A VACUUM FULL could not cope with that, which is why it needs the
ACCESS EXCLUSIVE lock.  A non-FULL VACUUM is OK because of
visibility checking and/or (depending on relation type) a special 
form of page locking used just for cleanups.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin