I have the setup in problem state. But I am not able to make psql
connections to view the lock details.
psql connections are hanging. Is there any other info which can be
collected in this state ?
Also we don't know the steps to reproduce the issue.
On Thu, Feb 7, 2013 at 1:23 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
> Anoop K wrote:
> > We are hitting a situation where REINDEX is resulting in postgresql to
> go to dead lock state for ever.
> > On debugging the issue we found that
> > 3 connections are going in to some dead lock state.
> >
> > 1. idle in transaction
> > 2. REINDEX waiting
> > 3. SELECT waiting
> >
> > All these connections are made in the same minute. Once in deadlock
> state we are not able to make new
> > connections to db.(So not able to view pg_locks also). New connections
> appears as 'startup waiting' in
> > ps output. Initially we suspected <idle in transaction> is the result of
> not closing a connection. But
> > it seems it got stuck after creating a connection and is not able to
> proceed.
> >
> > Any clues ..
>
> Check the contents of pg_locks:
> What locks does the "idle in transaction" session hold?
> Who holds the locks that block SELECT, REINDEX and new connections?
>
> Turn on log_statement='all' to see what the "idle in transaction"
> session did since it started.
>
> Yours,
> Laurenz Albe
>