Re: REINDEX deadlock - Postgresql -9.1

We did run out of conns as our processes which tried to connect (over few
days) got hung in '*startup waiting state'. *Even superuser conns are also
over.

Thanks
Anoop

On Thu, Feb 7, 2013 at 3:37 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> It sounds like you're running out of connections. Have you tried
> connecting as postgres? It has 2 or 3 superuser connections reserved
> by default.
>
> On Thu, Feb 7, 2013 at 1:38 AM, Anoop K <anoopk6(at)gmail(dot)com> wrote:
> > 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(dot)albe(at)wien(dot)gv(dot)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
> >
> >
>
>
>
> --
> To understand recursion, one must first understand recursion.
>