Re: REINDEX deadlock - Postgresql -9.1

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> 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
>