On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6@xxxxxxxxx> 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. > > idle in transaction > REINDEX waiting > 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. The 'idle in transaction' means that someone started a transaction (BEGIN) and did not finished it (COMMIT/ROLLBACK) in the first connections. The 'startup waiting' message means that something got an exclusive lock on some system catalogs. You should not allow persistent or long running 'idle in transaction's that could affect tables that are actively used by other connections mostly if these tables are system ones. You need to find out what caused this 'idle in transaction', in the other words why the transaction was not finished, to solve the problem. > > Any clues .. > > Thanks > Anoop -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
