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.
