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.
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