Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

On 07/06/10 10:29, John T. Dow wrote:
> One of my clients is getting this problem occasionally. Actually, we
> can cause it to happen quite reliably by pasting certain text into a
> couple of fields, but the vast majority of text entered into the vast
> majority of fields causes no problem.

> I've read enough to suggest that AV software might be the culprit.
> It has been said that it is not sufficient to exclude the database
> directory nor even to disable to AV protection, it has to be removed.

Depends on the AV software. That advice is general, and is given because
_some_ antivirus software is badly written and fails to properly exclude Pg.

Some AV software probably behaves fine.

> The problem is, their database server is also a file server. As a file server it must have AV protection. The server
isrunning Windows Server 2003 I believe. It has RAID etc.  My client's antivirus software is AVG (paid, not free). 

I would not trust AVG. I've been most unimpressed with it lately - their
resident scanning module is immature, incredibly slow, and seems prone
to false positives.

> Question: Is AV software still regarded as the likely culprit?

Likely enough - especially for intermittent issues - that the best thing
to do is uninstall it, reboot, and re-test to see if the issue remains.

If you can reproduce it without the AV software then it's worth
investigating further.

> Question: If so, is any particular brand less likely to cause problems, more likely?

While I can't speak for Pg here (I don't use Pg on any machine with
antivirus, and in fact don't use it in production on Windows at all) my
limited experience with AV on Windows has been "the bigger the better".
The antivirus modules from Symantec and McAfee seem to be the least
evil. Not good, just less evil.

In my experience supporting end users, at least, it's VERY, VERY
important never to install any "Internet Security" suite if you want the
computer to actually work, though. AV vendor firewall packages etc are
frickin' poison. Just the basic AV package seems to be OK, though again
I can't speak re Pg because I don't use Pg with AV.

I'm almost tempted to try tracking down some of these AV-related issues.
Unfortunately, though, AV software is not only lacking source code or
even .pdb debug symbol files, but tends to be designed to make it hard
to debug as part of its tamper resistance. So it's a nightmare to trace.
Add the fact that many of the issues reported seem to be races or bugs
in the AV file access hooks, and it quickly ceases being worth the pain.

It'd be interesting if someone with a paid contract for AV support would
go to their AV vendor and get them involved. With the active
co-operation of an AV vendor or two and a reproducible fault, some
progress might be possible.

> I'd had to tell my client to purchase more hardware because the
> database software I've recommended has a problem. I have a number of
> other clients using Postgres and nobody else has had any problem.
> Switching AV software wouldn't be such an issue.

First, I'd uninstall it, reboot, re-test to see if you can reproduce the
fault. If you still can, it's probably not AVG.

If you find the fault goes away when AVG does, try something else like
McAfee or Symantec's AV component and see how you go.

--
Craig Ringer