<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.marlowe@xxxxxxxxx> 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@xxxxxxxxx> 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.marlowe@xxxxxxxxx>
> wrote:
>>
>> So have you tried connecting as a superuser?
>>
>> On Thu, Feb 7, 2013 at 3:19 AM, Anoop K <anoopk6@xxxxxxxxx> 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@xxxxxxxxx>
>> > 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@xxxxxxxxx> 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@xxxxxxxxxx>
>> >> > 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.
