Re: REINDEX deadlock - Postgresql -9.1

So have you tried connecting as a superuser?

On Thu, Feb 7, 2013 at 3:19 AM, Anoop K <anoopk6@gmail.com> wrote:
> 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.marlowe@gmail.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@gmail.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.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
>> >
>> >
>>
>>
>>
>> --
>> To understand recursion, one must first understand recursion.
>
>



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