Skip site navigation (1) Skip section navigation (2)

Re: pg_autovacuum and REINDEX at the same time (?)

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Janar Kartau <janar(dot)kartau(at)cvkeskus(dot)ee>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: pg_autovacuum and REINDEX at the same time (?)
Date: 2007-02-28 11:15:36
Message-ID: 45E56458.2040908@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Janar Kartau wrote:
> Hi.
> I've been running autovacuum over a month now without any problems, but 
> today one of the critical tables got locked and made a pretty big mess. :)
> We have a cron script that does REINDEX on this table every 5 minutes. 
> So i wonder if running REINDEX and VACUUM on the same table at the same 
> time may cause this deadlock?
> Or can a VACUUM make so much trouble?

ISTM that you have two separate issues.

The "sorry, too many clients already" error means that you've reached 
the maximum number of connections, as set with the max_connections 
setting. The autovacuum processes needs one connection to run.

I suspect that the deadlock is not related to the autovacuum, but just 
an interaction between your transactions and the REINDEX.

What does the update_itemcount() function look like? Are you running the 
REINDEX in a transaction?

Why do you need to reindex every 5 minutes? How long does the vacuum run 
normally?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-bugs by date

Next:From: Janar KartauDate: 2007-02-28 11:38:50
Subject: Re: pg_autovacuum and REINDEX at the same time (?)
Previous:From: Janar KartauDate: 2007-02-28 10:12:12
Subject: pg_autovacuum and REINDEX at the same time (?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group