On Thu, Jun 23, 2016 at 11:57 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 06/23/2016 08:14 AM, Sameer Kumar 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. > > https://www.postgresql.org/docs/9.4/static/explicit-locking.html > > ACCESS EXCLUSIVE > Thanks! I had checked that and arrived at the list above. Why I wanted to confirm because, I am facing a situation similar (or rather same) as what is described in two threads below- https://www.postgresql.org/message-id/7f74c5ea-6741-44fc-b6c6-e96f18d76...@simply.name https://www.postgresql.org/message-id/BE95C564-0D49-462A-A57C-4C9DF6238F71%40simply.name pg_stat_database_conflicts.confl_lock is *non-zero* and connections on standby (idle in transaction or executing SELECT) are disconnected. I *do not* see the message - "User query might have needed to see row versions that must be removed." But I see disconnection on standby because of a "relation lock" being held for long. >From what I understood that if there is a LOCK conflict on standby (between a session an a WAL replay), it might cause even cause disconnection of an "idle in transaction" session (which is causing conflict on standby). Is this right? My understanding is only Access Exclusive Locks will cause conflicts against a read-only query. Is that right? So I checked and confirmed that there is no such operation on master which would result in Access Exclusive lock. I am using v9.4.4. Is there a bug which is hitting me or is there any other kind of query which might cause lock conflict on standby? The threads above seem to have same issue, but I did not see any conclusive reason explained in the hacker or admin thread. > > > > > > > Regards > > Sameer > > -- > > -- > > 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 > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.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
