Re: REINDEX deadlock - Postgresql -9.1

On Wed, Feb 6, 2013 at 11:55 PM, 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.

This may or may not be a deadlock. Unless you've got a circle it's
not a deadlock, it's just a "cascading lock overloading your
connection limit" failure. You can get these with slony and vacuums
and ddl. Say I want to run a DDL script. Someone is running vacuum
(could be autovac process). I run slony execute to run ddl and it
waits with hard table locks, and all the updates stall behind that.
Your db then runs out of connections. What we need to know is what
that idle in transaction is just sitting there waiting to do, which is
usually a combination of db state and application state.

As a short term fix you can set some reasonable statement level
timeout on the reindex's connection, user or database. If no reindex
ever takes more than a minute and you give it 5 minutes and check the
logs for it you can see how often it fails (once every month or once
every minute you check for a while etc then you could set that user's
connect. If that user is the superuser things become problematic.