Re: REINDEX deadlock - Postgresql -9.1

You might want to consider adding a pooler like pgbouncer to the
equation so that the pooler is what runs out of connections and not
the database. Then you could at least get into it to fix things.

On Thu, Feb 7, 2013 at 9:04 PM, Anoop K <anoopk6(at)gmail(dot)com> wrote:
> 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
>
>

--
To understand recursion, one must first understand recursion.