REINDEX was for the whole database. It seems REINDEX was blocked by the <*idle in transaction*> process.
What we are not able to explain is how that connection went in to <*idle in transaction*> state. The app stacktrace confirms that app (JDBC) is trying to open a connection. We do close connection after use. So can't think how transaction went in to idle state. Thanks Anoop On Fri, Feb 8, 2013 at 12:14 AM, Tom Lane <[email protected]> wrote: > Pavan Deolasee <[email protected]> writes: > > Sorry, I was going to ask what REINDEX was really indexing ? System > > tables ? > > The stack trace for the REINDEX process includes ReindexDatabase(), so > if it was running as a superuser it would be trying to reindex system > catalogs too. We don't actually know that the particular table it's > working on at the moment is a system catalog, but that seems like a > fairly good guess. The process that's blocked in startup is definitely > blocked on somebody's exclusive lock (or at least exclusive lock > request) on a system catalog index, and there are not that many > operations besides REINDEX that would take out such a lock. > > I'm guessing that something holds a lock (maybe only AccessShareLock) > on a system catalog index, and REINDEX is blocked trying to get > exclusive lock on that index, and then all incoming processes are > queuing up behind REINDEX's request, since they'll all be trying > to open the same set of catcache-supporting indexes. > > > ISTM that the idle in transaction connection was holding some > > kind of a heavy weight lock on one of the catalog tables and that may > > be causing all other transactions to just wait. > > It doesn't need to have been an exclusive lock to block REINDEX. > I suspect this theory is correct otherwise, because if it were a > true deadlock the deadlock detector should have noticed it. If it's > just "everybody is blocked behind that idle transaction", the deadlock > detector will not think that it should do anything about it. > > regards, tom lane >
