Re: REINDEX deadlock - Postgresql -9.1

From: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
To: Anoop K <anoopk6(at)gmail(dot)com>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org, Venkatraju TV <venkatraju(at)gmail(dot)com>
Subject: Re: REINDEX deadlock - Postgresql -9.1
Date: 2013-02-07 18:20:50
Message-ID: CABOikdPtcS_M=X8KtYaTW66ZZjr_OkgAsSoJd1W2jNix-hY0vg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Feb 7, 2013 at 8:19 PM, Anoop K <anoopk6(at)gmail(dot)com> wrote:
> In an attempt to get access, I ended up killing a postgres process and the
> whole thing recovered from hang state. Now don't have more data points to
> debug.
>

Sorry, I was going to ask what REINDEX was really indexing ? System
tables ? ISTM that the idle in transaction connection was holding some
kind of a heavy weight lock on one of the catalog tables and that may
be causing all other transactions to just wait. For example, I can
reproduce this by doing the following:

Session 1:
BEGIN;
REINDEX TABLE pg_class;
<stay idle in transaction>

Session 2:
REINDEX TABLE pg_attribute;
<will hang>

Try starting a new Session 3:
<will hung>

The stack traces of these processes will look similar to what you
posted. And as soon as you end the transaction on the first session,
everything will proceed.

You may want to look at your application code and see if you're
causing this kind of deadlock (or livelock, not sure what is a better
term to describe this situation)

Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-02-07 18:44:02 Re: REINDEX deadlock - Postgresql -9.1
Previous Message Milos Gajdos 2013-02-07 18:18:18 Postgres 9.1 statistics in pg_stat_database