Re: REINDEX deadlock - Postgresql -9.1

On Thu, Feb 7, 2013 at 8:19 PM, Anoop K <anoopk6(at)gmail(dot)com> wrote:
> In an attempt to get access, I ended up killing a postgres process and the
> whole thing recovered from hang state. Now don't have more data points to
> debug.
>

Sorry, I was going to ask what REINDEX was really indexing ? System
tables ? 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. For example, I can
reproduce this by doing the following:

Session 1:
BEGIN;
REINDEX TABLE pg_class;
<stay idle in transaction>

Session 2:
REINDEX TABLE pg_attribute;
<will hang>

Try starting a new Session 3:
<will hung>

The stack traces of these processes will look similar to what you
posted. And as soon as you end the transaction on the first session,
everything will proceed.

You may want to look at your application code and see if you're
causing this kind of deadlock (or livelock, not sure what is a better
term to describe this situation)

Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee