Just wanted to give a heads up to anyone who might be having a similar problem. We had an installation on a customer machine that had the AV product "ESET NOD32" installed. We quickly started having problems when there were two or more concurrent queries against the same tables in PostgreSQL v8.2.21 (over multiple connections via JDBC). Specifically we were getting errors like:
ERROR: could not open relation 1663/27267/27472: Permission denied
We checked the file permissions (which checked out), we used sysinternals tools to monitor the file system to make sure the file permsisions werent being changed by the backup software running on the system (they weren't), and finally uninstalled the "ESET NOD32" AV software. We have been running without issue now that the AV software has been uninstalled. The filters they use are apparently not very happy with the multi-process, highly-concurrent nature of PostgeSQL.
Thought this might be of help to anyone else out there who comes across this AV software during deployment and starts encountering strange behavior.
Resident AV monitors register special hook somewhere near the windows kernel, which intercepts all read/write requests and pipes the data through AV scanner, delaying I/O until they think it's clean. During that delay, you may get such error. I've seen it several times (with and without postgres). It happens only with concurrent access (the process that initiated the I/O does not get error - only delay)
For most office/home applications it's OK - the delay is small enough and most I/O is via single process/thread. But not for DBMS.
So, when running PostgreSQL on a Windows box together with resident on-access AV software, it's rather a must to take following actions (any combination of following):
* uninstall the AV software completely * disable the realtime monitor completely (leave on-demand scanning, it does not hurt) * use an exclusion mechanism (most AV have this) to ignore I/O actions on postgres data directory * use an exclusion mechanism (most AV have this) to ignore I/O actions taken postgres binaries