On Fri, 24 Jun 2016, 1:54 a.m. 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. > Thanks! I could reproduce this. The test setup- 1. I have master and standby databases. To get the error I reduced my max_streaming_delay to 10s 2. On standby start a new transaction and read data from a very huge table Begin transaction; Select count(*) from table_with10k_rows; 3. On master delete rows from the bottom of this table (i.e. the rows inserted last) 4. Run a vacuum on the table in master (normal vacuum). 5. Go back to the transaction on standby, fire Select 1; 6. You will see session is disconnected I repeated this a few times and if I don't run vacuum manually (and wait for a while) autovacuum would fire and results in similar situation. I repeated the same steps with REPEATABLE READ isolation level on standby transaction and I got SQLSTATE 40001 but with detail "User Query might have needed to see riw versions that must be removed". I have hot_standby_feedback on. Thanks! > 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 > -- -- 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
