Re: REINDEX deadlock - Postgresql -9.1

I will try. Here are the gdb stacktraces of hung processes.

<idle in transaction>

#0 0x00007fbdfaceb3e2 in recv () from /lib64/libc.so.6
#1 0x000000000058bde6 in secure_read ()
#2 0x000000000059697b in ?? ()
#3 0x0000000000596d7b in pq_getbyte ()
#4 0x00000000006334af in PostgresMain ()
#5 0x00000000005f4d69 in ?? ()
#6 0x00000000005f7501 in PostmasterMain ()
#7 0x0000000000598df0 in main ()

REINDEX:
-------
#0 0x00007fbdfacecca7 in semop () from /lib64/libc.so.6
#1 0x00000000005e8927 in PGSemaphoreLock ()
#2 0x0000000000624821 in ProcSleep ()
#3 0x000000000062145c in ?? ()
#4 0x0000000000622c6a in LockAcquireExtended ()
#5 0x0000000000620518 in LockRelationOid ()
#6 0x000000000046efe5 in relation_open ()
#7 0x0000000000476bc3 in index_open ()
#8 0x00000000004b8b14 in reindex_index ()
#9 0x00000000004b8f42 in reindex_relation ()
#10 0x000000000052d223 in ReindexDatabase ()
#11 0x00000000006347f7 in ?? ()
#12 0x000000000063580d in ?? ()
#13 0x0000000000635f92 in PortalRun ()
#14 0x00000000006325db in ?? ()
#15 0x0000000000633553 in PostgresMain ()
#16 0x00000000005f4d69 in ?? ()
#17 0x00000000005f7501 in PostmasterMain ()
#18 0x0000000000598df0 in main ()

SELECT BLOCKING
---------------

#0 0x00007fbdfacecca7 in semop () from /lib64/libc.so.6
#1 0x00000000005e8927 in PGSemaphoreLock ()
#2 0x0000000000624821 in ProcSleep ()
#3 0x000000000062145c in ?? ()
#4 0x0000000000622c6a in LockAcquireExtended ()
#5 0x0000000000620518 in LockRelationOid ()
#6 0x000000000046efe5 in relation_open ()
#7 0x0000000000476bc3 in index_open ()
#8 0x00000000005e47d1 in get_relation_info ()
#9 0x00000000005e67e9 in build_simple_rel ()
#10 0x00000000005cf6e2 in add_base_rels_to_query ()
#11 0x00000000005cf6e2 in add_base_rels_to_query ()
#12 0x00000000005cf733 in add_base_rels_to_query ()
#13 0x00000000005d0286 in query_planner ()
#14 0x00000000005d1dab in ?? ()
#15 0x00000000005d3866 in subquery_planner ()
#16 0x00000000005d3b20 in standard_planner ()
#17 0x00000000006315fa in pg_plan_query ()
#18 0x00000000006316e4 in pg_plan_queries ()
#19 0x00000000006326f2 in ?? ()
#20 0x0000000000633553 in PostgresMain ()
#21 0x00000000005f4d69 in ?? ()
#22 0x00000000005f7501 in PostmasterMain ()
#23 0x0000000000598df0 in main ()

NEW CONN
--------

(gdb) bt
#0 0x00007fbdfacecca7 in semop () from /lib64/libc.so.6
#1 0x00000000005e8927 in PGSemaphoreLock ()
#2 0x0000000000624821 in ProcSleep ()
#3 0x000000000062145c in ?? ()
#4 0x0000000000622c6a in LockAcquireExtended ()
#5 0x0000000000620518 in LockRelationOid ()
#6 0x000000000046efe5 in relation_open ()
#7 0x0000000000476bc3 in index_open ()
#8 0x00000000006dd185 in InitCatCachePhase2 ()
#9 0x00000000006e74ed in InitCatalogCachePhase2 ()
#10 0x00000000006e549a in RelationCacheInitializePhase3 ()
#11 0x00000000006fba51 in InitPostgres ()
#12 0x0000000000633153 in PostgresMain ()
#13 0x00000000005f4d69 in ?? ()
#14 0x00000000005f7501 in PostmasterMain ()
#15 0x0000000000598df0 in main ()

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

> 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.
>