Re: REINDEX deadlock - Postgresql -9.1

On Wed, Feb 6, 2013 at 1:28 AM, Anoop K <anoopk6(at)gmail(dot)com> 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(dot)ru(at)gmail(dot)com