Hello
We am running a web application on ubuntu 10.10 using postgres 8.4.3.
We are experiencing regular problems (each morning as the users come in) which seem to be caused by deadlocks in the postgres database. I am seeing messages like:
2012-11-30 10:24:36 GMT LOG: sending cancel to blocking autovacuum PID 16951 at character 62
2012-11-30 10:24:36 GMT DETAIL: Process 3368 waits for AccessShareLock on relation 36183 of database 33864.
2012-11-30 10:24:36 GMT STATEMENT: SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue WHERE indicatorid I
N (41471, 46324, 41481, 41487) AND periodid IN (46422, 46423, 46424) AND organisationunitid IN (67)
Almost all of the postgres processes seem to be stuck in the "PARSE WAITING" state and the application ceases to respond as it becomes starved of database connections. The only way to get things moving again seems to be to restart postgres.
Trying to interpret this, does this mean that the autovacuum process is holding a lock which is required tn order to complete the select query? Is it possible that the autovacuum process is ignoring that 'cancel' request so everything stays blocked?
Sorry if these seem like basic questions. I am not too sure where to look to start resolving this. Any suggestions would be appreciated.
Bob