Re: REINDEX deadlock - Postgresql -9.1

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.
>=20
> 1.    idle in transaction
> 2.    REINDEX waiting
> 3.    SELECT waiting
>=20
> 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 ap=
pears 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 proc=
eed.
>=20
> 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=3D'all' to see what the "idle in transaction"
session did since it started.

Yours,
Laurenz Albe