On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar <sameer.kumar@xxxxxxxxxx> wrote: > > Hi, > > I just wanted to understand what are the commands which will acquire Access > Exclusive Lock on a table? In my knowledge below operations will acquire > access exclusive lock:- > > 1. VACUUM FULL > 2. ALTER TABLE > 3. DROP TABLE > 4. TRUNCATE > 5. REINDEX > 6. LOCK command with Access Exclusive Mode (or no mode specified) > > I am using PostgreSQL v9.4. A regular VACUUM (not a FULL one), including autovac, will take an ACCESS EXCLUSIVE lock if it believes there are enough empty (truncatable) pages at the end of the table to be worth truncating and returning that storage to the OS. On master it will quickly abandon the lock if it detects someone else wants it, but that does not work on a standby. Before version 9.6, if there are bunch of all-visible (but non-empty) pages at the end of the table, then every vacuum will think it can possibly truncate those pages, take the lock, and immediately realize it can't truncate anything and release the lock. On master, this is harmless, but on a standby it can lead to spurious cancellations. In 9.6, we made it check those pages to see if they actually are truncatable before it takes the lock, then check again after it has the lock to make sure they are still truncatable. That should greatly decrease the occurrence of such cancellations. Cheers, Jeff -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
