From: Kevin Grittner <kgrittn@ymail.com>
To: John Scalia <jayknowsunix@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: vacuum and table locking
Date: Fri, 3 Apr 2015 17:59:05 +0000 (UTC)
Message-ID: <1905275951.4626814.1428083945794.JavaMail.yahoo@mail.yahoo.com> (raw)
In-Reply-To: <551EB8D4.7050406@gmail.com>
References: <551EB8D4.7050406@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, jayknowsunix@gmail.com
Subject: Re: vacuum and table locking
In-Reply-To: <1905275951.4626814.1428083945794.JavaMail.yahoo@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox