On Mon, 2006-08-21 at 11:50 -0700, Eamonn Kent wrote:
> So, my best guess is that something in our application is preventing
> vacuum from removing dead rows. What could cause this? Would it be
> caused by a long-living transaction? What is the best way to track
> the problem down...right now, I am looking through pg_stat_activity
> and pg_locks to find processes that are “in transaction” and what
> locks they are holding.
If you have any long running transactions - idle or active, that's your
problem. Vacuum can only clear out dead tuples older than that oldest
transaction. Deal with those. Make sure every single transaction your
app initiates commits or rolls back every single time.
You'll generally find them in pg_stat_activity, but not always. ps may
show you idle transactions not showing as idle in pg_stat_activity
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.