Re: REINDEX deadlock - Postgresql -9.1

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