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 <tgl@xxxxxxxxxxxxx> wrote:
Pavan Deolasee <pavan.deolasee@xxxxxxxxx> writes:The stack trace for the REINDEX process includes ReindexDatabase(), so
> Sorry, I was going to ask what REINDEX was really indexing ? System
> tables ?
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.
It doesn't need to have been an exclusive lock to block REINDEX.
> 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.
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
