Re: BUG #5599: Vacuum fails due to index corruption issues

On Wed, Aug 4, 2010 at 10:47 PM, Hitesh Bhambhani <hiteshb(at)asg(dot)com> wrote:
>

> PostgreSQL version: 8.2.9-1

Firstly, the current release of 8.2 is 8.2.17. There are a long list
of bugs fixed in those intervening releases including one involving
vacuum truncating relations. I don't think it's the same problem but I
would recommend updating immediately to 8.2.17.

> 2010-07-08 06:29:54,641  WARN DefaultQuartzScheduler_Worker-4
> maintenance.PostgreSqlVacuumer:32 - runVacuumFull(): running VACUUM FULL
> VERBOSE

Secondly we don't recommend running VACUUM FULL routinely. It should
only be necessary in extraordinary circumstances. Normally a plain
VACUUM (or VACUUM ANALYZE or VACUUM VERBOSE) should be sufficient as
long as it's being run regularly. Regular VACUUM without the "FULL"
has much less impact on the system.

> 2010-07-08 06:29:56,672 ERROR DefaultQuartzScheduler_Worker-4
> core.JobRunShell:211 - Job DEFAULT.postgreSqlVacuumJob threw an unhandled
> Exception:
> org.springframework.jdbc.BadSqlGrammarException: Hibernate-related JDBC
> operation;
> bad SQL grammar []; nested exception is org.postgresql.util.PSQLException:
> ERROR: could not truncate relation 1663/16403/41274 to 30 blocks: Permission
> denied

"Permission denied" smells like a Windows problem with concurrent file
operations. Are you sure you're not running any anti-virus software or
backup software which could have these files open and prevent Postgres
from performing regular file operations on its files? Many people have
reported other problems with anti-virus software in particular.

> Also, please let me know if there is a direct link between the truncate
> relation errors that I saw preceded the index corruption errors?

I'm a bit surprised at that, but I haven't checked what exactly
happens when an error occurs truncating files myself so I'm not sure
if I should be or not.

--
greg