Thread: VACUUM and locking

VACUUM and locking

From

"Denise Bossarte"

Date:

28 October 2002, 10:53:53

I am trying to automate VACUUMing several databases and am confused on how
client connections to the database might affect the VACUUM.

I have looked at the documentation on locking and VACUUM and have found
conflicting reports.

The 7.2.1 Documentation
http://www.postgresql.org/idocs/index.php?locking-tables.html states that
VACUUM (without FULL) acquires a ShareUpdateExclusiveLock and VACUUM with
FULL acquires an AcessExclusiveLock.

However, the 7.2.1 Appendix A. Release Notes state "Vacuuming no longer
locks tables, thus allowing normal user access during the vacuum. A new
VACUUM FULL command does old-style vacuum by locking the table and shrinking
the on-disk copy of the table."  Additionally, the "Transaction Processing
in Postgres" pdf http://developer.postgresql.org/pdf/transactions.pdf only
shows AcessExclusiveLock acquird by VACUUM (full? - not stated) (p. 18).

So which, if any, locks does VACUUM acquire?  How do client connections to
the database affect the VACUUM? What is the best way to automate VACUUM?

Thanks for your help,

Denise

Re: VACUUM and locking

From

Tom Lane

Date:

28 October 2002, 16:39:52

"Denise Bossarte" <mypostgreSQL@hotmail.com> writes:
> I have looked at the documentation on locking and VACUUM and have found
> conflicting reports.

> http://www.postgresql.org/idocs/index.php?locking-tables.html states that
> VACUUM (without FULL) acquires a ShareUpdateExclusiveLock and VACUUM with
> FULL acquires an AcessExclusiveLock.

This is correct.

> However, the 7.2.1 Appendix A. Release Notes state "Vacuuming no longer
> locks tables, thus allowing normal user access during the vacuum. A new
> VACUUM FULL command does old-style vacuum by locking the table and shrinking
> the on-disk copy of the table.

This is an oversimplification, as it says "lock" where it means
AccessExclusiveLock.

> Additionally, the "Transaction Processing
> in Postgres" pdf http://developer.postgresql.org/pdf/transactions.pdf only
> shows AcessExclusiveLock acquird by VACUUM (full? - not stated) (p. 18).

This document is pre-7.2.
        regards, tom lane