Re: What Causes Access Exclusive Lock?

On Thu, Jun 23, 2016 at 10:54 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:
>
>
> On Fri, 24 Jun 2016, 1:47 a.m. Jeff Janes, <jeff.janes@gmail.com> wrote:
>>
>> On Thu, Jun 23, 2016 at 8:14 AM, Sameer Kumar <sameer.kumar@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.

Cheers,

Jeff