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

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