On Fri, 24 Jun 2016, 1:47 a.m. Jeff Janes, <jeff.ja...@gmail.com> wrote:
> On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar <sameer.ku...@ashnik.com> > 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. > Thanks! This is helpful. I believe going by this explaination I can try to reproduce this issue manually. Is this part about regular vacuum acquiring an AccessExclusive Lock documented? I did not see a reference to it on page for Explicit Locking. > 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 > -- -- Best Regards Sameer Kumar | DB Solution Architect *ASHNIK PTE. LTD.* 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533 T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
