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.marl...@gmail.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 <anoo...@gmail.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.marl...@gmail.com> > > wrote: > >> > >> So have you tried connecting as a superuser? > >> > >> On Thu, Feb 7, 2013 at 3:19 AM, Anoop K <anoo...@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.marl...@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 <anoo...@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.a...@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. > > > > > > > > -- > To understand recursion, one must first understand recursion. >
