Hi Bambi,Thank you the prompt reply.This table is very volatile, lot of inserts/updates happen on this tables(atleast 20~30 inserts/min).
That number of inserts per minute is not all that many. I suspect that you have sessions which are holding open transactions (and thus locks) for much longer than necessary, and it is this idling on the locks, not the active insertions, that is causing the current problem.
If this is true, you should try to find the idle-in-transaction connections and fix them, because even if they didn't cause this particular problem, they will cause other ones.
When auto vacuum tries to run on this table, I get this warning.
>> LOG: automatic vacuum of table "DB1.nic.pvxt": could not (re)acquire exclusive lock for truncate scan
You have at least 8 MB of empty space at the end of the table, but to remove it it needs to acquire a lock that it cannot get and so it gives up. Unfortunately it now gives up on the following autoanalyze as well. In 9.2.2 and before, it would also give up on reclaiming the free space, but would likely still do the autoanalyze, which is probably why you didn't see it before.
Is there a way, I force it to happen, because the table/indexes statistics are becoming stale very quickly.
This is something introduced in 9.2.3, and will probably be fixed whenever 9.2.5 comes out.
In the mean time, a manual ANALYZE (But not a VACUUM ANALYZE, because would fail the same was autvac does) would fix the stats, but it would have to be repeated often as they would just get stale again.
You could try a VACUUM FULL or CLUSTER if you can tolerate the lock it would hold on the table while it operates. The reason that might solve the problem for you is that it would clear out the empty space, and therefore future autovac won't see that empty space and try to truncate it. Depending on how your table is used, either more empty space could accumulate at the end of the table causing the problem to recur, or maybe it would fix the problem for good.
Cheers,
Jeff
