Re: REINDEX deadlock - Postgresql -9.1

Processes should always connect by some other role with suspendable
superuser connections for situations like this. Do your processes
really need superuser access all the time? If you could turn it off
for a bit you could get into your database and troubleshoot from there
first. Not being able to connect to your db because you ran out of
superuser connections is a bad thing.

On Thu, Feb 7, 2013 at 4:00 AM, Anoop K <anoopk6(at)gmail(dot)com> wrote:
> Actually some of our processes connect as superuser. So even that is over
> and is in hung state.
>
>
> On Thu, Feb 7, 2013 at 4:29 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>>
>> So have you tried connecting as a superuser?
>>
>> On Thu, Feb 7, 2013 at 3:19 AM, Anoop K <anoopk6(at)gmail(dot)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(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.
>> >
>> >
>>
>>
>>
>> --
>> To understand recursion, one must first understand recursion.
>
>

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