John Scalia <jayknowsunix@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
