On Fri, 24 Jun 2016, 6:23 a.m. Jeff Janes, <jeff.ja...@gmail.com> wrote:
> On Thu, Jun 23, 2016 at 10:54 AM, Sameer Kumar <sameer.ku...@ashnik.com> > wrote: > > > > > > 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. > > Not that I know of. I don't think any part of the user documentation > attempts to make an exhaustive list of all actions which take which > level of locks. It only provides some illustrative examples. > Thanks! But is it something which is worth mentioning on the page about VACUUM? > 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